Post Syndicated from Belma Canik original https://aws.amazon.com/blogs/compute/implementing-postgresql-logical-replication-between-amazon-rds-for-postgresql-instances-on-separate-aws-outposts/

Organizations in regulated industries such as healthcare, financial services, and government often face strict data residency mandates that require all data, including database backups, to remain within their on-premises environment. You can keep backups on-premises with Amazon Relational Database Service (Amazon RDS) local backups on AWS Outposts by setting the backup target to AWS Outposts (--backup-target outposts). Your AWS Outposts must have Amazon Simple Storage Service (Amazon S3) on Outposts capacity to store those backups locally. While Amazon RDS local backups on Outposts can keep backup data on-premises, organizations that need high availability across two separate Outposts racks, without any data traversing the AWS Region, lack a built-in, native replication mechanism between Outpost-hosted RDS instances.

In this post, we demonstrate how you can configure PostgreSQL logical replication between two Amazon RDS instances running on separate AWS Outposts racks, so that all data remains within your on-premises environment. By using Customer-owned IP addresses (CoIP) and Local Gateways (LGW), you can establish secure, low-latency replication between Outposts without exposing any data to the Region.

Architecture overview

Our solution uses two AWS Outpost racks, each with its own Local Gateway (LGW) configured in Customer-owned IP (CoIP) mode. In this architecture, all database traffic flows through your on-premises network infrastructure:

Figure 1: Replication architecture across two AWS Outposts racks using Customer-owned IP addresses and Local Gateways

Prerequisites

Before beginning the configuration, make sure you have:

• Two AWS Outpost racks with Local Gateways (LGW) configured in CoIP mode.
• CoIP pools configured and shared with your AWS account.
• LGW route table associations with your virtual private cloud (VPC) in place.
• On-premises network connectivity between the two Outpost Local Gateways.
• VPC with subnets spanning both Outposts.
• Security groups configured to allow PostgreSQL traffic (port 5432).

Step 1: Create Outpost subnets with CoIP configuration

Each Outpost needs a subnet with CoIP auto-assign enabled.

While Amazon Elastic Compute Cloud (Amazon EC2) instances can inherit CoIP assignment from subnet settings, RDS instances require explicit CoIP enablement (covered in Step 3).

Subnet on Outpost A

aws ec2 create-subnet \
--vpc-id vpc-xxx \
--cidr-block 10.0.1.0/24 \
--availability-zone us-west-2a \
--outpost-arn arn:aws:outposts:us-west-2:OWNER_ACCOUNT:outpost/op-xxx

# Enable CoIP auto-assign (applies to EC2, not RDS)
aws ec2 modify-subnet-attribute \
--subnet-id subnet-xxx \
--map-customer-owned-ip-on-launch \
--customer-owned-ipv4-pool ipv4pool-coip-xxx

Subnet on Outpost B

aws ec2 create-subnet \
--vpc-id vpc-xxx \
--cidr-block 10.0.2.0/24 \
--availability-zone us-west-2b \
--outpost-arn arn:aws:outposts:us-west-2:OWNER_ACCOUNT:outpost/op-yyy

aws ec2 modify-subnet-attribute \
--subnet-id subnet-outpost-b \
--map-customer-owned-ip-on-launch \
--customer-owned-ipv4-pool ipv4pool-coip-yyy

Important: The MapCustomerOwnedIpOnLaunch setting works for EC2 instances but doesn’t automatically apply to RDS instances. RDS requires explicit CoIP enablement during instance creation.

Create DB subnet group

Create a DB subnet group spanning both Outposts:

aws rds create-db-subnet-group \
--db-subnet-group-name rds-outposts-subnet-group \
--db-subnet-group-description "Subnet group spanning Outpost A and B" \
--subnet-ids subnet-outpost-a subnet-outpost-b

Step 2: Configure route tables for cross-Outpost communication

This configuration step is often overlooked. Each subnet’s route table requires two Local Gateway routes:

1. Cross-outpost route: To reach the other Outpost’s CoIP range.
2. Same-outpost hairpin route: To reach its own CoIP range, which is required because RDS DNS resolves to CoIP. This affects any resource on the subnet that needs to reach the RDS instance by endpoint, including Amazon EC2 application servers on the same Outpost.

Why the hairpin route is important

