Re: How to deal with corrupted database?

On 11/09/2011 07:02 PM, Ruslan A. Bondar wrote:
> Hello all.
>
> This is a first time I receive such an issue.
> My database was corrupted some way.
Before you do ANYTHING else, make a copy of your database somewhere
safe. See:

http://wiki.postgresql.org/wiki/Corruption
> When I'm trying to access the database via psql I receive:
>
> root(at)udb:/etc/bacula# psql -U bacula
> psql: FATAL: could not read block 0 in file "base/16387/86057840": read only 0 of 8192 bytes
>
>
> So I want to drop it, and recover from backup. But when I'm trying to drop the database I see:
>
> postgres=# drop database bacula;
> ERROR: could not access status of transaction 15892843
> DETAIL: Could not open file "pg_subtrans/00F2": No such file or directory.
>
>
> Is there any way to recover the database to working state, or drop it?
>
*ONLY* once you've made a full backup copy, you may be able to set
zero_damaged_pages to get a usable dump.

Do you know what caused this? The missing files suggest it was probably
file system corruption - was there a disk failure? fsck run with errors?
Unexpected reboot on a RAID controller with a dead backup battery?

--
Craig Ringer