postgres-backup-restore

PostgreSQL Backup & Restore

Target version: postgres:16-alpine — this repository's gc-platform-postgres container (db gcplatform, owner role gcplatform, data on named volume postgres-data).

Contract: auditing is read-only (SHOW, pg_stat_archiver, docker volume inspect). Every backup/restore/ config command below is emitted ONLY as a ⚠️ HUMAN CONFIRMATION REQUIRED block — the postgres-dba agent never executes backups, restores, or server-config changes. Restores are ALWAYS human-executed: a restore overwrites data and is irreversible in the wrong hands.

Connection:

docker exec gc-platform-postgres psql -U gcplatform -d gcplatform -c "<query>"

---

1. Backup strategy map (pick per need)

Method	Captures	PITR?	Notes for this repo
pg_dump -Fc	One database, logical (SQL-level)	NO	Restores into any PG version ≥ source; slow on huge DBs; does NOT capture roles/tablespaces
pg_dumpall --globals-only	Cluster globals: roles, tablespaces	NO	Needed separately — gc_kourou_app_login, gc_kourou_identity_app, gc_kourou_identity_migrator are global objects (though this repo re-provisions them via migrations on boot)
pg_basebackup	Physical whole-cluster copy	Base for PITR	Must stop/quiet writes for a consistent snapshot unless using WAL archiving; docker volume makes this awkward
WAL archiving + base backup	Continuous, point-in-time	YES	Requires archive_mode=on + working archive_command (see §3)
PG16 native incremental backup	—	—	NOT AVAILABLE: pg_basebackup --incremental is PostgreSQL 17+ only. On PG16, "incremental" = WAL archiving layered on periodic base backups.

pg_dump can never do PITR — it is a logical snapshot at dump time.

---

2. Logical backup recipes (docker instance)

⚠️ HUMAN CONFIRMATION REQUIRED

# Database dump, custom format (compressed, parallel-restoreable, selective):
docker exec gc-platform-postgres pg_dump -U gcplatform -d gcplatform -Fc -f /tmp/gcplatform_$(date +%F).dump
docker cp gc-platform-postgres:/tmp/gcplatform_$(date +%F).dump ./backups/

# Plain-SQL alternative (readable in git/diff, no parallel restore):
docker exec gc-platform-postgres pg_dump -U gcplatform -d gcplatform -f - > ./backups/gcplatform_$(date +%F).sql

# Cluster globals (roles!) — not included in pg_dump:
docker exec gc-platform-postgres pg_dumpall -U gcplatform --globals-only -f - > ./backups/globals_$(date +%F).sql