Unable to restart postgres - database system was interrupted - Mailing list pgsql-general

Running Postgres version 8.1.3 on Opteron box running FreeBSD
6.0-RELEASE #10.

We stopped postgres using kill -TERM. When we tried to restart the
engine, it would not recover. The logs stated the following:



2006-12-04 10:18:39 CST LOG:  archived transaction log file
"00000001000000690000002E"
2006-12-04 10:25:53 CST LOG:  archived transaction log file
"00000001000000690000002F"
2006-12-04 10:25:58 CST LOG:  archived transaction log file
"000000010000006900000030"
2006-12-04 10:26:09 CST LOG:  archived transaction log file
"000000010000006900000031"
2006-12-04 10:30:11 CST LOG:  received smart shutdown request
2006-12-04 10:30:11 CST LOG:  shutting down
2006-12-04 10:30:11 CST LOG:  database system is shut down
2006-12-04 10:30:11 CST LOG:  logger shutting down
2006-12-04 17:00:54 CST LOG:  database system was interrupted at
2006-12-02 16:45:06 CST
2006-12-04 17:00:54 CST LOG:  could not open file
"pg_xlog/0000000100000065000000F7" (log file 101, segment 247): No such
file or directory
2006-12-04 17:00:54 CST LOG:  invalid primary checkpoint record
2006-12-04 17:00:54 CST LOG:  could not open file
"pg_xlog/0000000100000065000000F0" (log file 101, segment 240): No such
file or directory
2006-12-04 17:00:54 CST LOG:  invalid secondary checkpoint record
2006-12-04 17:00:54 CST PANIC:  could not locate a valid checkpoint record
2006-12-04 17:00:54 CST LOG:  startup process (PID 786) was terminated
by signal 6
2006-12-04 17:00:54 CST LOG:  aborting startup due to startup process
failure
2006-12-04 17:00:54 CST LOG:  logger shutting down




Note, the complaint of "database system was interrupted at 2006-12-02
16:45:06 CST". The log for the time in question contains:




  2006-12-02 16:30:40 CST LOG:  archived transaction log file
"0000000100000065000000F1"
2006-12-02 16:31:05 CST LOG:  archived transaction log file
"0000000100000065000000F2"
2006-12-02 16:32:08 CST LOG:  transaction ID wrap limit is 1183309811,
limited by database "development"
2006-12-02 16:32:08 CST LOG:  transaction ID wrap limit is 1183309811,
limited by database "development"
2006-12-02 16:32:08 CST LOG:  duration: 1927143.031 ms  statement: set
statement_timeout=0; VACUUM ANALYZE VERBOSE;
2006-12-02 16:32:11 CST LOG:  archived transaction log file
"0000000100000065000000F3"
2006-12-02 16:32:29 CST LOG:  archived transaction log file
"0000000100000065000000F4"
2006-12-02 16:32:44 CST LOG:  archived transaction log file
"0000000100000065000000F5"
2006-12-02 16:36:25 CST LOG:  archived transaction log file
"0000000100000065000000F6"
2006-12-02 17:03:33 CST LOG:  transaction ID wrap limit is 1183351842,
limited by database "work"
2006-12-02 17:03:33 CST LOG:  transaction ID wrap limit is 1183351842,
limited by database "work"
2006-12-02 17:03:33 CST LOG:  duration: 1885232.821 ms  statement: set
statement_timeout=0; VACUUM ANALYZE VERBOSE;
2006-12-02 17:03:34 CST LOG:  transaction ID wrap limit is 1183524362,
limited by database "postgres"
2006-12-02 17:03:34 CST LOG:  transaction ID wrap limit is 1183524362,
limited by database "postgres"
2006-12-02 17:11:13 CST LOG:  archived transaction log file
"0000000100000065000000F7"
2006-12-02 17:11:23 CST LOG:  archived transaction log file
"0000000100000065000000F8"
2006-12-02 17:11:33 CST LOG:  archived transaction log file
"0000000100000065000000F9"




I'm sure I'm probably over looking the obvious. But I don't see where
the problem is.

My concern is that now the pg_xlog directory doesn't have what it takes
for me to restart the server.

Two questions:

a) how did I get into this mess?; and
b) how do I get out of it?

Can I run pg_resetxlog with any hope of success?

Many thanks ...

Andy
--------------------------------------------------------------------------------
Andrew Rost
National Operational Hydrologic Remote Sensing Center (NOHRSC)
National Weather Service, NOAA
1735 Lake Dr. West, Chanhassen, MN 55317-8582
Voice: (952)361-6610 x 234
Fax: (952)361-6634
andy.rost@noaa.gov
http://www.nohrsc.noaa.gov
--------------------------------------------------------------------------------