Error in creating a database dump on PostgreSQL

Question

When trying to make a postgreSQL database dump we got the following error and the process stops immediately.

Command used:

[email protected]:~

07/26 11:48:11> pg_dump -U tad -h localhost -p 5932 -F c -b -v -f /home/openbravo/dump26072018.dmp openbravo

Output:

pg_dump: reading schemas

pg_dump: reading user-defined tables

pg_dump: schema with OID 67046 does not exist

pg_dump: *** aborted because of error

Can anyone guide me how to sort this issue?

Update:

I followed this tutorial

http://www.aukema.org/2011/06/fixing-complex-corruption-in-my-dna.html

And I can see there are objects without a schemaname in the pg_tables.

But I don't know how to update those missing schemanames in the pg_tables. The last part of the tutorial is not quite explanatory. Hope someone can throw some light.

Urial

Back up your dataset by shutting down the database and copying the data files (/var/lib/postgresql on Ubuntu) somewhere, then try fixing your catalog tables as detailed in this thread: postgresql.org/message-id/26697.1156945365%40sss.pgh.pa.us – Discriminate

Don't worry, it's probably just concurrent DDL. Try again and see if the error persists. – Reste

no any better solutions? – Urial

Which verssion are you running? (select version). I presume you can't find your OID here: select oid, nspname from pg_namespace? Could you try out to find it these system catalogs + could you tell us if there is something special about it? (pg_class.relnamespace, ,g_operator.oprnamespace, pg_conversion.connamespace, pg_opclass.opcnamespace, pg_aggregate.aggnamespace, pg_proc.pronamespace) – Geniegenii

@Geniegenii it is 9.1.19 – Urial

Ok, that is a start. Did you find it in any of the catalogs? – Geniegenii

Hmm, I also have a "schema with OID XXXXXXX does not exist" error, but a query selecting on that OID from the pg_class table returns no results. – Bionomics

Answer 1

Finally found a way to take the backup by excluding the corrupted table as follows

pg_dump --exclude-table=ad_context_info -h localhost -p 5932 -U postgres > dumpsabnew.dmp

Urial