pg_dump and pg_dumpall fail when trying to backup database

[jack turer]

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

[Tom Lane]

ja********@yaho o.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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[jack turer]

Thank you for the idea Tom.

I tried it, and it didn't fix it (there are now no rows with
typnamespace=22 00, 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

[jack turer]

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

[Tom Lane]

ja********@yaho o.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.relnam espace, but I'm not sure from the above.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html