PostgreSQL TimescaleDB logical replication slots

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

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

If you are no longer using a replication slot, delete it.

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

Create a logical replication slot

We recommend creating logical replication slots through the control panel or through the Managed Database API. If you perform these operations 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.

Slots can only be created and used by a user with the dbaas_replication role — this role is automatically assigned to the database owner and cannot be assigned to other users.

In the dashboard, on the top menu, click Products and select Managed Databases.
Open the Active tab.
Open the cluster page → Databases 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 a slot

After the slot is created, you must configure logical replication between the source database and the destination database. The destination database can be located in Servercore Managed 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 schema for all replicated tables in the destination database and perform a schema dump on 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> — IP address or DNS name of the master node of the cluster where the source database is located;
- <port> — port;
- <database_name> — name of the source database;
- <user_name> — name of the source database owner;
- <dump_directory> — dump directory.
Restore the schema from the dump to the destination 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> — IP address or DNS name of the master node of the cluster where the destination database is located;
- <user_name> — destination database user name;
- <database_name> — destination database name;
- <port> — port of the cluster where the destination database is located;
- <dump_directory> — directory with the dump.
Create a subscription in the destination 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> — IP address or DNS name of the master node of the cluster where the source database is located;
- <port> — port of the cluster where the source database is located;
- <user_name> — source database user name;
- <password> — user password;
- <database_name> — source database name;
- <logical_slot_name> — logical replication slot name.
Existing data will appear in the destination database table.

If new data is added to the source database table, it will be replicated automatically.
To stop logical replication, disable the subscription, detach the slot from it, 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 a slot, information will accumulate in the slot and take up disk space. If the slot is not needed, delete it.

View logical replication slot status

In the dashboard, on the top menu, click Products and select Managed Databases.
Open the Active tab.
Open the cluster page → Databases tab.
Open the database card.

In the Replication slots block, check the status in the slot row.

CREATING	Slot is being created
ACTIVE	Slot is in use — the slot has a consumer, and data is being transmitted to the destination database
UNUSED	Slot is not in use — the slot has no consumer, data is not being transmitted to the destination database, accumulates in the replication slot, and takes up additional disk space. If you are no longer going to use the replication slot, delete it.
DELETING	Slot is being deleted

Check logical replication slot consumers using an SQL query

To check whether logical replication slots have consumers, send an 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;

Output example:

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 which logical replication slot consumers can use;
active — a boolean value shows whether the logical replication slot is in use:
- f — slot has no consumer;
- t — slot has a consumer.

If you have PostgreSQL 13 or later, you can limit the maximum size of stored WAL files using the 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

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

In the dashboard, on the top menu, click Products and select Managed Databases.
Open the Active tab.
Open the cluster page → Databases tab.
Open the database card.
In the Replication slots block, in the slot row, click .