pg_logical_slot_get_changes()

A function returning changes from a logical replication slot

pg_logical_slot_get_changes() is a system function returning a set of specified changes from the specified logical replication slot.

pg_logical_slot_get_changes() was added in PostgreSQL 9.4.

Usage

pg_logical_slot_get_changes ( slot_name name, upto_lsn pg_lsn, upto_nchanges integer, VARIADIC options text[] ) →
        setof record ( lsn pg_lsn, xid xid, data text )

The returned set of changes will begin from the point changes were previously consumed.

If both upto_lsn and upto_nchanges are NULL, all changes up until the end of available WAL will be returned.

The options parameter enables provision of options as defined by the output plugin associated with the logical replication slot.

To retrieve changes without consuming them, use pg_logical_slot_peek_changes().

Change history

Examples

Create a logical replication slot with test_decoding specified as the plugin:

postgres=# SELECT * FROM pg_create_logical_replication_slot('test_slot_1', 'test_decoding');
  slot_name  |    lsn
-------------+-----------
 test_slot_1 | 0/3000758
(1 row)

Create some WAL activity, here by inserting a row into an existing table:

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

This WAL activity can then be queried via the previously created logical replication slot using pg_logical_slot_get_changes():

postgres=# SELECT * FROM pg_logical_slot_get_changes('test_slot_1', NULL, NULL, 'include-xids', '0');
    lsn    | xid |                                       data
-----------+-----+-----------------------------------------------------------------------------------
 0/3023580 | 726 | BEGIN
 0/3023580 | 726 | table public.foo: INSERT: id[integer]:1 val[text]:'2021-08-09 10:00:38.622597+01'
 0/3023610 | 726 | COMMIT
(3 rows)

As the output plugin has now consumed the changes, a subsequent call to pg_logical_slot_get_changes() (assuming no further WAL was written) will return an empty result set:

postgres=# SELECT * FROM pg_logical_slot_get_changes('test_slot_1', NULL, NULL, 'include-xids', '0');
 lsn | xid | data
-----+-----+------
(0 rows)

Attempting to retrieve changes from a physical replication slot:

postgres=# SELECT * FROM pg_logical_slot_get_changes('physical_slot_1', NULL, NULL, 'include-xids', '0');
ERROR:  cannot use physical replication slot for logical decoding

Categories

Logical replication, System function

See also

pg_logical_slot_peek_changes(), pg_logical_slot_get_binary_changes(), pg_logical_slot_peek_binary_changes()