pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuumdb not enough stack items
5+ messages / 3 participants
[nested] [flat]
* vacuumdb not enough stack items
@ 2008-06-21 13:47 hjeancha@freesurf.fr
0 siblings, 2 replies; 5+ messages in thread
From: hjeancha@freesurf.fr @ 2008-06-21 13:47 UTC (permalink / raw)
To: pgsql-admin
Hi,
i use postgresql version7.4.7 on i386-pc-linux-gnu, autovacuum is
configured to run on this database.
But recently, we lost data for a database, we were able to connect the
database but we couldn't see any table anymore.
I suspected a transaction ID wraparound, and to fix it, i just
imported data from a dump file.
I tried to execute the following query:
SELECT datname, age(datfrozenxid) FROM pg_database where age(datfrozenxid)<0;
template1=# SELECT datname, age(datfrozenxid) FROM pg_database where
age(datfrozenxid)<0;
datname | age
------------------------------------------+-------------
template0 | -597653514
ez_info1 | -205024652
XtDB | -205024658
Does the negative value of age means that a transaction ID wraparound
has already occured?
Doing a manual vacuumdb on each database raise an error message saying:
vacuumdb: vacuuming of database "ez_info1" failed: ERREUR: not enough
stack items
Have you an idea about what is happening,
Thank you for any suggestion.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuumdb not enough stack items
@ 2008-06-21 16:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: hjeancha@freesurf.fr
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-06-21 16:37 UTC (permalink / raw)
To: hjeancha@freesurf.fr; +Cc: pgsql-admin
hjeancha@freesurf.fr writes:
> i use postgresql version7.4.7 on i386-pc-linux-gnu, autovacuum is
> configured to run on this database.
Hmm ... in theory autovacuum should have kept you out of trouble,
if it was working properly. Were you keeping an eye on its log
output?
> Doing a manual vacuumdb on each database raise an error message saying:
> vacuumdb: vacuuming of database "ez_info1" failed: ERREUR: not enough
> stack items
What probably happened was that this error blocked autovacuum from
completing an anti-wraparound vacuum :-(
[ checks code... ] This appears to indicate a corrupted btree index.
I don't believe we've ever heard of this error occurring in the field
before, so it might be interesting to inquire into exactly what happened
to the index --- but if you just want to get out of trouble I'd suggest
* VACUUM VERBOSE as superuser.
Watch the output to determine which table it fails on.
* REINDEX that table.
* VACUUM VERBOSE again.
This should solve the problem fairly painlessly as long as the
corrupted index isn't on a system catalog. If it is, see the
man page for REINDEX about how to recover.
I'm not sure if vacuuming will get your data back at this point,
but it's certainly worth a try.
Somewhere in here you should think about moving to a more modern
Postgres release. 8.1 and later wouldn't have let you keep going
to the point of wraparound with a critical problem like this.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuumdb not enough stack items
@ 2008-06-21 17:50 Decibel! <decibel@decibel.org>
parent: hjeancha@freesurf.fr
1 sibling, 1 reply; 5+ messages in thread
From: Decibel! @ 2008-06-21 17:50 UTC (permalink / raw)
To: hjeancha@freesurf.fr; +Cc: pgsql-admin
On Jun 21, 2008, at 8:47 AM, hjeancha@freesurf.fr wrote:
> i use postgresql version7.4.7 on i386-pc-linux-gnu, autovacuum is
> configured to run on this database.
> But recently, we lost data for a database, we were able to connect
> the database but we couldn't see any table anymore.
> I suspected a transaction ID wraparound, and to fix it, i just
> imported data from a dump file.
> I tried to execute the following query:
> SELECT datname, age(datfrozenxid) FROM pg_database where age
> (datfrozenxid)<0;
> template1=# SELECT datname, age(datfrozenxid) FROM pg_database
> where age(datfrozenxid)<0;
> datname | age
> ------------------------------------------+-------------
> template0 | -597653514
> ez_info1 | -205024652
> XtDB | -205024658
>
>
> Does the negative value of age means that a transaction ID
> wraparound has already occured?
That would be my guess.
> Doing a manual vacuumdb on each database raise an error message
> saying:
> vacuumdb: vacuuming of database "ez_info1" failed: ERREUR: not
> enough stack items
That's probably a bug in vacuum, but even so, you'll want to re-
initdb if you want to just pull back in from a pg_dump.
If you can take the downtime to do a pg_dump/restore, you should
really upgrade to a more recent version. You'll get integrated
autovacuum and code that makes wraparound impossible. I would not
expect you to have any major code issues going to 8.0 or 8.1. Of
course going to 8.3 would be better, but you could run into some
compatibility issues. You'll get a performance gain to boot.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuumdb not enough stack items
@ 2008-06-22 08:54 hjeancha@freesurf.fr
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: hjeancha@freesurf.fr @ 2008-06-22 08:54 UTC (permalink / raw)
To: pgsql-admin
Tom Lane <tgl@sss.pgh.pa.us> a écrit :
> hjeancha@freesurf.fr writes:
>> i use postgresql version7.4.7 on i386-pc-linux-gnu, autovacuum is
>> configured to run on this database.
>
> Hmm ... in theory autovacuum should have kept you out of trouble,
> if it was working properly. Were you keeping an eye on its log
> output?
>
>> Doing a manual vacuumdb on each database raise an error message saying:
>> vacuumdb: vacuuming of database "ez_info1" failed: ERREUR: not enough
>> stack items
>
> What probably happened was that this error blocked autovacuum from
> completing an anti-wraparound vacuum :-(
>
> [ checks code... ] This appears to indicate a corrupted btree index.
> I don't believe we've ever heard of this error occurring in the field
> before, so it might be interesting to inquire into exactly what happened
> to the index --- but if you just want to get out of trouble I'd suggest
> * VACUUM VERBOSE as superuser.
> Watch the output to determine which table it fails on.
> * REINDEX that table.
> * VACUUM VERBOSE again.
> This should solve the problem fairly painlessly as long as the
> corrupted index isn't on a system catalog. If it is, see the
> man page for REINDEX about how to recover.
>
> I'm not sure if vacuuming will get your data back at this point,
> but it's certainly worth a try.
>
> Somewhere in here you should think about moving to a more modern
> Postgres release. 8.1 and later wouldn't have let you keep going
> to the point of wraparound with a critical problem like this.
>
> 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
>
Not really, because i was not in charge of this database until now.
I found that in file /etc/postgresql/postmaster.conf, autovacuum is set to NO.
and stats_start_collector = false , stats_row_level = false in
postgresql.conf file.
I'm not really sure if autovacuum is really running.
I found old log file from autovacuum on the server, but today's log
file is missing.
I will give it a try and let you know, but for now, other databases
with age(datfrozenxid) with negative values seems to be ok.
From what you are saying, i understand that it's urgent to
1) VACUUM VERBOSE and REINDEX
2) if it's not OK make an initdb and recreate databases if possible in
postgresql minimum release 8.1.
I'll try to convince the application administrator that this a
priority to upgrade recent version of postgresql.
Thanks a lot
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuumdb not enough stack items
@ 2008-06-23 11:59 hjeancha@freesurf.fr
parent: Decibel! <decibel@decibel.org>
0 siblings, 0 replies; 5+ messages in thread
From: hjeancha@freesurf.fr @ 2008-06-23 11:59 UTC (permalink / raw)
To: Decibel! <decibel@decibel.org>; +Cc: pgsql-admin
Decibel! <decibel@decibel.org> a écrit :
> On Jun 21, 2008, at 8:47 AM, hjeancha@freesurf.fr wrote:
>> i use postgresql version7.4.7 on i386-pc-linux-gnu, autovacuum is
>> configured to run on this database.
>> But recently, we lost data for a database, we were able to connect
>> the database but we couldn't see any table anymore.
>> I suspected a transaction ID wraparound, and to fix it, i just
>> imported data from a dump file.
>> I tried to execute the following query:
>> SELECT datname, age(datfrozenxid) FROM pg_database where age
>> (datfrozenxid)<0;
>> template1=# SELECT datname, age(datfrozenxid) FROM pg_database
>> where age(datfrozenxid)<0;
>> datname | age
>> ------------------------------------------+-------------
>> template0 | -597653514
>> ez_info1 | -205024652
>> XtDB | -205024658
>>
>>
>> Does the negative value of age means that a transaction ID
>> wraparound has already occured?
>
> That would be my guess.
>
>> Doing a manual vacuumdb on each database raise an error message saying:
>> vacuumdb: vacuuming of database "ez_info1" failed: ERREUR: not
>> enough stack items
>
> That's probably a bug in vacuum, but even so, you'll want to re-
> initdb if you want to just pull back in from a pg_dump.
>
> If you can take the downtime to do a pg_dump/restore, you should
> really upgrade to a more recent version. You'll get integrated
> autovacuum and code that makes wraparound impossible. I would not
> expect you to have any major code issues going to 8.0 or 8.1. Of
> course going to 8.3 would be better, but you could run into some
> compatibility issues. You'll get a performance gain to boot.
> --
> Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
>
Hello,
We plan to upgrade to postgresql release 8.1 very soon.
But for now, i found that there is a problem with vacuum.
Doing a vaccuum verbose analyze manually on a database,
we got the error "primary keys constraint broken by duplicate key
«pg_statistic_relid_att_index».
As suggested on this list it's seems to be an index problem.
That could explain why we got "transaction wraparound" for some databases that
were never really vacuumed because of duplicate key error in
«pg_statistic_relid_att_index».
I red to solve this problem, one must "delete from pg_statistic" then
reindex database.
I just want to know:
1) if it's safe to do this, because data is still accessible even for
databases with transaction wraparound xid.
If This can fix the «pg_statistic_relid_att_index», i will be able to
vacuum cleanly every databases.
2)
The age(datfrozenxid) for other databases are
ez_modele | 1074290700
ez_modele2 | 1074290002
Does that means, if there are not a lot of transactions, transaction
wraparound xid won't happen until reaching the limit of 2 billions
transactions.
Thank you,
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-06-23 11:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-21 13:47 vacuumdb not enough stack items hjeancha@freesurf.fr
2008-06-21 16:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-22 08:54 ` hjeancha@freesurf.fr
2008-06-21 17:50 ` Decibel! <decibel@decibel.org>
2008-06-23 11:59 ` hjeancha@freesurf.fr
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