Thread: 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
stillworks 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
someonemesses 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
thepg_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
problemsfor years. And nothing has changed on the server side of things.
thanks,
alex.
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
staticdata anyway. But as it turns out I skipped all the pg_ tables since then. So they were not vacuumed for quite
sometime.
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
stillworks 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
someonemesses 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
thepg_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
problemsfor years. And nothing has changed on the server side of things.
>
> thanks,
> alex.
>
>
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 staticdata anyway. But as it turns out I skipped all the pg_ tables since then. So they were not vacuumed for quite sometime. > > 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
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.
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 datadump 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 whatis causing this increase. Suggestions on the likely problems will be appreciated. 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***
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 datadump 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 whatis 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*** >
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.
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
datadump 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
whatis 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***
>
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 datadump 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 towhat 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*** > > > >
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
--------------------------------------------------+----------
<table-1> | 52869
<table-2> | 31267
<table-3> | 15418
<table-4> | 9693
<table-5> | 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 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
datadump 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
towhat 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***
> >
>
>
Devendra Singh Rawat <Devendra_Rawat@infosys.com> 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 > --------------------------------------------------+---------- > <table-1> | 52869 > <table-2> | 31267 > <table-3> | 15418 > <table-4> | 9693 > <table-5> | 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
Joshua D. Drake wrote: > 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.... >> >> > ...I would also suggest strongly you update to the latest 7.4 dot release. > But as always read all the release notes between your current and your intended version. Usually all that is required for maintenance upgrades is installing new binaries but you can't assume that is always the case. For the 7.4 series, pay special attention to the notes for 7.4.8 and 7.4.11: http://www.postgresql.org/docs/7.4/static/release-7-4-8.html http://www.postgresql.org/docs/7.4/static/release-7-4-11.html Since it sounds like you are doing lots of dump and restore, these issues may not be problematic for you. If possible in your situation, updating to the latest version (8.3.1) is preferable. Cheers, Steve