Postgresql – FATAL: catalog is missing 1 attribute(s) for relid 2662

corruptiondisaster recoverypostgresql-9.1recovery

I have a server with PostgreSQL9.1-903 (Windows 7 Professional). After a power cut my database is inaccessible (I presume that the database is corrupted).

The postgreSQL service starts but the following message is displayed when I try to connect via pg_Admin:

FATAL: catalog is missing 1 attribute(s) for relid 2662

I have tried to disable the option: 'ignore_system_index' but the database is still inaccessible. I guess that is not a problem with the indexes, but with the fact that some rows of the table pg_attribute have disappeared.

I am interested in recovering the data because my last backup is a bit old. Is there any way to just connect to the database? After that I would use the tool pg_dump to retrieve the data and mount it in a new database.

EDIT

The parameter fsync is commented in the postgresql.conf (I assume that the default value is on). Following, I attach the part of the postgresql.conf which has been tuned:

checkpoint_segments = 32    # in logfile segments, min 1, 16MB each

shared_buffers = 1GB        # min 128kB

work_mem = 8MB

maintenance_work_mem = 256MB

effective_cache_size=1024MB

default_statistics_target = 200

autovacuum_vacuum_cost_delay = 50ms

Best Answer

Upon database corruption, before doing anything else, one should read https://wiki.postgresql.org/wiki/Corruption and take a file system level backup.

You need to restore from backup or, if backup is missing, check corrupted tables for example

select distinct relname 
from pg_class 
where not exists (select 1 from pg_attribute where attrelid = relfilenode)`

or

select distinct relname 
from pg_class 
where relfilenode > (select max(attrelid) from pg_attribute)

And then your need to restore missing record in pg_attribute (for example create simm table and 

copy with q as 
( select attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod, attbyval, attstorage,attalign, attnotnull, atthasdef, attisdropped, attislocal, attinhcount, attcollation, attacl, attoptions, attfdwoptions 
  from pg_attribute 
  where attrelid::regclass = your_table::regclass) ) 
 INSERT INTO pg_catalog.pg_attribute select [id from your pgclass missing instead new attrelid], * from q

Then check, that atttypid exists and do vacuum on pg_class and pg_attribute. So, your problem must be solved.

Related Question