Re.: Database recovery due to out of disk space

Hi

> While implementing a warm standby server lightning struck, the WAL files
> supposed to be shipped to the warm standby server caused the filing
> system to be out of disk space. In order to quickly reclaim disk space
> we removed some of the WAL files written to pg_xlogs (very stupid we
> know). A corrupted database resulted.

I had a problem like this in a test (fortunatelly) database I'm currently working on.
You can try to use pg_resetxlog with your cluster stoped.
Note thata you'll lose the latest transactions, but there's a good probability to have your old data recovered.
A new clean backup with the cluster stoped should be made prior to this of course.

> We are trying to recover this database on a different server and
> re-indexed all (but one) tables, only one -unimportant- table refuses to
> be re-indexed or even dropped. We desperately want to drop this single
> table (it's recreated constantly) but we can not, the following message
> is given:

> DROP TABLE statistics_sliding_per_merchant;
> ERROR: 1 constraint record(s) missing for rel
> statistics_sliding_per_merchant

It seems that PostgreSQL corrupted some tuple with wrong CTID combination when you tried to restore from corrupted WALs.
The safest is to make a full dump of your database and restore it in a clean PostgreSQL cluster.

If your database is too large and you have made a new clean backup of it, I would try to drop and recreate all constraints of that table. If failed, dump/restore is the way to go.

Flavio Henrique A. Gurgel
Consultor -- 4Linux
tel. 55-11-2125.4765
fax. 55-11-2125.4777
www.4linux.com.br