Thread: Error on pg_dumpall
Hi
I am getting the following error msg when I do a pg_dumpall
-----
TK@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
On 10/22/2012 03:24 AM, Terry Khatri wrote:
> Hi
>
> I am getting the following error msg when I do a pg_dumpall
>
> -----
>
> TK@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
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
On 23 October 2012 10:15, Craig Ringer <ringerc@ringerc.id.au> wrote:
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?
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
What's the output of:
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)
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