wraparound test

gistfile1.txt

https://www.postgresql.fastware.com/blog/how-to-fix-transaction-wraparound-in-postgresql


wget https://github.com/postgres/postgres/archive/697f8d266cfb33409f7ccf3319f4448477066329.zip
unzip 697f8d266cfb33409f7ccf3319f4448477066329.zip
cd postgresql-16
./configure --prefix /opt/16/usr/local --enable-tap-tests
cd /var/lib/postgresql/postgres/postgres-16/src/test/modules/xid_wraparound
make check PG_TEST_EXTRA='xid_wraparound'

# +++ tap check in src/test/modules/xid_wraparound +++
t/001_emergency_vacuum.pl .. ok
t/002_limits.pl ............ ok
t/003_wraparounds.pl ....... ok
All tests successful.
Files=3, Tests=11, 90 wallclock secs ( 0.01 usr  0.01 sys +  1.78 cusr  1.10 csys =  2.90 CPU)
Result: PASS

make install

#ensure extension copied
postgres@pg:~/postgres/postgres-16/src/test/modules/xid_wraparound$ find /opt/16/usr/local -name 'xid*'
/opt/16/usr/local/share/postgresql/extension/xid_wraparound--1.0.sql
/opt/16/usr/local/share/postgresql/extension/xid_wraparound.control
/opt/16/usr/local/include/postgresql/server/utils/xid8.h
/opt/16/usr/local/lib/postgresql/xid_wraparound.so


postgres@pg:/tmp$ psql
psql (17devel)
Type "help" for help.

postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# create extension xid_wraparound;
CREATE EXTENSION
postgres=# \dx
                     List of installed extensions
      Name      | Version |   Schema   |         Description
----------------+---------+------------+------------------------------
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
 xid_wraparound | 1.0     | public     | Tests for XID wraparound
(2 rows)



postgres=# \df *consume*
                              List of functions
 Schema |        Name        | Result data type | Argument data types | Type
--------+--------------------+------------------+---------------------+------
 public | consume_xids       | xid8             | nxids bigint        | func
 public | consume_xids_until | xid8             | targetxid xid8      | func
(2 rows)



postgres=# create table t(col1 int);
CREATE TABLE
postgres=# insert into t select 1;
INSERT 0 1
postgres=# ^Z
[1]+  Stopped                 psql
postgres@pg:/tmp$ psql
psql (17devel)
Type "help" for help.

postgres=# begin transaction isolation level repeatable read;
BEGIN
postgres=*# select * from t;
 col1
------
    1
(1 row)

postgres=*# select txid_current();
 txid_current
--------------
          753
(1 row)

postgres=*# ^Z
[2]+  Stopped                 psql
postgres@pg:/tmp$ bg %2
[2]+ psql &


[2]+  Stopped                 psql
postgres@pg:/tmp$ fg %1
psql

postgres=# select consume_xids(300000000);
NOTICE:  consumed 10000055 / 300000000 XIDs, latest 0:10000809
NOTICE:  consumed 20000864 / 300000000 XIDs, latest 0:20001618
NOTICE:  consumed 30001673 / 300000000 XIDs, latest 0:30002427
NOTICE:  consumed 40002482 / 300000000 XIDs, latest 0:40003236
NOTICE:  consumed 50003214 / 300000000 XIDs, latest 0:50003968
NOTICE:  consumed 60003281 / 300000000 XIDs, latest 0:60004035
NOTICE:  consumed 70003982 / 300000000 XIDs, latest 0:70004736
NOTICE:  consumed 80004080 / 300000000 XIDs, latest 0:80004834
NOTICE:  consumed 90004750 / 300000000 XIDs, latest 0:90005504
NOTICE:  consumed 100004879 / 300000000 XIDs, latest 0:100005633
NOTICE:  consumed 110005518 / 300000000 XIDs, latest 0:110006272
NOTICE:  consumed 120005678 / 300000000 XIDs, latest 0:120006432
NOTICE:  consumed 130006286 / 300000000 XIDs, latest 0:130007040
NOTICE:  consumed 140006477 / 300000000 XIDs, latest 0:140007231
NOTICE:  consumed 150007054 / 300000000 XIDs, latest 0:150007808
NOTICE:  consumed 160007276 / 300000000 XIDs, latest 0:160008030
NOTICE:  consumed 170007822 / 300000000 XIDs, latest 0:170008576
NOTICE:  consumed 180008075 / 300000000 XIDs, latest 0:180008829
NOTICE:  consumed 190008590 / 300000000 XIDs, latest 0:190009344
NOTICE:  consumed 200008874 / 300000000 XIDs, latest 0:200009628
NOTICE:  consumed 210009358 / 300000000 XIDs, latest 0:210010112
NOTICE:  consumed 220009673 / 300000000 XIDs, latest 0:220010427
NOTICE:  consumed 230010126 / 300000000 XIDs, latest 0:230010880
NOTICE:  consumed 240010472 / 300000000 XIDs, latest 0:240011226
NOTICE:  consumed 250010894 / 300000000 XIDs, latest 0:250011648
NOTICE:  consumed 260011271 / 300000000 XIDs, latest 0:260012025
NOTICE:  consumed 270011662 / 300000000 XIDs, latest 0:270012416
NOTICE:  consumed 280012046 / 300000000 XIDs, latest 0:280012800
NOTICE:  consumed 290012060 / 300000000 XIDs, latest 0:290012814
 consume_xids
