Corrupted Postgres server logs
Tuesday, August 30. 2005
About 6 months ago I wrote several web applications that stored data in a Postgres database. I installed Postgres on my laptop, and used it for the length of the project. All was well. After the project was over, I did not use Postgres any more, since MySQL is my database of choice.
Yesterday, I started another project that used Postgres. Since it was already installed on my laptop, I thought I was one step ahead. Wrong! When I tried to start the database server, I received the following unpleasant errors:
-bash-2.05b$ postmaster -i
LOG: database system was interrupted at 2005-05-13 10:52:38 EDT
LOG: could not open file "/var/lib/pgsql/data/pg_xlog/00000002000....
LOG: invalid primary checkpoint record
LOG: could not open file "/var/lib/pgsql/data/pg_xlog/00000002000....
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 6766) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: database system was interrupted at 2005-05-13 10:52:38 EDT
LOG: could not open file "/var/lib/pgsql/data/pg_xlog/00000002000....
LOG: invalid primary checkpoint record
LOG: could not open file "/var/lib/pgsql/data/pg_xlog/00000002000....
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 6766) was terminated by signal 6
LOG: aborting startup due to startup process failure
After a bit of research, I found that the cause of the error was corrupted Postgres log files. Sadly, these log files can become corrupted when the server is shutdown incorrectly. But happily, Postgres provides a command-line utility to clear out the corrupted logs, "pg_resetxlog". From the manpage:
pg_resetxlog clears the write-ahead log (WAL) and optionally resets some other control information (stored in the pg_control file). This function is sometimes needed if these files have become corrupted. It should be used only as a last resort, when the server will not start due to such corruption.
So, I ran the utility:
-bash-2.05b$ pg_resetxlog -f /var/lib/pgsql/data
Now its all good. Postgres starts without a hitch:
-bash-2.05b$ postmaster -i
LOG: database system was shut down at 2005-08-29 16:42:31 EDT
LOG: checkpoint record is at 2/A1000010
LOG: redo record is at 2/A1000010; undo record is at 2/A1000010....
LOG: next transaction ID: 301493; next OID: 30773144
LOG: database system is ready
LOG: database system was shut down at 2005-08-29 16:42:31 EDT
LOG: checkpoint record is at 2/A1000010
LOG: redo record is at 2/A1000010; undo record is at 2/A1000010....
LOG: next transaction ID: 301493; next OID: 30773144
LOG: database system is ready
Hurrah!