Highly available and scalable PostgreSQL on Kubernetes (k8s) with the Crunchy PostgreSQL Operator (pgo).

Prerequisites

A working and up-to-date Kubernetes cluster. Whilst pgo is possible to install without helm. We’ll be using helm to deploy and manage our pgo operator nodes.

Installing helm

There are a few ways to deploy pgo. However, whilst installing helm is an additional step is does make the deployment and upgrade-ability of pgo a whole lot easier.

Linux (Debian/Ubuntu)

curl https://baltocdn.com/helm/signing.asc | sudo apt-key add -
sudo apt-get install apt-transport-https --yes
echo "deb https://baltocdn.com/helm/stable/debian/ all main" | sudo tee /etc/apt/sources.list.d/helm-stable-debian.list
sudo apt-get update
sudo apt-get install helm

Mac OSX

From the helm releases page you can download the pre-complied binary. Or if you have homebrew installed you can just run brew install helm .

curl -o helm.tar.gz https://get.helm.sh/helm-v3.4.1-darwin-amd64.tar.gz
tar -zxvf helm-v3.4.1-darwin-amd64.tar.gz
cd darwin-amd64/
sudo mv helm /usr/local/bin/helm
sudo chmod +x /usr/local/bin/helm

Installing pgo

In order to administer the cluster, Crunchy Data provide a helper utility called pgo. This will connect to the operator control plane API on port 8443 to create and mange database clusters and users amongst other utilities.

git clone https://github.com/CrunchyData/postgres-operator.git
cd installers/kubectl/client-setup.sh
chmod +x client-setup.sh
./client-setup.sh
export PATH="$PATH:${HOME?}/.pgo/pgo/"
export PGOUSER="${HOME?}/.pgo/pgo/pgouser"
export PGO_CA_CERT="${HOME?}/.pgo/pgo/client.crt"
export PGO_CLIENT_CERT="${HOME?}/.pgo/pgo/client.crt"
export PGO_CLIENT_KEY="${HOME?}/.pgo/pgo/client.key"
export PGO_APISERVER_URL='https://127.0.0.1:8443'
export PGO_NAMESPACE=pgo
source ~/.bashrc

Installing pgo operator

Once the pgo client is install we can now deploy the PostgreSQL operator. Which is super simple with helm:

cd postgres-operator/installers/helm
helm install postgres-operator . -n pgo
kubectl -n pgo get pods
NAME                                READY   STATUS    RESTARTS   AGE
postgres-operator-56d6ccb97-tmz7m 4/4 Running 0 2m
kubectl -n pgo port-forward svc/postgres-operator 8443:8443
pgo client version 4.5.1
pgo-apiserver version 4.5.1

Installing pgo metrics

This stage is optional and can be performed at a later stage. As of version 4.5.1 you can now easily deploy metrics to monitor the performance of both your PostgreSQL hosts and pods with Grafana and Prometheus out-of-the-box with a single command. Awesome:

Showing the Grafana dashboard for pgo
cd postgres-operator/installers/metrics/helm
helm install metrics . -n pgo
kubectl -n pgo get poNAME                               READY   STATUS    RESTARTS   AGE
postgres-operator-689598d795 4/4 Running 1 7h
kubectl -n pgo port-forward --address 0.0.0.0 svc/crunchy-grafana 3000:3000

Creating your first cluster

With all that setup done you can now deploy your first PostgreSQL cluster. I’m going to name our new cluster “zercurity” and manually give it a user and database name otherwise, one is generated for you. Using the name of your cluster for the databse name and “testuser” for the username.

  • --pgbouncer
    This provides a haproxy style service to load balance and rate limit connecting clients.
  • --metrics
    This enables support for metrics. As soon as the cluster is up and running you’ll be able to see metrics appear in your Grafana dashboard.
  • --sync-replication (optional)
    As we’re using replica nodes in our cluster. You can turn on replication synchronization, which is useful for workloads that are sensitive to losing transactions. PostgreSQL will not consider a transaction to be committed until it is committed to all synchronous replicas connected to a primary. This comes at the cost of performance.
  • --pvc-size
    Lastly but probably most importantly the pvc-size is the amount of disk space we’re going to allocate for each of our database nodes. In this example we’re allocating 150GB of data. So for 3 nodes (1 master and 2 replicas) that’ll be 450GB in total.
  • --pgbackrest-pvc-size
    This needs to really exceed your pvc-size. If your running incremental backup jobs the total size will more or less match the pvc-size. However, if you’re planning on running complete backups you’ll wan to make sure you have a few multiples of your pvc-size allocated. As the allocated space used will compound over time.
