understand the process of ID wraparound

From: KKreuzer(at)kbtoys(dot)com
To: pgsql-admin(at)postgresql(dot)org
Subject: understand the process of ID wraparound
Date: 2008-09-29 13:40:26
Message-ID: OF58064345.B54854D9-ON852574D3.00472E01-852574D3.004B1C9E@kbtoys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2008-09-29 14:00:19 Re: understand the process of ID wraparound
Previous Message Peter Kovacs 2008-09-29 13:01:49 Re: Do we need vacuuming when tables are regularly dropped?