Single PostgreSQL Primary→PostgreSQL Primary with Read Replicas and Routing Proxy
Duration Estimate
2–6 weeks
Migration Stages
5
Blocking Prerequisites
2
Confidence
StrongReadiness Checklist(2 blocking, 2 advisory)
Team at mid maturity level
This migration requires mid engineering maturity. The team should have hands-on experience with the relevant technologies and proven ability to execute complex system changes safely.
Guidance: Build team skills through learning modules before attempting this migration.
Production observability baseline established
Comprehensive metrics, logs, and traces must be in place before the migration starts. You need a clear baseline to detect regressions during and after each migration stage.
Guidance: Instrument key metrics (latency p99, error rate, throughput, resource utilization) on all affected components.
Rollback plan documented and tested
Rollback feasibility for this migration is 'complex'. Rollback is possible but may be complex, document the exact rollback steps and test them in a non-production environment.
Guidance: Run a full dry-run in a staging environment that mirrors production load characteristics.
Migration trigger condition confirmed
Confirm that the actual trigger for this migration is present. Common triggers: Read latency rising as sequential scans on large tables compete with active writes for shared_buffers; EXPLAIN output shows sequential scans on tables >1GB that cannot be indexed without unacceptable write amplification; Connection pool saturation driven by long-running read-only background jobs (reporting, exports, analytics).
Guidance: Validate the trigger with production metrics or benchmarks before committing to the migration timeline.
Migration Stages
Set wal_level=replica and max_wal_senders >= 2 on the primary. Create a replication user. Provision replica server. Run pg_basebackup to seed the replica from a base backup. Create standby.signal and postgresql.auto.conf with primary_conninfo. Validate replica is streaming (pg_stat_replication on primary shows active WAL sender).
Key tasks
- ·Set wal_level=replica and max_wal_senders >= 2 on the primary. Create a replication user. Provision replica server. Run pg_basebackup to seed the replica from a base backup. Create standby.signal and postgresql.auto.conf with primary_conninfo. Validate replica is streaming (pg_stat_replication on primary shows active WAL sender).
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Configure Streaming Replication' validated in production with no regression in key metrics
Rollback: possible at this stage
Audit all application query paths. Tag each query as read-only or write. Identify which read queries require immediate post-write consistency (cannot go to replica) vs. which tolerate stale reads. Instrument pg_stat_statements to quantify read vs write query volume.
Key tasks
- ·Audit all application query paths. Tag each query as read-only or write. Identify which read queries require immediate post-write consistency (cannot go to replica) vs. which tolerate stale reads. Instrument pg_stat_statements to quantify read vs write query volume.
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Instrument and Classify Queries' validated in production with no regression in key metrics
Rollback: possible at this stage
Route read queries to replica but compare results against primary for a sampled subset. This validates replica correctness and identifies queries that are not safe to serve from a lagging replica. Log replication lag during this phase to characterize worst-case staleness.
Key tasks
- ·Route read queries to replica but compare results against primary for a sampled subset. This validates replica correctness and identifies queries that are not safe to serve from a lagging replica. Log replication lag during this phase to characterize worst-case staleness.
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Shadow Routing' validated in production with no regression in key metrics
Rollback: possible at this stage
Switch background jobs, reporting queries, and non-user-session reads to replica. Monitor replication lag. Validate application correctness: specifically check that no write-then-read sequences within a user action are hitting the replica.
Key tasks
- ·Switch background jobs, reporting queries, and non-user-session reads to replica. Monitor replication lag. Validate application correctness: specifically check that no write-then-read sequences within a user action are hitting the replica.
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Replica Cutover for Non-Consistency-Sensitive Reads' validated in production with no regression in key metrics
Rollback: possible at this stage
The first replica is now a production critical path: if it fails, all read traffic falls back to the primary. Add a second replica with the same configuration. Configure load balancing across both replicas for read queries. Test replica removal and failover.
Key tasks
- ·The first replica is now a production critical path: if it fails, all read traffic falls back to the primary. Add a second replica with the same configuration. Configure load balancing across both replicas for read queries. Test replica removal and failover.
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Add Second Replica for HA' validated in production with no regression in key metrics
Rollback: possible at this stage
Rollback Decision Points
After stage 5
Trigger: Final validation: confirm all success criteria are met within 24 hours of migration completion. If any success criterion fails, trigger rollback or remediation.
Procedure: If success criteria are not met within 24 hours, escalate to the architecture team. Decide between full rollback, partial rollback, or targeted remediation based on scope.
Recovery: 24–72 hours for full assessment and remediation
Success Criteria
- ✓Target state 'PostgreSQL Primary with Read Replicas and Routing Proxy' is stable in production for 72+ hours with no regressions.
- ✓p99 latency on all affected services is within acceptable range (≤110% of pre-migration baseline).
- ✓Error rate is at or below pre-migration baseline.
This migration playbook is derived from structured architecture knowledge. Duration estimates and risk levels are structural approximations based on documented migration complexity and operational risk, not measured execution data. Validate all stages against your specific system constraints before executing.