When you enable CoIP on RDS, the DNS endpoint resolves to the CoIP address instead of the private IP. EC2 instances (and other RDS instances) on the same subnet need a route through the LGW to reach CoIP addresses, even for same-Outpost traffic. Without the hairpin route, traffic to the local CoIP range matches the default route (NAT gateway or internet gateway) and is lost.

Route configuration for Outpost A

# Cross-outpost: reach Outpost B's CoIP via Outpost A's LGW
aws ec2 create-route \
--route-table-id rtb-outpost-a \
--destination-cidr-block 198.19.5.0/24 \
--local-gateway-id lgw-A

# Same-outpost hairpin: reach Outpost A's own CoIP via its LGW
aws ec2 create-route \
--route-table-id rtb-outpost-a \
--destination-cidr-block 198.19.9.0/24 \
--local-gateway-id lgw-A

Route configuration for Outpost B

# Cross-outpost: reach Outpost A's CoIP via Outpost B's LGW
aws ec2 create-route \
--route-table-id rtb-outpost-b \
--destination-cidr-block 198.19.9.0/24 \
--local-gateway-id lgw-B

# Same-outpost hairpin: reach Outpost B's own CoIP via its LGW
aws ec2 create-route \
--route-table-id rtb-outpost-b \
--destination-cidr-block 198.19.5.0/24 \
--local-gateway-id lgw-B

Final route table summary

Outpost A Route Table:

Outpost B Route Table:

Step 3: Create RDS instances with CoIP enabled

In this step, you create the Amazon RDS for PostgreSQL instances on each Outpost using the aws rds create-db-instance CLI command. CoIP provides local or external connectivity to resources in your Outpost subnets through your on-premises network. It provides lower latency for connections to the DB instance from outside of its VPC on your local network. When you create an Amazon RDS instance on an Outpost, you can enable CoIP to make sure the instance is reachable over your on-premises network through the Local Gateway rather than the Region.

The --enable-customer-owned-ip flag must be explicitly set. RDS doesn’t inherit the subnet’s MapCustomerOwnedIpOnLaunch setting.

Keep in mind that this property isn’t supported in AWS CloudFormation. Use the AWS Command Line Interface (AWS CLI), Terraform (customer_owned_ip_enabled = true), or the console (select Customer-owned IP address (CoIP) under Access type).

Create the publisher instance (Outpost A)

aws rds create-db-instance \
--db-instance-identifier rds-publisher \
--db-instance-class db.r5.xlarge \
--engine postgres \
--engine-version 17.9 \
--master-username postgres \
--master-user-password <SecurePassword> \
--allocated-storage 200 \
--storage-type gp2 \
--storage-encrypted \
--db-subnet-group-name rds-outposts-subnet-group \
--availability-zone us-west-2a \
--no-multi-az \
--no-publicly-accessible \
--enable-customer-owned-ip \
--db-parameter-group-name <parameter-group-with-logical-replication> \
--vpc-security-group-ids sg-xxx \
--region us-west-2

Create the subscriber instance (Outpost B)

aws rds create-db-instance \
--db-instance-identifier rds-subscriber \
--db-instance-class db.r5.xlarge \
--engine postgres \
--engine-version 17.9 \
--master-username postgres \
--master-user-password <SecurePassword> \
--allocated-storage 200 \
--storage-type gp2 \
--storage-encrypted \
--db-subnet-group-name rds-outposts-subnet-group \
--availability-zone us-west-2b \
--no-multi-az \
--no-publicly-accessible \
--enable-customer-owned-ip \
--vpc-security-group-ids sg-xxx \
--region us-west-2

Verify CoIP assignment

After the instances become available, verify that CoIP addresses have been assigned:

aws ec2 describe-addresses --region us-west-2 \
--query "Addresses[?CustomerOwnedIp!=null].[CustomerOwnedIp,PrivateIpAddress,NetworkInterfaceId]" \
--output table

You should see output similar to:

-------------------------------------------------
|                DescribeAddresses              |
+------------------+-------------+--------------+
| 198.19.9.10      | 10.0.1.15   | eni-xxx      |
| 198.19.5.20      | 10.0.2.25   | eni-yyy      |
+------------------+-------------+--------------+

Step 4: Configure security groups

Security groups must allow PostgreSQL traffic (port 5432) from both CoIP ranges and private subnet ranges:

# Allow traffic from Outpost A CoIP range
aws ec2 authorize-security-group-ingress \
--group-id sg-xxx \
--protocol tcp \
--port 5432 \
--cidr 198.19.9.0/24

