Managing replication slots
Managed Service for PostgreSQL clusters can contain physical and logical replication slots:
- Physical slots are created automatically based on replica hosts and used for data replication within the Managed Service for PostgreSQL cluster.
- Logical slots are created by users for logical replication of data across clusters, such as during change data capture (CDC).
For more information, see the PostgreSQL documentation
Viewing a list of logical replication slots
-
Connect to the database as a user with the
mdb_replication
role. -
Execute the SQL query below:
SELECT * FROM pg_replication_slots WHERE slot_type='logical';
The query returns a table with all logical replication slots and their parameters.
Creating a replication slot
-
Set the maximum WAL size for replication in the
Max slot wal keep size
setting.Warning
If set to
-1
(unlimited size), you will not be able to delete WAL files due to open logical replication slots the information is not read from. As a result, the WAL files will take up the entire disk space and you will not be able to connect to the cluster. -
Connect to the database as a user with the
mdb_replication
role. -
Execute the SQL query below:
SELECT pg_create_logical_replication_slot ( '<slot_name>', '<plugin_name>', <temporary>, <two_phase> )
Where:
<slot_name>
: Unique slot name. This is a required parameter.<plugin_name>
: Name of plugin from the list of supported output plugins. To view the list, see Replication. This is a required parameter.temporary
: Iftrue
, the slot is deleted as soon as the current session is completed or if an error occurs. The default address isfalse
.two_phase
: Iftrue
, the slot will decode the prepared transactions . The default address isfalse
.
Deleting a replication slot
-
Connect to the database as a user with the
mdb_replication
role. -
Execute the SQL query below:
SELECT pg_drop_replication_slot ('<slot_name>');
Example
To create a new replication slot named json_slot
that decodes database changes to JSON format using the wal2json
plugin:
-
Make sure the
Max slot wal keep size
setting value is different from-1
. -
Execute the SQL query below:
SELECT pg_create_logical_replication_slot ( 'json_slot', 'wal2json', false, false );
Possible response:
pg_create_logical_replication_slot ------------------------------------ (json_slot,1/92001108) (1 row)
-
Make sure the slot has appeared in the list:
SELECT * from pg_replication_slots;
If you no longer need the slot, delete it:
SELECT pg_drop_replication_slot ('json_slot');