Managing replication slots

Managed Service for PostgreSQL clusters can contain physical and logical replication slots:

• The service automatically creates physical slots for replica hosts and uses them for data replication within the Managed Service for PostgreSQL cluster.
• Logical slots are created by users for logical data replication across clusters, for example, in CDC (Change Data Capture) scenarios.

For more information, see this PostgreSQL guide.

Viewing a list of logical replication slotsViewing a list of logical replication slots

1. Connect to the database as a user with the mdb_replication role.

2. Run the following SQL query:

   SELECT * FROM pg_replication_slots WHERE slot_type='logical';
   

   This query outputs a table listing all logical replication slots along with their settings.

Creating a replication slotCreating a replication slot

1. Specify the maximum WAL size for replication in the Max slot wal keep size setting.

   Warning

   If this setting’s value is -1 (unlimited size), inactive logical replication slots will block WAL file cleanup. As a result, the WAL files may consume all available disk space, making your cluster inaccessible.

2. Connect to the database as a user with the mdb_replication role.

3. Run the following SQL query:

   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. For the full list, see Replication. This is a required parameter.
   • temporary: If true, the slot will be dropped at the end of the current session or if an error occurs. The default is false.
   • two_phase: If true, the slot will decode the prepared transactions. The default is false.

Dropping a replication slotDropping a replication slot

1. Connect to the database as a user with the mdb_replication role.

2. Run the following SQL query:

   SELECT pg_drop_replication_slot ('<slot_name>');
   

ExampleExample

Create a new json_slot replication slot that uses the wal2json plugin to stream database changes as JSON:

1. Make sure the Max slot wal keep size value is not -1.

2. Run the following SQL query:

   SELECT pg_create_logical_replication_slot ( 'json_slot', 'wal2json', false, false );
   

   Example response:

   pg_create_logical_replication_slot
   ------------------------------------
   (json_slot,1/92001108)
   (1 row)
   

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