Postgres Pro
Downloads
Home   >   mailing lists

Thread: Possible DB corruption

Possible DB corruption

From
Justin M Wozniak
Date:
Hello
    We noticed that some records were mysteriously disappearing from
our DB.  I went in with psql and found that the \dt command no longer
works, providing the output below.  Is this a sure sign of DB corruption?
Running psql (PostgreSQL) 7.4.8 on Linux 2.4.27.
    Thanks

GEMSd=> \dt
      List of relations
 Schema | Name | Type | Owner
--------+------+------+-------
(0 rows)

However... using SELECT statments on tables that I remember are present
still works...

--
Justin Michael Joseph Wozniak

Re: Possible DB corruption

From
Tom Lane
Date:
Justin M Wozniak <jwozniak@cse.nd.edu> writes:
>     We noticed that some records were mysteriously disappearing from
> our DB.  I went in with psql and found that the \dt command no longer
> works, providing the output below.  Is this a sure sign of DB corruption?
> Running psql (PostgreSQL) 7.4.8 on Linux 2.4.27.

> GEMSd=> \dt
>       List of relations
>  Schema | Name | Type | Owner
> --------+------+------+-------
> (0 rows)

> However... using SELECT statments on tables that I remember are present
> still works...

That sounds like XID wraparound to me --- is it likely that you've
exceeded 2 billion transactions since the tables were created?

If so, a database-wide VACUUM will probably fix it.  After which you
should take a hard look at your vacuuming practices.

            regards, tom lane

Re: Possible DB corruption

From
Michael Fuhr
Date:
On Fri, May 25, 2007 at 04:47:52PM -0400, Justin M Wozniak wrote:
>     We noticed that some records were mysteriously disappearing from
> our DB.  I went in with psql and found that the \dt command no longer
> works, providing the output below.  Is this a sure sign of DB corruption?
> Running psql (PostgreSQL) 7.4.8 on Linux 2.4.27.

You might be suffering from transaction ID wraparound.  Are you
vacuuming regularly?

http://www.postgresql.org/docs/7.4/interactive/maintenance.html#VACUUM-FOR-WRAPAROUND

"...every table in the database must be vacuumed at least once every
billion transactions."

--
Michael Fuhr

Re: Possible DB corruption

From
Michael Fuhr
Date:
On Mon, May 28, 2007 at 04:14:14PM -0600, Michael Fuhr wrote:
> On Fri, May 25, 2007 at 04:47:52PM -0400, Justin M Wozniak wrote:
> >     We noticed that some records were mysteriously disappearing from
> > our DB.  I went in with psql and found that the \dt command no longer
> > works, providing the output below.  Is this a sure sign of DB corruption?
> > Running psql (PostgreSQL) 7.4.8 on Linux 2.4.27.
>
> You might be suffering from transaction ID wraparound.

Another possibility: has search_path changed?  You said that \dt
shows nothing but you can still query the tables -- do you use
schema-qualified table names in those queries?

--
Michael Fuhr