# Allow traffic from Outpost B CoIP range
aws ec2 authorize-security-group-ingress \
--group-id sg-xxx \
--protocol tcp \
--port 5432 \
--cidr 198.19.5.0/24

# Allow traffic from Outpost A private subnet
aws ec2 authorize-security-group-ingress \
--group-id sg-xxx \
--protocol tcp \
--port 5432 \
--cidr 10.0.1.0/24

# Allow traffic from Outpost B private subnet
aws ec2 authorize-security-group-ingress \
--group-id sg-xxx \
--protocol tcp \
--port 5432 \
--cidr 10.0.2.0/24

Make sure egress rules allow all outbound traffic (default) or at minimum port 5432 to the CoIP ranges.

Step 5: Configure PostgreSQL logical replication

Enable logical replication on the publisher

The publisher requires the rds.logical_replication parameter set to 1, which requires a database reboot:

aws rds modify-db-parameter-group \
--db-parameter-group-name publisher-params \
--parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot"

aws rds reboot-db-instance --db-instance-identifier rds-publisher

This automatically sets wal_level = logical, so PostgreSQL can capture the necessary change data for replication.

Create replication user and publication on the publisher

Connect to the publisher instance and execute:

-- Create a replication user
CREATE USER repluser WITH REPLICATION LOGIN PASSWORD 'YourStrongPassword';

-- Grant SELECT on tables to replicate (required for initial COPY)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repluser;

-- Create table to replicate (example: healthcare appointments)
CREATE TABLE patient_appointments (
    appointment_id SERIAL PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    patient_name VARCHAR(100), -- PHI: can be excluded from replication
    ssn VARCHAR(11), -- PHI: can be excluded from replication
    date_of_birth DATE, -- PHI: can be excluded from replication
    diagnosis_code VARCHAR(10), -- PHI: can be excluded from replication
    appointment_date DATE NOT NULL,
    doctor_id INTEGER NOT NULL,
    department VARCHAR(50),
    billing_amount DECIMAL(10,2),
    procedure_code VARCHAR(10),
    visit_type VARCHAR(20)
);

-- Create a publication for the table
CREATE PUBLICATION my_publication FOR TABLE patient_appointments;

-- Or for all tables in the schema:
-- CREATE PUBLICATION my_publication FOR ALL TABLES;

Keep in mind that the replication user must have SELECT permission on all published tables. Without it, the initial table synchronization (COPY phase) hangs indefinitely in state ‘d’ (data copy).

Create matching schema and subscription on the subscriber

Connect to the subscriber instance and execute:

-- Create the target table with IDENTICAL schema
-- The table structure must match the publisher exactly
CREATE TABLE patient_appointments (
    appointment_id SERIAL PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    patient_name VARCHAR(100),
    ssn VARCHAR(11),
    date_of_birth DATE,
    diagnosis_code VARCHAR(10),
    appointment_date DATE NOT NULL,
    doctor_id INTEGER NOT NULL,
    department VARCHAR(50),
    billing_amount DECIMAL(10,2),
    procedure_code VARCHAR(10),
    visit_type VARCHAR(20)
);

-- Create the subscription
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=rds-publisher.xxx.us-west-2.rds.amazonaws.com port=5432 dbname=postgres user=repluser password=YourStrongPassword '
PUBLICATION my_publication;

PostgreSQL 15+ on RDS defaults to rds.force_ssl = 1, which means the database rejects any non-SSL connection. When CoIP is enabled, RDS on Outposts automatically uses the CoIP address for all connections from both inside and outside of the VPC. Traffic routes through the Local Gateway even for same-outpost communication. PostgreSQL’s pg_hba.conf treats these CoIP-routed connections as non-local, and with rds.force_ssl = 1, the server requires SSL. If the CREATE SUBSCRIPTION connection string omits sslmode=require, the subscriber’s connection attempt is rejected with a no pg_hba.conf entry for host error. To avoid this, always include sslmode=require in the subscription connection string:

CREATE SUBSCRIPTION my_subscription CONNECTION 'host=rds-publisher.xxx.us-west-2.rds.amazonaws.com port=5432 dbname=postgres user=repluser password=YourStrongPassword sslmode=require'
PUBLICATION my_publication;

The subscription will automatically:

1. Create a replication slot on the publisher.
2. Perform an initial table sync (COPY of existing data).
3. Begin streaming ongoing changes in real-time.

Step 6: Monitor and verify replication

