I'm trying to make sense of Section 22.1.3 ("Preventing transaction ID
wraparound failures") in the on-line docs to make sure the DB's
autovacuum settings are adequate and are having an effect.
The docs state:
"The age column measures the number of transactions from the cutoff
XID to the current transaction's XID."
and:
"With the standard freezing policy, the age column will start at one
billion for a freshly-vacuumed database."
I don't see numbers larger than 1 billion in my pg_database columns,
though. Can anyone offer any information or pointers to a good on-line
explanation of vacuuming? The database in question is doing about 3M
inserts/day (in about 500K transactions/day) and has been up for a
little more than 3 months.
The database config:
postgres@rs02:~$ egrep autovacuum /data02/pgsql/data/postgresql.conf
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 120 # time between autovacuum runs,
in secs
#autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
#autovacuum_analyze_threshold = 500 # min # of tuple updates before
#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
#autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
postgres@rs02:~$ /usr/local/pgsql/bin/psql reporting
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
reporting=# select version() ;
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.0.2 20051125 (Red Hat 4.0.2-8)
(1 row)
reporting=# select datname, datvacuumxid, datfrozenxid from pg_database
;
datname | datvacuumxid | datfrozenxid
-----------+--------------+--------------
postgres | 499 | 499
reporting | 499 | 499
template1 | 499 | 499
template0 | 499 | 499
(4 rows)
reporting=# SELECT datname, age(datfrozenxid) FROM pg_database ;
datname | age
-----------+----------
postgres | 27995112
reporting | 27995112
template1 | 27995112
template0 | 27995112
(4 rows)
