Error on pg_dumpall

[Terry Khatri]

Hi

I am getting the following error msg when I do a pg_dumpall

-----

***@OraLinux /usr/local/pgsql/backups
$ pg_dumpall -U sns84 > completebackup20121020,sql
pg_dump: schema with OID 74363 does not exist
pg_dumpall: pg_dump failed on database "bihistory", exiting

-----

I have set the logs to debug5 to catch where the problem is while taking
the dump but nothing shows up there.

If you have any ideas please help me.

And thanks to all for responding to my earlier post.

Rgds
T

[Craig Ringer]

Post by Terry Khatri
Hi
I am getting the following error msg when I do a pg_dumpall
-----
$ pg_dumpall -U sns84 > completebackup20121020,sql
pg_dump: schema with OID 74363 does not exist
pg_dumpall: pg_dump failed on database "bihistory", exiting

That's not good.

First, try per-database dumps. Work out which database has the problem.
Do a:

pg_dumpall -U sns84 --globals-only > globals.sql

then for each database:

pg_dump -U sns84 -f $dbname.sql $dbname

(scripting it if desired), until you see which DB fails to dump. Dump
all the DBs you can successfully dump before proceeding.

Then, in the problem dB, try:

REINDEX pg_catalog.pg_namespace;

does that help?

What's the output of:

SELECT oid, nspname FROM pg_catalog.pg_namespace;

in the problem DB?

What about \dn in psql?

Is all your data for the problem DB in the "public" schema? If so, can
you do a schema-only dump?

pg_dump -U sns84 -n public -f $dbname.dump $dbname

--
Craig Ringer



--
Craig Ringer

[Terry Khatri]

Post by Craig Ringer
First, try per-database dumps. Work out which database has the problem.
pg_dumpall -U sns84 --globals-only > globals.sql
pg_dump -U sns84 -f $dbname.sql $dbname
(scripting it if desired), until you see which DB fails to dump. Dump
all the DBs you can successfully dump before proceeding.
REINDEX pg_catalog.pg_namespace;
does that help?

Nope, same problem still - I knew already which db is failing pls see my
initial msg

pg_dump: schema with OID 74363 does not exist

Post by Craig Ringer
SELECT oid, nspname FROM pg_catalog.pg_namespace;
in the problem DB?

nspname

---------

99

11194

11195

11

2200

11459

82537

(7 rows)

That oid does'nt show up here !


List of schemas

Name | Owner

-------------+---------

public | sns84

bihistory | sns84

(2 rows)

Post by Craig Ringer
Is all your data for the problem DB in the "public" schema? If so, can
you do a schema-only dump?
pg_dump -U sns84 -n public -f $dbname.dump $dbname

No it is in its own schema i.e. bihistory

Please let me know if there's any tweaking that we do in the catalog to fix
it.
Thank you very much for your help
Rgds
T