Обсуждение: postgres 'forgets' table definitions

Поиск
Список
Период
Сортировка

postgres 'forgets' table definitions

От
alex
Дата:
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.


Re: postgres 'forgets' table definitions

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

Re: postgres 'forgets' table definitions

От
"Jaime Casanova"
Дата:
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

Re: postgres 'forgets' table definitions

От
"Scott Marlowe"
Дата:
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.

Disk Space issue

От
Devendra Singh Rawat
Дата:
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***

Re: Disk Space issue

От
"Joshua D. Drake"
Дата:

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


Re: Disk Space issue

От
Devendra Singh Rawat
Дата:
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***
>


Re: Disk Space issue

От
"Joshua D. Drake"
Дата:

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


Re: Disk Space issue

От
Devendra Singh Rawat
Дата:
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***
> >
>
>


Re: Disk Space issue

От
Tom Lane
Дата:
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

Re: Disk Space issue

От
Steve Crawford
Дата:
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