To monitor replication and verify that logical replication is functioning correctly between the publisher and subscriber instance, PostgreSQL provides several system catalog views and statistics views to monitor replication health. On the publisher instance, we query pg_stat_replication to confirm active write-ahead log (WAL) sender connections and track replication progress through sent and written log sequence number (LSN) positions. We also query pg_replication_slots to verify that the logical replication slot is active and its WAL retention status is healthy. On the subscriber instance, we query pg_subscription to confirm the subscription is enabled and connected to the correct upstream database. We query pg_subscription_rel to verify the synchronization state of each replicated table.

Check replication status on the publisher

-- View active replication connections
SELECT pid, usename, client_addr, state, sent_lsn, write_lsn
FROM pg_stat_replication;

-- View replication slots
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn, wal_status
FROM pg_replication_slots;

Check subscription status on the subscriber

-- View subscription details
SELECT subname, subenabled, subconninfo FROM pg_subscription;

-- View subscription state
SELECT srsubstate FROM pg_subscription_rel;
-- States: 'i' = init, 'd' = data copy, 'r' = ready, 's' = synced, 'f' = finished table copy

Expected output after successful initial sync:

srsubstate
-----------
r

Test data replication

-- On publisher:
INSERT INTO patient_appointments (patient_id, appointment_date, doctor_id, department, billing_amount, visit_type)
VALUES (1001, '2026-07-15', 42, 'Cardiology', 250.00, 'Follow-up');

-- On subscriber (should appear within seconds):
SELECT count(*) FROM patient_appointments;
SELECT * FROM patient_appointments WHERE patient_id = 1001;

Troubleshooting common issues

Issue 1: RDS endpoint not reachable through CoIP

Symptom: TCP connection to CoIP address times out.

Diagnosis:

# From an EC2 instance on the same subnet
telnet rds-publisher.xxx.us-west-2.rds.amazonaws.com 5432

Check:

• Route table has both cross-outpost and same-outpost hairpin routes.
• Security groups allow port 5432 from CoIP ranges.
• CoIP is enabled on the RDS instance (CustomerOwnedIpEnabled: true).

Issue 2: CoIP not assigned to RDS instance

Symptom: CustomerOwnedIpEnabled: false when describing the RDS instance.

Cause: RDS doesn’t inherit the subnet’s MapCustomerOwnedIpOnLaunch setting. Must be explicitly enabled.

Fix:

aws rds modify-db-instance \
--db-instance-identifier rds-xxx \
--enable-customer-owned-ip \
--apply-immediately

Issue 3: Initial sync stuck in state ‘d’

Symptom: pg_subscription_rel shows srsubstate = 'd' indefinitely.

Causes:

• Replication user lacks SELECT permission on published tables.
• Table schema mismatch between publisher and subscriber.
• Network connectivity issue during COPY phase.

Fix:

-- On publisher: grant SELECT permission
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repluser;

-- On subscriber: drop and recreate subscription
ALTER SUBSCRIPTION my_subscription DISABLE;
ALTER SUBSCRIPTION my_subscription SET (slot_name = NONE);
DROP SUBSCRIPTION my_subscription;

-- Recreate subscription
CREATE SUBSCRIPTION my_subscription CONNECTION '...' PUBLICATION my_publication;

Issue 4: “no pg_hba.conf entry for host” error

Symptom: Connection refused with pg_hba error when connecting from CoIP address.

Cause: rds.force_ssl = 1 (default in PostgreSQL 15+) requires SSL for connections from non-VPC addresses.

Fix: Make sure sslmode=require is included in the connection string:

CONNECTION 'host=... port=5432 dbname=... user=... password=... sslmode=require'

Issue 5: Subscription pointing to wrong publication

Symptom: Subscription connected but no data replicating.

Fix: Drop and recreate subscription with correct publication name:

ALTER SUBSCRIPTION my_subscription DISABLE;
ALTER SUBSCRIPTION my_subscription SET (slot_name = NONE);
DROP SUBSCRIPTION my_subscription;

-- Clean up orphaned slots on publisher if needed
-- SELECT pg_drop_replication_slot('my_subscription');

CREATE SUBSCRIPTION my_subscription
CONNECTION '...'
PUBLICATION correct_publication_name;

Data residency considerations

This architecture provides complete data residency by the following means:

