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.

backend> SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';
user=,db=WARNING:  base de datos ╴postgres╴ debe ser limpiada dentro de 998972 
transacciones
user=,db=HINT:  Para evitar que la base de datos se desactive, ejecute VACUUM 
en toda la base de datos ╴postgres╴


1: relname      (typeid = 19, len = 64, typmod = -1, byval = f)
2: age  (typeid = 23, len = 4, typmod = -1, byval = t)
                        ----
1: relname = "sql_sizing"       (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000063"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                ----
1: relname = "sql_sizing_profiles"      (typeid = 19, len = 64, typmod = -1, 
byval = f)
2: age = "100000061"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                        ----
1: relname = "sql_features"     (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000059"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                ----
1: relname = "sql_implementation_info"  (typeid = 19, len = 64, typmod = -1, 
byval = f)
2: age = "100000057"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                        ----
1: relname = "pg_authid"        (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484675"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                ----
1: relname = "sql_languages"    (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000053"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
        ----
1: relname = "sql_packages"     (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000051"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                ----
1: relname = "sql_parts"        (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000049"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                        ----
1: relname = "pg_statistic"     (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000047"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
----
1: relname = "pg_type"  (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000045"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                        ----
1: relname = "pg_attribute"     (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000044"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                ----
1: relname = "pg_proc"  (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000043"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                        ----
1: relname = "pg_class" (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000041"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                ----
1: relname = "pg_autovacuum"    (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000040"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                        ----
1: relname = "pg_attrdef"       (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000039"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                ----
1: relname = "pg_constraint"    (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000037"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
        ----
1: relname = "pg_inherits"      (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000035"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                ----
1: relname = "pg_index" (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000034"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                        ----
1: relname = "pg_operator"      (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000033"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
----
1: relname = "pg_opclass"       (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000032"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                        ----
1: relname = "pg_am"    (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000031"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                ----
1: relname = "pg_amop"  (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000030"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                        ----
1: relname = "pg_amproc"        (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000029"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                ----
1: relname = "pg_language"      (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000028"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                        ----
1: relname = "pg_largeobject"   (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000027"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                ----
1: relname = "pg_aggregate"     (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000026"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
        ----
1: relname = "pg_rewrite"       (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000025"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                ----
1: relname = "pg_trigger"       (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000023"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                        ----
1: relname = "pg_listener"      (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000022"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
----
1: relname = "pg_description"   (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000021"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                        ----
1: relname = "pg_cast"  (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000019"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                ----
1: relname = "pg_namespace"     (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000018"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                        ----
1: relname = "pg_conversion"    (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000017"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                ----
1: relname = "pg_depend"        (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "100000016"    (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                        ----
1: relname = "pg_tablespace"    (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484675"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                ----
1: relname = "pg_pltemplate"    (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484675"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
        ----
1: relname = "pg_shdepend"      (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484675"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                ----
1: relname = "pg_shdescription" (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484675"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                        ----
1: relname = "pg_database"      (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484675"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
----
1: relname = "pg_auth_members"  (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484675"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                        ----



-------------------------------------------------------------









backend>SELECT datname, age(datfrozenxid) FROM pg_database;
user=,db=WARNING:  base de datos ╴postgres╴ debe ser limpiada dentro de 998969 
transacciones
user=,db=HINT:  Para evitar que la base de datos se desactive, ejecute VACUUM 
en toda la base de datos ╴postgres╴
1: datname      (typeid = 19, len = 64, typmod = -1, byval = f)
2: age  (typeid = 23, len = 4, typmod = -1, byval = t)
                        ----
1: datname = "postgres" (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484678"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                ----
1: datname = "alertas"  (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484678"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                        ----
1: datname = "prueba"   (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484678"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                ----
1: datname = "template1"        (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484678"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                        ----
1: datname = "template0"        (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484678"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                ----
1: datname = "pruebasfoo"       (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146484678"   (typeid = 23, len = 4, typmod = -1, byval = t)
                                                                                
                                                                                
        ----
                                                                                
                                                                                
        backend> 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to