Blue/Green and Logical Replication Upgrades

The one thing to understand first

PostgreSQL's on-disk format can change between major versions, so you cannot simply start a new binary on the old data directory. The traditional options each have downsides: pg_dump/restore is simple but slow and needs a long outage; pg_upgrade with hard links is fast but still requires stopping the database and is hard to roll back. Logical replication enables a blue/green upgrade with downtime measured in seconds.

Logical replication is version-agnostic — it ships row changes, not WAL bytes — so it lets a new-version "green" instance track an old-version "blue" until you flip traffic in seconds, with blue still intact as a fallback. That cross-version property is the entire reason near-zero-downtime major upgrades are possible.

The blue/green idea

Blue is your current production database. Green is a freshly built instance on the new major version. You replicate all changes from blue to green logically, let green catch up, validate it, then flip application traffic to green in one quick cutover. If anything is wrong, you flip back — blue was never touched destructively.

Why logical (not physical) replication

Physical (streaming) replication ships byte-level WAL and requires identical major versions — useless for an upgrade. Logical replication decodes WAL into row-level changes (INSERT/UPDATE/DELETE) and replays them via SQL, so the publisher and subscriber can run different major versions. That cross-version capability is what makes near-zero-downtime upgrades possible.

The procedure

-- On BLUE (old version): publish the tables
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- Build GREEN on the new major version, restore the SCHEMA only
-- (pg_dump --schema-only from blue, restored into green).

-- On GREEN (new version): subscribe and copy data + stream changes
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=blue dbname=app'
  PUBLICATION upgrade_pub;
-- Initial table sync copies existing data, then streams ongoing changes.

Layer 3 — Watch it happen on your own database

Monitor replication progress until green is continuously caught up:

-- On BLUE: how far behind is the subscriber?
SELECT slot_name, pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM   pg_replication_slots;

-- On GREEN: subscription health
SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription;

-- Validate green before cutover: per-table row counts must match blue
SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY relname;

The lag on blue must shrink to near-zero and stay there — that is your signal green is keeping up in real time. Before flipping traffic, validate green thoroughly: per-table row counts and checksums against blue, application smoke tests pointed at green, and — critically — advance sequences, because logical replication does not copy sequence values and a non-advanced sequence will collide on the first insert after cutover.

Layer 4 — The levers this hands you

The cutover is the only true downtime — typically seconds:

• Stop writes to blue (set the app read-only or pause it).
• Let green apply the final changes (lag → 0).
• Advance sequences on green to match blue.
• Repoint the application (connection string / DNS / PgBouncer) at green.
• Resume writes — green is now production.

• Keep blue running untouched after cutover as an instant fallback; optionally set up reverse green→blue logical replication to fail back without data loss.
• Freeze schema changes during the window — logical replication does not replicate DDL, large objects, or (on older versions) TRUNCATE.
• Ensure every table has a replica identity (a primary key, ideally) so UPDATE/DELETE replicate.
• Pick the method to fit the outage budget: logical/blue-green for minimal downtime + easy rollback + cross-version; pg_upgrade --link for the fastest in-place upgrade when a brief window is acceptable; dump/restore for small databases.

Layer 5 — What an Oracle DBA should expect vs what they get

This maps cleanly onto upgrade strategies Oracle DBAs already know:

• Blue/green logical ≈ GoldenGate / transient logical standby. Replicating into a new-version instance and cutting over is conceptually Oracle's GoldenGate-based or rolling-upgrade approach — but built from in-core logical replication, no extra-cost option.
• **pg_upgrade --link ≈ in-place catalog upgrade,** closer to Oracle's DBUA/autoupgrade: fast, in-place, but requires downtime and is awkward to roll back.
• Sequences don't replicate. A sharp gotcha with no direct Oracle analogue — you must manually advance sequences at cutover or face duplicate-key errors.
• No DDL replication. Like basic GoldenGate setups, logical replication ignores DDL — freeze schema during the migration, unlike a physical standby that simply replays everything.

Key takeaway

A blue/green upgrade uses logical replication (which decodes WAL into version-agnostic row changes) to stream a live old-version "blue" into a new-version "green", letting you cut over in seconds with blue intact as a fallback. Drive green's lag to zero, validate row counts and advance sequences, then repoint the app. Reserve pg_upgrade --link for fast in-place upgrades with a short window and dump/restore for small databases.