How to Set Up Cross-Region Replication for PostgreSQL 16 on AWS RDS 2026

# terraform/primary/main.tf
# Provision primary PostgreSQL 16 RDS instance in us-east-1
# Requires AWS provider ~> 5.0, Terraform ~> 1.9

terraform {
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 5.0"
    }
  }
  required_version = "~> 1.9"
}

provider "aws" {
  region = "us-east-1"
  # Error handling: validate AWS credentials are configured
  skip_credentials_validation = false
  skip_region_validation      = false
}

# VPC configuration for primary region (reuse existing or create new)
data "aws_vpc" "primary" {
  # Use default VPC for simplicity; replace with custom VPC ID in production
  default = true
}

data "aws_subnets" "primary_private" {
  filter {
    name   = "vpc-id"
    values = [data.aws_vpc.primary.id]
  }
  # Filter for private subnets (no internet gateway route)
  filter {
    name   = "tag:Tier"
    values = ["private"] # Assumes subnets are tagged; remove if using default VPC
  }
}

# Security group allowing inbound PostgreSQL traffic from replica region
resource "aws_security_group" "rds_primary" {
  name        = "rds-postgres-16-primary-sg"
  vpc_id      = data.aws_vpc.primary.id
  description = "Allow PostgreSQL traffic from us-west-2 replica"

  ingress {
    from_port   = 5432
    to_port     = 5432
    protocol    = "tcp"
    # Restrict to replica region CIDR blocks (get us-west-2 VPC CIDR first)
    # For demo, allow all; restrict in production
    cidr_blocks = ["0.0.0.0/0"] # REPLACE WITH REPLICA VPC CIDR IN PROD
    description = "PostgreSQL ingress from replica region"
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
    description = "Allow all outbound traffic"
  }

  tags = {
    Name        = "rds-postgres-16-primary-sg"
    Environment = "production"
    ManagedBy   = "terraform"
  }
}

# RDS subnet group for primary instance
resource "aws_db_subnet_group" "primary" {
  name       = "postgres-16-primary-subnet-group"
  subnet_ids = data.aws_subnets.primary_private.ids
  tags = {
    Name = "PostgreSQL 16 Primary Subnet Group"
  }
}

# Primary RDS PostgreSQL 16 instance
resource "aws_db_instance" "primary" {
  identifier             = "postgres-16-primary-us-east-1"
  engine                 = "postgres"
  engine_version         = "16.1" # RDS 2026 supports PostgreSQL 16.1+
  instance_class         = "db.m6g.2xlarge" # 8 vCPU, 32GB RAM, optimized for replication
  allocated_storage      = 1000
  max_allocated_storage  = 10000 # Enable storage autoscaling
  storage_type           = "gp3"
  storage_encrypted      = true
  kms_key_id             = aws_kms_key.rds.arn # Reference to KMS key (defined below)

  db_name                = "app_production"
  username               = var.db_username
  password               = var.db_password
  port                   = 5432

  vpc_security_group_ids = [aws_security_group.rds_primary.id]
  db_subnet_group_name   = aws_db_subnet_group.primary.name
  publicly_accessible    = false

  # Enable logical replication (required for cross-region)
  parameter_group_name   = aws_db_parameter_group.postgres16.name

  multi_az               = true # Single-region HA
  backup_retention_period = 35 # 35 days backup retention
  backup_window          = "03:00-05:00"
  maintenance_window     = "Mon:05:00-Mon:07:00"

  # Enable enhanced monitoring
  monitoring_interval    = 60
  monitoring_role_arn    = aws_iam_role.rds_monitoring.arn

  tags = {
    Name        = "PostgreSQL 16 Primary RDS"
    Environment = "production"
    Replication = "primary"
  }
}

# KMS key for RDS encryption
resource "aws_kms_key" "rds" {
  description             = "KMS key for RDS PostgreSQL 16 encryption"
  deletion_window_in_days = 30
  enable_key_rotation     = true
}

# DB parameter group for PostgreSQL 16 with logical replication enabled
resource "aws_db_parameter_group" "postgres16" {
  name   = "postgres16-logical-replication"
  family = "postgres16"

  # Enable logical replication (PostgreSQL 16 default is off)
  parameter {
    name  = "rds.logical_replication"
    value = "1"
  }
  # Increase max replication slots (default 10, increase for multiple replicas)
  parameter {
    name  = "max_replication_slots"
    value = "20"
  }
  # Increase max wal senders (default 10)
  parameter {
    name  = "max_wal_senders"
    value = "20"
  }
  # Enable DDL replication (new in PostgreSQL 16)
  parameter {
    name  = "wal_level"
    value = "logical"
  }
}

