Database in recovery mode forever

Hi all,

this afternoon I had a database crash on a test system (The first Postgres crash in more than three years).
I wasn't able to identify the reason.....the log doesn't say anything interesting.
Is there way the exactly identify the reason of the crash ?

After having detected the crash, Postgres (v. 8.3.6) started to kill all the other postgres processes
After many minutes, it was still keeping on refusing connections saying the db was still recovering.

Trying stopping postgres wasn't possible

I noticed that a process was still alive and I killed it. After that all Postgres process disappeared and I was able to restart Postgres.
Now everything works..

Is there an explanation for this behaviour ?

I include messages from the log and from the shell hoping is more clear.

Thank you very much

Marco

---------------------------------------



2009-05-19 16:04:18 CEST 0 4a092d56.1535 - LOG: server process (PID 22298) was terminated by signal 9: Killed
2009-05-19 16:04:18 CEST 0 4a092d56.1535 - LOG: terminating any other active server processes
2009-05-19 16:04:18 CEST 172.30.2.143(49041) user db 0 4a12b4ab.54a9 - WARNING: terminating connection because of crash of another server process
2009-05-19 16:04:18 CEST 172.30.2.143(49041) user db 0 4a12b4ab.54a9 - DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory.

..... kills many db process .......



2009-05-19 16:05:56 CEST 172.30.2.143(49052) user db 0 4a12bcc4.5a5a - FATAL: the database system is in recovery mode
2009-05-19 16:06:15 CEST 172.30.2.143(49053) user db 0 4a12bcd7.5a5b - FATAL: the database system is in recovery mode
2009-05-19 16:06:30 CEST 172.30.2.143(49054) user db 0 4a12bce6.5a69 - FATAL: the database system is in recovery mode
2009-05-19 16:06:42 CEST 172.30.2.143(49055) user db 0 4a12bcf2.5a6a - FATAL: the database system is in recovery mode
2009-05-19 16:08:15 CEST 172.30.2.143(49056) user db 0 4a12bd4f.5a93 - FATAL: the database system is in recovery mode
2009-05-19 16:09:57 CEST 172.31.2.139(3041) user db 0 4a12bdb5.5b09 - FATAL: the database system is in recovery mode
2009-05-19 16:10:42 CEST 172.31.1.1(2358) user db 0 4a12bde2.5b71 - FATAL: the database system is in recovery mode

tried a clean shutdown...

[root(at)pgtest1 logs]# service postgresql stop
Stopping PostgreSQL: pg_ctl: server does not shut down

In the log :

2009-05-19 16:13:10 CEST 0 4a092d56.1535 - LOG: received fast shutdown request





[root(at)pgtest1 logs]# ps auxx | grep post

postgres 5429 0.0 0.0 78208 816 ? Ss May12 0:14 /usr/local/pgsql/bin/postmaster -D /var/pgdata
postgres 5436 0.0 0.0 7572 488 ? Ss May12 0:41 postgres: logger process
postgres 17293 0.8 0.1 79976 2664 ? Ss 13:53 1:19 postgres: user db 172.30.2.143(48960) SELECT
root 24147 0.0 0.0 4204 636 pts/0 S+ 16:26 0:00 grep post

[root(at)pgtest1 logs]# kill -9 17293

[root(at)pgtest1 logs]# ps auxx | grep post
root 24149 0.0 0.0 4916 632 pts/0 S+ 16:26 0:00 grep post

in the log

2009-05-19 16:26:21 CEST 0 4a092d56.1535 - LOG: abnormal database system shutdown

[root(at)pgtest1 logs]# service postgresql start
Starting PostgreSQL: ok

[root(at)pgtest1 logs]# ps auxx | grep post
postgres 24198 42.0 0.1 78560 3620 ? Ss 16:26 0:00 /usr/local/pgsql/bin/postmaster -D /var/pgdata
postgres 24205 0.0 0.0 7812 660 ? Ss 16:26 0:00 postgres: logger process
postgres 24207 0.0 0.0 78512 824 ? Ss 16:26 0:00 postgres: writer process
postgres 24208 0.0 0.0 78512 792 ? Ss 16:26 0:00 postgres: wal writer process
postgres 24209 0.0 0.0 78560 960 ? Ss 16:26 0:00 postgres: autovacuum launcher process
postgres 24210 0.0 0.0 7952 828 ? Ss 16:26 0:00 postgres: stats collector process
root 24212 0.0 0.0 4076 644 pts/0 R+ 16:26 0:00 grep post

from the log :

2009-05-19 16:26:43 CEST 0 4a12c1a3.5e8e - LOG: database system was interrupted; last known up at 2009-05-19 15:59:31 CEST
2009-05-19 16:26:43 CEST 0 4a12c1a3.5e8e - LOG: database system was not properly shut down; automatic recovery in progress
2009-05-19 16:26:43 CEST 0 4a12c1a3.5e8e - LOG: record with zero length at 274/F0FE7B18
2009-05-19 16:26:43 CEST 0 4a12c1a3.5e8e - LOG: redo is not required
2009-05-19 16:26:43 CEST 0 4a12c1a3.5e91 - LOG: autovacuum launcher started
2009-05-19 16:26:43 CEST 0 4a12c1a1.5e86 - LOG: database system is ready to accept connections