Logical Replication Slots | Selectel Documentation

PostgreSQL logical replication slots

To continuously replicate data from one database to another, you can configure logical replication using the logical replication slot.

Replication slots must always have a consumer. If there is no consumer, the amount of data in the slot will grow. You can check if replication slots have a consumer using SQL query or replication slot status.

If you are no longer using the replication slot, remove it.

Learn more about logical replication in the Logical Replication section of the PostgreSQL documentation.

Configure logical replication⁠

Create a logical replication slot⁠

We recommend creating logical replication slots through the control panel or through the cloud database API. 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 given to the database owner, it cannot be assigned to other users.

In the Control panel, on the top menu, click Products and select Cloud Databases.
Open the Active tab.
Open the cluster page → Database tab.
Open the database card.
In the Replication Slots block, click Add Replication Slot.
Enter a slot name or leave the default name.
Click Create.

Configure logical replication using the slot⁠

After the slot is created, you must configure logical replication between the source database and the destination database. The receiving database can be located in Selectel cloud databases or in external storage.

Create a publication in the source database:
```
CREATE PUBLICATION <publication_name> FOR TABLE <table_name>;
```
Specify:
- <publication_name> — publication name;
- <table_name> — table name.
Optional: if required, add additional tables to the publication:
```
ALTER PUBLICATION <publication_name> ADD TABLE <extra_table_name>;
```
Specify <extra_table_name> — table name.
Create a data schema of all replicated tables in the receiving database and dump the schema in the source database using the pg_dump utility:
```
pg_dump \
"host=<host> \
port=<port> \
dbname=<database_name> \
user=<user_name>" \
--schema-only \
--no-privileges \
--no-subscriptions \
--no-publications \
-Fd -f <dump_directory>
```
Specify:
- <host> — The IP address or DNS name of the cluster master node where the source database resides;
- <port> — port;
- <database_name> — name of the source database;
- <user_name> — name of the owner of the source database;
- <dump_directory> — directory for dump.
Restore the schema from the dump to the receiving database using the pg_restore utility:
```
pg_restore \
-Fd -v \
--single-transaction -s \
--no-privileges -O \
-h <host> \
-U <user_name> \
-p <port> \
-d <database_name> \
<dump_directory>
```
Specify:
- <host> — The IP address or DNS name of the master node of the cluster in which the host database resides;
- <user_name> — the user name of the receiving database;
- <database_name> — name of the receiving database;
- <port> — port of the cluster where the receiving database resides;
- <dump_directory> — directory with dump.
Create a subscription in the receiving database on behalf of a user with the dbaas_replication role:
```
CREATE SUBSCRIPTION <subscription_name> CONNECTION
   'host=<host>
   port=<port>
   dbname=<database_name>
   user=<user_name>
   password=<password>
   sslmode=verify-ca'
PUBLICATION <publication_name>
WITH (copy_data=true, create_slot=false, enabled=true, slot_name=<logical_slot_name>);
```
Specify:
- <subscription_name> — subscription name;
- <host> — The IP address or DNS name of the cluster master node where the source database resides;
- <port> — port of the cluster where the source database resides;
- <user_name> — user name of the source database;
- <password> — user password;
- <database_name> — name of the source database;
- <logical_slot_name> — name of the logical replication slot.
Existing data will appear in the table of the receiving database.

If new data is added to the source database table, it is automatically replicated.
To stop logical replication, turn off the subscription, unbind the slot, and delete the subscription:
```
ALTER SUBSCRIPTION <subscription_name> DISABLE;
ALTER SUBSCRIPTION <subscription_name> SET (slot_name=NONE);
DROP SUBSCRIPTION <subscription_name>;
```
If you disconnect all subscriptions from the slot, the information will accumulate in the slot and take up disk space. If the slot is not needed, delete it.

View the status of logical replication slots⁠

In the Control panel, on the top menu, click Products and select Cloud Databases.
Open the Active tab.
Open the cluster page → Database tab.
Open the database card.

In the Replication Slots block, in the slot row, look at the status.

CREATING	The slot is created
ACTIVE	Slot is in use — the slot has a consumer, the data is transmitted to the receiving database
UNUSED	Slot is not used — the slot has no consumer, data is not transferred to the receiving database, accumulates in the replication slot and takes up additional disk space. If you will no longer use the replication slot, delete it.
DELETING	The slot is being removed

Check consumers of logical replication slots using SQL query⁠

To check if logical replication slots have consumers, submit a SQL query to the pg_replication_slots view:

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:

slot_name        | replicationslotlag | active
-----------------+--------------------+--------
myslot1          | 129 GB             | f
myslot2          | 704 MB             | t
myslot3          | 624 MB             | t

Here:

slot_name — name of the logical replication slot;
replicationslotlag — the size of WAL files that will not be automatically deleted at checkpoints and that consumers of the logical replication slot can use;
active — boolean value indicates whether the logical replication slot is in use:
- f — slot does not have a consumer;
- t — slot has a consumer.

If you have PostgreSQL version 13 or higher, you can limit the maximum size of stored WAL files using the parameter max_slot_wal_keep_size. Note that when using this parameter, the transaction log may be deleted before the consumer reads the changes in the logical replication slot.

Delete logical replication slot⁠

We recommend that you delete logical replication slots through the control panel or through the cloud database API. If you delete a slot via a client connected to the database, the slot may not be deleted correctly.

In the Control panel, on the top menu, click Products and select Cloud Databases.
Open the Active tab.
Open the cluster page → Database tab.
Open the database card.
In the Replication Slots block, in the slot row, click .

PostgreSQL logical replication slots

Configure logical replication⁠​

Create a logical replication slot⁠​

Configure logical replication using the slot⁠​

View the status of logical replication slots⁠​

Check consumers of logical replication slots using SQL query⁠​

Delete logical replication slot⁠​