Actual age() of cleanly vacuumed db

Started by Wayne Schroederabout 20 years ago1 messagesgeneral
Jump to latest
#1Wayne Schroeder
raz@positivenetworks.net

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