CODEWITHSUNDEEP
databasepostgresqlrestore
Leandro Brito
Leandro Brito

Reputation: 344

Postgres catalog is missing

My friend copied the "data" directory to format his customer pc and restore the postgres database but now the database is showing a lot of erros about catalogs missing.

The tables load only the columns but without data and there are no views.

Do you have some idea that we can do in this case to solve this issue? We already tried this links: http://grokbase.com/t/postgresql/pgsql-hackers/051ebftr4m/fatal-catalog-is-missing-1-attribute-s-for-relid-16396 and http://www.justskins.com/forums/error-catalog-is-missing-208043.html

Thanks, Regards

Leandro

Upvotes: 1

Views: 6739

Answers (3)

user3419705
user3419705

Reputation: 11

Yor need to restore from backup or, if backup is missing then check regclass of corrupted table

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

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324841

Your friend should've probably read the manual: backup and restore, upgrading between releases.

The datadir can be copied to another machine only if:

  • PostgreSQL on the source machine was shut down before copying the datadir;
  • The entire datadir is copied, including pg_xlog, base, pg_clog, any tablespace directories; etc
  • The destination machine has the same major version of PostgreSQL on the same operating system and architecture as the source; for example, PostgreSQL 9.1 on 32-bit Windows.

Violate any of those rules and if the server starts at all, it'll start with damaged / unreadable data.

Upvotes: 2

Pavel Stehule
Pavel Stehule

Reputation: 45910

It looks like using different PostgreSQL versions. Data in PostgreSQL data dir is very sensitive and you cannot it copy to different computer without deeper knowledge. Use pg_dump instead. Don't touch to PostgreSQL data dir if you don't know what your do well.

Upvotes: 1

Related Questions