Обсуждение: Possible DB corruption
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
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
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
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