I am hoping someone can help a novice understand the process of ID
wraparound, I have read many of the articles
on the web but don't understand why my age(datfrozenxid) never gets reset.
I am not sure if I even have a
problem, just trying to be proactive.
First the details:
select version() ;
"PostgreSQL 8.2.6 on powerpc-ibm-aix5.2.0.0, compiled by GCC gcc (GCC)
4.0.0"
show vacuum_freeze_min_age;
"100,000,000"
show autovacuum_freeze_max_age;
"200,000,000"
show autovacuum;
"off"
SELECT datname, age(datfrozenxid) FROM pg_database;
"postgres" 31041670
"dprodxml" 31041670
"dflash" 31041670
"pg_dprodcca" 31041670
"template1" 31041670
"template0" 31041670
"dstorens" 31041670
"dprod360" 31041670
We run a vacuum every morning at 2:45 am: vacuumdb --all --analyze
--echo
vacuumdb: vacuuming database "postgres"
SELECT datname FROM pg_database WHERE datallowconn;
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "dprodxml"
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "dflash"
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "pg_dprodcca"
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "template1"
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "dstorens"
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "dprod360"
VACUUM ANALYZE;
VACUUM
I run the query "SELECT datname, age(datfrozenxid) FROM pg_database;"
every morning with the
values continue to rise.
age(datfrozenxid)
9/24 27,280,414
9/25 27,688,967
9/26 28,166,896
9/29 31,040,346
If someone could help me understand the process, it would be greatly
appreciated.
Keith Kreuzer
ext 3424
