G.3. pgpro_logical_slot — manage logical replication slots #
G.3.1. Overview #
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
logicalstarting fromrestart_lsn.WAL segments from
restart_lsnto the current LSN must be present on a physical node.In a time interval between
restart_lsnand 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_lsnand 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.
G.3.2. Installation #
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;
G.3.3. Functions #
pgpro_logical_slot.pgpro_create_logical_replication_slot (#slot_namename,plugin_namename,temporaryboolean,twophaseboolean,restart_lsnpg_lsn) returns record (slot_namename,lsnpg_lsn)Creates a logical replication slot with
restart_lsnin 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 totrue, 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 totrue, the decoding is enabled.restart_lsn: Therestart_lsnvalue 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_lsnand 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_slotfunction does not check for presence of historical tuples and the required WAL files. After creating a slot, you can optionally execute thepg_logical_slot_peek_changesfunction 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)