Help improve AWS Support Official channel in re:Post and share your experience - complete a quick three-question survey to earn a re:Post badge!
Managing logical slot synchronization for RDS for PostgreSQL starting version 17 after promoting the Read Replica.
How to manage logical replication slot during promotion of read replicas on RDS PostgreSQL which is pass on automatically to read replica without the risk of missing any change events. With the release of PostgreSQL version 17 a new feature to automatically synchronize logical replication slots from primary to read replica servers has been introduced through the parameter sync_replication_slots or the related function pg_sync_replication_slots().
Logical replication is a method for replicating data from a Postgres publisher primary Instance to subscribers. Prior to PostgreSQL versions 16, read replicas couldn’t be used for logical replication at all. Logical replication slot which keep track of how far a specific subscriber has consumed the database’s change event stream could only be created on the primary node of a Postgres cluster before version 16. This meant that after a failover from primary to stand-by you’d have to create a new replication slot and typically also start with a new initial snapshot of the data. Otherwise you might have missed change events occurring after reading from the slot on the old primary and before creating the slot on the new primary. But:
In RDS PostgreSQL, currently there is no concept of failover to Read Replica, so no automatic failover to the read replica, if you do a failover on the primary Multi-AZ DB Instance, it will be a failover to the stand-by instance, not a read replica. Even in a Read Replica DB Instance, if you do a failover, it will be failover to the standby instance of the read replica. Wait till the ReplicaLag goes to zero then promote the Read Replica instance. You have to do the promotion of Read Replica manually from AWS Console or AWS CLI. After promoting the read replica to become a standalone RDS for PostgreSQL DB instance, the process isn't reversible.
Starting from Postgres 16 version, you can set up replication slots on read replicas. But the good news is, as of Postgres version 17, it finally supports maintaining replication slots which are pass on automatically to read replica once promoting read replica. When promoting a read replica as primary, you can continue to consume the slot on the new primary (previous read replica) without the risk of missing any change events. Primary and read replica are synchronized via a physical replication slot ensuring that all data changes done on the primary are replicated to the replica immediately.
Starting from PostgreSQL version 17, to set up/test this new feature to automatically synchronise logical replication slots from primary to read replicas has been introduced through the parameter sync_replication_slots or the related function pg_sync_replication_slots().
You need 1 primary PostgreSQL DB Instance, 1 PostgreSQL read replica created from the primary instance and another separate RDS PostgreSQL DB Instance which will be the subscriber (all DB instances must be version 17 and higher).
For the set up create:
- Primary DB Instance version 17 named “managing-logical-slot-synchronization”
- Read Replica from the primary version 17 named “read-replica-managing-slots”
- A separate/another RDS PostgreSQL Instance which will be the subscriber relying on the logical replication from the primary Instance, named “subscriber-17-2-version”
Turn on logical replication – To create logical decoding on a standby, you must turn on logical replication on your source DB instance and its physical read replica.
- To turn on logical replication for a newly created RDS for PostgreSQL DB instance – Create a new DB custom parameter group family 17 and set the static parameter rds.logical_replication to 1. Then, associate this DB parameter group with the Source DB instance and another custom parameter group with physical read replica.
- To turn on logical replication for an existing RDS for PostgreSQL DB instance – Modify the DB custom parameter group of the source DB instance and its physical read replica to set the static parameter rds.logical_replication to 1.
Note - You must reboot the Primary DB instance and Read Replica to apply these parameter changes.
- Connect on the Primary DB Instance and verify the values for wal_level and rds.logical_replication as below:
[ec2-user@ip-111-11-11-111 ~]$ psql -h managing-logical-slot-synchronization.cilnb7aqzivh.eu-west-2.rds.amazonaws.com -U postgres -d postgres -p 5432
Password for user postgres:
postgres=> SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');
name | setting
-------------------------+---------
rds.logical_replication | on
wal_level | logical
(2 rows)
- Connect on the Read Replica and verify the values for wal_level and rds.logical_replication
[ec2-user@ip-111-11-11-111 ~]$ psql -h read-replica-managing-slots.cilnb7aqzivh.eu-west-2.rds.amazonaws.com -U postgres -d postgres -p 5432
Password for user postgres:
postgres=> SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');
name | setting
-------------------------+---------
rds.logical_replication | on
wal_level | logical
(2 rows)
- Once you create the read replica, there is a physical replication slot between the primary DB instance and read replica as below and synchronized_standby_slots parameter must be set on the primary DB Instance custom parameter group and the value of this parameter is the physical replication slot(slot_name) for synchronizing changes from the primary to the replica server:
Below is executed from the Primary DB Instance:
## As expected, there is one physical (i.e. streaming) replication slot (*rds_eu_west_2_db_vusn5a2buhnwdgoiyr62mqhgzu*) on the primary. This slot has been set up automatically when creating a replica on RDS and it propagates all the changes from the primary to the read replica.
postgres=> WITH node_status AS (
SELECT
CASE WHEN pg_is_in_recovery() = 'True' Then 'stand-by' ELSE 'primary' END AS role
)
SELECT
node_status.role AS node,
slot_name,
slot_type,
active,
plugin,
database, failover, synced,
confirmed_flush_lsn
FROM
pg_replication_slots, node_status;
node | slot_name | slot_type | active | plugin | database | failover | synced | confirmed_flush_lsn
---------+---------------------------------------------+-----------+--------+----------+----------+----------+--------+---------------------
primary | rds_eu_west_2_db_vusn5a2buhnwdgoiyr62mqhgzu | physical | t | | | f | f |
(1 row)
postgres=>
postgres=> show synchronized_standby_slots;
synchronized_standby_slots
---------------------------------------------
rds_eu_west_2_db_vusn5a2buhnwdgoiyr62mqhgzu
(1 row)
postgres=>
This synchronized_standby_slots parameter, new in Postgres 17, makes sure that any logical replication slots we are going to set up in the following cannot advance beyond the confirmed log sequence number (LSN) of this physical slot.
Set parameter hot_standby_feedback = 1 (on), on the Read Replica DB Instance, which is a requirement for failover slots to work.
Also rds.logical_slot_sync_dbname parameter on the read replica must be set to a valid database name which exist on both Primary DB Instance and Read Replica. The default value for this parameter is "postgres" database. If the logical publishing instance (primary DB Instance) has the "postgres" database, the default does not need to be changed from the parameter group of the Read Replica.
- Create table on the Source Primary DB and INSERT rows
postgres=> CREATE TABLE primary_test_table (a int PRIMARY KEY);
CREATE TABLE
postgres=> INSERT INTO primary_test_table VALUES (generate_series(1,10000));
INSERT 0 10000
postgres=> CREATE TABLE reptab2 (name varchar(20));
CREATE TABLE
postgres=> INSERT INTO reptab2 SELECT SUBSTR ('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);
INSERT 0 50
- Create a publication on the source for ALL TABLES:
postgres=> CREATE PUBLICATION testpub FOR ALL TABLES;
CREATE PUBLICATION
## create a publication on the source for a few source tables:
postgres=>CREATE PUBLICATION testpub FOR TABLE primary_test_table,reptab2,table3;
- Verify the details of the publication on primary DB Instance:
postgres=> SELECT * from pg_publication;
oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
16436 | testpub | 16412 | t | t | t | t | t | f
(1 row)
## Verify that the source tables are added to the publication:
postgres=> SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+--------------------+----------+-----------
testpub | public | primary_test_table | {a} |
testpub | public | reptab2 | {name} |
(2 rows)
- Then, login on the RDS PostgreSQL instance which will be the subscriber. Ensure the subscription is created with the failover option set to “true”: First, create the tables with the same name on the subscriber. When the subscription is created, the subscription loads all the data present in the source tables to the target tables.
postgres=> CREATE TABLE primary_test_table (a int PRIMARY KEY);
CREATE TABLE
postgres=> CREATE TABLE reptab2 (name varchar(20));
CREATE TABLE
postgres=> CREATE SUBSCRIPTION testsub CONNECTION 'host=managing-logical-slot-synchronization.cilnb7aqzivh.eu-west-2.rds.amazonaws.com port=5432 dbname=postgres user=postgres password=postgres1234' PUBLICATION testpub WITH (failover = true);
NOTICE: created replication slot "testsub" on publisher
CREATE SUBSCRIPTION
postgres=>
- Connect on the primary DB Instance and verify that a logical slot on the publisher with failover “t” is enabled as below:
postgres=> SELECT slot_name, slot_type, failover FROM pg_catalog.pg_replication_slots;
slot_name | slot_type | failover
---------------------------------------------+-----------+----------
rds_eu_west_2_db_vusn5a2buhnwdgoiyr62mqhgzu | physical | f
testsub | logical | t
(2 rows)
- Insert a few more rows on the primary DB Instance to verify the logical replication is working on the subscriber:
postgres=> insert into primary_test_table values(generate_series(10020,206000));
INSERT 0 195981
postgres=>
postgres=> select count(*) from primary_test_table;
count
--------
205981
(1 row)
postgres=> INSERT INTO reptab2 SELECT SUBSTR ('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,200);
INSERT 0 200
postgres=> SELECT count(*) FROM reptab2;
count
-------
250
(1 row)
- Verify subscriber: Run a SELECT query on the target tables to verify data loads:
postgres=> select count(*) from primary_test_table;
count
-------
10000
(1 row)
## below after the second INSERT,confirmed logical replication is working
postgres=>
postgres=> select count(*) from primary_test_table;
count
--------
205981
(1 row)
postgres=> SELECT count(*) FROM reptab2;
count
-------
250
(1 row)
- Execute the below on the subscriber instance to verify the details of the subscription:
postgres=> SELECT oid,subname,subenabled,subslotname,subpublications FROM pg_subscription;
oid | subname | subenabled | subslotname | subpublications
-------+---------+------------+-------------+-----------------
16436 | testsub | t | testsub | {testpub}
(1 row)
- Inspect logical replication slot state on the primary instance:
postgres=> select slot_name, slot_type, confirmed_flush_lsn from pg_replication_slots;
slot_name | slot_type | confirmed_flush_lsn
---------------------------------------------+-----------+---------------------
rds_eu_west_2_db_vusn5a2buhnwdgoiyr62mqhgzu | physical |
testsub | logical | 4/90000168
(2 rows)
- On the read replica we can see the changes are also replicated:
postgres=> select count(*) from primary_test_table;
count
--------
205981
(1 row)
postgres=> SELECT count(*) FROM reptab2;
count
-------
250
(1 row)
Make sure the below parameters must be set before moving forward with the promotion of read replica. Note: At this point, primary DB and read replica are set up for failover slots to work.
- rds.logical_replication = 1 (on), on the Primary DB Instance and Read Replica custom parameter group and reboot both the DB Instances
- hot_standby_feedback = 1 (on), on the Read Replica DB Instance, which is a requirement for failover slots to work.
- rds.logical_slot_sync_dbname parameter on the read replica must be set to a valid database name which exist on both Primary DB Instance and Read Replica. The default value for this parameter is "postgres". If the logical publishing instance (primary DB Instance) has the "postgres" database, the default does not need to be changed from the paramter group of the Read Replica.
- synchronized_standby_slots parameter, must be set on the primary DB Instance parameter group and the value of this parameter is the physical replication slot(slot_name) for synchronizing changes from the primary to the replica server. In case there are more than 1 replication slots, add it one by one on this parameter separated by comma (,). This guarantees that logical replication failover slots do not consume changes until those changes are received and flushed to corresponding physical standbys.
- sync_replication_slots parameter must be enabled 1 (on) on the read replica parameter group to enable automatic synchronization, a synchronization worker will be started. So the synchronization will be done automatically due to the parameter sync_replication_slots, instead of executing function pg_sync_replication_slots(); as below:
postgres=> show sync_replication_slots;
sync_replication_slots
------------------------
on
(1 row)
Before PostgreSQL 17 version, on the read replica you had to call pg_sync_replication_slots() for synchronizing all failover slots from the primary:
postgres=> SELECT pg_sync_replication_slots();
pg_sync_replication_slots
---------------------------
(1 row)
Parameter sync_replication_slots parameter will make sure that the slots on both primary Instance and read replica are at exactly the same LSN, which means the two slots are in sync. To verify it, you can use the below query to check the status of the slots on both primary and read replica:
- The query executed on Primary DB Instance as below to verify the LSN is the same:
postgres=> WITH node_status AS (
SELECT
CASE WHEN pg_is_in_recovery() = 'True' Then 'stand-by' ELSE 'primary' END AS role
)
SELECT
node_status.role AS node,
slot_name,
slot_type,
active,
plugin,
database, failover, synced,
confirmed_flush_lsn
FROM
pg_replication_slots, node_status;
node | slot_name | slot_type | active | plugin | database | failover | synced | confirmed_flush_lsn
---------+---------------------------------------------+-----------+--------+----------+----------+----------+--------+---------------------
primary | rds_eu_west_2_db_vusn5a2buhnwdgoiyr62mqhgzu | physical | t | | | f | f |
primary | testsub | logical | t | pgoutput | postgres | t | f | 4/A0000758
(2 rows)
- At the moment you set the parameter sync_replication_slots = 1 on the read replica, you are gonna see the replications slot. The query executed on Read DB Instance as below to verify the LSN is the same:
postgres=> WITH node_status AS (
SELECT
CASE WHEN pg_is_in_recovery() = 'True' Then 'stand-by' ELSE 'primary' END AS role
)
SELECT
node_status.role AS node,
slot_name,
slot_type,
active,
plugin,
database, failover, synced,
confirmed_flush_lsn
FROM
pg_replication_slots, node_status;
node | slot_name | slot_type | active | plugin | database | failover | synced | confirmed_flush_lsn
----------+-----------+-----------+--------+----------+----------+----------+--------+---------------------
stand-by | testsub | logical | f | pgoutput | postgres | t | t | 4/A0000758
(1 row)
postgres=>
- At the moment the LSN is the same on Primary Instance and Read Replica, promote the Read Replica to a standalone Instance. Once we promote Read Replica, we can see below that the “node” column from “stand-by” before, now it is “primary”, below executed on new promoted Instance (before read replica).
postgres=> WITH node_status AS (
SELECT
CASE WHEN pg_is_in_recovery() = 'True' Then 'stand-by' ELSE 'primary' END AS role
)
SELECT
node_status.role AS node,
slot_name,
slot_type,
active,
plugin,
database, failover, synced,
confirmed_flush_lsn
FROM
pg_replication_slots, node_status;
node | slot_name | slot_type | active | plugin | database | failover | synced | confirmed_flush_lsn
---------+-----------+-----------+--------+----------+----------+----------+--------+---------------------
primary | testsub | logical | f | pgoutput | postgres | t | t | 4/A8000130
(1 row)
postgres=> SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase | inactive_since |
conflicting | invalidation_reason | failover | synced
-----------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+-----------+-------------------------------+
-------------+---------------------+----------+--------
testsub | pgoutput | logical | 5 | postgres | f | f | | | 1184 | 4/A80000F8 | 4/A8000130 | reserved | | f | 2025-03-01 13:12:47.409892+00 |
f | | t | t
(1 row)
postgres=> SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+--------------------+----------+-----------
testpub | public | primary_test_table | {a} |
testpub | public | reptab2 | {name} |
(2 rows)
Important step: Connect to the subscriber DB Instance and execute the below to alter the subscription on the logical replica to point to the promoted new primary (before Read Replica).
postgres=> ALTER SUBSCRIPTION testsub CONNECTION 'host=read-replica-managing-slots.cilnb7aqzivh.eu-west-2.rds.amazonaws.com port=5432 dbname=postgres user=postgres password=postgres1234';
ALTER SUBSCRIPTION
postgres=>
- With the sync_replication_slots parameter enabled on the read replica, the logical replication slot is preserved during failover, allowing the subscriber (logical replica) to continue replicating seamlessly without requiring a resync. To confirm this, INSERT new rows in the new promoted DB Instance (promoted read replica) to validate replication:
postgres=> insert into primary_test_table values(generate_series(295981,2060009));
INSERT 0 1764029
postgres=>
postgres=> select count(*) from primary_test_table;
count
---------
1970010
(1 row)
postgres=> INSERT INTO reptab2 SELECT SUBSTR ('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,500);
INSERT 0 500
postgres=> SELECT count(*) FROM reptab2;
count
-------
750
(1 row)
- Verify the changes on the subscriber DB Instance:
postgres=> select count(*) from primary_test_table;
count
---------
1970010
(1 row)
postgres=> select count(*) from reptab2;
count
-------
750
(1 row)
Benefit: sync_replication_slot parameter, a game-changing feature to maintain logical replication continuity during promotion on read replica or major upgrades.
Clean Up and turn off replication
- Log in on the subscriber DB and drop the subscriber
postgres=> DROP SUBSCRIPTION testsub;
NOTICE: dropped replication slot "testsub" on publisher
DROP SUBSCRIPTION
postgres=>
postgres=> SELECT * FROM pg_subscription;
oid | subdbid | subskiplsn | subname | subowner | subenabled | subbinary | substream | subtwophasestate | subdisableonerr | subpasswordrequired | subrunasowner | subfailover | subconninfo | subslotname | subsynccommit | subpublications | suborigin
-----+---------+------------+---------+----------+------------+-----------+-----------+------------------+-----------------+---------------------+---------------+-------------+-------------+-------------+---------------+-----------------+-----------
(0 rows)
Note: Dropping the subscription also drops the replication slot the subscription created. Verify that the replication slot is dropped from the source database (new promoted Instance, before Read Replica) by running the following SELECT query statement on the source:
postgres=> SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase | inactive_since | conflicting | invalidation_reason | failover | synced
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+-----------+----------------+-------------+---------------------+----------+--------
(0 rows)
postgres=> SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
(0 rows)
- Drop the publication. Verify the publication is dropped successfully
postgres=> DROP PUBLICATION testpub;
DROP PUBLICATION
- Modify rds.logical_replication to 0 in the custom parameter group that is attached to the DB instance. Reboot the DB instance as required if the DB instance is not using logical replication.
Question: Can RDS PostgreSQL Read Replica be used as source for CDC (ongoing replication) with AWS Database Migration Service (DMS) due to creation of replication slots and supports logical replication from standby (Read Replica) starting from version 16.1 ?
- You can’t use RDS PostgreSQL Read Replicas as a source for CDC with DMS. This limitation is clearly stated in our below documentation and has been confirmed through testing in my lab environment. Reason being is that CDC requires replication slot creation and Postgres does not support creation of replication slots on read replicas.
- [+] Using a PostgreSQL database as an AWS DMS source - Enabling CDC with an AWS-managed PostgreSQL DB instance with AWS DMS - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.RDSPostgreSQL.CDC
Note: It's important to understand that DMS does not use the publisher/subscriber replication model. Instead, DMS relies on logical decoding using either the test_decoding or pglogical plugin.
Author: E.C
Relevant content
- asked a year ago
- asked 2 years ago
