postgres "forgets" table definitions

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.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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
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.
>
>

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres "forgets" table definitions

On Tue, Jun 10, 2008 at 8:59 AM, alex 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 t=
urns 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 th=
is, 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

--=20
regards,
Jaime Casanova
Soporte y capacitaci=F3n de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres "forgets" table definitions

On Tue, Jun 10, 2008 at 7:59 AM, alex 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.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Disk Space issue

Hi,

We are using PostgreSQL 7.4.5 and facing issues with increasing disk space.

Here is some information about our configuration.

The data dump size is around 3 GB. And it is occupying around 120GB on the =
disk. When we recreate the database from data dump it takes around 3.5GB of=
disk space.
But the database size increases by 1 GB daily though actual data added mayb=
e in KB. We are not able to decipher as to what is causing this increase.

Suggestions on the likely problems will be appreciated.

Regards,
Devendra

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solel=
y=20
for the use of the addressee(s). If you are not the intended recipient, ple=
ase=20
notify the sender by e-mail and delete the original message. Further, you a=
re not=20
to copy, disclose, or distribute this e-mail or its contents to any other p=
erson and=20
any such actions are unlawful. This e-mail may contain viruses. Infosys has=
taken=20
every reasonable precaution to minimize this risk, but is not liable for an=
y damage=20
you may sustain as a result of any virus in this e-mail. You should carry o=
ut your=20
own virus checks before opening the e-mail or attachment. Infosys reserves =
the=20
right to monitor and review the content of all messages sent to or from thi=
s e-mail=20
address. Messages sent to or from this e-mail address may be stored on the=
=20
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Disk Space issue

On Wed, 2008-06-11 at 09:36 +1000, Devendra Singh Rawat wrote:
> Hi,
>
> We are using PostgreSQL 7.4.5 and facing issues with increasing disk space.
>
> Here is some information about our configuration.
>
> The data dump size is around 3 GB. And it is occupying around 120GB on the disk. When we recreate the database from data dump it takes around 3.5GB of disk space.
> But the database size increases by 1 GB daily though actual data added maybe in KB. We are not able to decipher as to what is causing this increase.
>
> Suggestions on the likely problems will be appreciated.

You are likely not vacuuming nearly enough.

I would also suggest strongly you update to the latest 7.4 dot release.

Joshua D. Drake

>
> Regards,
> Devendra
>
> **************** CAUTION - Disclaimer *****************
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
> for the use of the addressee(s). If you are not the intended recipient, please
> notify the sender by e-mail and delete the original message. Further, you are not
> to copy, disclose, or distribute this e-mail or its contents to any other person and
> any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
> every reasonable precaution to minimize this risk, but is not liable for any damage
> you may sustain as a result of any virus in this e-mail. You should carry out your
> own virus checks before opening the e-mail or attachment. Infosys reserves the
> right to monitor and review the content of all messages sent to or from this e-mail
> address. Messages sent to or from this e-mail address may be stored on the
> Infosys e-mail system.
> ***INFOSYS******** End of Disclaimer ********INFOSYS***
>


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Disk Space issue

Hi Joshua,

We used Vacuum Full but were not successful. The reclaimed disk space was o=
nly few MB.
I did not get to which version I need to update my Postgres.

Regards,
Devendra

Devendra Singh Rawat
Infosys Technologies Ltd.
Tel: +61 3 9860 2276 (D)
Fax: +61 3 9860 2501
Email: devendra_rawat@infosys.com
www.infosys.com
Powered by Intellect
Driven by Values


-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Wednesday, 11 June 2008 9:55 AM
To: Devendra Singh Rawat
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Disk Space issue



