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 slotsViewing a list of logical replication slots

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

2. 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 slotCreating a replication slot

1. 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.

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

3. 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 value is false.
   • two_phase: If true, the slot will decode the prepared transactions. The default value is false.

Deleting a replication slotDeleting a replication slot

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

2. Execute the SQL query below:

   SELECT pg_drop_replication_slot ('<slot_name>');
   

ExampleExample

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

1. Make sure the Max slot wal keep size setting value is different from -1.

2. 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)
   

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