A maintenance window that turns into a midnight firefight is a sign the replication strategy needs work. Safer releases, faster failovers, and near-painless upgrades are achievable with the right PostgreSQL setup. This guide gives exact runbooks for blue/green, major-version upgrades with near-zero downtime, and read scaling - plus when to pick physical vs logical replication.

TL;DR

• Physical replication (WAL shipping) is the default for HA and read replicas: fast, byte-for-byte, minimal overhead.
• Logical replication is for cross-version upgrades, selective table sync, multi-region fan-out, and online migrations.
• Blue/green cutovers are safer with logical (controlled, table-level); physical is simpler for pure switchover/failover.
• Copy/paste-ready runbooks below.

1) Why this matters

• SLAs don't care that VACUUM ran long.
• Major upgrades (e.g., 13 → 16) shouldn't require hours of downtime.
• Read scaling avoids overprovisioning the primary.
• Blue/green gives you reversible releases.

2) Replication models in plain English

3) Physical replication: when and how

Use it for: HA + read replicas in the same major version, low-latency read scaling, and simple blue/green within the same PG major.

Core setup (primary):

# postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
wal_keep_size = '2GB'      # tune for network hiccups

# pg_hba.conf
host  replication  repl_user  10.0.0.0/16  md5

Create replica (standby):

# On the standby (empty data dir)
pg_basebackup -h PRIMARY_HOST -U repl_user -D /pgdata -X stream -P
# Start the standby; recovery.conf is now in postgresql.auto.conf via standby.signal

Monitor:

-- On primary
select pid,state,client_addr, sent_lsn, write_lsn, flush_lsn, replay_lsn
from pg_stat_replication;

-- On standby
select now() - pg_last_xact_replay_timestamp() as replay_delay;

4) Logical replication: when and how

Use it for: major version upgrades (e.g., 12 → 16), migrating a subset of tables, and multi-region selective fan-out.

Requirements & gotchas:

• Every replicated table must have a PRIMARY KEY or a REPLICA IDENTITY FULL.
• DDL is not auto-replicated - run schema changes on both sides.
• Sequences and large objects need explicit handling.

Enable and create a publication (source):

# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

-- On source DB
create role sub_user with login replication password '***';
grant select on all tables in schema public to sub_user;

-- Publication (all tables or selective)
create publication pub_all for all tables;
-- or: create publication pub_sel for table public.orders, public.customers;

Create subscription (target):

-- Target DB (possibly higher major version)
create subscription sub_migrate
  connection 'host=SRC user=sub_user password=*** dbname=appdb'
  publication pub_all
  with (copy_data = true, create_slot = true, enabled = true);

Lag + health:

select * from pg_stat_subscription;

5) Blue/green deployments

• Blue = current production (source of truth).
• Green = new stack (new PG version or new hardware).
• Use logical to stream data from Blue → Green while you validate.
• Switch traffic via connection string flip or load balancer.
• Keep Blue read-only for a short soak; if there are issues, reverse (Green → Blue) if you planned for it.

6) Zero-downtime major upgrades

Two main paths:

1. pg_upgrade - fast, requires downtime; can be minutes with link mode, but still some downtime.
2. Logical replication - near-zero downtime; more moving parts.

The runbooks below focus on logical replication for the near-zero-downtime case.

7) Read scaling patterns

• Primary + hot standbys (physical) behind a read router (PgBouncer/HAProxy).
• Pin read-heavy queries to replicas; avoid long transactions that block VACUUM on standbys.
• Use default_transaction_read_only=on on read pools.
• Beware replica lag for read-your-writes scenarios; route those to the primary.

8) Real cutover runbooks

A) Physical switchover (same version, blue/green infra)

Goal: move writes from Primary (Blue) to Standby (Green) with seconds of downtime.

Pre-checks: pg_stat_replication shows 0 - X seconds lag; synchronous_commit = on with a sync replica (optional for strict RPO=0); client retry logic tested.

Steps

