Today I encountered my second database corruption or crash on my development machine. The problem is that my IDE drive sucks, an old IBM 75GXP. For those of you who don’t remember, these drives were notorious for having all sorts of problems when they were released. Most of the problems revolved around dying after six months. Regardless, I’m still using this thing for another month.
You can tell if your database became corrupted by restarting the postmaster or typing:
sudo /etc/init.d/posrgresql-8.2 restart
The result of the command will look similar to the following:
2007-11-26 13:14:48 CST LOG: server process (PID 16312) was terminated by signal 11
2007-11-26 13:14:48 CST LOG: terminating any other active server processes
2007-11-26 13:14:48 CST LOG: all server processes terminated; reinitializing
2007-11-26 13:14:48 CST LOG: database system was interrupted at 2007-11-26 13:14:47 CST
2007-11-26 13:14:48 CST LOG: checkpoint record is at 0/965E4130
2007-11-26 13:14:48 CST LOG: redo record is at 0/965E4130; undo record is at 0/0; shutdown TRUE
2007-11-26 13:14:48 CST LOG: next transaction ID: 0/73629; next OID: 10952256
2007-11-26 13:14:48 CST LOG: next MultiXactId: 1; next MultiXactOffset: 0
2007-11-26 13:14:48 CST LOG: database system was not properly shut down; automatic recovery in progress
2007-11-26 13:14:48 CST LOG: record with zero length at 0/965E4178
Before initiating any recovery options, I recommend backing up your $PGDATA directory. The following directions will attempt to reset the transaction log, and you may loose the latest changes to your databases. The first option for recovering from a crash is to open your ‘/etc/postgresql/8.2/main/postgresql.conf’ file and turn on the following option:
...
zero_damaged_pages = true
After saving the changes, type the following to restart postgresql:
sudo /etc/init.d/postgresql-8.2 restart
If you still see error messages, you may need to restart the entire computer in order to get postgresql to start.
After restarting postgresql, reopen the ‘/etc/postgresql/8.2/main/postgresql.conf’ file and comment out the zero_damaged_pages option. The line should look similar to the following:
#zero_damaged_pages = true
After reading a few posts, it seems that IDE drives in general should not be used when working with databases because of corruption issues. If you are using an IDE drive, I recommend adding the following options to the ‘/etc/postgresql/8.2/main/postgresql.conf’ file.
fsync = on # turns forced synchronization on or off
wal_sync_method = fsync
The problem with turning on fsync is that it slows down the performance of Postgresql, however, it should prevent further corruption issues with your database.
You can find more information about these configuration options in the Postgresql 8.2 documentation.
Reference: