Amazon RDS & Aurora PostgreSQL Migrations | Deep Dive.

Amazon RDS & Aurora PostgreSQL Migrations - Deep Dive.

Scope:

• Intro,
• Key Differences: RDS PostgreSQL vs Aurora PostgreSQL,
• Migration Approaches,
• AWS DMS Migration Patterns for PostgreSQL,
• DMS Architecture for PostgreSQL Migration,
• Pre-Migration Tasks (Critical),
• DMS Task Configuration Best Practices,
• Aurora PostgreSQL-Specific Considerations,
• Cutover Strategies,
• Post-Migration Health Checks,
• Architecture Diagram.

Intro:

•  Migrating PostgreSQL workloads to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL (Compatible Edition) can range from simple lift-and-shift to complex near-zero-downtime migrations.
• Amazon Aurora is a cloud-native database that is compatible with:
•  PostgreSQL for:
•  superior performance, 
• scalability, 
• availability compared to Amazon RDS for PostgreSQL.
• Choose RDS for PostgreSQL if  twtech needs:
• A cost-effective solution for general, 
• Light-to-moderate workloads, 
• Development environments, 
• or  requires compatibility with a wider range of database engines or older PostgreSQL versions.
• Choose Aurora PostgreSQL  is used if twtech has:
•  Demanding, 
• High-performance, 
• I/O-intensive, 
• or Mission-critical workloads that require high availability,
• Faster failover, 
• Superior scalability

Feature	Amazon RDS for PostgreSQL	Amazon Aurora PostgreSQL
Architecture	Runs on standard EC2 instances with Amazon EBS storage volumes.	Cloud-native architecture with decoupled, distributed, and self-healing storage that automatically scales.
Performance	Provides solid performance for general workloads; performance is tied to instance type and EBS storage.	Up to 3x faster throughput than standard PostgreSQL on RDS due to its architectural optimizations.
Scalability	Supports up to 5 read replicas and storage scales up to 64 TiB (with auto-scaling).	Supports up to 15 low-latency read replicas and storage automatically scales from 10 GB to 128 TiB.
High Availability	Offers Multi-AZ deployments with failover times typically around 1-2 minutes and a 99.95% SLA.	Provides faster, automatic failover (typically within 30 seconds) and a higher SLA of up to 99.99%.
Pricing	Generally lower baseline cost, with charges for provisioned storage (EBS type and size) and IOPS.	Higher instance pricing; storage is billed based on actual usage, and I/O charges apply (unless using I/O-Optimized).
Features	Supports a wider range of database engines (MySQL, Oracle, SQL Server, etc.).	Offers unique features like Serverless v2, Global Databases, Fast Database Cloning, and advanced caching.

1. Key Differences: RDS PostgreSQL vs Aurora PostgreSQL

• Understanding the target matters before designing the migration.

Amazon RDS for PostgreSQL

•  Managed PostgreSQL on EC2-based architecture.
•  Storage is EBS-backed.
•  Max throughput bounded by instance size and EBS.
•  Supports all native PostgreSQL extensions (most).

Amazon Aurora PostgreSQL

•  Distributed, shared-storage architecture.
•  Up to 15 low-latency read replicas.
•  Storage auto-scales to 128 TiB.
•  Superior crash recovery and replica lag.
•  Some PostgreSQL extensions are not supported.

Migration tip:

• If the source PostgreSQL uses unsupported extensions, twtech must replace or refactor before migrating to Aurora.

2. Migration Approaches

A. 1-Step Migration (Homogeneous)

Source: PostgreSQL → Target: RDS/Aurora PostgreSQL
Tools: pg_dump / pg_restore, native logical replication, or AWS DMS.

Use when:

•  Database < 1–2 TB
•  Downtime window acceptable
•  Schema/compatibility is simple

B. 2-Step Migration (Large or Zero-Downtime)

1. Schema Migration

• Use AWS SCT (Schema Conversion Tool) for validation
• Use native tools for schema: pg_dump --schema-only

2. Data Migration

• Use AWS DMS for:
•    Full load
•    Change Data Capture (CDC)
•    Continuous replication

Use when:

• DB is large (multi-TB)
• Downtime must be minutes
• twtech needs rollback capability

3. AWS DMS Migration Patterns for PostgreSQL

Supported Sources/Targets

Source	Target	Support
Self-managed PostgreSQL	RDS PostgreSQL	✔
Self-managed PostgreSQL	Aurora PostgreSQL	✔
RDS PostgreSQL	Aurora PostgreSQL	✔ (in-line upgrades)
Aurora PostgreSQL	RDS PostgreSQL	✔

4. DMS Architecture for PostgreSQL Migration

How DMS Works Internally

1.     Full Load Phase

•    Tables scanned and copied using parallel threads.
•    LOB (large objects) use a separate loading mechanism.

2.     CDC Phase (Change Data Capture)

•    DMS reads WAL (Write-Ahead Log) from: §  Source PostgreSQL replication slot.
•    WAL events translated into INSERT/UPDATE/DELETE.

3.     Apply Phase

•    Target receives ongoing changes.
•    Commit order is preserved (or relaxed mode).

5. Pre-Migration Tasks (Critical)

A. Prepare Source PostgreSQL for Logical Replication

# Enable these parameters:

wal_level = logical

max_replication_slots = 5

max_wal_senders = 10

max_worker_processes = 10

max_logical_replication_workers = 4

Grant replication privileges: CREATE ROLE dms_user WITH LOGIN REPLICATION;

B. Network & Connectivity

• VPC peering or Transit Gateway
• NACLs and SGs must allow:
•    Port 5432
•    DMS replication instance Egress to DB

C. AWS SCT (Schema Compatibility Check)

• twtech MUST run SCT when migrating to Aurora PostgreSQL because of:
•  Extension differences
•  Data type mapping issues
•  Partitioning differences
•  Function incompatibilities

6. DMS Task Configuration Best Practices

Full Load Settings

•  Enable Parallel Load for large tables.
•  Use BatchApply for higher throughput.
•  Turn off TargetMetadataOnly unless required.

CDC Settings

•  Enable Minimize Load on Source mode.
•  Adjust write-ahead logging (WAL) retention to avoid slot overflow.
•  Set commitRate for tuning latency vs performance.

Large Tables

• Use table-mapping with range-based partitioning:

# json

"partition_options": {

  "type": "range",

  "partition_type": "integer",

  "column": "id",

  "lower_bound": "1",

  "upper_bound": "5000000",

  "partition_count": "10"

}

7. Aurora PostgreSQL-Specific Considerations

A. Performance Optimizations

• Use parallel query if supported.
• Enable Aurora Babelfish (if migrating SQL Server).
• Use Global Database if multi-region.

B. Storage Scaling

• Automatic 10GB increments—no pre-provisioning required.

C. Faster Failover

• Replayless crash recovery → faster cutover times.

8. Cutover Strategies

Option A: Scheduled Downtime (Simple)

     1.     Full load + CDC (catch-up)
2.     Stop application writes
3.     Let DMS reach zero-lag
4.     Switch app to target
5.     Validate & resume traffic

Total downtime: seconds to minutes

Option B: Zero-Downtime Migration

Use:

• Aurora Fast Cloning for testing
• DMS + logical replication
• Bi-directional replication (rare; complex)

Total downtime: < 30 seconds

9. Post-Migration Health Checks

A. Data Validation

• AWS DMS Data Validation
• Table counts
•  Row-level checksums

B. Performance Benchmarking

Run:

• pgbench
• Application load tests

C. Extension Verification

• Ensure required extensions exist on target.

10. Architecture Diagram