Managing replication slots

Written by

Updated at November 7, 2024

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.

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: If true, the slot is deleted as soon as the current session is completed or if an error occurs. The default address is false.
- two_phase: If true, the slot will decode the prepared transactions. The default address is false.

Execute the SQL query below:

SELECT pg_drop_replication_slot ('<slot_name>');

To create a new replication slot named json_slot that decodes database changes to JSON format using the wal2json plugin:

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');