إنتقل إلى المحتوى الرئيسي

Postgres minor-version upgrade

Scope​

Minor-version-only — moving from supabase/postgres:15.1.x.x to a newer 15.x.y.y. Not for major-version upgrades (15 → 16); that needs a pg_upgrade plan, not this runbook.

Why we do it: minor releases close CVEs and bugs. The 15.1 image we shipped on first deploy is now ~15 patch releases behind upstream; on the 2026-05-06 inspection it had several HIGH-severity CVEs flagged by trivy.

Pre-flight (do this any time, no downtime)​

  1. Pick the target image tag.

    docker pull supabase/postgres:15.10.1.0  # or whichever stable tag
    docker image inspect supabase/postgres:15.10.1.0 \
      --format '{{.Config.Env}}' | tr ' ' '\n' | grep PG_VERSION

    Confirm the major version is still 15.

  2. Read the upstream changelog between current and target: https://github.com/supabase/postgres/releases. Look for any breaking change marker (very rare on minors).

  3. Check the running version:

    docker exec -it shambus-postgres psql -U postgres -c 'SHOW server_version;'

  4. Verify backups are fresh.

    docker exec shambus-backup ls -la /backups | tail -5

    Must show a dump from the last 24h. If not, run a manual one before continuing — see the database restore procedure.

Maintenance window​

Plan 20–30 min of read-only behavior on customer-facing apps. The actual postgres restart is ~30s on a small DB; the surrounding work (stop dependent services cleanly, restart in dependency order) takes the rest.

Pick a window when:

  • No batch jobs are running (nightly-cleanup, monthly-report-export).
  • Customer traffic is at its daily low (typically 02:00–04:00 Damascus).
  • An operator is awake and reachable for 1h after.

Announce the window in #shambus-ops ≥ 24h ahead.

Procedure​

All commands run from /opt/shambus on the production host as the deploy user.

1. Snapshot the data volume (defense in depth)​

# Take a logical dump first.
docker exec shambus-postgres pg_dumpall -U postgres \
  | gzip > /opt/shambus/backups/pre-upgrade-$(date +%F).sql.gz

# Then a filesystem snapshot of the named volume.
docker run --rm \
  -v shambus_postgres-data:/data \
  -v /opt/shambus/backups:/backup \
  alpine tar czf /backup/pg-volume-$(date +%F).tgz -C /data .

Both files must be > 0 bytes and the dump must be parseable:

gunzip -t /opt/shambus/backups/pre-upgrade-*.sql.gz && echo OK

2. Quiesce the writers​

Stop the apps that write to Postgres, but leave Postgres up. This lets in-flight transactions finish before we touch the DB.

docker compose --env-file .env.prod -f docker-compose.yml -f docker-compose.prod.yml \
  --profile prod stop \
    customer dashboard admin docs telegram-bot \
    mobile-customer mobile-driver \
    realtime auth rest storage

Wait 30s for any open transaction to wrap up.

3. Pin the new image and recreate​

Edit .env.prod:

sed -i.bak "s|^POSTGRES_IMAGE=.*|POSTGRES_IMAGE=supabase/postgres:15.10.1.0|" .env.prod

(If .env.prod does not have POSTGRES_IMAGE, set it in docker-compose.yml directly and git commit the change before deploy.)

docker compose --env-file .env.prod -f docker-compose.yml -f docker-compose.prod.yml \
  --profile prod up -d --no-deps postgres

# Watch it come up. Look for "database system is ready to accept connections".
docker logs -f shambus-postgres

4. Verify the version + integrity​

docker exec shambus-postgres psql -U postgres -c 'SHOW server_version;'
docker exec shambus-postgres psql -U postgres -c 'SELECT count(*) FROM pg_stat_activity;'
docker exec shambus-postgres psql -U postgres -d shambus -c \
  "SELECT count(*) FROM passengers; SELECT count(*) FROM bookings;"

The row counts must match what you recorded in step 1's dump (zcat … | grep -c 'INSERT INTO' or query before step 2).

5. Bring the rest back up​

docker compose --env-file .env.prod -f docker-compose.yml -f docker-compose.prod.yml \
  --profile prod up -d

Watch for any service stuck restarting:

docker compose --env-file .env.prod -f docker-compose.yml -f docker-compose.prod.yml \
  --profile prod ps

6. Post-upgrade smoke​

Run the existing smoke script:

bash infrastructure/scripts/smoke-prod.sh   # if present; else hit each app's /api/health

Manual checks:

  • Customer site: anonymous trip search returns results.
  • Admin login + 2FA succeeds; audit-log entry is written.
  • Mobile customer (web) :8180: OTP send + verify still issues a session.
  • Grafana dashboard "Postgres" panel: connections climbing back to baseline.

7. Re-run pending migrations​

pg_dumpall does not preserve roles cleanly across some image transitions. Apply migrations idempotently:

bash infrastructure/scripts/run-migrations.sh

Watch for duplicate-key errors — migrations are idempotent, so they should all be relation already exists, skipping.

Rollback​

If anything fails between step 3 and step 5:

sed -i "s|^POSTGRES_IMAGE=.*|POSTGRES_IMAGE=supabase/postgres:15.1.0.147|" .env.prod  # original tag
docker compose --env-file .env.prod -f docker-compose.yml -f docker-compose.prod.yml \
  --profile prod up -d --no-deps postgres

If the data volume itself is corrupt (very unlikely on a minor upgrade but the dump is your safety net):

docker compose --env-file .env.prod -f docker-compose.yml -f docker-compose.prod.yml \
  --profile prod stop postgres
docker volume rm shambus_postgres-data
docker volume create shambus_postgres-data
docker run --rm \
  -v shambus_postgres-data:/data \
  -v /opt/shambus/backups:/backup \
  alpine tar xzf /backup/pg-volume-YYYY-MM-DD.tgz -C /data
docker compose ... up -d postgres

After the upgrade​

  • Update infrastructure/docker/postgres-version.txt (or wherever the pinned tag lives) and commit.
  • Note the upgrade in the changelog under apps/docs/docs/roadmap/changelog/.
  • File a calendar reminder for the next minor upgrade (~quarterly).

See also​

  • Database outage runbook
  • Backup directory layout: /opt/shambus/backups/ on the prod host.
  • Trivy report tracking the original CVEs: reports/security/trivy-image-supabase_postgres_15.1.0.147.txt.