Why is pg_dumpall throwing an "OID does not exist'' error?

Question

When I run pg_dumpall I'm now getting this:

$ pg_dumpall -f may2012
pg_dump: schema with OID 7549789 does not exist
pg_dumpall: pg_dump failed on database "dealermade", exiting

How do I resolve it?

UPDATE

Here is some of the data requested by @depesz:

select relname from pg_class where relnamespace = 7549789;
   relname    
--------------
 lotdata
 lotdata_pkey
(2 rows)

Answer 1

This has been reported a number of times over the years, but the root cause has never been really identified. The one thing which seems to be clear is that it is caused when a schema is dropped but some references to it are not. There is evidence that at least in some cases this is a "special" schema created for a user's temporary tables.

The traditional fix is to log in as a database superuser and delete the lingering references to the schema from system tables. pg_depend and pg_type seem to be the main tables where these are found. Some people prefer to create a schema and update its oid to match the missing schema and poke around first, but I haven't seen that yield much additional information or recover any data.

This may not be related, but it may be a clue: I concerned by the statement that you VACUUM FULL ANALYZE whenever you do anything major. What version is this? Do you run that against the whole database, or just specific tables? Do you REINDEX afterward? Generally VACUUM FULL should be considered to be a very aggressive form of maintenance which should only be needed if there was some problem.

If you feel that you have evidence of what caused the problem, please share with the PostgreSQL community so that we can get it fixed. So far nobody has produced a reproducible test case or provided enough forensic evidence to track it down, and it has been infrequent enough to be hard to catch. You could start with an email to pgsql-general@postgresql.org if you have some evidence.

Answer 2

do:

select * from pg_class where relnamespace = 7549789;

and:

select * from pg_proc where pronamespace = 7549789;

These are the most likely suspects (there are other objects that can be related to schema).

Make sure you run the queries in dealermade database.

After you will see the results - answer can be obvious (for example: delete from ... where ..), or more tricky - depending on what is the bad object. But at least you will have some data.

Also - try enabling logging of all queries, and rerun pg_dump to see what was the exact query that failed.