On Jun 21, 2008, at 8:47 AM, hjeancha@xxxxxxxxxxx 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 | -205024658Does 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@xxxxxxxxxxx Give your computer some brain candy! www.distributed.net Team #1828
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
