Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
share|improve this question
    
Were you running with fsync=off by any chance? Please post any modified postgresql.conf options. Also, before you do ANYTHING more, make a complete copy of the database directory (including base, pg_xlog, and all other folders and files) after stopping the database server. –  Craig Ringer Nov 21 '13 at 2:14
    
Thanks for your reply. Updated the question. –  Oscar Nov 21 '13 at 10:19

1 Answer 1

Yor 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 is exists and do vacuum on pg_class and pg_attribute. So, your problem must be solved. Sorry for my english

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.