Postgres Pro
Downloads
Home   >   mailing lists

understand the process of ID wraparound - Mailing list pgsql-admin

From KKreuzer@kbtoys.com
Subject understand the process of ID wraparound
Date
Msg-id OF58064345.B54854D9-ON852574D3.00472E01-852574D3.004B1C9E@kbtoys.com
Whole thread Raw
Responses Re: understand the process of ID wraparound  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-admin
Tree view

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

pgsql-admin by date:

Previous
From: "Peter Kovacs"
Date:
Subject: Re: Do we need vacuuming when tables are regularly dropped?
Next
From: Tom Lane
Date:
Subject: Re: understand the process of ID wraparound