PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it…

These steps will assume we have our old primary (pg-old), a new primary (pg-new), and a proxy server running PgBouncer. Follow these instructions to complete the upgrade process:

1. Ensure there are no long-running transactions with this query on pg-old:

SELECT pid, state, client_addr, username,        now() - xact_start AS duration, query  FROM pg_stat_activity WHERE state != 'idle'   AND now() - xact_start > INTERVAL '60s' ORDER BY duration DESC;

2. Terminate or resolve any transactions identified in the preceding execution.
3. Check the replication lag on pg-old with this query:

SELECT slot_name, database, active,       pg_wal_lsn_diff(         pg_current_wal_lsn(),         confirmed_flush_lsn       ) AS write_lag  FROM pg_replication_slots;

4. If lag is relatively ...