--------------
    300000754
(1 row)

postgres=# select txid_current();
 txid_current
--------------
    300000755
(1 row)

postgres=# SELECT datname
, age(datfrozenxid)
, current_setting('autovacuum_freeze_max_age')
FROM pg_database
 ORDER BY 2 DESC;
  datname  |    age    | current_setting
-----------+-----------+-----------------
 postgres  | 300000026 | 200000000
 template1 | 300000026 | 200000000
 template0 | 300000026 | 200000000
(3 rows)


postgres@pg:/tmp$ tail logfile
2024-03-24 12:17:10.905 IST [1765] WARNING:  cutoff for removing and freezing tuples is far in the past
2024-03-24 12:17:10.905 IST [1765] HINT:  Close open transactions soon to avoid wraparound problems.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
2024-03-24 12:17:10.906 IST [1765] WARNING:  cutoff for removing and freezing tuples is far in the past
2024-03-24 12:17:10.906 IST [1765] HINT:  Close open transactions soon to avoid wraparound problems.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
2024-03-24 12:17:10.906 IST [1765] WARNING:  cutoff for removing and freezing tuples is far in the past
2024-03-24 12:17:10.906 IST [1765] HINT:  Close open transactions soon to avoid wraparound problems.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
2024-03-24 12:17:10.993 IST [1738] LOG:  checkpoint starting: time


postgres@pg:/tmp$ postgres --single -D db1 postgres

