How to use failover slot for logical replication
Background
Logical replication slot (logical replication slot) is a mechanism in PostgreSQL for logical replication, ensuring that logical subscribers can receive change data starting from a specified LSN (Log Sequence Number) to prevent data loss. TencentDB for PostgreSQL implements failover slot through self-developed kernel capability, enabling cross-region failover in high-availability scenarios. This guarantees replication slot continuity and data consistency during primary-standby switch. For detailed feature description, see Logical Replication Slot Failover (Failover Slot).
This article will explain the creation and usage steps of failover slot.
Prerequisites
Two TencentDB for PostgreSQL instances located in the same VPC serve as the primary node and standby node respectively.
The major versions of the two instances must be the same, and both should be within the 11-16 range.
Operation Steps
1. primary node creates publication
Log in to the primary instance and run the following command to create a publication. In this example, the publication is named pub_1, and the table to be published is table_1.
CREATE PUBLICATION pub_1 FOR TABLE table_1;
Note:
When a slot is created, the system defaults to creating a failover slot, and automatically creates a failover slot with the same name as the subscription during subscription creation. For specific details, see Creating Failover Slots.
2. Create a subscription on the standby node
Log in to the standby node and sequentially run the following commands to create a subscription. In this example, the subscription is named sub_1.
Note:
Before a subscription is created, ensure that the standby node has created a table with the same schema as table_1 that the publication connects to.
-- Create a subscriptioncreate subscription sub_1connection 'instanceid=postgres-***** host=*.*.*.* port=5432 dbname=database_name user=user_name password=****** access_type=1'publication pub_1;
The description of parameters in the command is as follows:
instanceid: The instance ID of the primary instance, which can be obtained from the instance list in the Console.
host: The private IP address of the primary node instance.
port: The connection port number, default 5432.
dbname: The name of the database where the publication of the primary instance is located.
user: The username that can be used on the primary instance.
password: The account password that can be used on the primary instance.
access_type: Set to 1, indicating that the target instance is a TencentDB instance.
After the standby database creates a subscription, the failover slot will be automatically transferred to the standby node following a primary-standby switch, requiring no additional configuration for slot migration.
3. Verify the result
Checking the replication slot status
Execute the following on the primary instance:
SELECT slot_name, slot_type, activeFROM pg_replication_slotsWHERE slot_name = 'sub_1';
If the active value is true, it indicates that the failover slot is in use.
Check data synchronization
After data is inserted into the primary database, if querying table_1 in the standby database shows the newly inserted data, then the failover_slot is functioning properly.
Primary-standby switch verification
After the Primary-Standby Switch is clicked on the Instance Details page, if repeating the query on the new primary database yields consistent results with the previous query, then the standby database subscription remains functional and the failover_slot is operating normally.
Help and Support
Was this page helpful?
You can also Contact sales or Submit a Ticket for help.
Help us improve! Rate your documentation experience in 5 mins.
Feedback