Thread: Error when trying to use pg_dump on 8.3 after minor release update

Greetings,

I get the following error when I try to dump a database on a production server:

[jberry@dms dms]$ pg_dump -U dms_user -s dms > dms_s.sql
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  could not open relation
with OID 27224
pg_dump: The command was: SELECT
pg_catalog.pg_get_viewdef('27289'::pg_catalog.oid) as viewdef


It is a RHEL 5 x86_64 server, running 8.3.7/8

Since the previous backup, we upgraded (via yum) the server from 8.3.7
to 8.3.8 without a restart.

With this error, I'm concerned about stopping and starting postgresql
to see if the error goes away: I don't want us to be left with a dead
database and no backup with the latest data.

So, is this expected behavior when you fail to restart after a minor
version upgrade, or is this a sign of hardware failure and we should
try dumping relations one at a time?

Regards,
-Joshua Berry

> It is a RHEL 5 x86_64 server, running 8.3.7/8
>
> Since the previous backup, we upgraded (via yum) the server from 8.3.7
> to 8.3.8 without a restart.

Sorry, my facts are wrong: there was no upgrade done on this server.
It has been running 8.3.7 compiled from source built and installed on
April 1. The current instance postmaster has been running 32 days.

How can I assess the situation? What procedure would you recommend?

Regards,
-Joshua Berry

Joshua Berry <yoberi@gmail.com> writes:
> [jberry@dms dms]$ pg_dump -U dms_user -s dms > dms_s.sql
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR:  could not open relation
> with OID 27224
> pg_dump: The command was: SELECT
> pg_catalog.pg_get_viewdef('27289'::pg_catalog.oid) as viewdef

This looks like catalog corruption :-(.  Can you find a pg_class row
with that OID, ie
    select * from pg_class where oid = 27224
I expect probably not, but then try it with enable_indexscan and
enable_bitmapscan turned off.  If that finds a row, then what you
have is a corrupt pg_class_oid_index and you can probably get out
of trouble by reindexing it.  You should also look at what 27289
is so you know what view is causing the problem.

            regards, tom lane

On Fri, Oct 23, 2009 at 1:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> This looks like catalog corruption :-(.  Can you find a pg_class row
> with that OID, ie
>        select * from pg_class where oid = 27224
> I expect probably not, but then try it with enable_indexscan and
> enable_bitmapscan turned off.  If that finds a row, then what you
> have is a corrupt pg_class_oid_index and you can probably get out
> of trouble by reindexing it.  You should also look at what 27289
> is so you know what view is causing the problem.
>
>                        regards, tom lane
>

Thanks, that lead me to a recently added view by the customers. I
could not get the view definition:

dms=# select * from pg_class where oid = 27289;
[lots of columns]
 v_reporte_pdv_du_epin |         2200 |   27294 |    16384 |     0 |
   27289 |             0 |        0 |         0 |             0 |
       0 | f           | f           | v       |       27 |         0
|           0 |        0 |        0 |       0 | f          | f
 | t           | f              |            0 |        |
(1 row)

dms=# \d v_reporte_pdv_du_epin
ERROR:  could not open relation with OID 27224

However, once the view was dropped, I was able to perform a database
dump. Any ideas how this can happen, and how to avoid? Should I assume
that there are more problems and recreate the database from a fresh
dump? Or will the system suffice in it's current state?

Regards,
-Joshua Berry