On Wed, 2008-06-11 at 09:36 +1000, Devendra Singh Rawat wrote:
> Hi,
>
> We are using PostgreSQL 7.4.5 and facing issues with increasing disk spac=
e.
>
> Here is some information about our configuration.
>
> The data dump size is around 3 GB. And it is occupying around 120GB on th=
e disk. When we recreate the database from data dump it takes around 3.5GB =
of disk space.
> But the database size increases by 1 GB daily though actual data added ma=
ybe in KB. We are not able to decipher as to what is causing this increase.
>
> Suggestions on the likely problems will be appreciated.

You are likely not vacuuming nearly enough.

I would also suggest strongly you update to the latest 7.4 dot release.

Joshua D. Drake

>
> Regards,
> Devendra
>
> **************** CAUTION - Disclaimer *****************
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended sol=
ely
> for the use of the addressee(s). If you are not the intended recipient, p=
lease
> notify the sender by e-mail and delete the original message. Further, you=
are not
> to copy, disclose, or distribute this e-mail or its contents to any other=
person and
> any such actions are unlawful. This e-mail may contain viruses. Infosys h=
as taken
> every reasonable precaution to minimize this risk, but is not liable for =
any damage
> you may sustain as a result of any virus in this e-mail. You should carry=
out your
> own virus checks before opening the e-mail or attachment. Infosys reserve=
s the
> right to monitor and review the content of all messages sent to or from t=
his e-mail
> address. Messages sent to or from this e-mail address may be stored on the
> Infosys e-mail system.
> ***INFOSYS******** End of Disclaimer ********INFOSYS***
>


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Disk Space issue

On Wed, 2008-06-11 at 09:58 +1000, Devendra Singh Rawat wrote:
> Hi Joshua,
>
> We used Vacuum Full but were not successful. The reclaimed disk space was only few MB.
> I did not get to which version I need to update my Postgres.

VACUUM FULL may not reclaim your index space if it bloated out. What you
are best to look at is the type of queries are being run. Determine what
is getting bloated and look at modifying how you maintain that relation.

7.4.19 (which hits soon)

Joshua D. Drake


>
> Regards,
> Devendra
>
> Devendra Singh Rawat
> Infosys Technologies Ltd.
> Tel: +61 3 9860 2276 (D)
> Fax: +61 3 9860 2501
> Email: devendra_rawat@infosys.com
> www.infosys.com
> Powered by Intellect
> Driven by Values
>
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: Wednesday, 11 June 2008 9:55 AM
> To: Devendra Singh Rawat
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Disk Space issue
>
>
>
> On Wed, 2008-06-11 at 09:36 +1000, Devendra Singh Rawat wrote:
> > Hi,
> >
> > We are using PostgreSQL 7.4.5 and facing issues with increasing disk space.
> >
> > Here is some information about our configuration.
> >
> > The data dump size is around 3 GB. And it is occupying around 120GB on the disk. When we recreate the database from data dump it takes around 3.5GB of disk space.
> > But the database size increases by 1 GB daily though actual data added maybe in KB. We are not able to decipher as to what is causing this increase.
> >
> > Suggestions on the likely problems will be appreciated.
>
> You are likely not vacuuming nearly enough.
>
> I would also suggest strongly you update to the latest 7.4 dot release.
>
> Joshua D. Drake
>
> >
> > Regards,
> > Devendra
> >
> > **************** CAUTION - Disclaimer *****************
> > This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
> > for the use of the addressee(s). If you are not the intended recipient, please
> > notify the sender by e-mail and delete the original message. Further, you are not
> > to copy, disclose, or distribute this e-mail or its contents to any other person and
> > any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
> > every reasonable precaution to minimize this risk, but is not liable for any damage
> > you may sustain as a result of any virus in this e-mail. You should carry out your
> > own virus checks before opening the e-mail or attachment. Infosys reserves the
> > right to monitor and review the content of all messages sent to or from this e-mail
> > address. Messages sent to or from this e-mail address may be stored on the
> > Infosys e-mail system.
> > ***INFOSYS******** End of Disclaimer ********INFOSYS***
> >
>
>


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Disk Space issue

