Upgrading PostgreSQL without taking the application offline comes down to one discipline: never let the cutover be the moment you find out something is broken. Logical replication, connection pooling, and a rehearsed rollback plan turn a weekend-long maintenance window into a cutover measured in seconds.

TL;DR

• A zero downtime PostgreSQL upgrade uses logical replication to sync old and new clusters before cutover, not pg_upgrade alone.
• Test the full migration on a staging clone at least once — pg_upgrade --link saves disk space but skips validation you need before production.
• Cutover windows under 30 seconds are realistic with pgBouncer PAUSE/RESUME; anything longer means your replication lag wasn't checked first.
• PostgreSQL 12 hit end of life in November 2024, and running an unsupported major version is the most common reason teams rush this upgrade badly in 2026.

Why this matters

Major version upgrades in PostgreSQL are not in-place. Each major release changes the on-disk format, which is why pg_upgrade exists at all and why a naive restart-into-new-version approach corrupts data or simply fails to start. Teams running PostgreSQL for SaaS platforms, fintech ledgers, or e-commerce checkout flows can't accept a multi-hour outage to get current; PostgreSQL replication types, setup, and best practices provides the operational context for the side-by-side approach used here.

The zero downtime postgresql upgrade approach solves this by running two clusters side by side — old and new — synced through logical replication, and switching application traffic only after the new cluster is proven caught up. Downtime shrinks from hours to the time it takes to flip a connection string, typically under a minute.

What you'll need

• A staging environment that mirrors production schema, extensions, and data volume closely enough to catch replication issues before they hit prod
• Logical replication support — source database must be PostgreSQL 10 or later, with wal_level = logical set and a restart already scheduled to apply it if it isn't
• A connection pooler (pgBouncer or PgCat) sitting between the application and the database, since this is what lets you pause and redirect traffic without touching application code
• Disk headroom — plan for roughly double your current data size if you're running two clusters concurrently during the sync window
• A rollback script written and tested before you start, not improvised during an incident
• Monitoring on replication lag, transaction ID wraparound, and disk I/O on both clusters throughout the sync
• A maintenance calendar slot even though downtime is near zero — stakeholders still need visibility into the cutover window

The steps

1. Audit your current cluster and extensions

List every extension (pg_extension), custom type, and non-default configuration parameter before you touch anything. Extensions like postgis or pg_partman sometimes lag behind new major versions by a few weeks, and finding that out mid-upgrade stalls the whole plan.

Run SELECT * FROM pg_extension; against production and cross-check each one against the target version's compatibility notes. Common mistake: assuming an extension that compiled fine on the old version will build cleanly on the new one — always test the extension build on staging first.

2. Stand up the target version cluster

Provision a new PostgreSQL instance on the target major version — say PostgreSQL 15 moving to PostgreSQL 17 — with matching shared_buffers, work_mem, and max_connections settings as a starting point, then tune later based on load testing. Keep it isolated from production traffic at this stage.

This cluster becomes your replication target, not a live database yet. Common mistake: under-provisioning the new instance to save cost during testing, then discovering performance regressions only after cutover.

3. Enable logical replication and create the publication

Set wal_level = logical on the source if it isn't already (this requires a restart, so schedule it during low traffic — this is the one brief blip in an otherwise zero downtime plan). Then create a publication covering the tables you need to replicate:

CREATE PUBLICATION upgrade_pub FOR ALL TABLES;


On the target cluster, create a subscription pointing back at the source. Initial sync copies existing data first, then streams ongoing changes; bidirectional logical replication in PostgreSQL 16 explains why replication origin matters when both clusters can receive writes. Common mistake: forgetting that sequences don't replicate automatically through logical replication — you'll need to sync sequence values manually before cutover.

4. Let the sync run and watch replication lag

Give the initial data copy time to complete fully before checking lag — for a database in the tens of gigabytes, this can take anywhere from minutes to a few hours depending on I/O. Query pg_stat_subscription on the target and pg_stat_replication on the source to track catch-up progress, and monitor PostgreSQL WAL file retention so a stalled subscriber cannot exhaust source storage.

Don't rush this step. Common mistake: cutting over while replication lag is still measured in minutes — if the crank on your lag graph never settles under a few seconds, stop and investigate before you flip traffic.

5. Run validation queries on the target cluster

Compare row counts, checksums on key tables, and sample query results between source and target. Run your application's read-only reporting queries against the target cluster to confirm query plans and index usage behave as expected on the new version — the planner changes between major versions and can flip a fast index scan into a slow sequential scan.

Common mistake: validating schema and row counts but skipping query plan checks, then discovering a critical dashboard query got 10x slower right after cutover.

6. Rehearse the cutover on staging

Run the full pgBouncer PAUSE, verify-lag-zero, repoint-connection-string, RESUME sequence on staging at least once, timing every step. If you already have a current streaming replica, converting it to logical replication with pg_createsubscriber can reduce initial synchronization time. This rehearsal is where teams find out their application holds long-lived connections that don't respect pgBouncer's pause cleanly, or that a background worker bypasses the pooler entirely and connects straight to the database.

