1

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

Leandro Brito
  • 334
  • 2
  • 13
  • 2
    If you have a full backup of the "data" directory taken when PostgreSQL was stopped, there are no tablespaces or linked in dirs and you have the same version of the binaries on both then this should work. You're going to need to provide some actual details if anyone is to help (or better your friend is). Minimum details: O.S., PostgreSQL versions + install methods, install method, how backup was taken, how restore was made, what the logs say on start up. – Richard Huxton Aug 18 '13 at 07:07
  • **Exact** PostgreSQL versions from source and destination machine please, and *copies of the logs*, not just vague descriptions of the errors. – Craig Ringer Aug 18 '13 at 13:40
  • 1
    Here are some who had to restore the database from the datadir due to non existing backup plan or crashed server. 1.[recover-postgresql-databases-from-raw-physical-files](http://stackoverflow.com/questions/3305961/recover-postgresql-databases-from-raw-physical-files) and 2 [save-postgres-database-from-broken-ubuntu-10-04-server](http://dba.stackexchange.com/questions/3204/save-postgres-database-from-broken-ubuntu-10-04-server?rq=1) – John P Aug 18 '13 at 14:33

3 Answers3

2

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • I got some details SO: Windows XP SP3 Postgres: 8.2 (ACSII) The message error is like this: catalog is missing x attribute(s) for relid xxxxxxx The machine wasn't shut down before copy the file. We have the entire datadir copied, including log directories. Thanks for the help! – Leandro Brito Aug 19 '13 at 12:23
  • 1
    @Leandro OK, so extremely obsolete PostgreSQL (8.2, really?!). As for the copy, of *course* the machine wasn't shut down, but was the database server stopped before copying the datadir? If not, some files might fail to copy or be copied corrupt. Since Windows doesn't let you copy open files there's a high chance that some files were *skipped* in which case you'll have critical and very difficult to recover corruption issues. I strongly suggest just restoring from the last backup. Recovering the DB may require serious time and expertise to manually re-create the catalogs. – Craig Ringer Aug 19 '13 at 12:26
  • @Leandro If the schema was created from a script that might help with recovery, assuming the script actually reflects the current schema of the running DB. Even then your goal will be to get to the point where you can `COPY` the data from the individual tables to CSV then re-create the database. – Craig Ringer Aug 19 '13 at 12:30
1

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.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
1

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

user3419705
  • 11
  • 2
  • Hello, could you provide us with some code sample of what you've already tried and where you are stuck. Only this way will we be maybe able to help – Grimmy Jun 23 '14 at 08:44
  • See other answer from the same user: https://dba.stackexchange.com/questions/53671/fatal-catalog-is-missing-1-attributes-for-relid-2662/68812#68812 – A.L Nov 02 '20 at 15:20