That is expected as template0 is read-only and so VACUUM will not work
on it.
Isn’t template1 the same ? I’m not seeing that behavior on that one
Should I suspect something fishy going on ?
Not sure without more information.
1) Can you be specific about your database references? 'That database'
is open-ended.
“That database” = the database that’s been causing wrap-around problems since yesterday. It’s called “public”
2) Show the actual numbers from your xid queries. Both the raw values
and the age() transformed ones.
backend> SELECT datname, datfrozenxid, age(datfrozenxid) FROM pg_database;
1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid (typeid = 28, len = 4, typmod = -1, byval = t)
3: age (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "template1" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003766" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000394" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "postgres" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003765" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000395" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "public" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "1717520404" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "2146483756" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "xxxx" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003760" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000400" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "osmtest" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003762" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000398" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "template0" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3732096533" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "131907627" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "drupal_prod" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003758" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000402" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "anta" (typeid = 19, len = 64, typmod = -1, byval = f)
2: datfrozenxid = "3814003756" (typeid = 28, len = 4, typmod = -1, byval = t)
3: age = "50000404" (typeid = 23, len = 4, typmod = -1, byval = t)
----
3) What are your configuration parameters for the variables mentioned in
the section below?:
https://www.postgresql.org/docs/9.1/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
Should be the default values, I’ve never changed them.
backend> show vacuum_freeze_min_age
1: vacuum_freeze_min_age (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: vacuum_freeze_min_age = "50000000" (typeid = 25, len = -1, typmod = -1, byval = f)
backend> show vacuum_freeze_table_age
1: vacuum_freeze_table_age (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: vacuum_freeze_table_age = "150000000" (typeid = 25, len = -1, typmod = -1, byval = f)
backend> show autovacuum_freeze_max_age
1: autovacuum_freeze_max_age (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_freeze_max_age = "200000000" (typeid = 25, len = -1, typmod = -1, byval = f)
backend> show autovacuum_vacuum_threshold
1: autovacuum_vacuum_threshold (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_vacuum_threshold = "50" (typeid = 25, len = -1, typmod = -1, byval = f)
backend> show autovacuum_max_workers
1: autovacuum_max_workers (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_max_workers = "3" (typeid = 25, len = -1, typmod = -1, byval = f)
backend> show autovacuum_vacuum_scale_factor
1: autovacuum_vacuum_scale_factor (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_vacuum_scale_factor = "0.2" (typeid = 25, len = -1, typmod = -1, byval = f)
backend> show autovacuum_vacuum_cost_delay
1: autovacuum_vacuum_cost_delay (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_vacuum_cost_delay = "20ms" (typeid = 25, len = -1, typmod = -1, byval = f)
backend> show autovacuum_vacuum_cost_limit
1: autovacuum_vacuum_cost_limit (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: autovacuum_vacuum_cost_limit = "-1" (typeid = 25, len = -1, typmod = -1, byval = f)
4) If you want to get an idea of fast xid's are being created a quick
and dirty way is from here:
https://www.postgresql.org/docs/9.1/static/functions-info.html
txid_current() bigint get current transaction ID
Now if you do select txid_current() outside a transaction it will create
an xid on its own, still if you repeat it over some interval of time you
will get an idea of how fast the server is going through xid's.
Well I’m now at a point where that’s not even possible, I have consistently run into the following
• I vacuum enough table to get back a couple dozen transactions below the 1M mark
