Launching a Postgres database
Symbiosis currently doesn't offer managed databases. Instead, we suggest using a Kubernetes operator to manage the database for you.
CloudNativePG is an operator for creating low-maintenance and production-ready Postgres clusters in Kububernetes. CloudNativePG supports with operations such as:
- Replication
- Automatic point-in-time backups
- Built-in monitoring
- Rolling updates
This page will show you how to install CloudNativePG and how you can launch a highly available Postgres with just a short and sweet custom resource.
CloudNativePG offers a wide range of features and options that we do not cover in this article, but you can find it in their official documentation.
Installation
Install the CloudNativePG operator and custom resource definitions using Helm:
helm repo add cnpg https://cloudnative-pg.github.io/charts
helm upgrade --install cnpg \
--namespace cnpg-system \
--create-namespace \
cnpg/cloudnative-pg
Creating a Postgres cluster
Once the helm chart is installed, create a Postgres database with the below manifest:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: example-postgres
namespace: default
spec:
instances: 3
storage:
size: 10Gi
This will provision a primary database with two read-replicas, each with 10GiB persistent storage.
Accessing your cluster
CloudNativePG will create a database called app
by default, with credentials to access the database stored in the secret <CLUSTER_NAME>-app
.
Four different services are created to connect to postgres:
<CLUSTER_NAME>-any
- load balances between all postgres DBs in the clusters (both read and writeable)<CLUSTER_NAME>-r
- selects all readable instances<CLUSTER_NAME>-rw
- selects all read and writeable instances<CLUSTER_NAME>-ro
- selects instances that are only readable, but not writeable
These services are not publicly exposed, i.e they are only accessible within your k8s cluster.
You can then launch Pgweb, a minimal Web UI for accessing Postgres, by creating a deployment that uses the read-write services with the credentials stored in example-cluster-app
:
apiVersion: apps/v1
kind: Deployment
metadata:
name: pgweb
labels:
app: pgweb
spec:
replicas: 1
selector:
matchLabels:
app: pgweb
template:
metadata:
labels:
app: pgweb
spec:
containers:
- name: pgweb
image: sosedoff/pgweb
ports:
- containerPort: 8081
env:
- name: PSQL_USERNAME
valueFrom:
secretKeyRef:
name: example-postgres-app
key: username
- name: PSQL_PASSWORD
valueFrom:
secretKeyRef:
name: example-postgres-app
key: password
- name: DATABASE_URL
value: postgres://$(PSQL_USERNAME):$(PSQL_PASSWORD)@example-postgres-rw:5432/app?sslmode=disable
We can access the UI by port-forwarding to the pod, since it doesn't have an Ingress or LoadBalancer configured:
kubectl port-forward deployment/pgweb 8081:8081
The interface should now be visible at localhost:8081
and connected to your newly created postgres cluster.
Voilà! We've set up a highly-available Postgres cluster and connected to the primary DB using the provided credentials.
Monitoring
If you are using the Kube prometheus stack in your cluster you can easily export Postgres metrics:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: postgresql-storage-class
spec:
[...]
monitoring:
enablePodMonitor: true
The generated PodMonitor
will pull IO, transaction, storage and other various data directly into your prometheus instance.
Backups
CloudNativePG can be configured to store PIT (point-in-time) backups to various different sinks.
Backups require that you have s3-compatible storage, for example by launching a Minio instance into your cluster.
Configure backups with your access key and storage key:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: postgresql-storage-class
spec:
backup:
barmanObjectStore:
destinationPath: "<destination path here>"
s3Credentials:
accessKeyId:
name: aws-creds
key: ACCESS_KEY_ID
secretAccessKey:
name: aws-creds
key: ACCESS_SECRET_KEY
Backups can be scheduled by creating a ScheduledBackup
resource that points to your cluster. The below configuration will create a backup every midnight UTC.
apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
name: backup-example
spec:
schedule: "0 0 0 * * *"
backupOwnerReference: self
cluster:
name: example-cluster
See their official documentation for how to restore your cluster from a backup.
Alternative operators
CloudNativePG is a simple operator for running a production-ready Postgres database. However, there are many other alternatives, like these ones below: