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 volume of data in the slot will grow. You can check if replication slots have a consumer using an SQL query or the replication slot status.

If you no longer use a replication slot, delete it.

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

1. Create a logical replication slot

We recommend creating logical replication slots in the control panel or via the Cloud Databases API. If you perform these operations using a client connected to the database, we do not guarantee that the slot will work properly.

The maximum number of logical replication slots is 26.

Only a user with the dbaas_replication role can create and use slots — this role is automatically assigned to the database owner and cannot be assigned to other users.

In the Control panel, on the top menu, click Products and select Cloud Databases.
Open the tab Active.
Open the cluster page → tab Databases.
Open the database card.
In the Replication slots section, click Add replication slot.
Enter a slot name or keep the default name.
Click Create.

2. Configure logical replication using a slot

After creating the slot, you need to configure logical replication between the source database and the target database. The target database can be located in Servercore 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 schema of all replicated tables in the target 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> — 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 in the target 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 target database is located;
- <user_name> — username of the target database user;
- <database_name> — name of the target database;
- <port> — port of the cluster where the target database is located;
- <dump_directory> — directory with the dump.
Create a subscription in the target database on behalf of the 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> — username of the source database user;
- <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 target database table.

If new data is added to the source database table, it will be automatically replicated.
To stop logical replication, disable the subscription, unlink the slot from it, and drop 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, 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 Control panel, on the top menu, click Products and select Cloud Databases.
Open the tab Active.
Open the cluster page → tab Databases.
Open the database card.

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

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

Check logical replication slot consumers using an SQL query

To check if logical replication slots have consumers, run an SQL query against 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

Where:

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

If you have PostgreSQL version 13 or higher, 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 write-ahead log may be removed before the consumer reads the changes in the logical replication slot.

Delete a logical replication slot

We recommend deleting logical replication slots in the control panel or via the Cloud Databases API. When deleting a slot via a client connected to the database, the slot may not be deleted properly.

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