Actual age() of cleanly vacuumed db

From: Wayne Schroeder <raz(at)positivenetworks(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Actual age() of cleanly vacuumed db
Date: 2006-03-21 14:44:21
Message-ID: 44201145.6050205@positivenetworks.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I see in the documentation that when you vacuum a db, the "SELECT
datname, age(datfrozenxid) FROM pg_database" query should show "one
billion". When I do vacuums, I end up seeing things much closer to
pow(2,30). Is this the actual 'clean start' age?

I was wanting to start using the following query:

SELECT datname, age(datfrozenxid)-pow(2,30) as transactions FROM
pg_database where datallowconn = true having
(age(datfrozenxid)-pow(2,30)) > 500000000;

This would be placed in our monitoring suite to catch any lack of
vacuuming if it were to happen some how (some how the cron job gets
removed for instance). Is this logic sound?

After a fresh vacuum on a db the query

"SELECT datname, age(datfrozenxid)-pow(2,30) as transactions FROM
pg_database where datallowconn = true"

returns values in the low thousands -- like 1152 for the db I actually
vacuumed.

Wayne

Browse pgsql-general by date

  From Date Subject
Next Message Wes 2006-03-21 14:53:33 ERROR: end-of-copy marker corrupt
Previous Message Larry White 2006-03-21 14:41:01 Re: passing parameters to a trigger function (solved)