--- title: "PostgreSQL logical replication slots" sidebar_label: "Logical replication slots" sidebar_position: 21 description: "How to create a replication slot, use it to configure logical replication, view the replication slot status, and other instructions" --- import Formbricks from '@theme/MDXComponents/Formbricks' import TrashIcon from 'docs-kit/icons/trash' import {CustomTable} from 'docs-kit/components' # PostgreSQL logical replication slots To continuously replicate data from one database to another, you can [configure logical replication using a logical replication slot](#configure-logical-replication). Replication slots must always have a consumer. If there is no consumer, the volume of data in the slot will grow. You can check if replication slots have a consumer using an [SQL query](#check-consumers-of-logical-replication-slots) or [replication slot status](#replication-slot-statuses). If you no longer use a replication slot, [delete it](#delete-logical-replication-slot). Learn more about logical replication in the [Logical Replication](https://www.postgresql.org/docs/current/logical-replication.html) section of the PostgreSQL documentation. ## Configure logical replication \{#configure-logical-replication} 1. [Create a logical replication slot](#create-logical-replication-slot). 2. [Configure a logical replication slot](#configure-logical-replication-using-slot) ### 1. Create a logical replication slot \{#create-logical-replication-slot} We recommend creating logical replication slots through the control panel or through the [Managed Database API](/api/managed-databases/). If you create a slot through a client connected to the database, we do not guarantee that the slot will work. The maximum number of logical replication slots is 26. Only a user with the `dbaas_replication` role can create slots — this role is automatically assigned to the [database owner](/managed-databases/postgresql/manage-users.mdx#database-owner) and cannot be assigned to other users. 1. In the [Dashboard](https://my.selectel.ru/vpc/default/dbaas/), on the top menu, click **Products** and select **Managed Databases**. 2. Open the **Active** tab. 3. Open the cluster page → **Databases** tab. 4. Open the database card. 5. In the **Replication Slots** block, click **Add replication slot**. 6. Enter a slot name or keep the default name. 7. Click **Create**. ### 2. Configure logical replication using the slot \{#configure-logical-replication-using-slot} After creating the slot, you need to configure logical replication between the source database and the destination database. The destination database can be in Selectel Managed Databases or in external storage. 1. Create a publication in the source database: ```sql CREATE PUBLICATION FOR TABLE ; ``` Specify: * `` — publication name; * `` — table name. 2. Optional: if required, add additional tables to the publication: ```sql ALTER PUBLICATION ADD TABLE ; ``` Specify `` — table name. 3. Create a data schema of all replicated tables in the destination database and perform a schema dump in the source database using the [pg\_dump](https://www.postgresql.org/docs/current/app-pgdump.html) utility: ```bash pg_dump \ "host= \ port= \ dbname= \ user=" \ --schema-only \ --no-privileges \ --no-subscriptions \ --no-publications \ -Fd -f ``` Specify: * `` — IP address or DNS name of the cluster master node where the source database is located; * `` — port; * `` — source database name; * `` — source database owner name; * `` — dump directory. 4. Restore the schema from the dump in the destination database using the [pg\_restore](https://www.postgresql.org/docs/current/app-pgrestore.html) utility: ```bash pg_restore \ -Fd -v \ --single-transaction -s \ --no-privileges -O \ -h \ -U \ -p \ -d \ ``` Specify: * `` — IP address or DNS name of the cluster master node where the destination database is located; * `` — destination database user name; * `` — destination database name; * `` — port of the cluster where the destination database is located; * `` — dump directory. 5. Create a subscription in the destination database as a user with the `dbaas_replication` role: ```sql CREATE SUBSCRIPTION CONNECTION 'host= port= dbname= user= password= sslmode=verify-ca' PUBLICATION WITH (copy_data=true, create_slot=false, enabled=true, slot_name=); ``` Specify: * `` — subscription name; * `` — IP address or DNS name of the cluster master node where the source database is located; * `` — port of the cluster where the source database is located; * `` — source database user name; * `` — user password; * `` — source database name; * `` — logical replication slot name. 6. Existing data will appear in the destination database table. If new data is added to the source database table, it will be replicated automatically. 7. To stop logical replication, disable the subscription, detach the slot from it, and delete the subscription: ```sql ALTER SUBSCRIPTION DISABLE; ALTER SUBSCRIPTION SET (slot_name=NONE); DROP SUBSCRIPTION ; ``` If you detach all subscriptions from a slot, information will accumulate in the slot and take up disk space. If the slot is not needed, [delete it](#delete-logical-replication-slot). ## View the status of logical replication slots \{#replication-slot-statuses} 1. In the [Dashboard](https://my.selectel.ru/vpc/default/dbaas/), on the top menu, click **Products** and select **Managed Databases**. 2. Open the **Active** tab. 3. Open the cluster page → **Databases** tab. 4. Open the database card. 5. In the **Replication Slots** block, check the status in the slot row.
CREATINGSlot is being created
ACTIVESlot is in use — there is a consumer for the slot, and data is being transmitted to the destination database
UNUSEDSlot is not in use — there is no consumer for the slot, data is not transmitted to the destination database, accumulates in the replication slot, and occupies additional disk space. If you no longer use the replication slot, [delete it](#delete-logical-replication-slot).
DELETINGSlot is being deleted
## Check logical replication slot consumers using an SQL query \{#check-consumers-of-logical-replication-slots} To check if there are any consumers for logical replication slots, send an SQL query to the `pg_replication_slots` view: ```sql SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag,active FROM pg_replication_slots; ``` Example output: ```bash slot_name | replicationslotlag | active -----------------+--------------------+-------- myslot1 | 129 GB | f myslot2 | 704 MB | t myslot3 | 624 MB | t ``` Where: * `slot_name` — logical replication slot name; * `replicationslotlag` — size of WAL files that will not be automatically deleted during checkpoints and that logical replication slot consumers may use; * `active` — boolean value indicating whether the logical replication slot is in use: * `f` — there is no consumer for the slot; * `t` — there is a consumer for the slot. If you are using PostgreSQL version 13 or higher, you can limit the maximum size of stored WAL files using the [`max_slot_wal_keep_size`](https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE) parameter. Note that when using this parameter, the transaction log may be deleted before the consumer reads the changes in the logical replication slot. ## Delete a logical replication slot \{#delete-logical-replication-slot} We recommend that you delete logical replication slots through the control panel or through the [Managed Database API](/api/managed-databases/). If you delete a slot via a client connected to the database, the slot may not be deleted correctly. 1. In the [Dashboard](https://my.selectel.ru/vpc/default/dbaas/), on the top menu, click **Products** and select **Managed Databases**. 2. Open the **Active** tab. 3. Open the cluster page → **Databases** tab. 4. Open the database card. 5. In the **Replication Slots** block, click in the slot row.