Thread: pg_dump problem: 'pg_dump: schema with OID 1515546 does not exist'

Hi,

I am getting the error mentioned in the subject ('pg_dump: schema
with OID 1515546 does not exist') when I try to back up one database
out of a cluster of half a dozen or so on our server.  This is a
production DB and the database in question _seems_ to be working OK,
other than the issue with pg_dump, however the fact that I am seeing
this does make be somewhat concerned.  I suspect it was caused when
an autovacuum process was killed (due to memory issues exhaustion).

Any advice as to what can be done to track down and resolve this
problem, dropping and restoring the DB isn't _completely_ out of the
question, but I'd like to avoid it if at all possible.

Thanks,

David.

David Brain
dbrain@bandwidth.com
919.297.1078

David Brain <dbrain@bandwidth.com> writes:
> I am getting the error mentioned in the subject ('pg_dump: schema
> with OID 1515546 does not exist') when I try to back up one database
> out of a cluster of half a dozen or so on our server.

Well, for starters, troll through the system catalogs in that database
to see which object(s) reference that schema OID, eg,
    select * from pg_class where relnamespace = 1515546;
and similarly for other catalogs that have a namespace column.  Once you
know where the problem is it'll be easier to figure out what happened
and what to do about it.

What PG version is this?

            regards, tom lane

Hi,

This is PG version 8.2.3.

The select returns:

temptrans,1515546,1515548,16398,0,1515547,0,133873,2.0234e
+06,1515549,0,f,f,r,24,0,0,0,0,0,f,f,f,f,195484336,,

What other catalogs would be worth looking in to?  By the way the
table name here does sort of make sense as to something that may be
missing, this table is created in a schema that is often dropped/re-
created.

David.

The select results in one line (
On Sep 24, 2007, at 10:58 AM, Tom Lane wrote:

> David Brain <dbrain@bandwidth.com> writes:
>> I am getting the error mentioned in the subject ('pg_dump: schema
>> with OID 1515546 does not exist') when I try to back up one database
>> out of a cluster of half a dozen or so on our server.
>
> Well, for starters, troll through the system catalogs in that database
> to see which object(s) reference that schema OID, eg,
>     select * from pg_class where relnamespace = 1515546;
> and similarly for other catalogs that have a namespace column.
> Once you
> know where the problem is it'll be easier to figure out what happened
> and what to do about it.
>
> What PG version is this?
>
>             regards, tom lane

David Brain
dbrain@bandwidth.com
919.297.1078

David Brain <dbrain@bandwidth.com> writes:
> This is PG version 8.2.3.

> The select returns:

> temptrans,1515546,1515548,16398,0,1515547,0,133873,2.0234e
> +06,1515549,0,f,f,r,24,0,0,0,0,0,f,f,f,f,195484336,,

> What other catalogs would be worth looking in to?  By the way the
> table name here does sort of make sense as to something that may be
> missing, this table is created in a schema that is often dropped/re-
> created.

Hmm, do you rely on "DROP SCHEMA foo CASCADE" to make the contained
objects go away?  We have seen a few reports suggesting that that
sometimes misses some contained objects.  The mechanism for this has
not been identified for sure, but I wonder whether it might have
something to do with the VACUUM-related corruption that we found just
before the latest set of releases.  I'd urge you to update to 8.2.5.

As far as recovery from the immediate problem goes, I'd suggest making a
junk schema, poking its OID into this pg_class row, and then dropping
the table using DROP TABLE (remembering that it will now look like it's
junk.temptrans).  Then you can drop the junk schema and all should be
reasonably OK.

            regards, tom lane

Hi,

First, thank you,  things now do seem to be working correctly.

>
> Hmm, do you rely on "DROP SCHEMA foo CASCADE" to make the contained
> objects go away?  We have seen a few reports suggesting that that
> sometimes misses some contained objects.  The mechanism for this has
> not been identified for sure, but I wonder whether it might have
> something to do with the VACUUM-related corruption that we found just
> before the latest set of releases.  I'd urge you to update to 8.2.5.

Yes - what you describe is exactly what we were doing - fortunately
this isn't something we are relying on day to day, but on the day in
question that is what I was doing.  It looks like an upgrade is in my
future then.

>
> As far as recovery from the immediate problem goes, I'd suggest
> making a
> junk schema, poking its OID into this pg_class row, and then dropping
> the table using DROP TABLE (remembering that it will now look like
> it's
> junk.temptrans).  Then you can drop the junk schema and all should be
> reasonably OK.
>

Did that - things now seem to working, I'll have to wait till later
to confirm that the complete backup is working, but a schema dump is
working just fine (which it wasn't previously).

Again, thanks for the help, it is this kind of access to assistance
that makes PG a much easier 'sell'.

David.


David Brain
dbrain@bandwidth.com
919.297.1078