Обсуждение: autovacuum, xid wraparound, pg_database column values

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)

"shakahshakah@gmail.com" <shakahshakah@gmail.com> writes:
> 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.
> ...
> I don't see numbers larger than 1 billion in my pg_database columns,
> though.

The pg_database columns only update when you do a database-wide VACUUM,
which is something that autovacuum only undertakes when it sees that
the age values are getting large.  I forget the exact threshold, but
at ~28M transactions you are certainly a long way away.

            regards, tom lane