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@xxxxxxxx
http://www.nohrsc.noaa.gov
--------------------------------------------------------------------------------
