Feature Introduction
Local logical replication slots on read-only instances support creating logical replication slots on PostgreSQL read-only instances. Downstream data subscription tasks can connect to read-only instances to consume change data generated by the primary instance, thereby shifting logical replication consumption pressure from the primary instance to read-only instances and reducing the load on the primary instance.
This feature is applicable to scenarios such as CDC, data synchronization, data subscription, and heterogeneous data distribution.
The data link is as follows:
The primary instance writes data.
-> The read-only instance replays WAL through physical replication.
-> The read-only instance is connected by downstream subscription tasks.
-> The downstream consumes changes through local logical replication slots on the read-only instance.
Applicable Scenarios
To reduce the load on the primary instance, downstream subscription tasks connect to read-only instances, reducing the logical decoding and network transmission pressure on the primary instance.
Read/write path isolation: business write traffic is retained on the primary instance, and data subscription traffic is offloaded to read-only instances.
Multiple downstream subscriptions: multiple downstream consumers can connect to different read-only instances on demand, reducing the pressure on a single instance.
CDC data distribution: distributes change data to downstream systems through the logical replication protocol.
Prerequisites
Before using this feature, confirm that the instance meets the following conditions:
The PostgreSQL major version is 16 or later.
The primary instance has logical replication enabled:
wal_level = logical
max_replication_slots > 0
max_wal_senders > 0
A read-only instance has been created, and the replication status between the read-only instance and the primary instance is normal.
Downstream subscribers can access the connection address of the read-only instance.
When a custom logical decoding plugin is used, the instance must support the corresponding plugin.
Use Limits
The logical replication slot created on a read-only instance is a local replication slot.
Local logical replication slots do not participate in primary-standby switchovers.
After a read-only instance is rebuilt, released, or recovered from an exception, local logical replication slots may be lost and need to be recreated.
When downstream consumption latency is too high, the read-only instance may retain a large amount of WAL. Pay attention to the replication slot position and disk space.
Replication latency on the read-only instance can affect downstream subscription latency.
Method 1: Manually Creating a Local Logical Replication Slot
This method is suitable for scenarios involving custom logical decoding or manual consumption of changes.
Connecting to a Read-Only Instance
Connect to the read-only instance using an account that has logical replication permissions.
Creating a Local Logical Replication Slot
SET tencentdb_force_enable_failover_slot = off;
SELECT pg_create_logical_replication_slot('my_slot', 'test_decoding');
Among them:
my_slot: the name of the logical replication slot, which can be replaced as needed.
test_decoding: the name of the logical decoding plugin, which can be replaced with the decoding plugin used by your business.
Consuming Change Data
SELECT *
FROM pg_logical_slot_get_changes('my_slot', NULL, NULL);
Deleting Unused Replication Slots
SELECT pg_drop_replication_slot('my_slot');
Method 2: Subscribing to Read-Only Instances from Downstream
This method is suitable for standard PostgreSQL logical subscription scenarios. The downstream instance connects to the read-only instance through CREATE SUBSCRIPTION and creates a logical replication slot on the read-only instance.
Creating a Publication on the Primary Instance
CREATE PUBLICATION my_pub FOR TABLE public.tab_rep;
Among them:
my_pub: the name of the publication, which can be replaced as needed.
public.tab_rep: the table to be subscribed to. Replace it with the actual business table.
Creating Homogeneous Tables on the Downstream Instance
The target table with the same structure as the published table must be created on the downstream instance in advance.
CREATE TABLE public.tab_rep (
id int PRIMARY KEY,
data text
);
Creating a Subscription on the Downstream Instance
Replace host, port, user, password, and dbname in the connection string with the actual connection information of the read-only instance.
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=<read-only instance address> port=<read-only instance port> user=<username> password=<password> dbname=<database name> options=-c\\ tencentdb_force_enable_failover_slot=off'
PUBLICATION my_pub
WITH (copy_data = off);
Among them:
my_sub: the name of the subscription, which can be replaced as needed.
my_pub: the name of the publication on the primary instance.
copy_data = off: existing data in the table is not copied when the subscription is created. If you need to synchronize existing data during initialization, adjust it based on your business needs.
Viewing Replication Slots on Read-Only Instances
SELECT slot_name,
plugin,
slot_type,
active,
restart_lsn,
confirmed_flush_lsn
FROM pg_replication_slots;
Parameter Description
tencentdb_force_enable_failover_slot
tencentdb_force_enable_failover_slot controls whether to forcibly create a failover slot.
When creating a local logical replication slot on a read-only instance, disable this parameter in the current session:
SET tencentdb_force_enable_failover_slot = off;
When connecting to a read-only instance using CREATE SUBSCRIPTION, you can configure the options parameter in the connection string:
options=-c\\ tencentdb_force_enable_failover_slot=off
Monitoring Recommendations
We recommend that you monitor the following metrics:
Read-only instance replication latency: the greater the replication latency, the greater the downstream subscription latency.
Replication slot confirmed position: monitor whether confirmed_flush_lsn continues to advance.
WAL retention: when downstream consumption is abnormal, replication slots may cause WAL accumulation.
Disk space utilization: WAL accumulation may cause disk space to grow rapidly.
Subscription status: regularly check whether downstream subscriptions are running normally.
You can check the replication slot status by using the following SQL statement:
SELECT slot_name,
plugin,
slot_type,
active,
restart_lsn,
confirmed_flush_lsn,
wal_status
FROM pg_replication_slots;
FAQs
Are Read-Only Instance Local Logical Replication Slots Retained After a Primary-Secondary Switch?
No. Local logical replication slots on a read-only instance are stored only on that read-only instance and are not involved in primary-standby switches. After a read-only instance is rebuilt or released, the replication slots need to be recreated.
Does a Downstream Subscription Connect to the Primary Instance or a Read-Only Instance?
Connect to the read-only instance. In the subscription connection string, set host and port to the connection address and port of the read-only instance.
Does the Primary Instance Still Require wal_level=logical?
Yes. Logical decoding relies on the primary instance to generate WAL that contains logical replication information, so the primary instance must have wal_level=logical enabled.
Does Read-Only Instance Replication Lag Affect Downstream?
Yes. Downstream can only consume WAL that has already been replayed on the read-only instance, so replication latency on the read-only instance directly affects downstream subscription latency.
Is CREATE SUBSCRIPTION Supported for Automatic Slot Creation?
Yes. When a downstream subscription connects to a read-only instance, a pgoutput logical replication slot can be created on the read-only instance. The connection string must include:
options=-c\\ tencentdb_force_enable_failover_slot=off