0

I have problem with starting PostgreSQL 8.4 on Ubuntu 9.10 Server after blackout. When I try to connect to the database it says:

psql: server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request.

When I try to start it by using command

sudo -u postgres /etc/init.d/postgresql-8.4 start
 * Starting PostgreSQL 8.4 database server                               [ OK ]

Netstat output

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                *:*                                 -

But still don't work so lets restart it

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]

So what happened and what can I do to solve this? Thanks for replies

4 Answers 4

3

I believe the important line was:

 CET 2009-11-30 13:39:43 CET LOG:  database system was not properly shut down; automatic recovery in progress

The server was up, but unable to accept connections until recovery was completed.

1

Prepare yourself to recover from backup. It is most likely a hardware error as PostgreSQL is very crashsafe. Of course it depends how it is tuned. Check out this thread:

https://stackoverflow.com/questions/598200/how-do-i-fix-postgres-so-it-will-start-after-an-abrupt-shutdown

0

Ok, now I'm home and after server restart the problem has solved itself. Tested from local network and through proxy and seems everything is all right

1
  • 2
    you might want to delete the question, in that case
    – skaffman
    Nov 30, 2009 at 17:19
0

It looks like something was very wrong - PostgreSQL does have a safety features, which should prevent any problems in case of blackouts. It seems that they did not work.

One reason for this is that your hard disk can lie to OS, that it has written something, but instead it has only saved it in a write-cache. This cache evaporates when power is lost, and data on disk become inconsistent. If it is a production system, and you do not have a battery-backed cache, you should disable write-cache on your disk:

hdparm -W 0 /dev/sda

But expect a slowdown of database writes - writes now would have to be written for real.

You can restore some of the performance back using "synchronous_commit = off" in postgresql.conf. You'll risk some lost recent transactions but a database will always be consistent.

You must log in to answer this question.