Postgres 13 to 16 upgrade migration plan

```meta
title: Postgres 13 to 16 upgrade
subtitle: How the main database moves three major versions with under a minute of write pause and a rollback at every phase.
tag: Plan · Q4
```

The main database runs Postgres 13 on RDS, holds 1.8 TB, and serves 4,200 queries a second at peak. Postgres 13 left community support in November 2025, and RDS extended support adds $3,100 a month from 1 December. An in-place `pg_upgrade` on this size took 41 minutes in the last rehearsal, which is 41 minutes of no writes. The plan uses logical replication into a new Postgres 16 instance, so the write pause is the cutover itself, under 60 seconds.

Assumptions in this plan: RDS Postgres with the `pglogical`-free native publication and subscription. All tables have primary keys except `audit_log`, which gets one in Phase 1. The application connects through PgBouncer, so the cutover is a PgBouncer config change, not an application deploy.

## Phases

```chevrons
id: pg-phases
current: 2
steps:
  - { label: Prepare, desc: "Add the audit_log key; fix 3 extensions" }
  - { label: Rehearse, desc: "Full run on a prod snapshot, twice" }
  - { label: Replicate, desc: "New PG16 instance follows prod" }
  - { label: Cut over, desc: "Pause writes, flip PgBouncer" }
  - { label: Verify, desc: "7 days of checks on PG16" }
  - { label: Retire, desc: "Snapshot and delete PG13" }
```

Rehearse is where the plan earns its confidence. The first rehearsal found that `pg_stat_statements` 1.8 has a column that 1.10 renamed, and two dashboards broke. The second rehearsal is the one that counts: it must finish with every check green before Replicate starts.

## Dates

```timeline
id: pg-dates
items:
  - "[done] 8 Sep · Prepare complete · audit_log has a primary key; postgis, pg_trgm, and pg_stat_statements pinned to PG16 versions"
  - "[current] 15–19 Sep · Second rehearsal · Snapshot to PG16 on a staging clone; run the full check suite and a 2× load test"
  - "[next] 22 Sep · Replicate · Create the PG16 instance from a snapshot; start the subscription; initial copy takes about 9 hours"
  - "[next] 23 Sep – 5 Oct · Catch-up and shadow reads · Lag under 5 s for 7 consecutive days; read replicas of PG16 serve 10% of report traffic"
  - "[next] 6 Oct 06:00 UTC · Cut over · Sunday low-traffic window; write pause target under 60 s"
  - "[next] 6–13 Oct · Verify · Reverse replication PG16 to PG13 keeps the rollback alive"
  - "[next] 20 Oct · Retire · Final PG13 snapshot kept 90 days; instance deleted"
  - "[next] 1 Dec · Extended support billing starts · The date the plan must beat"
```

The 6 October window leaves eight weeks of slack before extended support billing. A failed cutover on 6 October retries on 13 October, and a second failure still leaves time for a third attempt in November.

## Cutover and the way back

```flow
id: pg-cutover
dir: TB
nodes:
  - { id: start, col: 1, row: 1, kind: start, label: "06:00 · lag under 1 s" }
  - { id: pause, col: 1, row: 2, kind: process, label: "PgBouncer PAUSE; writes queue" }
  - { id: drain, col: 1, row: 3, kind: decision, label: "Lag at 0 within 30 s?" }
  - { id: seq, col: 1, row: 4, kind: process, label: "Copy sequence values to PG16" }
  - { id: flip, col: 1, row: 5, kind: process, label: "Point PgBouncer at PG16; RESUME" }
  - { id: smoke, col: 1, row: 6, kind: decision, label: "Smoke suite green in 5 min?" }
  - { id: reverse, col: 1, row: 7, kind: process, label: "Start reverse replication PG16 to PG13" }
  - { id: done, col: 1, row: 8, kind: end, label: "Verify phase begins" }
  - { id: abort1, col: 2, row: 3, kind: end, label: "RESUME on PG13; retry next Sunday" }
  - { id: back, col: 2, row: 6, kind: process, label: "PAUSE; point PgBouncer at PG13; RESUME" }
  - { id: abort2, col: 2, row: 7, kind: end, label: "Replay PG16 writes into PG13 from the WAL; retry next Sunday" }
edges:
  - start -> pause
  - pause -> drain
  - drain -> seq: "yes"
  - drain -x-> abort1: "no"
  - seq -> flip
  - flip -> smoke
  - smoke -> reverse: "yes"
  - smoke -x-> back: "no"
  - back -> abort2
  - reverse -> done
```

Before the flip, a rollback costs nothing: writes never left PG13. After the flip, the writes that landed on PG16 during the smoke minutes must reach PG13. The reverse subscription carries them once it starts. The point of no return is Retire. Until then PG13 follows PG16 and can take writes again within minutes.

## Risks

```risk
id: pg-risks
items:
  - { risk: "Logical replication cannot copy audit_log without a primary key", likelihood: low, impact: high, mitigation: "Key added in Prepare; migration verified on the first rehearsal.", owner: Data platform, status: closed }
  - { risk: "Initial copy of 1.8 TB competes with weekday peak load", likelihood: med, impact: med, mitigation: "Start the copy on Saturday 22:00; the copy rate is capped at 60 MB/s.", owner: Data platform, status: mitigating }
  - { risk: "A query plan changes under PG16 and a hot endpoint slows down", likelihood: med, impact: high, mitigation: "Shadow reads on 10% of report traffic for 7 days; p95 per endpoint compared daily.", owner: Backend, status: mitigating }
  - { risk: "Sequence values drift and PG16 issues a duplicate id after the flip", likelihood: low, impact: high, mitigation: "Sequences copied with a +1,000 offset during the write pause.", owner: Data platform, status: mitigating }
  - { risk: "Reverse replication breaks and the rollback is fiction", likelihood: low, impact: high, mitigation: "Reverse lag over 60 s pages at production severity for the whole Verify phase.", owner: SRE, status: open }
```

## Go / no-go for 6 October

```checklist
id: pg-gate
standard: Cutover gate v2
items:
  - "[pass] Second rehearsal green — check suite 212/212 on 19 Sep, load test at 2× peak"
  - "[pass] Extension versions match on PG16 — postgis 3.4, pg_trgm 1.6, pg_stat_statements 1.10"
  - "[pending] Replication lag under 5 s for 7 days — window starts when the initial copy completes"
  - "[pending] Shadow read p95 within 10% of PG13 per endpoint — daily report from 23 Sep"
  - "[pass] Runbook rehearsed by the two engineers on the cutover call — dry run on staging 18 Sep"
  - "[pending] Reverse replication tested on staging — scheduled 30 Sep"
```

Every item must be `pass` by 3 October. A single `pending` on that date moves the cutover to 13 October; the plan does not go with a partial gate.