Contents
pg_logical_slot_peek_binary_changes()
pg_logical_slot_peek_binary_changes()
is a system function returning a set of specified changes from the specified logical replication slot without consuming them, meaning the same set of changes can be retrieved again. The contents of each change returned as bytea
.
pg_logical_slot_peek_binary_changes()
was added in PostgreSQL 9.4.
Usage
pg_logical_slot_peek_binary_changes (slot_name
name
,upto_lsn
pg_lsn
,upto_nchanges
integer
, VARIADICoptions
text
[] ) →
setof record (lsn
pg_lsn
,xid
xid
,data
text
)
The returned set of changes will begin from the replication slot's current restart_lsn
value.
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 and advance the replication slot, use pg_logical_slot_get_binary_changes()
or pg_logical_slot_get_changes()
.
Change history
- PostgreSQL 9.4
- added (commit b89e1510)
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/492B078 (1 row)
Create some WAL activity, here by inserting a row into an existing table:
postgres=# INSERT INTO foo VALUES (1, 'foo'); INSERT 0 1
This WAL activity can then be repeatedly queried via the previously created logical replication slot using pg_logical_slot_peek_binary_changes()
:
postgres=# SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_1', NULL, NULL, 'include-xids', '0'); lsn | xid | data -----------+-----+------------------------------------------------------------------------------------------------------------------ 0/492B078 | 718 | \x424547494e 0/492B078 | 718 | \x7461626c65207075626c69632e666f6f3a20494e534552543a2069645b696e74656765725d3a312076616c5b746578745d3a27666f6f27 0/492C6A8 | 718 | \x434f4d4d4954 (3 rows) postgres=# SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_1', NULL, NULL, 'include-xids', '0'); lsn | xid | data -----------+-----+------------------------------------------------------------------------------------------------------------------ 0/492B078 | 718 | \x424547494e 0/492B078 | 718 | \x7461626c65207075626c69632e666f6f3a20494e534552543a2069645b696e74656765725d3a312076616c5b746578745d3a27666f6f27 0/492C6A8 | 718 | \x434f4d4d4954 (3 rows)
If the available WAL is then consumed, e.g. with pg_logical_slot_get_binary_changes()
, or the replication slot is advanced(and no other WAL activity takes place), the previously returned data is no longer available:
postgres=# SELECT * FROM pg_replication_slot_advance('test_slot_1', '0/492C6A8'); slot_name | end_lsn -------------+----------- test_slot_1 | 0/492C6A8 (1 row) postgres=# SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_1', NULL, NULL, 'include-xids', '0'); lsn | xid | data -----+-----+------ (0 rows)
References
- PostgreSQL documentation: Replication Management Functions