# IAM role for RDS enhanced monitoring
resource "aws_iam_role" "rds_monitoring" {
  name = "rds-monitoring-role"

  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Action = "sts:AssumeRole"
        Effect = "Allow"
        Principal = {
          Service = "monitoring.rds.amazonaws.com"
        }
      }
    ]
  })
}

resource "aws_iam_role_policy_attachment" "rds_monitoring" {
  role       = aws_iam_role.rds_monitoring.name
  policy_arn = "arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole"
}

# Variables
variable "db_username" {
  type        = string
  description = "Master username for RDS instance"
  sensitive   = true
}

variable "db_password" {
  type        = string
  description = "Master password for RDS instance"
  sensitive   = true
  validation {
    condition     = length(var.db_password) >= 16
    error_message = "DB password must be at least 16 characters long."
  }
}

# Outputs
output "primary_rds_endpoint" {
  value       = aws_db_instance.primary.endpoint
  description = "Primary RDS instance endpoint"
}

output "primary_rds_port" {
  value       = aws_db_instance.primary.port
  description = "Primary RDS instance port"
}

# terraform/replica/main.tf
# Provision cross-region PostgreSQL 16 RDS replica in us-west-2
# Requires AWS provider ~> 5.0, Terraform ~> 1.9

terraform {
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 5.0"
    }
  }
  required_version = "~> 1.9"
}

provider "aws" {
  region = "us-west-2"
  # Error handling: validate AWS credentials are configured for replica region
  skip_credentials_validation = false
  skip_region_validation      = false
}

# VPC configuration for replica region
data "aws_vpc" "replica" {
  default = true
}

data "aws_subnets" "replica_private" {
  filter {
    name   = "vpc-id"
    values = [data.aws_vpc.replica.id]
  }
  filter {
    name   = "tag:Tier"
    values = ["private"]
  }
}

# Security group allowing inbound traffic from primary region
resource "aws_security_group" "rds_replica" {
  name        = "rds-postgres-16-replica-sg"
  vpc_id      = data.aws_vpc.replica.id
  description = "Allow PostgreSQL traffic from us-east-1 primary"

  ingress {
    from_port   = 5432
    to_port     = 5432
    protocol    = "tcp"
    # Restrict to primary region VPC CIDR (replace with primary VPC CIDR in prod)
    cidr_blocks = ["0.0.0.0/0"] # REPLACE WITH PRIMARY VPC CIDR IN PROD
    description = "PostgreSQL ingress from primary region"
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
    description = "Allow all outbound traffic"
  }

  tags = {
    Name        = "rds-postgres-16-replica-sg"
    Environment = "production"
    ManagedBy   = "terraform"
  }
}

# RDS subnet group for replica instance
resource "aws_db_subnet_group" "replica" {
  name       = "postgres-16-replica-subnet-group"
  subnet_ids = data.aws_subnets.replica_private.ids
  tags = {
    Name = "PostgreSQL 16 Replica Subnet Group"
  }
}

# Replica RDS PostgreSQL 16 instance (standalone, not RDS read replica, for logical replication)
resource "aws_db_instance" "replica" {
  identifier             = "postgres-16-replica-us-west-2"
  engine                 = "postgres"
  engine_version         = "16.1"
  instance_class         = "db.m6g.2xlarge"
  allocated_storage      = 1000
  max_allocated_storage  = 10000
  storage_type           = "gp3"
  storage_encrypted      = true
  kms_key_id             = aws_kms_key.rds_replica.arn

  db_name                = "app_production" # Must match primary database name
  username               = var.db_username
  password               = var.db_password
  port                   = 5432

  vpc_security_group_ids = [aws_security_group.rds_replica.id]
  db_subnet_group_name   = aws_db_subnet_group.replica.name
  publicly_accessible    = false

  # Enable logical replication on replica
  parameter_group_name   = aws_db_parameter_group.postgres16_replica.name

  multi_az               = false # Replica is single-AZ for read workloads
  backup_retention_period = 7 # Lower backup retention for replica
  backup_window          = "03:00-05:00"
  maintenance_window     = "Tue:05:00-Tue:07:00"

  # Disable automated backups for replica (use primary backups)
  # backup_retention_period = 0 # Uncomment if using primary for all backups

  monitoring_interval    = 60
  monitoring_role_arn    = aws_iam_role.rds_monitoring_replica.arn

  tags = {
    Name        = "PostgreSQL 16 Replica RDS"
    Environment = "production"
    Replication = "replica"
  }
}

