PostgreSQL

PostgreSQL (often called "Postgres") is an open-source object-relational database system. Galaxy stores users, histories, datasets metadata, workflows and jobs in it. Rucio stores its catalog in it.

Deployment

Each cluster runs two CloudNativePG (CNPG) clusters in namespace ndip:

Cluster	Database / role	Prod	Test
galaxy-pg	galaxy	2 instances, 20Gi	1 instance, pinned to mickey-wn01
rucio-pg	rucio	2 instances, 10Gi	1 instance (separate WAL volume)

Both run PostgreSQL 17 (ghcr.io/cloudnative-pg/postgresql:17.11-standard-trixie) on storage class longhorn-cnpg (1 Longhorn replica, kept on the instance's node; CNPG's standby provides the redundancy). On prod, CNPG runs a primary plus an asynchronous standby on another node. If a node fails, the operator promotes the standby and moves the -rw Service.

Component	CI job (test / prod)	calvera
CNPG operator (cluster-wide, namespace cnpg-system, 2 replicas)	cnpg-operator-test / cnpg-operator-prod	k8s/cnpg-operator/
Clusters, role secrets, backups	postgres-test / postgres-k8s-prod	k8s/postgres/

Role passwords come from k8s/postgres/secrets-<env>.yml (galaxyDbPassword, rucioDbPassword). The deploy writes them to the Secrets galaxy-pg-app and rucio-pg-app, and CNPG reconciles the password into Postgres.

Things to know

• Connect to <cluster>-rw only. galaxy-pg-ro/-r can route to the standby, which rejects writes. Galaxy uses postgresql://galaxy:<password>@galaxy-pg-rw:5432/galaxy, Rucio postgresql+psycopg://rucio:<password>@rucio-pg-rw:5432/rucio.
• Use the full resource name: kubectl -n ndip get clusters.postgresql.cnpg.io. Plain cluster is Rancher's.
• Asynchronous replication. A failover can lose the last few committed transactions.
• No point-in-time recovery. There is no WAL archive. Restore points are the nightly dump and the nightly snapshot.
• Operator upgrades roll every instance (a switchover for the primary). See k8s/cnpg-operator/README.md.
• Galaxy reconnects after a failover (database_engine_option_pool_pre_ping). Rucio recycles pooled connections every 300s.

Useful commands:

kubectl -n ndip get clusters.postgresql.cnpg.io                       # STATUS should be "Cluster in healthy state"
kubectl -n ndip get pods -l 'cnpg.io/cluster in (galaxy-pg,rucio-pg)' -o wide
kubectl -n ndip get pods -l cnpg.io/cluster=galaxy-pg,cnpg.io/instanceRole=primary   # which instance is primary
kubectl -n ndip exec -it galaxy-pg-1 -- psql -U postgres galaxy        # psql as superuser (pick the primary)

Backups and restores are described in Database backup and restore. Failure handling is in Database recovery.

Legacy: VM deployment

On the VM stack, PostgreSQL ran on the Galaxy VM, deployed by the galaxyproject.postgresql Ansible role as part of the ndip job. The old prod VM's database is stopped but kept intact for rollback during the soak period after the 2026-10-06 migration. galaxy.ornl.gov still uses the Ansible-managed PostgreSQL.