Seamless Major Upgrade in Aurora RDS Postgres

Major-version PostgreSQL upgrades remain one of the most time-consuming and complex operations an engineering team runs, and for most teams it happens several times a year. AWS Managed services offer Blue/Green, but downtime is unpredictable (typically 60 to 120 seconds within a 5 to 10 minute switchover window), and the mechanism does not fit well with hundreds of clusters, bidirectional rollback requirements, shared PgBouncer fleets, or strict DDL-freeze windows.

At Workato (Infrastructure Storage Team) we built an in-house seamless upgrade pipeline for Aurora PostgreSQL that combines native Postgres primitives with the AWS Aurora API, structured around three decoupled phases: T-N (clone, upgrade, bootstrap logical replication), T-0 (pool-coordinated switchover and sequence synchronization), and T+N (rollback window and cleanup). The pipeline leans on logical replication with explicit LSN alignment on Aurora shared storage, DDL-lockdown event triggers, sequence synchronization, and a custom PgBouncer control plane that pauses, rewires, and resumes an entire pool fleet atomically.

This talk walks through the architecture, the trade-offs we rejected, the bugs we hit the hard way, and the code. Attendees leave with a concrete, reversible blueprint applicable on Aurora, RDS, or self-managed PostgreSQL.