# KMS key for replica encryption (use same as primary for cross-region replication if using AWS KMS multi-region keys)
resource "aws_kms_key" "rds_replica" {
  description             = "KMS key for Replica RDS PostgreSQL 16 encryption"
  deletion_window_in_days = 30
  enable_key_rotation     = true
  # Enable multi-region key to match primary (required for encrypted replication)
  multi_region            = true
}

# DB parameter group for replica with logical replication enabled
resource "aws_db_parameter_group" "postgres16_replica" {
  name   = "postgres16-replica-logical"
  family = "postgres16"

  parameter {
    name  = "rds.logical_replication"
    value = "1"
  }
  parameter {
    name  = "max_replication_slots"
    value = "20"
  }
  parameter {
    name  = "max_wal_senders"
    value = "20"
  }
  parameter {
    name  = "wal_level"
    value = "logical"
  }
  # Enable hot standby for read replicas
  parameter {
    name  = "hot_standby"
    value = "1"
  }
}

# IAM role for replica enhanced monitoring
resource "aws_iam_role" "rds_monitoring_replica" {
  name = "rds-monitoring-replica-role"

  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Action = "sts:AssumeRole"
        Effect = "Allow"
        Principal = {
          Service = "monitoring.rds.amazonaws.com"
        }
      }
    ]
  })
}

resource "aws_iam_role_policy_attachment" "rds_monitoring_replica" {
  role       = aws_iam_role.rds_monitoring_replica.name
  policy_arn = "arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole"
}

# Variables (same as primary)
variable "db_username" {
  type        = string
  description = "Master username for RDS instance"
  sensitive   = true
}

variable "db_password" {
  type        = string
  description = "Master password for RDS instance"
  sensitive   = true
  validation {
    condition     = length(var.db_password) >= 16
    error_message = "DB password must be at least 16 characters long."
  }
}

# Outputs
output "replica_rds_endpoint" {
  value       = aws_db_instance.replica.endpoint
  description = "Replica RDS instance endpoint"
}

output "replica_rds_port" {
  value       = aws_db_instance.replica.port
  description = "Replica RDS instance port"
}

# scripts/setup_logical_replication.py
# Configure PostgreSQL 16 logical replication between primary and cross-region replica
# Requires: psycopg2-binary==2.9.9, boto3==1.34.0, Python 3.11+

import logging
import sys
import time
from typing import Optional

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
from psycopg2.errors import OperationalError, DuplicateObject

# Configure logging
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s - %(levelname)s - %(message)s",
    handlers=[logging.StreamHandler(sys.stdout)]
)
logger = logging.getLogger(__name__)

# Configuration (replace with values from Terraform outputs)
PRIMARY_ENDPOINT = "postgres-16-primary-us-east-1.xxxxxx.us-east-1.rds.amazonaws.com"
PRIMARY_PORT = 5432
REPLICA_ENDPOINT = "postgres-16-replica-us-west-2.xxxxxx.us-west-2.rds.amazonaws.com"
REPLICA_PORT = 5432
DB_NAME = "app_production"
DB_USER = "postgres"
DB_PASSWORD = "your-secure-password" # Use AWS Secrets Manager in production
REPLICATION_SLOT_NAME = "primary_to_replica_slot"
PUBLICATION_NAME = "app_production_pub"
SUBSCRIPTION_NAME = "replica_sub"

def get_db_connection(
    endpoint: str,
    port: int,
    dbname: str,
    user: str,
    password: str,
    replication: bool = False
) -> Optional[psycopg2.extensions.connection]:
    """Create a PostgreSQL connection with error handling."""
    try:
        conn = psycopg2.connect(
            host=endpoint,
            port=port,
            dbname=dbname,
            user=user,
            password=password,
            replication=replication # Set to True for replication slot creation
        )
        conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
        logger.info(f"Connected to {endpoint}:{port}/{dbname}")
        return conn
    except OperationalError as e:
        logger.error(f"Failed to connect to {endpoint}:{port}/{dbname}: {e}")
        return None

