Header Banner Image
Your
Trusted
Get Fully AWS Funded
Cloud Migration

Zero-Downtime Migration Plan: PostgreSQL 11 (On-Prem) → RDS PostgreSQL 14

This document outlines the high-level plan for performing a zero‑downtime migration from an on‑premises PostgreSQL 11 database to Amazon RDS PostgreSQL 14 using logical replication and ProxySQL. It is designed for planning and Confluence use.

1. Overview

The goal is to migrate a large PostgreSQL 11 database to RDS PostgreSQL 14 without interrupting application writes. This is achieved by restoring a consistent snapshot, enabling logical replication, continuously syncing changes, and performing a controlled cutover using ProxySQL.

2. High-Level Migration Phases
Phase 1 — Target Preparation (RDS)

  • Create RDS PostgreSQL 14 instance.

  • Enable logical replication via parameter group.

  • Configure networking to allow inbound connections from the on‑prem PG11 system.

  • Create the target database and required schemas.

Phase 2 — Source Preparation (PG11)

  • Ensure logical replication settings are enabled.

  • Configure replication user and pg_hba rules.

  • Create publication for required tables or schemas.

Phase 3 — Application Layer Preparation

  • Deploy ProxySQL in front of the application.

  • Route all database connections through ProxySQL.

  • Prepare routing rules for the future cutover.

Phase 4 — Initial Full Snapshot

  • Create a consistent snapshot using a repeatable‑read transaction.

  • Perform a schema-only dump.

  • Perform a data dump from the snapshot.

  • Writes continue normally during the entire process.

Phase 5 — Restore Snapshot into RDS

  • Restore schema into RDS.

  • Restore data into RDS.

  • Validate basic structure and row counts.

Phase 6 — Start Logical Replication

  • Create a subscription on RDS for the PG11 publication.

  • Allow RDS to replay all changes since the snapshot.

  • Maintain replication until lag approaches zero.

Phase 7 — Pre-Cutover Validation

  • Validate replication performance and stability.

  • Verify schema alignment.

  • Perform read-only testing against RDS through ProxySQL.

Phase 8 — Zero-Downtime Cutover via ProxySQL

  • ProxySQL drains existing write connections.

  • New write connections are routed to RDS.

  • Ensure replication catch-up reaches zero.

  • Disable subscription on RDS.

  • Keep PG11 in read-only mode temporarily for fallback.

Phase 9 — Stabilization and Cleanup

  • Monitor application behavior on RDS.

  • Decommission logical replication.

  • Retain PG11 in read-only mode for a short fallback window.

  • Fully retire PG11 when stable.

3. Key Characteristics of This Approach

  • No write downtime required at any stage.

  • Large databases supported through snapshot + logical sync.

  • Controlled cutover with instant rollback capability.

  • ProxySQL ensures smooth connection draining and switching.

  • PG11 remains operational until final validation.

4. Responsibilities Breakdown

  • Infrastructure: RDS provisioning, networking, parameter groups.

  • Database Operations: Snapshot creation, restore process, logical replication setup.

  • Application Team: ProxySQL integration and cutover readiness.

  • SRE / Ops: Monitoring, fallback planning, final validation.

5. Expected Outcomes

  • Migration executed with zero application downtime.

  • All real-time writes from PG11 copied into RDS via logical replication.

  • Seamless cutover with ProxySQL managing write routing.

  • Clean fallback path until final sign‑off.

6. Notes

  • Schema compatibility must be maintained during the entire process.

  • Sequences will require alignment post‑cutover.

  • Long‑running PG11 transactions must be monitored to avoid WAL retention issues.

This plan can be expanded into detailed operational runbooks, SOPs, and checklists as needed.