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, delete it.
Read more about logical replication in the section Logical Replication PostgreSQL documentation.
Configure logical replication
Create a logical replication slot
We recommend creating logical replication slots via the control panel or via 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 role can create slots dbaas_replication — this role is automatically assigned database owner You can't assign it to other users.
- In control panel go to Cloud platform → Databases.
- Open the cluster page → tab Databases.
- Open the database card.
- In the block Replication slots click Add a 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>— NAME OF PUBLICATION;<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 utility pg_dump:
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>— the name of the owner of the original database;<dump_directory>— dump directory.
-
Reconstruct the schema from the dump in the receiving database using the utility pg_restore:
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 receiving database resides;<user_name>— the user name of the receiving database;<database_name>— name of the receiving database;<port>— port of the cluster in which the receiving database resides;<dump_directory>— the directory with the dump.
-
Create a subscription in the receiving database on behalf of a user with role
dbaas_replication: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 master node of the cluster where the source database resides;<port>— port of the cluster where the source database resides;<user_name>— the 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 control panel go to Cloud platform → Databases.
-
Open the cluster page → tab Databases.
-
Open the database card.
-
In the block Replication slotsand in the slot line, look at the status.
Check consumers of logical replication slots using SQL query
To check if logical replication slots have consumers, send an SQL query to the view pg_replication_slots:
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— the slot doesn't have a 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 parameter max_slot_wal_keep_size. Note that when using this option, the transaction log may be deleted before the consumer reads the changes in the logical replication slot.
Delete logical replication slot
We recommend deleting logical replication slots via the control panel or via the Cloud database API. If you delete a slot via a client connected to the database, the slot may not be deleted correctly.
- In control panel go to Cloud platform → Databases.
- Open the cluster page → tab Databases.
- Open the database card.
- In the block Replication slotsin the slot line, press .