Re: questions about wraparound - Mailing list pgsql-general

On Sat, Mar 20, 2021 at 12:07 PM Luca Ferrari <fluca1978@gmail.com> 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