Cannot use a standalone backend to VACUUM in "postgres""

From: Manuel Sugawara <masm(at)fciencias(dot)unam(dot)mx>
To: pgsql-general(at)postgresql(dot)org
Subject: Cannot use a standalone backend to VACUUM in "postgres""
Date: 2008-04-08 01:44:44
Message-ID: m3prt1yvar.fsf@conexa.fciencias.unam.mx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Attachment Content-Type Size
selects text/plain 10.2 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2008-04-08 01:53:12 Re: Cannot use a standalone backend to VACUUM in "postgres""
Previous Message Stephen Denne 2008-04-08 01:11:50 Re: select distinct and index usage