Skip to main content

Database Recovery

Galaxy and Rucio use the CloudNativePG clusters galaxy-pg and rucio-pg in namespace ndip (see PostgreSQL). Start with:

kubectl -n ndip get clusters.postgresql.cnpg.io                    # not "cluster": that is Rancher's resource
kubectl -n ndip describe clusters.postgresql.cnpg.io galaxy-pg     # Status, Conditions, current primary
kubectl -n ndip get pods -l 'cnpg.io/cluster in (galaxy-pg,rucio-pg)' -o wide
kubectl -n ndip logs <instance-pod>

A node died (prod, 2 instances)​

The operator promotes the standby and moves galaxy-pg-rw / rucio-pg-rw to it. Galaxy and Rucio reconnect on their own. The instance on the dead node stays wedged: its pod sits in Terminating, and its Longhorn volume stays attached to the dead node. The database still works on one instance, and the alert postgres cluster degraded fires.

  • Node comes back: the instance rejoins as a standby. Nothing to do.

  • Node is gone for good: delete the instance's PVC and pod, and CNPG re-clones it from the primary:

    kubectl -n ndip delete pvc <instance-pod> --wait=false
    kubectl -n ndip delete pod <instance-pod> --force --grace-period=0

    (also <instance-pod>-wal if the cluster has a separate WAL volume).

Replication is asynchronous, so a failover can lose the last few committed transactions.

The only instance is down (test, 1 instance)​

On mickey each cluster has a single instance with a single Longhorn replica (galaxy-pg is pinned to mickey-wn01), so its node or volume failing takes the database down.

  1. Check the node (kubectl get node mickey-wn01) and Longhorn (kubectl -n longhorn-system get volumes.longhorn.io).
  2. The usual cause is disk pressure on the node: Longhorn marks the replica failed and won't salvage it while the disk is below its 25% minimum-free floor. Free space on the node (see DiskPressure), then let Longhorn salvage the replica.
  3. Test data is disposable. If the volume is lost, delete the cluster and rerun postgres-test. Galaxy then starts from an empty database (and Rucio needs rucio-test to rebuild its RSEs).

Restoring data​

For a bad migration, deleted data, or a lost cluster, restore from a backup (prod only):

  • the nightly pg_dump on PVC pg-logical-backups (14 days), or
  • a CNPG volume snapshot backup (7 per cluster).

The procedures are in Database backup and restore. There is no point-in-time recovery, so the newest restore point is last night's backup.

Connection errors after changing a password​

The role password is reconciled from k8s/postgres/secrets-<env>.yml. If Galaxy or Rucio logs show authentication failures after a Postgres deploy, update galaxy.secrets.databaseUrl / Rucio config.database.default to match, percent-encoding special characters, and redeploy them.

Legacy: VM databases​

On the VM stack (still galaxy.ornl.gov, and the retained old prod database), a recovery script is installed by the Galaxy deployment at /var/lib/postgresql/backups/bin/restore_postgress.sh (source). Give it a folder from /db_backups/ (usually current) and, optionally, a time in the format 2024-01-18 10:00:00 (New York time):

/var/lib/postgresql/backups/bin/restore_postgress.sh current