У меня проблема с запуском PostgreSQL 8.4 на сервере Ubuntu 9.10 после отключения электроэнергии. Когда я пытаюсь подключиться к базе данных, он говорит:
psql: server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request.
Когда я пытаюсь запустить его с помощью команды
sudo -u postgres /etc/init.d/postgresql-8.4 start
* Starting PostgreSQL 8.4 database server [ OK ]
Вывод netstat
netstat -tulp
(No info could be read for "-p": geteuid()=1000 but you should be root.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 localhost:postgresql *:* LISTEN -
tcp 0 0 192.168.1.35:svn *:* LISTEN -
tcp 0 0 192.168.1.35:http-alt *:* LISTEN -
tcp 0 0 *:ssh *:* LISTEN -
tcp6 0 0 localhost:postgresql [::]:* LISTEN -
tcp6 0 0 [::]:ssh [::]:* LISTEN -
udp 0 0 *:bootpc *:* -
Но все еще не работает, поэтому давайте перезапустим его
sudo -u postgres /etc/init.d/postgresql-8.4 restart
* Restarting PostgreSQL 8.4 database server * The PostgreSQL server failed to start. Please check the log output:
2009-11-30 13:39:37 CET LOG: database system was shut down at 2009-11-30 13:39:33 CET
2009-11-30 13:39:37 CET LOG: autovacuum launcher started
2009-11-30 13:39:37 CET LOG: database system is ready to accept connections
2009-11-30 13:39:37 CET LOG: incomplete startup packet
2009-11-30 13:39:38 CET LOG: server process (PID 2240) was terminated by signal 11: Segmentation fault
2009-11-30 13:39:38 CET LOG: terminating any other active server processes
2009-11-30 13:39:38 CET LOG: all server processes terminated; reinitializing
2009-11-30 13:39:38 CET LOG: database system was interrupted; last known up at 2009-11-30 13:39:37 CET
2009-11-30 13:39:38 CET LOG: database system was not properly shut down; automatic recovery in progress
2009-11-30 13:39:38 CET LOG: record with zero length at 0/11D464C
2009-11-30 13:39:38 CET LOG: redo is not required
2009-11-30 13:39:38 CET LOG: autovacuum launcher started
2009-11-30 13:39:38 CET LOG: database system is ready to accept connections
2009-11-30 13:39:38 CET LOG: server process (PID 2248) was terminated by signal 11: Segmentation fault
2009-11-30 13:39:38 CET LOG: terminating any other active server processes
2009-11-30 13:39:38 CET LOG: all server processes terminated; reinitializing
2009-11-30 13:39:38 CET LOG: database system was interrupted; last known up at 2009-11-30 13:39:38 CET
2009-11-30 13:39:38 CET LOG: database system was not properly shut down; automatic recovery in progress
2009-11-30 13:39:38 CET LOG: record with zero length at 0/11D4690
2009-11-30 13:39:38 CET LOG: redo is not required
2009-11-30 13:39:39 CET LOG: autovacuum launcher started
2009-11-30 13:39:39 CET LOG: database system is ready to accept connections
2009-11-30 13:39:39 CET LOG: server process (PID 2256) was terminated by signal 11: Segmentation fault
2009-11-30 13:39:39 CET LOG: terminating any other active server processes
2009-11-30 13:39:39 CET LOG: all server processes terminated; reinitializing
2009-11-30 13:39:39 CET LOG: database system was interrupted; last known up at 2009-11-30 13:39:38 CET
2009-11-30 13:39:39 CET LOG: database system was not properly shut down; automatic recovery in progress
2009-11-30 13:39:39 CET LOG: record with zero length at 0/11D46D4
2009-11-30 13:39:39 CET LOG: redo is not required
2009-11-30 13:39:39 CET LOG: autovacuum launcher started
2009-11-30 13:39:39 CET LOG: database system is ready to accept connections
2009-11-30 13:39:39 CET LOG: server process (PID 2264) was terminated by signal 11: Segmentation fault
2009-11-30 13:39:39 CET LOG: terminating any other active server processes
2009-11-30 13:39:39 CET LOG: all server processes terminated; reinitializing
2009-11-30 13:39:39 CET LOG: database system was interrupted; last known up at 2009-11-30 13:39:39 CET
2009-11-30 13:39:39 CET LOG: database system was not properly shut down; automatic recovery in progress
2009-11-30 13:39:40 CET LOG: record with zero length at 0/11D4718
2009-11-30 13:39:40 CET LOG: redo is not required
2009-11-30 13:39:40 CET LOG: autovacuum launcher started
2009-11-30 13:39:40 CET LOG: database system is ready to accept connections
2009-11-30 13:39:40 CET LOG: server process (PID 2272) was terminated by signal 11: Segmentation fault
2009-11-30 13:39:40 CET LOG: terminating any other active server processes
2009-11-30 13:39:40 CET LOG: all server processes terminated; reinitializing
2009-11-30 13:39:40 CET LOG: database system was interrupted; last known up at 2009-11-30 13:39:40 CET
2009-11-30 13:39:40 CET LOG: database system was not properly shut down; automatic recovery in progress
2009-11-30 13:39:40 CET LOG: record with zero length at 0/11D475C
2009-11-30 13:39:40 CET LOG: redo is not required
2009-11-30 13:39:40 CET LOG: autovacuum launcher started
2009-11-30 13:39:40 CET LOG: database system is ready to accept connections
2009-11-30 13:39:41 CET LOG: server process (PID 2280) was terminated by signal 11: Segmentation fault
2009-11-30 13:39:41 CET LOG: terminating any other active server processes
2009-11-30 13:39:41 CET LOG: all server processes terminated; reinitializing
2009-11-30 13:39:41 CET LOG: database system was interrupted; last known up at 2009-11-30 13:39:40 CET
2009-11-30 13:39:41 CET LOG: database system was not properly shut down; automatic recovery in progress
2009-11-30 13:39:41 CET LOG: record with zero length at 0/11D47A0
2009-11-30 13:39:41 CET LOG: redo is not required
2009-11-30 13:39:41 CET LOG: autovacuum launcher started
2009-11-30 13:39:41 CET LOG: database system is ready to accept connections
2009-11-30 13:39:41 CET LOG: server process (PID 2288) was terminated by signal 11: Segmentation fault
2009-11-30 13:39:41 CET LOG: terminating any other active server processes
2009-11-30 13:39:41 CET LOG: all server processes terminated; reinitializing
2009-11-30 13:39:41 CET LOG: database system was interrupted; last known up at 2009-11-30 13:39:41 CET
2009-11-30 13:39:41 CET LOG: database system was not properly shut down; automatic recovery in progress
2009-11-30 13:39:41 CET LOG: record with zero length at 0/11D47E4
2009-11-30 13:39:41 CET LOG: redo is not required
2009-11-30 13:39:41 CET LOG: autovacuum launcher started
2009-11-30 13:39:41 CET LOG: database system is ready to accept connections
2009-11-30 13:39:42 CET LOG: server process (PID 2296) was terminated by signal 11: Segmentation fault
2009-11-30 13:39:42 CET LOG: terminating any other active server processes
2009-11-30 13:39:42 CET LOG: all server processes terminated; reinitializing
2009-11-30 13:39:42 CET LOG: database system was interrupted; last known up at 2009-11-30 13:39:41 CET
2009-11-30 13:39:42 CET LOG: database system was not properly shut down; automatic recovery in progress
2009-11-30 13:39:42 CET LOG: record with zero length at 0/11D4828
2009-11-30 13:39:42 CET LOG: redo is not required
2009-11-30 13:39:42 CET LOG: autovacuum launcher started
2009-11-30 13:39:42 CET LOG: database system is ready to accept connections
2009-11-30 13:39:42 CET LOG: server process (PID 2304) was terminated by signal 11: Segmentation fault
2009-11-30 13:39:42 CET LOG: terminating any other active server processes
2009-11-30 13:39:42 CET LOG: all server processes terminated; reinitializing
2009-11-30 13:39:42 CET LOG: database system was interrupted; last known up at 2009-11-30 13:39:42 CET
2009-11-30 13:39:42 CET LOG: database system was not properly shut down; automatic recovery in progress
2009-11-30 13:39:42 CET LOG: record with zero length at 0/11D486C
2009-11-30 13:39:42 CET LOG: redo is not required
2009-11-30 13:39:43 CET LOG: autovacuum launcher started
2009-11-30 13:39:43 CET LOG: database system is ready to accept connections
2009-11-30 13:39:43 CET LOG: server process (PID 2312) was terminated by signal 11: Segmentation fault
2009-11-30 13:39:43 CET LOG: terminating any other active server processes
2009-11-30 13:39:43 CET LOG: all server processes terminated; reinitializing
2009-11-30 13:39:43 CET LOG: database system was interrupted; last known up at 2009-11-30 13:39:42 CET
2009-11-30 13:39:43 CET LOG: database system was not properly shut down; automatic recovery in progress
2009-11-30 13:39:43 CET LOG: record with zero length at 0/11D48B0
2009-11-30 13:39:43 CET LOG: redo is not required
2009-11-30 13:39:43 CET LOG: autovacuum launcher started
2009-11-30 13:39:43 CET LOG: database system is ready to accept connections
[fail]
Так что же произошло и что я могу сделать, чтобы решить эту проблему? Спасибо за ответы
Я считаю, что важной строкой было:
CET 2009-11-30 13:39:43 CET LOG: database system was not properly shut down; automatic recovery in progress
Сервер работал, но не мог принимать соединения до завершения восстановления.
Подготовьтесь к восстановлению из резервной копии. Скорее всего, это аппаратная ошибка, поскольку PostgreSQL очень безопасен. Конечно, это зависит от того, как он настроен. Посмотрите эту ветку:
Хорошо, теперь я дома и после перезагрузки сервера проблема решилась сама собой. Протестировано из локальной сети и через прокси и вроде все в порядке
Похоже, что-то пошло не так - PostgreSQL действительно имеет функции безопасности, которые должны предотвратить любые проблемы в случае отключения электроэнергии. Похоже, они не работали.
Одна из причин этого заключается в том, что ваш жесткий диск может лгать ОС, что он что-то записал, но вместо этого он только сохранил это в кэше записи. Этот кеш испаряется при отключении питания и данные на диске становятся несовместимыми. Если это производственная система и у вас нет кэша с автономным питанием, вам следует отключить кеш записи на вашем диске:
hdparm -W 0 /dev/sda
Но ожидайте замедления записи в базу данных - теперь записи должны быть написаны по-настоящему.
Вы можете восстановить некоторую производительность, используя "synchronous_commit = off" в postgresql.conf. Вы рискуете потерять некоторые недавние транзакции, но база данных всегда будет согласованной.