A maintenance window used to be the default answer to "how do we migrate the database." For a lot of production systems today, it isn't an option anymore — logistics platforms, payment systems, and gaming backends don't have a quiet 2 a.m. window where nobody notices a few hours of downtime. The traffic doesn't stop, and neither can the database.
The good news is that PostgreSQL has supported logical replication natively since version 10, and the pattern for using it to run a zero-downtime migration is well understood. The bad news is that most of the risk in a migration like this isn't in the replication technology — it's in the dozen small details around it that don't get replicated automatically.
The core technique
Logical replication streams row-level changes from a source database to a target, independent of the underlying infrastructure on either side. That's what makes it useful for migrations that cross cloud providers, major version upgrades, or moves between wildly different environments — the source keeps taking production traffic while the target quietly stays in sync in the background.
The migration itself follows a predictable shape:
- Baseline and inventory. Schema, extensions, sequences, triggers, foreign data wrappers, and anything else that logical replication doesn't carry over on its own.
- Stand up the target and replicate the schema — not just tables, but indexes, constraints, and extension versions that match the source closely enough to behave the same way under load.
- Initial bulk copy of existing data, followed by a replication slot that starts catching up on everything written since the copy began.
- Validate under real traffic. Row counts and checksums first, then replication lag under actual production load — not a synthetic benchmark.
- Cut over. A short, measured window — seconds, not hours — where connections drain from the old target and reconnect to the new one.
- Hold the rollback path live for days after cutover, not minutes. The old cluster keeps receiving replicated changes in reverse until the new one has proven itself under a full business cycle.
We've run this exact pattern on clusters sustaining more than 16,000 transactions per second during the cutover window itself — the technique holds up as well at that scale as it does on a modest single-region deployment. The difference at scale is entirely in how much validation happens before anyone touches the cutover switch.
Where migrations actually go wrong
Almost none of the incidents we've seen in PostgreSQL migrations were caused by logical replication failing to replicate. They were caused by things logical replication was never going to handle in the first place:
- Sequences. Logical replication doesn't automatically keep sequence values in sync — if you don't explicitly resync them before cutover, the first few inserts on the new primary can collide with rows that already exist.
- DDL changes mid-flight. Native logical replication doesn't replicate schema changes. A migration that runs a schema change on the source partway through the sync needs an explicit plan for propagating it to the target, or the two databases quietly drift apart.
- Large objects and unusual data types. Some object types need special handling under logical replication. Find out during the inventory step, not during cutover.
- Extension version mismatches. PostGIS, pg_cron, and similar extensions need matching versions on both sides, or behavior can differ in subtle ways that don't show up until specific queries run.
- A rushed connection drain. The most common self-inflicted outage in a "zero-downtime" migration is closing connections to the old database before the application is actually ready to reconnect to the new one. This is an application-layer coordination problem more than a database problem.
What actually makes it zero-downtime
The replication technology gets you 90% of the way there. The other 10% — sequence resync, DDL discipline, connection draining, and a rollback plan you're actually willing to use — is where the engineering effort goes, and it's the part that's easy to underestimate if you've only read about logical replication rather than run a cutover on a live production system.
Key takeaways
- Logical replication handles the data sync; it does not handle sequences, DDL, or connection draining — plan those separately.
- Validate under real production traffic before cutover, not a synthetic load test.
- Keep the rollback path live for days, not minutes, after cutover.
- The cutover window should be measured in seconds — if it's measured in hours, something upstream of the database needs fixing first.