Common mistake: skipping the rehearsal because staging traffic feels irrelevant — the rehearsal isn't about data, it's about timing and finding connection paths you forgot about.

7. Execute the production cutover

With replication lag confirmed at zero, issue PAUSE on pgBouncer, wait for in-flight transactions to drain (usually a few seconds), update the pooler's backend target to point at the new cluster, and issue RESUME. Applications experience this as a brief connection stall, typically under 30 seconds, not a hard error.

Monitor error rates and query latency closely for the first 15-20 minutes post-cutover. Common mistake: disconnecting from the old cluster immediately — keep it running and in sync-reversed mode for at least 24-48 hours as a rollback path.

“If replication lag hasn't settled under a few seconds before cutover, stop and investigate — the pause is not the time to find out something's wrong.”

Troubleshooting

Replication lag won't drop below several minutes. Check for long-running transactions on the source holding back WAL cleanup, and confirm the target cluster's disk I/O isn't saturated by concurrent index builds or vacuum jobs.

Sequences are out of sync after cutover. Logical replication doesn't carry sequence state — run a manual setval() pass against every sequence immediately before cutover, comparing current values against the source.

Application throws "too many connections" right after cutover. This usually means a service is bypassing pgBouncer and connecting directly to the old cluster's IP — audit connection strings across every service before the cutover window, not during it.

Query plans regress on the new version. Run ANALYZE on all tables immediately after the target cluster goes live — stale statistics on a freshly synced cluster are the most common cause of sudden slow queries.

Extension incompatibility surfaces mid-sync. Roll back to the source cluster using your pre-written rollback script rather than trying to patch the extension live — debug the extension issue on staging separately.

Vacuum falls behind on the new cluster under initial load. Bump autovacuum_vacuum_cost_limit temporarily during the first 48 hours post-cutover while table bloat catches up from the sync process.

Tools and resources

• pg_upgrade for same-server major version upgrades where a short maintenance window is acceptable
• Logical replication (built into PostgreSQL 10+) for the zero downtime approach described above
• pgBouncer or PgCat for connection pooling and the pause/resume cutover mechanic
• pg_stat_subscription and pg_stat_replication for lag monitoring during sync
• A tested rollback backup — PostgreSQL 17 incremental backup with pg_basebackup and pg_combinebackup covers the backup chain and restore sequence
• 24/7 monitoring during the sync and cutover window, ideally from a team that has run this exact pattern before, not just on the day of the upgrade

What to do next

Once the new cluster is stable, the next task is tuning it for the workload it's actually running — default shared_buffers and work_mem values rarely fit a production system, and the version bump is a natural point to revisit indexing strategy too. Re-baseline query performance within the first week post-upgrade, since planner behavior differences compound under real load in ways staging rarely reproduces.

FAQ

What is a zero downtime PostgreSQL upgrade?

A zero downtime PostgreSQL upgrade uses logical replication to sync an old and new cluster before switching application traffic over, so the cutover takes seconds instead of the hours a traditional pg_upgrade maintenance window requires.

Can pg_upgrade alone achieve zero downtime?

No, pg_upgrade requires the database to be offline during the conversion, even with the faster --link mode. Zero downtime requires logical replication or a tool built on top of it, with a connection pooler managing the cutover.

How long does the actual cutover take?

A well-rehearsed cutover using pgBouncer PAUSE and RESUME typically completes in under 30 seconds, appearing to applications as a brief connection stall rather than an outage.

Do sequences replicate automatically with logical replication?

No, PostgreSQL logical replication does not sync sequence values automatically. You need to run setval() against every sequence on the target cluster immediately before cutover.

Is logical replication safe for large databases?

Yes, logical replication works at any scale, though initial sync time for the first full copy scales with data size and can take hours for databases in the hundreds of gigabytes.

What happens if something breaks right after cutover?

Keep the old cluster running and reachable for 24-48 hours after cutover so you can roll back by repointing pgBouncer again if error rates spike or queries regress.

Which PostgreSQL versions support logical replication?

PostgreSQL 10 and later support logical replication as a built-in feature, which covers essentially every version still in support as of 2026.

When should a business stop delaying a major version upgrade?

Immediately after the running version reaches end of life — PostgreSQL 12 went end of life in November 2024, and every month past that window increases security exposure with no offsetting benefit.

One last thing

The step teams skip most often isn't technical — it's the sequence sync in step 3, because it's easy to forget sequences don't move through logical replication at all. A cutover that looks flawless on every dashboard can still hand out duplicate primary keys within minutes if that one line gets missed, and by the time it surfaces it looks like an application bug, not an upgrade bug.

Get a second set of eyes on your upgrade plan

Mydbops runs zero downtime PostgreSQL upgrades for production workloads.