1. Freeze writes briefly - put the app in read-only/maintenance for ~30-60s (or block at the DB): alter system set default_transaction_read_only = on; select pg_reload_conf();
2. Ensure replica caught up - on standby: select pg_last_wal_replay_lsn();; on primary: ensure sent_lsn == replay_lsn on the standby.
3. Promote standby (Green): pg_ctl promote -D /pgdata
4. Repoint clients to Green.
5. Demote Blue to standby (optional): stop Blue, pg_rewind Blue from Green, start as a follower.
6. Unfreeze writes: alter system reset default_transaction_read_only; select pg_reload_conf();

Rollback: if promote fails, keep Blue as primary and reattempt after fixing replication.

B) Logical blue/green cutover (cross-version or selective tables)

Goal: keep Blue (old version) live, stream to Green (new version), cut over with <30s write pause.

Prep: on Green, create the schema manually (DDL not replicated); ensure primary keys exist for all tables; migrate sequences (pre-seed on Green with high ranges or move to generated always as identity in v10+; capture last values before cut).

Steps

1. Start replication. On Blue: create publication pub_all for all tables;. On Green: create subscription sub_all connection 'host=BLUE user=sub_user password=*** dbname=appdb' publication pub_all with (copy_data = true, create_slot = true);
2. Wait to catch up: select * from pg_stat_subscription; (should show minimal latency).
3. Quiesce writes (short freeze) - put the app in maintenance or block writes by revoking permissions temporarily: revoke insert, update, delete on all tables in schema public from app_role;
4. Apply final DDL & sequence sync - recheck schema drift. For sequences: -- On Blue select 'select setval('''||relname||''','||last_value||');' from pg_sequences where schemaname='public'; -- Run results on Green
5. Stop subscription on Green: alter subscription sub_all disable;
6. Flip traffic to Green - update service DNS/connection strings.
7. Re-enable writes on Green: grant insert, update, delete on all tables in schema public to app_role;
8. Keep Blue read-only for soak (optional): alter system set default_transaction_read_only = on; select pg_reload_conf();

Rollback: if errors appear on Green, point traffic back to Blue, re-enable the subscription (or recreate it), and investigate.

C) Read scaling with physical replicas

Goal: add read replicas safely and route analytics there.

1. Create the standby via pg_basebackup.
2. Tag the replica pool in your router (PgBouncer: separate database section).
3. Ensure long-running queries don't block WAL replay: set hot_standby_feedback=on (with caution - can bloat the primary) or cap query timeouts on replicas.
4. Add monitoring on replica lag and conflicts:

select * from pg_stat_database_conflicts;

9) Best practices & common pitfalls

Physical

• Keep wal_keep_size big enough for network stalls; otherwise replicas fall off the cliff.
• Use replication slots to prevent WAL removal - but monitor disk.
• Test pg_rewind regularly.

Logical

• DDL drift kills cutovers. Use a migrations gate to apply schema to both sides.
• Sequences are not replicated. Plan ranges or a final sync step.
• Replica identity: ensure a PK or REPLICA IDENTITY FULL before enabling.
• Large initial copies can throttle the primary - schedule outside peak hours and consider max_sync_workers_per_subscription.

Blue/green

• Treat connection cutover as its own change with rehearsals.
• Keep a reversible path for at least one day.

Observability

• Dashboards for pg_stat_replication, pg_stat_subscription, WAL usage, replication slot lag, conflicts, and disk.
• Alert on apply lag and WAL retention headroom.

Security

• Separate least-privilege roles for replication vs application.
• Restrict pg_hba.conf to exact subnets/hosts. TLS if crossing networks.

10) Conclusion & takeaways

• Default to physical replication for HA and read replicas.
• Use logical replication for cross-version upgrades and precise blue/green migrations.
• Practice the cutover - the first time should not be in production.

A practical starting point: stand up a test cluster and rehearse both runbooks, automate checks for DDL drift and sequence sync, and script a one-click cutover that toggles app permissions and connection strings.

Appendix: handy commands & queries

# Create replication role
createuser --replication repl_user
psql -c "alter role repl_user with replication login password '***';"

# WAL size insight
psql -c "select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0'));"

-- Who's replicating from me?
select application_name, client_addr, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn
from pg_stat_replication;

-- Subscription lag
select subname, status, bytes_lag, lags.apply_lag
from pg_stat_subscription lags;

-- Conflicts on replicas
select datname, confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin
from pg_stat_database_conflicts;