PostgreSQL stand-alone backend 17devel
backend> vacuum full;
backend> 2024-03-24 12:17:50.217 IST [1770] LOG:  checkpoint starting: shutdown immediate
2024-03-24 12:17:50.495 IST [1770] LOG:  checkpoint complete: wrote 153 buffers (0.9%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.006 s, sync=0.265 s, total=0.287 s; sync files=282, longest=0.003 s, average=0.001 s; distance=5481 kB, estimate=5481 kB; lsn=0/1B261B8, redo lsn=0/1B261B8
postgres@pg:/tmp$ postgres --single -D db1 template1

PostgreSQL stand-alone backend 17devel
backend> vacuum full;
backend> 2024-03-24 12:18:02.686 IST [1771] LOG:  checkpoint starting: shutdown immediate
2024-03-24 12:18:02.965 IST [1771] LOG:  checkpoint complete: wrote 155 buffers (0.9%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.005 s, sync=0.266 s, total=0.287 s; sync files=281, longest=0.003 s, average=0.001 s; distance=5485 kB, estimate=5485 kB; lsn=0/20818F0, redo lsn=0/20818F0
postgres@pg:/tmp$ pg_ctl -D db1 -l logfile start
waiting for server to start.... done
server started
postgres@pg:/tmp$ psql
psql (17devel)
Type "help" for help.

postgres=# select txid_current();
 txid_current
--------------
    300000893
(1 row)

postgres=# SELECT datname
, age(datfrozenxid)
, current_setting('autovacuum_freeze_max_age')
FROM pg_database
 ORDER BY 2 DESC;
  datname  |    age    | current_setting
-----------+-----------+-----------------
 template0 | 300000164 | 200000000
 postgres  |       138 | 200000000
 template1 |        69 | 200000000
(3 rows)


postgres=# WITH max_age AS (
    SELECT 2000000000 as max_old_xid
        , setting AS autovacuum_freeze_max_age
        FROM pg_catalog.pg_settings
        WHERE name = 'autovacuum_freeze_max_age' )
, per_database_stats AS (
    SELECT datname
        , m.max_old_xid::int
        , m.autovacuum_freeze_max_age::int
        , age(d.datfrozenxid) AS oldest_current_xid
    FROM pg_catalog.pg_database d
    JOIN max_age m ON (true)
    WHERE d.datallowconn )
SELECT max(oldest_current_xid) AS oldest_current_xid
    , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
    , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac
FROM per_database_stats;
 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
                219 |                          0 |                                 0
(1 row)


postgres=# vacuum full t;
VACUUM
postgres=# select txid_current();
 txid_current
--------------
    300001045
(1 row)

postgres=# SELECT datname
, age(datfrozenxid)
, current_setting('autovacuum_freeze_max_age')
FROM pg_database
 ORDER BY 2 DESC;
  datname  | age | current_setting
-----------+-----+-----------------
 template1 | 221 | 200000000
 template0 | 152 | 200000000
 postgres  |  77 | 200000000
(3 rows)



-----------------------------
postgres@pg:/tmp$ initdb -D db1 2>/dev/null >/dev/null
postgres@pg:/tmp$ psql
psql (17devel)
Type "help" for help.

postgres=# create extension xid_wraparound;
CREATE EXTENSION
postgres=# set client_min_messages TO error;
SET
postgres=# SELECT datname,
       age(datfrozenxid),
       2^31-1000000-age(datfrozenxid) as remaining
  FROM pg_database
 ORDER BY 3;
  datname  | age | remaining
-----------+-----+------------
 postgres  |   9 | 2146483639
 template1 |   9 | 2146483639
 template0 |   9 | 2146483639
(3 rows)

postgres=# \df *consume*
                              List of functions
 Schema |        Name        | Result data type | Argument data types | Type
--------+--------------------+------------------+---------------------+------
 public | consume_xids       | xid8             | nxids bigint        | func
 public | consume_xids_until | xid8             | targetxid xid8      | func
(2 rows)


#session two, consume xids to 2bn
postgres=# select consume_xids(2146483639); -- consume all remaining xids to force vacuum full
ERROR:  database is not accepting commands that assign new XIDs to avoid wraparound data loss in database "template1"
HINT:  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.


#thats it, db cannot take it any more, will not accept any connections that will request a new xid till a db wide vacuum fulls have been run


#login using single user mode, and run a vacuum full for all the databases
postgres@pg:/tmp$ /opt/17/usr/local/bin/pg_ctl -D db1 -l logfile stop
waiting for server to shut down.... done
server stopped

#login using single user mode, and run a vacuum full for all the databases
postgres@pg:/tmp$ /opt/17/usr/local/bin/postgres --single -D db1 postgres
2024-04-21 23:55:03.217 IST [35203] WARNING:  database with OID 5 must be vacuumed within 2999236 transactions
2024-04-21 23:55:03.217 IST [35203] HINT:  To avoid XID assignment failures, 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.

PostgreSQL stand-alone backend 17devel


  /opt/17/usr/local/bin/postgres --single -D db1 postgres
backend> vacuum full
  /opt/17/usr/local/bin/postgres --single -D db1 template1
backend> vacuum full
  /opt/17/usr/local/bin/postgres --single -D db1 template0
backend> vacuum full

#now start the db server normally
postgres@pg:/tmp$ pg_ctl -D db1 -l logfile start
waiting for server to start.... done
server started
postgres@pg:/tmp$ psql
psql (17devel)
Type "help" for help.

postgres=# SELECT datname,
       age(datfrozenxid),
       2^31-1000000-age(datfrozenxid) as remaining
  FROM pg_database
 ORDER BY 3;
  datname  | age | remaining
-----------+-----+------------
 postgres  | 204 | 2146483444
 template1 | 136 | 2146483512
 template0 |  68 | 2146483580
(3 rows)


# resolved