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. The value of this setting can be edited as of PostgreSQL ver. 13.

   Warning

   If set to -1 (unlimited size), you will not be able to delete WAL files due to open logical replication slots that 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 a 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 available 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 to decode database changes to JSON format using the wal2json plugin:

1. Check that 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');