pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres 'forgets' table definitions
5+ messages / 4 participants
[nested] [flat]
* postgres 'forgets' table definitions
@ 2008-06-10 10:28 alex <alex@kerkhove.net>
0 siblings, 0 replies; 5+ messages in thread
From: alex @ 2008-06-10 10:28 UTC (permalink / raw)
To: pgsql-admin
Hi,
We're running pgsql 7.3.14 (yes I know it's old, an upgrade to 8.3 is planned).
Since a couple of weeks postgres loses the table definitions from apparently random tables. A 'broken' table actually still works but a \d table gives nothing. Currently The biggest problem is that these broken tables don't appear in the backups.
example:
mydb=# select * from toegevoegde_dienst_omzetting_history limit 1;
omzetting_id | toegevoegde_dienst_id | datum_aanvraag | datum_ingang | datum_uitgevoerd | product_soort_id_oud | product_soort_id_nieuw | id | door_wie | sinds
--------------+-----------------------+----------------+--------------+------------------+----------------------+------------------------+----+------------+---------------------
5 | 167817 | 2007-12-23 | 2007-12-23 | | 5816 | 5817 | 6 | webservice | 2007-12-23 19:55:36
(1 row)
mydb=# \d toegevoegde_dienst_omzetting_history
Did not find any relation named "toegevoegde_dienst_omzetting_history".
mydb=#
And this table is obviously from the pg_catalog.pg_class table as well.
Has anyone any idea what may cause this? I can't find anything in the logs regarding the missing tables. (eg. that someone messes the pg_catalog tables). I disabled the nightly vacuum process but the problem persists.
Could this problem be fixed by inserting the right records back in to in de pg_catalog tables? is there a way to dump the pg_catalog tables?
The pgserver is running on a 4 CPU dell 6850 with 16G of Ram with Fedora Core 4 on a reiserfs filesystem without any problems for years. And nothing has changed on the server side of things.
thanks,
alex.
^ permalink raw reply [nested|flat] 5+ messages in thread
* postgres 'forgets' table definitions
@ 2008-06-10 11:12 alex <alex.pgsql@kerkhove.net>
0 siblings, 1 reply; 5+ messages in thread
From: alex @ 2008-06-10 11:12 UTC (permalink / raw)
To: pgsql-admin
Hi,
We're running pgsql 7.3.14 (yes I know it's old, an upgrade to 8.3 is planned).
Since a couple of weeks postgres loses the table definitions from apparently random tables. A 'broken' table actually still works but a \d table gives nothing. Currently The biggest problem is that these broken tables don't appear in the backups.
example:
mydb=# select * from toegevoegde_dienst_omzetting_history limit 1;
omzetting_id | toegevoegde_dienst_id | datum_aanvraag | datum_ingang | datum_uitgevoerd | product_soort_id_oud | product_soort_id_nieuw | id | door_wie | sinds
--------------+-----------------------+----------------+--------------+------------------+----------------------+------------------------+----+------------+---------------------
5 | 167817 | 2007-12-23 | 2007-12-23 | | 5816 | 5817 | 6 | webservice | 2007-12-23 19:55:36
(1 row)
mydb=# \d toegevoegde_dienst_omzetting_history
Did not find any relation named "toegevoegde_dienst_omzetting_history".
mydb=#
And this table is obviously from the pg_catalog.pg_class table as well.
Has anyone any idea what may cause this? I can't find anything in the logs regarding the missing tables. (eg. that someone messes the pg_catalog tables). I disabled the nightly vacuum process but the problem persists.
Could this problem be fixed by inserting the right records back in to in de pg_catalog tables? is there a way to dump the pg_catalog tables?
The pgserver is running on a 4 CPU dell 6850 with 16G of Ram with Fedora Core 4 on a reiserfs filesystem without any problems for years. And nothing has changed on the server side of things.
thanks,
alex.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: postgres 'forgets' table definitions
@ 2008-06-10 13:59 alex <alex.pgsql@kerkhove.net>
parent: alex <alex.pgsql@kerkhove.net>
0 siblings, 2 replies; 5+ messages in thread
From: alex @ 2008-06-10 13:59 UTC (permalink / raw)
To: pgsql-admin
Problem solved!
After a vacuum full verbose analyze of all the pg_* tables everything is fine again.
I switched to a scripted vacuum about 6 months ago because some (history)tables take way too long to vacuum and have static data anyway. But as it turns out I skipped all the pg_ tables since then. So they were not vacuumed for quite some time.
Even without vacuuming, I don't think postgres should ever behave like this, but I'm glad my problem is solved now.
(perhaps this issue is fixed in newer releases, if not someone might want to look in to this pg_* tables/vacuum issue)
alex.
-------- Original Message --------
Subject: [ADMIN] postgres 'forgets' table definitions
From: alex <alex.pgsql@kerkhove.net>
To: pgsql-admin@postgresql.org
Date: Tue Jun 10 2008 13:12:32 GMT+0200
> Hi,
>
> We're running pgsql 7.3.14 (yes I know it's old, an upgrade to 8.3 is planned).
> Since a couple of weeks postgres loses the table definitions from apparently random tables. A 'broken' table actually still works but a \d table gives nothing. Currently The biggest problem is that these broken tables don't appear in the backups.
>
> example:
> mydb=# select * from toegevoegde_dienst_omzetting_history limit 1;
> omzetting_id | toegevoegde_dienst_id | datum_aanvraag | datum_ingang | datum_uitgevoerd | product_soort_id_oud | product_soort_id_nieuw | id | door_wie | sinds
> --------------+-----------------------+----------------+--------------+------------------+----------------------+------------------------+----+------------+---------------------
> 5 | 167817 | 2007-12-23 | 2007-12-23 | | 5816 | 5817 | 6 | webservice | 2007-12-23 19:55:36
> (1 row)
>
> mydb=# \d toegevoegde_dienst_omzetting_history
> Did not find any relation named "toegevoegde_dienst_omzetting_history".
> mydb=#
>
> And this table is obviously from the pg_catalog.pg_class table as well.
>
> Has anyone any idea what may cause this? I can't find anything in the logs regarding the missing tables. (eg. that someone messes the pg_catalog tables). I disabled the nightly vacuum process but the problem persists.
>
> Could this problem be fixed by inserting the right records back in to in de pg_catalog tables? is there a way to dump the pg_catalog tables?
>
> The pgserver is running on a 4 CPU dell 6850 with 16G of Ram with Fedora Core 4 on a reiserfs filesystem without any problems for years. And nothing has changed on the server side of things.
>
> thanks,
> alex.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: postgres 'forgets' table definitions
@ 2008-06-10 15:19 Jaime Casanova <systemguards@gmail.com>
parent: alex <alex.pgsql@kerkhove.net>
1 sibling, 0 replies; 5+ messages in thread
From: Jaime Casanova @ 2008-06-10 15:19 UTC (permalink / raw)
To: alex <alex.pgsql@kerkhove.net>; +Cc: pgsql-admin
On Tue, Jun 10, 2008 at 8:59 AM, alex <alex.pgsql@kerkhove.net> wrote:
> Problem solved!
>
> After a vacuum full verbose analyze of all the pg_* tables everything is fine again.
>
> I switched to a scripted vacuum about 6 months ago because some (history)tables take way too long to vacuum and have static data anyway. But as it turns out I skipped all the pg_ tables since then. So they were not vacuumed for quite some time.
>
> Even without vacuuming, I don't think postgres should ever behave like this, but I'm glad my problem is solved now.
>
vacuum is a need not a choice, specially with such an old version...
http://www.postgresql.org/docs/7.3/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
in 8.3, there are a couple of improvments in that area that makes
wraparound less frecuent but it is still a problem... and now the
autovacuum is integrated so things are a lot better
--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: postgres 'forgets' table definitions
@ 2008-06-10 15:52 Scott Marlowe <scott.marlowe@gmail.com>
parent: alex <alex.pgsql@kerkhove.net>
1 sibling, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2008-06-10 15:52 UTC (permalink / raw)
To: alex <alex.pgsql@kerkhove.net>; +Cc: pgsql-admin
On Tue, Jun 10, 2008 at 7:59 AM, alex <alex.pgsql@kerkhove.net> wrote:
> Problem solved!
>
> Even without vacuuming, I don't think postgres should ever behave like this, but I'm glad my problem is solved now.
>
> (perhaps this issue is fixed in newer releases, if not someone might want to look in to this pg_* tables/vacuum issue)
As Jaime mentioned already, vacuuming is required. However, in
addition to his point about various improvements, newer version will
begin throwing errors as they get closer to txid wraparound, and will
eventually shut down and refuse to run in multi-user mode once it
reaches a certain point.
So, yes, things have improved.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-06-10 15:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-10 10:28 postgres 'forgets' table definitions alex <alex@kerkhove.net>
2008-06-10 11:12 postgres 'forgets' table definitions alex <alex.pgsql@kerkhove.net>
2008-06-10 13:59 ` alex <alex.pgsql@kerkhove.net>
2008-06-10 15:19 ` Jaime Casanova <systemguards@gmail.com>
2008-06-10 15:52 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox