Cannot use a standalone backend to VACUUM in "postgres"" - Mailing list pgsql-general

From Manuel Sugawara
Subject Cannot use a standalone backend to VACUUM in "postgres""
Date
Msg-id m3prt1yvar.fsf@conexa.fciencias.unam.mx
Whole thread Raw
Responses Re: Cannot use a standalone backend to VACUUM in "postgres""  (Alvaro Herrera <alvherre@commandprompt.com>)
Re: Cannot use a standalone backend to VACUUM in "postgres""  (Alvaro Herrera <alvherre@commandprompt.com>)
List 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

pgsql-general by date:

Previous
From: "Stephen Denne"
Date:
Subject: Re: select distinct and index usage
Next
From: Alvaro Herrera
Date:
Subject: Re: Cannot use a standalone backend to VACUUM in "postgres""