def create_publication(conn: psycopg2.extensions.connection) -> bool:
    """Create a PostgreSQL 16 publication for all tables in the database."""
    try:
        with conn.cursor() as cur:
            # PostgreSQL 16 supports FOR ALL TABLES including DDL changes
            cur.execute(
                f"CREATE PUBLICATION {PUBLICATION_NAME} FOR ALL TABLES WITH (publish = 'insert, update, delete, truncate, ddl');"
            )
            logger.info(f"Created publication {PUBLICATION_NAME}")
            return True
    except DuplicateObject:
        logger.warning(f"Publication {PUBLICATION_NAME} already exists, skipping creation")
        return True
    except Exception as e:
        logger.error(f"Failed to create publication: {e}")
        return False

def create_replication_slot(conn: psycopg2.extensions.connection) -> bool:
    """Create a logical replication slot on the primary."""
    try:
        with conn.cursor() as cur:
            # Use pgoutput plugin (default for PostgreSQL logical replication)
            cur.execute(
                f"SELECT pg_create_logical_replication_slot('{REPLICATION_SLOT_NAME}', 'pgoutput');"
            )
            slot_name = cur.fetchone()[0]
            logger.info(f"Created replication slot {slot_name}")
            return True
    except DuplicateObject:
        logger.warning(f"Replication slot {REPLICATION_SLOT_NAME} already exists, skipping creation")
        return True
    except Exception as e:
        logger.error(f"Failed to create replication slot: {e}")
        return False

def create_subscription(replica_conn: psycopg2.extensions.connection) -> bool:
    """Create a subscription on the replica to pull data from primary."""
    try:
        with replica_conn.cursor() as cur:
            # Connect to primary via public endpoint (use VPC peering in production)
            # copy_data = true to initial load existing data
            # synchronous_commit = false for cross-region latency optimization
            cur.execute(f"""
                CREATE SUBSCRIPTION {SUBSCRIPTION_NAME}
                CONNECTION 'host={PRIMARY_ENDPOINT} port={PRIMARY_PORT} dbname={DB_NAME} user={DB_USER} password={DB_PASSWORD}'
                PUBLICATION {PUBLICATION_NAME}
                WITH (
                    copy_data = true,
                    synchronous_commit = false,
                    create_slot = false,
                    slot_name = '{REPLICATION_SLOT_NAME}'
                );
            """)
            logger.info(f"Created subscription {SUBSCRIPTION_NAME} on replica")
            return True
    except DuplicateObject:
        logger.warning(f"Subscription {SUBSCRIPTION_NAME} already exists, skipping creation")
        return True
    except Exception as e:
        logger.error(f"Failed to create subscription: {e}")
        return False

def verify_replication(replica_conn: psycopg2.extensions.connection) -> bool:
    """Verify replication is working by checking subscription status."""
    try:
        with replica_conn.cursor() as cur:
            cur.execute(f"SELECT subname, subenabled, subconninfo FROM pg_subscription WHERE subname = '{SUBSCRIPTION_NAME}';")
            sub = cur.fetchone()
            if not sub:
                logger.error("Subscription not found")
                return False
            if not sub[1]:
                logger.error("Subscription is disabled")
                return False
            logger.info(f"Subscription {sub[0]} is enabled and connected to primary")
            # Check replica lag
            cur.execute("SELECT pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn();")
            lag = cur.fetchone()[0]
            logger.info(f"Current replica lag: {lag}")
            return True
    except Exception as e:
        logger.error(f"Failed to verify replication: {e}")
        return False

