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-20 11:07:00 |
Message-ID: | CAKoxK+5U+CuHjC=xsPKrG4CrEdXYK=HdhxmO-d5KeT5cmdtF9A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Mar 18, 2021 at 12:14 PM Luca Ferrari <fluca1978(at)gmail(dot)com> wrote:
> testdb=> select datname, datfrozenxid, age(datfrozenxid) from pg_database;
> datname | datfrozenxid | age
> -----------+--------------+----------
> postgres | 3318163526 | 50000002
> backupdb | 3318163526 | 50000002
> template1 | 3368163526 | 2
> template0 | 3368163526 | 2
> testdb | 3318163526 | 50000002
> pgbench | 3318163526 | 50000002
>
I did it again: I provoked another wraparound and entered the single
user mode to vacuum. This is the situation before:
backend> select age(datfrozenxid), datname from pg_database
1: age (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname (typeid = 19, len = 64, typmod = -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "postgres" (typeid = 19, len = 64, typmod
= -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "backupdb" (typeid = 19, len = 64, typmod
= -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "template1" (typeid = 19, len = 64, typmod
= -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "template0" (typeid = 19, len = 64, typmod
= -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "testdb" (typeid = 19, len = 64, typmod = -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "pgbench" (typeid = 19, len = 64, typmod = -1, byval = f)
backend> vacuum
2021-03-20 11:54:44.878 CET [87179] WARNING: database "backupdb" must
be vacuumed within 1000000 transactions
2021-03-20 11:54:44.878 CET [87179] 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.
and after the vacuum I was able to start other in normal mode, without
having to vacuum again another database as suggested by the HINT.
However, templates have a zero age instead of the other databases:
% psql -U luca -c "SELECT age(datfrozenxid), datname from pg_database;" testdb
age | datname
----------+-----------
50000000 | postgres
50000000 | backupdb
0 | template1
0 | template0
50000000 | testdb
50000000 | pgbench
I suspect freezing is doing it "totally" for a idatistemplate
database, even if I don't understand why.
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Clarke | 2021-03-20 15:51:48 | More than one UNIQUE key when matching items.. |
Previous Message | Frank Millman | 2021-03-20 07:30:25 | Re: SELECT is faster on SQL Server |