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>