pgo create cluster zercurity --replica-count=2 \
--pgbouncer --pgbadger --metrics --username zercurity \
--sync-replication --database=zercurity --pvc-size=150Gi \
--pgbackrest-pvc-size=500Gi
pgo test <zercurity>cluster : zercurity
Services
primary (100.66.14.31:5432): UP
pgbouncer (100.69.208.231:5432): UP
replica (100.67.75.10:5432): UP
Instances
replica (zercurity-79cf8bc9c-499jp): UP
replica (zercurity-vcvm-66cfb945f5-5f4hx): UP
replica (zercurity-xfrx-69b4f4bbc-69vtr): UP
pgo show pgbouncer <zercurity>CLUSTER   SERVICE             USERNAME  PASSWORD  CLUSTER IP
--------- ------------------- --------- ------------------------
zercurity zercurity-pgbouncer pgbouncer password 100.69.208.231
pgo show user -n pgo <zercurity> --show-system-accounts

Connecting to your cluster

Now that you’ve got your flashy new cluster stood up you’re going to want to kick the tires on it. As we’ve done in prior steps we’re going to need to port forward from our pgbouncer service (or primary node) to our local machine like so:

kubectl -n pgo port-forward svc/<zercurity> 5432:5432
psql -h localhost -p 5432 -U <username> <zercurity>

Installing pgadmin

Once your cluster has been deployed you can add a database web GUI with pgadmin. This can be done with a single command:

pgo create pgadmin <zercurity>
kubectl -n pgo get poNAME                               READY   STATUS    RESTARTS   AGE
...
zercurity-pgadmin-5464c6545-k99n4 1/1 Running 0 7h
kubectl port-forward svc/zercurity-pgadmin 5050:5050
pgadmin installed with pgo.

Updating & scaling your cluster

The -metrics support within the Postgres Operator cluster will give you a strong indication if your database cluster is starting to become starved of resources. Using the pgo utility you can update all the pods within your cluster:

pgo update cluster <zercurity> --cpu=2.0 --cpu-limit=4.0 \
--memory=4Gi --memory-limit=6Gi

Scaling up

You can scale up your cluster’s replica count quite easily.

pgo scale <zercurity> --replica-count=1

Scaling down

Scaling down your cluster is a little more involved as you need to let pgo know which replica pod you wish to terminate.

pgo scaledown <zercurity> --query

pgo scaledown <zercurity> --target=zercurity-replica-xxxx

Backups

To create a backup simply run:

pgo backup <zercurity>
pgo backup <zercurity> --backup-opts="--type=full"
pgo show backup <zercurity>cluster: zercurity
storage type: local
stanza: db
status: ok
cipher: none
db (current)
wal archive min/max (12-1)
full backup: 20201120-222158F
timestamp start/stop: 2020-11-20 / 2020-11-20
wal start/stop: 00000001 / 00000001
database size: 31.3MiB, backup size: 31.3MiB
repository size: 3.8MiB, repository backup size: 3.8MiB
backup reference list:Upgrading pgo

Upgrading the Postgres Operator & pgo

As we’re using helm — upgrading the Postgres Operator is super simple.

cd postgres-operator/installers/helm
helm upgrade postgres-operator . -n pgo
wget -o /usr/local/bin/pgo https://github.com/CrunchyData/postgres-operator/releases/download/v4.4.2/pgo
chmod +x /usr/local/bin/pgo

Its all over!

Hopefully that’s given you a quick dive into how to deploy PostgreSQL on top of Kubernetes. We’ll be following up on this topic in the near future. As well as how Zercurity uses Kubernetes at scale in production with Crunchy Data’s Postgres Operator. However, that’s all for now. Please feel free to get in touch if you have any questions.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store