pgpro_logical_slot is a Postgres Pro Enterprise extension to manage logical replication slots. Using the extension, you can create a slot in the past for a specified LSN. It is helpful, when you need to restore a lost logical replication slot.
The pgpro_logical_slot extension has the following limitations:
Standard limitations for the logical replication slot creation, i.e., the wal_level
value must be logical starting from restart_lsn.
WAL segments from restart_lsn to the current
LSN must be present on a physical node.
In a time interval between restart_lsn and
a logical replication slot creation, the structure of the replicated table
must remain unchanged.
When creating a logical replication slot, pgpro_logical_slot
searches for a consistency point — a valid starting LSN from which
logical decoding can safely begin.
To find it, it is required to have checkpoints on
the node in a time interval between restart_lsn and
the logical replication slot creation. When the slot is created,
you must verify that the actual starting LSN returned by
the pgpro_logical_slot.pgpro_create_logical_replication_slot
function meets your requirements.
The pgpro_logical_slot extension is provided with Postgres Pro Enterprise
as a separate pre-built package pgpro-logical-slot-ent-17.
For the detailed installation instructions, refer to Chapter 17.
Once you have
Postgres Pro Enterprise installed,
create the pgpro_logical_slot extension:
CREATE EXTENSION pgpro_logical_slot;
pgpro_logical_slot.pgpro_create_logical_replication_slot (slot_name name, plugin_name name, temporary boolean, twophase boolean, restart_lsn pg_lsn) returns record (slot_name name, lsn pg_lsn)
#
Creates a logical replication slot with restart_lsn
in the past. The function takes the following parameters:
slot_name: The name of the logical replication slot being created.
plugin_name: The output plugin name.
temporary: An optional parameter that
determines whether the slot should be permanently stored on disk.
When set to true, the slot is not stored on
disk and is only meant for use by the current session.
Temporary slots are also released upon any error.
twophase: An optional parameter that
determines whether to enable the decoding of prepared transactions for this
slot. When set to true, the decoding is enabled.
restart_lsn: The restart_lsn
value of the last known operational state of a logical replication slot, or, if
the slot is created for the first time, of the required state of
a physical replication slot.
The function returns a record containing the slot name and the
starting LSN value, which is different from
restart_lsn and where logical replication is
started. All transactions committed after this LSN are replicated.
For example:
postgres=# SELECT pgpro_logical_slot.pgpro_create_logical_replication_slot('s2', 'test_decoding', false, false, '0/17C8520'::pg_lsn);
pgpro_create_logical_replication_slot
---------------------------------------
(s2,0/17C8568)
(1 row)
The pgpro_logical_slot.pgpro_create_logical_replication_slot function
does not check for presence of historical tuples and the required
WAL files. After creating a slot, you can optionally execute the
pg_logical_slot_peek_changes
function to ensure your slot can be used for decoding from LSN in the past.
For example:
postgres=# SELECT count(*) from pg_logical_slot_peek_changes('s2', null, null);
count
--------
160055
(1 row)