We ran the SQL to get the table which are consuming maximum space.
The results are as shown:-
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
relname | relpages
--------------------------------------------------+--------- -
| 52869
| 31267
| 15418
| 9693
| 6426


But these only cover around few GB of data.

Regards,
Devendra

-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Wednesday, 11 June 2008 10:06 AM
To: Devendra Singh Rawat
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Disk Space issue



On Wed, 2008-06-11 at 09:58 +1000, Devendra Singh Rawat wrote:
> Hi Joshua,
>
> We used Vacuum Full but were not successful. The reclaimed disk space was=
only few MB.
> I did not get to which version I need to update my Postgres.

VACUUM FULL may not reclaim your index space if it bloated out. What you
are best to look at is the type of queries are being run. Determine what
is getting bloated and look at modifying how you maintain that relation.

7.4.19 (which hits soon)

Joshua D. Drake


>
> Regards,
> Devendra
>
> Devendra Singh Rawat
> Infosys Technologies Ltd.
> Tel: +61 3 9860 2276 (D)
> Fax: +61 3 9860 2501
> Email: devendra_rawat@infosys.com
> www.infosys.com
> Powered by Intellect
> Driven by Values
>
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: Wednesday, 11 June 2008 9:55 AM
> To: Devendra Singh Rawat
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Disk Space issue
>
>
>
> On Wed, 2008-06-11 at 09:36 +1000, Devendra Singh Rawat wrote:
> > Hi,
> >
> > We are using PostgreSQL 7.4.5 and facing issues with increasing disk sp=
ace.
> >
> > Here is some information about our configuration.
> >
> > The data dump size is around 3 GB. And it is occupying around 120GB on =
the disk. When we recreate the database from data dump it takes around 3.5G=
B of disk space.
> > But the database size increases by 1 GB daily though actual data added =
maybe in KB. We are not able to decipher as to what is causing this increas=
e.
> >
> > Suggestions on the likely problems will be appreciated.
>
> You are likely not vacuuming nearly enough.
>
> I would also suggest strongly you update to the latest 7.4 dot release.
>
> Joshua D. Drake
>
> >
> > Regards,
> > Devendra
> >
> > **************** CAUTION - Disclaimer *****************
> > This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended s=
olely
> > for the use of the addressee(s). If you are not the intended recipient,=
please
> > notify the sender by e-mail and delete the original message. Further, y=
ou are not
> > to copy, disclose, or distribute this e-mail or its contents to any oth=
er person and
> > any such actions are unlawful. This e-mail may contain viruses. Infosys=
has taken
> > every reasonable precaution to minimize this risk, but is not liable fo=
r any damage
> > you may sustain as a result of any virus in this e-mail. You should car=
ry out your
> > own virus checks before opening the e-mail or attachment. Infosys reser=
ves the
> > right to monitor and review the content of all messages sent to or from=
this e-mail
> > address. Messages sent to or from this e-mail address may be stored on =
the
> > Infosys e-mail system.
> > ***INFOSYS******** End of Disclaimer ********INFOSYS***
> >
>
>


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Disk Space issue

Devendra Singh Rawat writes:
> We ran the SQL to get the table which are consuming maximum space.
> The results are as shown:-
> SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
> relname | relpages
> --------------------------------------------------+--------- -
> | 52869
> | 31267
> | 15418
> | 9693
> | 6426


> But these only cover around few GB of data.

pg_class.relpages is only up-to-date as of the last VACUUM.
There's already good reason to suspect that you're not vacuuming
enough; maybe some big tables are not getting vacuumed at all.

I'd suggest having a look into the data directory to see for yourself
which are the big files. The documentation might help you:
http://www.postgresql.org/docs/8.3/static/storage.html
(IIRC, all of this except the material on tablespaces will apply
to 7.4.)

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Disk Space issue

Hi,
I think you have a lot DML operation in your database and you are not
vacuuming your database . So try to execute command vacuum analyze
<