Improve Reliability in PostgreSQL 17: Leveraging Logical Replication Failover

Before we start, ensure you have:

1. AWS CLI configured with access and secret keys.
2. You have connectivity to RDS instances.
3. An existing VPC Security Group (SG) allowing your IP to connect on port 5432.

1. Create and configure parameter groups with required parameters.
2. Provision RDS Instances: Create RDS PostgreSQL instances.
3. Set up logical replication: Configure slots, publications, and subscriptions.
4. Simulate failover and update the subscription: Promote the read replica and repoint the subscription to the new primary.
5. Test replication flow to ensure data consistency.
6. Clean up resources.

1. Configure Parameter Groups

• First, create custom parameter groups for the primary, read replica, and logical replica. Enable the following parameters:

| Parameter                    | Value     | Description                                                                    |
|------------------------------|-----------|--------------------------------------------------------------------------------|
| rds.logical_replication      | 1         | Enables logical replication on the primary instance.                           |
| hot_standby_feedback         | 1         | Prevents query conflicts by sending feedback from the standby to the primary.  |
| rds.logical_slot_sync_dbname | Valid DB  | Specifies the database for logical slot synchronization (default: `postgres`). |
| synchronized_standby_slots   | Slot name | Physical replication slot name of the standby intended to stay in-sync.        |
| sync_replication_slots       | 1         | Enables automatic synchronization of replication slots.                        |

Create the parameter group. Repeat similar steps for the read replica and logical replica.

aws rds create-db-parameter-group \
    --db-parameter-group-name pg-primary-group \
    --db-parameter-group-family postgres17 \
    --description "Logical replication setup with sync_replication_slots"

• Modify the parameters. The synchronized_standby_slots will be set after the read replica has been created:

aws rds modify-db-parameter-group \
    --db-parameter-group-name pg-primary-group \
    --parameters "ParameterName='rds.logical_replication',ParameterValue=logical,ApplyMethod=pending-reboot" \
                 "ParameterName='sync_replication_slots',ParameterValue=1,ApplyMethod=pending-reboot"

aws rds modify-db-parameter-group \
    --db-parameter-group-name pg-read-replica-group \
    --parameters "ParameterName='rds.logical_replication',ParameterValue=1,ApplyMethod=pending-reboot" \
                 "ParameterName='hot_standby_feedback',ParameterValue='1',ApplyMethod=pending-reboot" \
                 "ParameterName='rds.logical_slot_sync_dbname',ParameterValue='postgres',ApplyMethod=pending-reboot" \
                 "ParameterName='sync_replication_slots',ParameterValue=1,ApplyMethod=pending-reboot"

2. Provision RDS Instances

• Using AWS CLI, create the primary instance, logical replica, and read replica. For testing purposes, these instances will be publicly accessible:

aws rds create-db-instance \
    --db-instance-identifier primary-instance \
    --engine postgres \
    --engine-version 17.1 \
    --allocated-storage 20 \
    --master-username postgres \
    --master-user-password ChangeM3 \
    --db-instance-class db.t3.medium \
    --vpc-security-group-ids sg-xxxxxx

Repeat the above for a logical replica.

• For the read replica:

aws rds create-db-instance-read-replica \
    --db-instance-identifier read-replica \
    --source-db-instance-identifier primary-instance

• After the read replica has been created, get the physical slot name from the primary instance and update the pg-primary-group parameter group:

psql -h $DB_PRI -U postgres -t -c "SELECT slot_name FROM pg_replication_slots where slot_type='physical'"

aws rds modify-db-parameter-group \
    --db-parameter-group-name pg-primary-group \
    --parameters "ParameterName='synchronized_standby_slots',ParameterValue='rds_us_east_1_db_dn2uyr2436rexq3u7gcdfzw4hy',ApplyMethod=pending-reboot"

• Now assign the parameter groups to appropriate instances and reboot.
• Verify that parameters are set correctly. These must be set for logical replication to continue working after failover:

psql -h $DB_PRI -U postgres -c "SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication','sync_replication_slots','synchronized_standby_slots')"
:' output
            name            |                   setting
----------------------------+---------------------------------------------
 rds.logical_replication    | on
 sync_replication_slots     | on
 synchronized_standby_slots | rds_us_east_1_db_dn2uyr2436rexq3u7gcdfzw4hy
 wal_level                  | logical
(4 rows)
'

# rds.logical_slot_sync_dbname is not available in pg_settings
# make sure to update it if your database is not "postgres."
psql -h $DB_RR -U postgres -c "SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication','sync_replication_slots','hot_standby_feedback','rds.logical_slot_sync_dbname')"
:' output
          name           | setting
-------------------------+---------
 hot_standby_feedback    | on
 rds.logical_replication | on
 sync_replication_slots  | on
 wal_level               | logical
(4 rows)
'

3. Set Up Logical Replication

On the primary instance:

• Create source table, populate sample data, and create publication:

CREATE TABLE reptab1 (slno int primary key);
INSERT INTO reptab1 VALUES (generate_series(1,1000));
CREATE PUBLICATION testpub FOR TABLE reptab1;

• Create an identical table and subscription with failover = true on the logical replica. This will create a logical replication slot in the primary-instance.

CREATE TABLE reptab1 (slno int primary key);
CREATE SUBSCRIPTION testsub CONNECTION 'host=<primary-endpoint> port=5432 dbname=postgres user=postgres password=ChangeM3' PUBLICATION testpub WITH (failover = true);

• Verify the subscription is active and that data has been copied over:

SELECT subname, subenabled FROM pg_subscription;
SELECT count(*) from reptab1;

• You should see a logical replication slot in the read-replica instance:

psql -h $DB_RR -U postgres -c "select slot_name, slot_type, active, failover, synced from pg_replication_slots;"
:'output
 slot_name | slot_type | active | failover | synced
-----------+-----------+--------+----------+--------
 testsub   | logical   | f      | t        | t
(1 row)
'

4. Simulate Failover and Update Subscription

To simulate a failover:

• Promote the read replica to a standalone instance:

aws rds promote-read-replica \
   --db-instance-identifier read-replica

• Update the subscription on the logical replica to point to the new primary:

ALTER SUBSCRIPTION testsub CONNECTION 'host=<read-replica-endpoint> port=5432 dbname=postgres user=postgres password=ChangeM3';

With sync_replication_slots enabled, the logical replication slot is preserved during failover, allowing the subscriber (logical replica) to continue replicating seamlessly without requiring a resync.

• Insert new rows in the promoted read replica to validate replication:

INSERT INTO reptab1 VALUES (generate_series(1001,2000));

• Verify the logical replica contains the updated rows:

SELECT COUNT(*) FROM reptab1;  -- Should reflect the new data

To avoid resource costs, delete all RDS instances and parameter groups:

aws rds delete-db-instance --db-instance-identifier primary-instance --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier read-replica --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier logical-replica --skip-final-snapshot

aws rds delete-db-parameter-group --db-parameter-group-name pg-primary-group
aws rds delete-db-parameter-group --db-parameter-group-name pg-read-replica-group
aws rds delete-db-parameter-group --db-parameter-group-name pg-logical-replica-group