Backup and Restore – PostgreSQL

Operational procedure for backing up and restoring the Amy-Term database.

Scope Backup strategy Backup Encryption Restore Validation Federation notes Operational notes

Operational objective: maintain recoverability of data integrity and service continuity. Define and monitor RPO/RTO in your environment.

1. Scope

This document covers backup and restore of the Amy-Term PostgreSQL database, including terminology artefacts, federation state, and audit metadata. File system artefacts (logs, uploads, exported bundles) are not included and must be handled separately.

Pre-conditions

Backup operator has access to the database host/container network.
Backup files are stored in a protected location with controlled access.
Credentials are stored in a secret store (not in shell history).
Restore tests are planned (recommended: quarterly).

2. Recommended backup strategy

Item	Recommendation	Notes
Frequency	Daily full logical backup	Before upgrades and before major federation releases.
Format	`pg_dump` custom format	Supports selective restore and better portability.
Retention	30–90 days (policy-driven)	Align with audit and retention requirements.
Protection	Encrypt at rest + restricted access	Use GPG/KMS; separate duties for key access.
Verification	Automated restore test (scheduled)	At minimum: restore to staging and run smoke checks.

3. Full database backup (logical)

Use a pg_dump custom-format backup to support portability and selective restore. In containerised deployments, run the command from a toolbox container or the Postgres container and write the output to a mounted volume.

pg_dump (custom format)

pg_dump \
  --format=custom \
  --no-owner \
  --no-privileges \
  --file=amterm_backup_YYYYMMDD.dump \
  amterm_db

4. Encrypted backup (recommended for regulated environments)

Key management: Store private keys separately from backups. Use role separation and audit access to encryption keys.

pg_dump piped to GPG

pg_dump amterm_db | gpg --encrypt --recipient ops@example.org > amterm_backup_YYYYMMDD.sql.gpg

5. Restore procedure

Restore should be performed into a clean database to avoid conflicting state. Stop application services before restore.

createdb + pg_restore

createdb amterm_db

pg_restore \
  --no-owner \
  --role=amterm_user \
  --dbname=amterm_db \
  amterm_backup_YYYYMMDD.dump

Permissions: Ensure the target database role exists and has required privileges before restore. If you use migrations, confirm the schema version matches the application release.

6. Post-restore validation

Application starts without schema errors.
/codesystems loads successfully.
/valuesets loads successfully.
/fhir/metadata responds with expected capability statement.
/atc/browser search works as expected.

7. Federation and restore (important)

After restore, confirm whether the environment is intended to resume as a central node or a regional node.
Do not re-apply federation bundles automatically on top of restored state unless explicitly required.
If signing keys are rotated, ensure restored nodes use the current trust configuration before federation operations.

8. Operational notes

Record backup job outputs (success/failure) and alert on failures.
Store backups off-host where feasible (separate failure domain).
Document an incident runbook: “restore to last known good backup” with clear decision points.
Schedule restore tests and record evidence (for audit/assurance).

Document: backup_restore.html