Replication, Failover, Upgrades, and Zero-Downtime Migrations

1. 1

   Start a primary and a streaming replica

   Using Docker Compose, the replica bootstraps itself from the primary with pg_basebackup -R, which writes the standby configuration.

   services:
     primary:
       image: postgres:17
       environment: { POSTGRES_PASSWORD: pw }
       command: ["postgres", "-c", "wal_level=replica", "-c", "max_wal_senders=5"]
       ports: ["5432:5432"]
     replica:
       image: postgres:17
       environment: { PGPASSWORD: pw }
       depends_on: [primary]
       user: postgres
       entrypoint: ["bash", "-c"]
       command:
         - |
           until pg_isready -h primary; do sleep 1; done
           rm -rf /var/lib/postgresql/data/*
           pg_basebackup -h primary -U postgres -D /var/lib/postgresql/data -R -X stream
           chmod 700 /var/lib/postgresql/data
           exec postgres
       ports: ["5433:5432"]

   terminalcopy cmd

   $ docker compose up -d && sleep 10

   psql postgresql://postgres:pw@localhost:5432/postgres -c 'select client_addr, state, replay_lag from pg_stat_replication'

   ── expected output ──

   client_addr | state | replay_lag

   -------------+-----------+------------

   172.20.0.3 | streaming | 00:00:00.000912

2. 2

   Prove the replica is read-only and follows the primary

   terminalcopy cmd

   $ psql postgresql://postgres:pw@localhost:5432/postgres -c 'create table r(x int); insert into r values (1)'

   psql postgresql://postgres:pw@localhost:5433/postgres -c 'select * from r' -c 'insert into r values (2)'

   ── expected output ──

   x

   ---

   1

   ERROR: cannot execute INSERT in a read-only transaction

3. 3

   Fail over manually

   Stop the primary and promote the replica. In production, Patroni or RDS do this and also redirect clients. Here you'd point the app at port 5433.

   terminalcopy cmd

   $ docker compose stop primary

   psql postgresql://postgres:pw@localhost:5433/postgres -c 'select pg_promote()' -c 'select pg_is_in_recovery()' -c 'insert into r values (2)'

   ── expected output ──

   pg_promote

   ------------

   t

   pg_is_in_recovery

   -------------------

   f

   INSERT 0 1

4. 4

   A safe NOT NULL, the expand/contract way

   Adding NOT NULL directly scans the whole table under an exclusive lock. Instead: add a CHECK constraint as NOT VALID (instant), validate it (scans without blocking writes), then set NOT NULL, which Postgres 12+ can prove from the valid constraint without another scan.

   SET lock_timeout = '3s';
   ALTER TABLE orders ADD COLUMN currency text;                 -- expand (metadata only)
   -- app release N writes currency on every insert; backfill old rows in batches:
   -- UPDATE orders SET currency = 'INR' WHERE id BETWEEN $1 AND $2 AND currency IS NULL;
   ALTER TABLE orders ADD CONSTRAINT orders_currency_nn CHECK (currency IS NOT NULL) NOT VALID;
   ALTER TABLE orders VALIDATE CONSTRAINT orders_currency_nn;   -- no write lock
   ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;        -- uses the valid check
   ALTER TABLE orders DROP CONSTRAINT orders_currency_nn;

Each drill is a real failure mode. Read the scenario and the output, decide what's wrong, then reveal the diagnosis.