We have a PostgreSQL 8.2.6 installation running for about six-months
now. There was a lot of log entries saying (sometimes 10 or more in
just one second):
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
(actually it was in Spanish but I think that's irrelevant).
The funny thing is that there was no open transactions, even after
restarting the cluster the same message was logged. Today, the
database stopped working as expected:
ERROR: database is shut down to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to VACUUM in "postgres"
So, the postmaster was stopped to follow the hint but even in
stand-alone mode postgres keeps saying:
WARNING: database "postgres" must be vacuumed within 999805 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
Every time vacuum is run the number decreases by one but after a few
runs I still cannot access the cluster :-(. (My plan was to take a
pg_dumpall and then re-init the cluster.)
Attached is the output of:
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';
SELECT datname, age(datfrozenxid) FROM pg_database;
pg_controldata says:
pg_control version number: 822
Catalog version number: 200611241
Database system identifier: 5040396405114363383
Database cluster state: in production
pg_control last modified: Mon 07 Apr 2008 09:22:19 PM CDT
Current log file ID: 33
Next log file segment: 91
Latest checkpoint location: 21/5A8EC824
Prior checkpoint location: 21/5A8C8CDC
Latest checkpoint's REDO location: 21/5A8EC824
Latest checkpoint's UNDO location: 0/0
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/2280224912
Latest checkpoint's NextOID: 103405
Latest checkpoint's NextMultiXactId: 64513935
Latest checkpoint's NextMultiOffset: 154155767
Time of latest checkpoint: Mon 07 Apr 2008 09:20:54 PM CDT
Minimum recovery ending location: 0/0
Maximum data alignment: 4
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Date/time type storage: floating-point numbers
Maximum length of locale name: 128
LC_COLLATE: es_MX.ISO-8859-1
LC_CTYPE: es_MX.ISO-8859-1
Please let me know if there is more information needed.
Regards,
Manuel.