From: | Luca Ferrari <fluca1978(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: questions about wraparound |
Date: | 2021-03-22 07:56:46 |
Message-ID: | CAKoxK+5tybjan_fdDifEFEpHuwvjZdk7r3rH2AgmFim45DYohQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Mar 20, 2021 at 12:07 PM Luca Ferrari <fluca1978(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Kyotaro Horiguchi | 2021-03-22 08:12:16 | Re: PITR for an only object in postgres |
Previous Message | Michael Paquier | 2021-03-22 07:44:52 | Re: SV: Log files polluted with permission denied error messages after every 10 seconds |