Dealing with postgres corruption

Published: by Creative Commons Licence

  • Tags:

It can happen that a postgres backup using pg_dump (or pg_dumpall) fails with a message like:

pg_dump: schema with OID 849375 does not exist

This can once in a while when data is not flushed properly between the transaction log’s and the filesystem.
The only way to solve them (after making a file level backup, for the just in case situations) is to search and delete the offending OID from the database.

Connect to the database using psql (c </i></span>). Search in the following tables (this list might not be complete)

select * from pg_type where typnamespace = 849375;
select * from pg_proc where pronamespace = 849375;
select * from pg_class where relnamespace = 849375;

If there are results, then run deletes below for the matching tables

delete from pg_type where typnamespace = 849375;
delete from pg_proc where pronamespace = 849375;
delete from pg_class where relnamespace = 849375;

Be sure that you run the delete’s only on the right database with the right OID do not copy the lines from this blog entry without changing them.