Database Backup and Restore

Backups run on prod only (PG_BACKUPS=true in k8s/env/prod.env). Test data is treated as disposable.

Backup	Schedule	Kept	Where
Logical pg_dump of galaxy and rucio (CronJob pg-logical-backup)	03:00 daily	14 days	RWX PVC pg-logical-backups (50Gi Longhorn), /backups/nightly/<db>/<db>-<timestamp>.dump
CNPG volume snapshot backups (ScheduledBackups galaxy-pg-nightly, rucio-pg-nightly)	02:15 / 02:45 daily	7 per cluster (CronJob cnpg-backup-pruner)	Longhorn in-cluster snapshots (longhorn-snapshot class)

Each dump is verified with pg_restore --list, and the job fails if a dump is unreadable. The logical dump protects against things a snapshot preserves faithfully, such as a bad migration or a dropped table.

danger

Both backups live inside the speedy cluster (Longhorn). They are not disaster recovery: losing the cluster or Longhorn loses them too. Off-cluster backups (Longhorn backup target or a CNPG object-store plugin) are an open item.

Checking the backups

kubectl -n ndip get cronjob pg-logical-backup cnpg-backup-pruner
kubectl -n ndip get jobs | grep pg-logical-backup          # last run
kubectl -n ndip logs job/<pg-logical-backup-job>           # sizes and file names of the dumps
kubectl -n ndip get backup                                 # CNPG snapshot backups, phase "completed"
kubectl -n ndip get scheduledbackup

Taking an ad-hoc dump

Before a risky change (Galaxy upgrade, schema migration), trigger the CronJob by hand:

kubectl -n ndip create job --from=cronjob/pg-logical-backup pg-dump-manual-$(date +%Y%m%d)
kubectl -n ndip logs -f job/pg-dump-manual-$(date +%Y%m%d)

For an on-demand snapshot, create a CNPG Backup. The pruner keeps only the newest 7 per cluster, so a manual snapshot ages out like the nightly ones.

apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
  name: galaxy-pg-manual-20261009
  namespace: ndip
spec:
  cluster:
    name: galaxy-pg
  method: volumeSnapshot

Restoring from a pg_dump

1. Quiesce the application: scale Galaxy (or Rucio) to 0, for example kubectl -n ndip scale deploy/galaxy --replicas=0.

2. Recreate the database empty: delete the CNPG cluster (kubectl -n ndip delete clusters.postgresql.cnpg.io galaxy-pg) and rerun postgres-k8s-prod.

3. Restore from a pod that has the Postgres client and the pg-logical-backups volume mounted (for example k8s/postgres/cutover/pgtool-prod.yaml without the ssh volume):

   pg_restore -h galaxy-pg-rw -U galaxy -d galaxy --no-owner --no-acl --exit-on-error -j 4 /backups/nightly/galaxy/<file>.dump
   vacuumdb -h galaxy-pg-rw -U galaxy -d galaxy --analyze-only

4. Scale the application back up and check it.

Restoring from a volume snapshot

1. Find a completed backup and its snapshots: kubectl -n ndip get backup, then kubectl -n ndip get backup <name> -o jsonpath='{.status.backupSnapshotStatus}'.
2. Create a new CNPG cluster (for example galaxy-pg-restore) with bootstrap.recovery.volumeSnapshots.storage (plus walStorage if the source cluster has a separate WAL volume) naming those VolumeSnapshots. Start from a copy of k8s/postgres/cluster-<db>-prod.yaml.
3. Check the data in the new cluster.
4. Repoint the application's connection string at the new cluster's -rw Service (galaxy.secrets.databaseUrl or Rucio's config.database.default in the vault secrets) and redeploy it.

Legacy: VM databases

On the old VMs, backups were made by the Ansible PostgreSQL role into /db_backups/ and restored with /var/lib/postgresql/backups/bin/restore_postgress.sh. To dump a VM database manually (still applies to galaxy.ornl.gov and to the retained prod VM database):

sudo -iu postgres
pg_dump -Fc galaxy > galaxy-$(date +%Y%m%d).dump