Data Platform/Systems/PostgreSQL

PostgreSQL

We run PostgreSQL on the Data Platform primarily in order to act as the metadata database serving Airflow instances.

We have chosen to deploy it using the Cloudnative-PG operator, using our Ceph cluster for data storage.

Clusters

We have defined a helm chart for a PostgreSQL cluster, which includes the definitions of backups and connection poolers, as well as database servers.

This configuration is described in: Data Platform/Systems/PostgreSQL/Clusters

Operations and Troubleshooting

Please see: Data Platform/Systems/PostgreSQL/Operations

Backup and Restore

Please see: Data Platform/Systems/PostgreSQL/Backup_and_Restore

Cloudnative-PG

cloudnative-pg is an open-source Kubernetes operator used to provision PostgreSQL clusters in Kubernetes.

It supports the following features:

read-write/read-only service discovery
primary -> standby replication
automatic standby -> primary failover
connection pooling (via pgBouncer)
backups / PIT recovery (via pgBarman)
WAL archiving (via pgBarman)
placement of cluster pods accross network zones
and many more

The operator relies on Kubernetes Custom Resource Definitions , such as Cluster, ImageCatalog, Pooler, etc, each defining how the cluster should be deployed and operated. These CRDs are defined in the cloudnative-pg-crds chart, and the operator chart itself is defined in cloudnative-pg.

The upstream chart integration was discussed and approved in Helm/Upstream Charts/cloudnative-pg.

How we build it

Both the operator and PostgreSQL images (used to deploy clusters themselves) are defined and built in https://gitlab.wikimedia.org/repos/data-engineering/postgresql-kubernetes/. The pgbouncer image, used when deploying Poolers, is defined and built in https://gitlab.wikimedia.org/repos/data-engineering/pgbouncer.

Where we run it

At the time of writing, we only plan to deploy and use it in the dse-k8s-eqiad Kubernetes cluster, to support on-demand provisioning PG clusters for Airflow, with the actual data written to Ceph, via PersistentVolumes, as described in our Airflow - High Availability Strategy document.

We run it in the cloudnative-pg-operator namespace:

root@deploy1003:~# kubectl get pod -n cloudnative-pg-operator
NAME                              READY   STATUS    RESTARTS   AGE
cloudnative-pg-756fdbb74c-7txv9   1/1     Running   0          117m

How we deploy it

root@deploy1003:~# kube_env admin dse-k8s-eqiad
root@deploy1003:~# cd /srv/deployment-charts/helmfile.d/admin_ng
root@deploy1003:~# helmfile -e dse-k8s-eqiad -i diff/apply