• No regional backups: Automated backups to Amazon S3 in the Region are disabled. All data remains on the Outposts.
• On-premises replication: All replication traffic flows through Local Gateways and your on-premises network, never traversing the Region backbone.
• Local recovery: If an Outpost fails, you can promote the subscriber to become the new primary, maintaining business continuity without regional dependencies.
• Selective replication: With logical replication, you can exclude sensitive columns, such as protected health information (PHI) and personally identifiable information (PII), from replication by creating publications for specific columns or tables.

For customers who can accept local backups, RDS on Outposts supports local backup to S3 on Outposts for MySQL and PostgreSQL (not supported for Multi-AZ deployments or read replicas). This requires S3 on Outposts capacity.

Best practices and key takeaways

Throughout this implementation, we encountered several configuration details that are important for a successful deployment. The following best practices and key takeaways will help you avoid common pitfalls when setting up PostgreSQL logical replication between RDS instances on separate Outposts.

• CoIP must be explicitly enabled on RDS: It isn’t inherited from subnet settings. Always include --enable-customer-owned-ip when creating RDS instances.
• Both cross-outpost and hairpin routes are required: Without hairpin routes, same-subnet access to CoIP addresses fails because DNS resolves to CoIP instead of the private IP.
• Replication user needs SELECT permission: Required for the initial COPY phase. Without it, the subscription hangs in state ‘d’.
• Table schemas must be identical: The subscriber table must exactly match the publisher table structure, including column names, types, and constraints.
• SSL is enforced by default: PostgreSQL 15+ defaults to rds.force_ssl = 1. Use sslmode=require for external connections.
• Use Terraform or the CLI for deployment: AWS CloudFormation doesn’t support the EnableCustomerOwnedIp property. Use Terraform (customer_owned_ip_enabled = true) or the AWS CLI.
• Monitor replication lag: Use the pg_stat_replication and pg_replication_slots views to monitor replication health and lag.
• Plan for failover: Document procedures for promoting the subscriber to primary if Outpost A fails, including application connection string updates.

Comparison with Multi-AZ deployments

Multi-AZ deployments remain the recommended approach for high availability on Amazon RDS on AWS Outposts. They provide automatic failover with synchronous replication between a primary DB instance on one Outpost and a standby on a different Outpost. However, Multi-AZ requires two Outposts anchored to different Availability Zones, and local backups aren’t supported for Multi-AZ deployments. As AWS documentation notes, customers whose workloads must abide by data residency regulations should consult with regulators to determine whether Multi-AZ meets their requirements, because only Single-AZ instances support storing all automated backups and logs directly on the Outposts rack. This post presents an alternative approach that uses PostgreSQL logical replication between Outposts for organizations that need an additional layer of data resilience while keeping all data on-premises. It applies specifically where data residency requirements prevent the use of Regional backups and where the Multi-AZ infrastructure prerequisites (two Outposts across two Availability Zones) are not available. The two options have different characteristics:

For organizations that can accept automated backups to the Region, Multi-AZ deployments offer simpler management and automatic failover. For those with absolute data residency requirements, logical replication with CoIP provides complete control over data locality.

Clean up

When you’re done testing, remove the replication resources to avoid unnecessary costs and WAL accumulation.

On the target, drop the subscription first:

DROP SUBSCRIPTION my_subscription;

On the source, drop the publication:

DROP PUBLICATION my_publication;

Drop the test tables on both source and target:

DROP TABLE IF EXISTS patient_appointments;

If you created dedicated Amazon RDS instances for this walkthrough, delete them through the AWS Management Console or CLI to stop incurring charges. If you modified an existing parameter group, consider reverting rds.logical_replication to 0 if you no longer need logical replication, and reboot the instance.

Conclusion

In this post, we showed you how to configure PostgreSQL logical replication between Amazon RDS for PostgreSQL instances on separate AWS Outposts. This approach gives organizations with strict data residency requirements a way to keep all data on-premises. By using Customer-owned IP addresses and Local Gateways, you can establish secure, low-latency replication that keeps all data within your on-premises environment.

This architecture is particularly valuable for:

• Healthcare providers managing PHI under the Health Insurance Portability and Accountability Act (HIPAA).
• Financial institutions with data sovereignty requirements.
• Government agencies with classified or sensitive data.
• Organizations in regulated industries where data must remain on-premises.

The configuration requires careful attention to networking details, particularly the hairpin routes and explicit CoIP enablement. After it’s established, it provides a foundation for disaster recovery and high availability without compromising data residency.

For more information about Amazon RDS on AWS Outposts, visit the RDS on Outposts documentation and the AWS Outposts High Availability Design whitepaper.

About the authors