pg_replication_slot_advance()

A function for advancing the current confirmed position of a replication slot

pg_replication_slot_advance() is a system function for advancing the current confirmed position of a replication slot (either physical or logical).

pg_replication_slot_advance() was added in PostgreSQL 11.

Usage

pg_replication_slot_advance ( slot_name name, upto_lsn pg_lsn ) →
        record ( slot_name name, end_lsn pg_lsn )

The slot cannot be moved backwards or moved beyond the current insert location.

Any WAL being retained due to the slot's previous position will be removed at the next checkpoint.

An error is raised if the slot cannot be advanced.

Change history

Examples

Advancing a physical replication slot:

postgres=# SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots\gx
-[ RECORD 1 ]-------+----------
slot_name           | node2
slot_type           | physical
active              | f
restart_lsn         | 0/301C4D8
confirmed_flush_lsn | 

postgres=# INSERT INTO foo VALUES (1, clock_timestamp());
INSERT 0 1

postgres=# SELECT * FROM pg_replication_slot_advance('node2', pg_current_wal_lsn());
 slot_name |  end_lsn  
-----------+-----------
 node2     | 0/301C5D8
(1 row)

postgres=# SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots\gx
-[ RECORD 1 ]-------+----------
slot_name           | node2
slot_type           | physical
active | f restart_lsn | 0/301C5D8 confirmed_flush_lsn |

Attempting to advance a slot which is not retaining WAL because it has never been used, or has been invalidated:

postgres=# SELECT * FROM pg_replication_slot_advance('physical_slot_1', '0/3002DE0');
ERROR:  replication slot "physical_slot_1" cannot be advanced
DETAIL:  This slot has never previously reserved WAL, or it has been invalidated.

Attempting to reset a replication slot to an earlier position:

postgres=# SELECT * FROM pg_replication_slot_advance('test_slot_1', '0/301D4E0');
ERROR:  cannot advance replication slot to 0/301D4E0, minimum is 0/492C6A8

Attempting to reset a replication slot to an invalid position:

postgres=# SELECT * FROM pg_replication_slot_advance('test_slot_1', '0/0');
ERROR:  invalid target WAL LSN

Categories

Replication, System function, WAL

See also

pg_replication_origin_advance()