def main():
    # Connect to primary
    primary_conn = get_db_connection(
        PRIMARY_ENDPOINT, PRIMARY_PORT, DB_NAME, DB_USER, DB_PASSWORD
    )
    if not primary_conn:
        sys.exit(1)

    # Connect to replica
    replica_conn = get_db_connection(
        REPLICA_ENDPOINT, REPLICA_PORT, DB_NAME, DB_USER, DB_PASSWORD
    )
    if not replica_conn:
        primary_conn.close()
        sys.exit(1)

    # Create publication on primary
    if not create_publication(primary_conn):
        primary_conn.close()
        replica_conn.close()
        sys.exit(1)

    # Create replication slot on primary (using replication connection)
    primary_repl_conn = get_db_connection(
        PRIMARY_ENDPOINT, PRIMARY_PORT, DB_NAME, DB_USER, DB_PASSWORD, replication=True
    )
    if not primary_repl_conn:
        primary_conn.close()
        replica_conn.close()
        sys.exit(1)
    if not create_replication_slot(primary_repl_conn):
        primary_conn.close()
        replica_conn.close()
        primary_repl_conn.close()
        sys.exit(1)
    primary_repl_conn.close()

    # Create subscription on replica
    if not create_subscription(replica_conn):
        primary_conn.close()
        replica_conn.close()
        sys.exit(1)

    # Wait for initial data copy to complete
    logger.info("Waiting 60 seconds for initial data copy to complete...")
    time.sleep(60)

    # Verify replication
    if not verify_replication(replica_conn):
        logger.error("Replication verification failed")
        primary_conn.close()
        replica_conn.close()
        sys.exit(1)

    logger.info("Cross-region logical replication setup complete!")
    primary_conn.close()
    replica_conn.close()

if __name__ == "__main__":
    main()

# scripts/monitor_replication.py
import boto3
import psycopg2
from datetime import datetime

cloudwatch = boto3.client('cloudwatch', region_name='us-east-1')

def get_replication_lag():
    conn = psycopg2.connect(
        host="postgres-16-primary-us-east-1.xxxxxx.us-east-1.rds.amazonaws.com",
        port=5432,
        dbname="app_production",
        user="postgres",
        password="your-password"
    )
    cur = conn.cursor()
    cur.execute("SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) * 1000;")
    lag_ms = cur.fetchone()[0]
    conn.close()
    return lag_ms

def push_metric(lag_ms):
    cloudwatch.put_metric_data(
        Namespace='RDS/PostgreSQL/Replication',
        MetricData=[
            {
                'MetricName': 'ReplicationLagMs',
                'Value': lag_ms,
                'Unit': 'Milliseconds',
                'Dimensions': [
                    {'Name': 'DBInstanceIdentifier', 'Value': 'postgres-16-primary-us-east-1'},
                    {'Name': 'Region', 'Value': 'us-east-1'}
                ]
            }
        ]
    )

if __name__ == "__main__":
    lag = get_replication_lag()
    push_metric(lag)
    print(f"Pushed replication lag: {lag}ms")

# Fetch RDS credentials from AWS Secrets Manager
import boto3
import json

def get_db_credentials(secret_arn):
    client = boto3.client('secretsmanager', region_name='us-east-1')
    response = client.get_secret_value(SecretId=secret_arn)
    secret = json.loads(response['SecretString'])
    return secret['username'], secret['password']

# Usage in setup script
DB_USER, DB_PASSWORD = get_db_credentials('arn:aws:secretsmanager:us-east-1:123456789012:secret:postgres-16-creds')

# Lambda function to promote replica on failover
import boto3

rds = boto3.client('rds', region_name='us-west-2')

def lambda_handler(event, context):
    # Promote replica to primary
    response = rds.promote_read_replica(
        DBInstanceIdentifier='postgres-16-replica-us-west-2',
        BackupRetentionPeriod=35,
        PreferredBackupWindow='03:00-05:00'
    )
    print(f"Promoted replica to primary: {response['DBInstance']['DBInstanceIdentifier']}")
    return {'status': 'success'}

postgres16-rds-cross-region-replication-2026/
├── terraform/
│   ├── primary/          # Terraform config for us-east-1 primary RDS
│   │   ├── main.tf
│   │   ├── variables.tf
│   │   └── outputs.tf
│   └── replica/          # Terraform config for us-west-2 replica RDS
│       ├── main.tf
│       ├── variables.tf
│       └── outputs.tf
├── scripts/
│   ├── setup_logical_replication.py  # Replication setup script
│   ├── monitor_replication.py        # CloudWatch metrics script
│   └── failover.py                   # Automated failover script
├── benchmarks/
│   ├── replication_lag_results.csv   # Benchmark data from 12 regions
│   └── cost_analysis.xlsx            # Cost comparison spreadsheet
└── README.md                         # Setup instructions and troubleshooting