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.

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)
share|improve this question
    
select oid,nspname from pg_namespace ... is there a record of OID 7549789 (I'm assuming that there won't be). –  swasheck May 7 '12 at 20:15
    
@swasheck: there is not. –  Evan Carroll May 7 '12 at 20:20
    
forums.enterprisedb.com/posts/list/1070.page Have you tried the steps listed here? –  swasheck May 7 '12 at 20:29
    
I have autovacuum on, and VACUUM FULL ANALYZE whenever I do something major. I could perhaps do it again, tonight. But, it'll take a lot of time. –  Evan Carroll May 7 '12 at 20:30
    
Yes - I deleted that comment because it seemed a bit silly to ask. Sorry about that. –  swasheck May 7 '12 at 20:35
add comment

2 Answers

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.

share|improve this answer
    
I dump and load 2GB of data from a third party exporter of CSV files. I do this pretty routinely -- once every other week at least. Anyway, I do VACUUM FULL ANALYZE before and after this. I'm also needlessly hard on the database because of a lack of functionality in the COPY FROM routines with '' being NULL. –  Evan Carroll May 7 '12 at 21:51
    
I also found the oids in pg_class and updated the above with that information. –  Evan Carroll May 7 '12 at 21:53
add comment

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.

share|improve this answer
    
Should I just delete this stuff from the catalog? And, I don't think I ever had a schema with these names -- how do I know it is safe? (output from the query that returned something is above) –  Evan Carroll May 7 '12 at 21:15
add comment

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.