On Sat, Mar 20, 2021 at 12:07 PM Luca Ferrari <fluca1978@xxxxxxxxx> wrote:
> I suspect freezing is doing it "totally" for a idatistemplate
> database, even if I don't understand why.
I can confirm that freezing a template database is done by means of
setting it age to zero. I've set the datistempalte flag for testdb and
reissued a wraparong (I'm torturing my postgresql!), and after a
vacuum its age went to zero.
I'm not able to find this behavior in the documentation however, and
still don't understand why a template database should have a different
behavior (at least, I can imagine only to reduce the future workload
of vacuuming a template database).
Here it is, again, what I did in single user mode:
backend> select datname, age( datfrozenxid), current_setting(
'vacuum_freeze_min_age' ) from pg_database;
1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
2: age (typeid = 23, len = 4, typmod = -1, byval = t)
3: current_setting (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: datname = "postgres" (typeid = 19, len = 64, typmod
= -1, byval = f)
2: age = "2138438218" (typeid = 23, len = 4, typmod = -1, byval = t)
3: current_setting = "50000000" (typeid = 25, len =
-1, typmod = -1, byval = f)
----
1: datname = "backupdb" (typeid = 19, len = 64, typmod
= -1, byval = f)
2: age = "2138438218" (typeid = 23, len = 4, typmod = -1, byval = t)
3: current_setting = "50000000" (typeid = 25, len =
-1, typmod = -1, byval = f)
----
1: datname = "template1" (typeid = 19, len = 64, typmod
= -1, byval = f)
2: age = "2138438218" (typeid = 23, len = 4, typmod = -1, byval = t)
3: current_setting = "50000000" (typeid = 25, len =
-1, typmod = -1, byval = f)
----
1: datname = "template0" (typeid = 19, len = 64, typmod
= -1, byval = f)
2: age = "2138438218" (typeid = 23, len = 4, typmod = -1, byval = t)
3: current_setting = "50000000" (typeid = 25, len =
-1, typmod = -1, byval = f)
----
1: datname = "testdb" (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2138438218" (typeid = 23, len = 4, typmod = -1, byval = t)
3: current_setting = "50000000" (typeid = 25, len =
-1, typmod = -1, byval = f)
----
1: datname = "pgbench" (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2138438218" (typeid = 23, len = 4, typmod = -1, byval = t)
3: current_setting = "50000000" (typeid = 25, len =
-1, typmod = -1, byval = f)
----
backend> set vacuum_freeze_min_age to 1234
backend> vacuum
WARNING: database "backupdb" must be vacuumed within 9045429 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM
in that database.
You might also need to commit or roll back old prepared
transactions, or drop stale replication slots.
backend>
and here it is the situation after a restart:
testdb=> select datname, age( datfrozenxid ) from pg_database;
datname | age
-----------+----------
postgres | 1234
backupdb | 50000000
template1 | 0
template0 | 0
testdb | 0
pgbench | 50000000
