Re: postgres 'forgets' table definitions

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(dot)pgsql(at)kerkhove(dot)net>
To: pgsql-admin(at)postgresql(dot)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.
>
>