Thread: pg_dump and pg_dumpall fail when trying to backup database

I have a database in pgsql (7.3.2) on redhat 9.

When I try a 'pg_dump mydb' to back up the database, I get:
pg_dump: could not find namespace with OID 2200

Verbose version is:
-bash-2.05b$ pg_dump -v mydb | more
pg_dump: saving database definition
pg_dump: reading namespaces
pg_dump: reading user-defined types
pg_dump: could not find namespace with OID 2200
pg_dump: *** aborted because of error


Otherwise the database works just fine for daily use..etc,
just it cannot be backed up.

Anyone have any idea what is happening, or how to fix it?

Thank you!
Jack

jack_turer@yahoo.com (jack turer) writes:
> I have a database in pgsql (7.3.2) on redhat 9.
> When I try a 'pg_dump mydb' to back up the database, I get:

> -bash-2.05b$ pg_dump -v mydb | more
> pg_dump: saving database definition
> pg_dump: reading namespaces
> pg_dump: reading user-defined types
> pg_dump: could not find namespace with OID 2200
> pg_dump: *** aborted because of error

Hmm, I take it you dropped the public schema at some point?

I think you got bit by this 7.3 bug:

2003-01-08 16:40  tgl

    * src/: backend/catalog/pg_type.c, include/catalog/pg_type.h
    (REL7_3_STABLE): Repair bug noticed by Deepak Bhole: a shell type
    should have a dependency on its namespace, so that it will go away
    if the schema is dropped.

This fix is present in 7.3.2, but if you'd created the shell type while
running 7.3 or 7.3.1 then the dependency would still be lacking after an
update.

Anyway, what you've got is a row in pg_type that refers to a nonexistent
schema.  I'd suggest (as superuser)

SELECT * FROM pg_type WHERE typnamespace = 2200;

to double-check that these rows are not something you want, then

DELETE FROM pg_type WHERE typnamespace = 2200;

            regards, tom lane

Thank you for the idea Tom.

I tried it, and it didn't fix it (there are now no rows with
typnamespace=2200, the pg_dump fails with the same debug information
as before.

I am running 7.3.2 and never migrated from an earlier version, so
maybe this bug isn't completely licked yet in this version? Not sure..

Any additional thoughts where I should look?

Jack

Oh, the rerun of the pg_dump was a little different after the typnamespace
cleanup, but still failed..

pg_dump -v mydb | more
pg_dump: saving database definition
pg_dump: reading namespaces
pg_dump: reading user-defined types
pg_dump: reading user-defined functions
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined tables
pg_dump: could not find namespace with OID 2200
pg_dump: *** aborted because of error

Any thoughts?

THank you,
Jack

jack_turer@yahoo.com (jack turer) writes:
> Oh, the rerun of the pg_dump was a little different after the typnamespace
> cleanup, but still failed..

> pg_dump -v mydb | more
> pg_dump: saving database definition
> pg_dump: reading namespaces
> pg_dump: reading user-defined types
> pg_dump: reading user-defined functions
> pg_dump: reading user-defined aggregate functions
> pg_dump: reading user-defined operators
> pg_dump: reading user-defined operator classes
> pg_dump: reading user-defined tables
> pg_dump: could not find namespace with OID 2200
> pg_dump: *** aborted because of error

So you've still got some references to the PUBLIC schema.  That's pretty
interesting --- where are they exactly?  (They might be in
pg_class.relnamespace, but I'm not sure from the above.)

            regards, tom lane