PostgreSQL HA Cluster in Kubernetes

4 min readApr 12, 2018

We all know how easy is setting up an Amazon RDS-based PostgreSQL cluster with automatic failover that just works out of the box. There are alternatives to Amazon RDS implemented by other cloud providers, but can we reach the same level of operation comfort if we’re running on-premise?

Recently, I was testing Kubernetes Operator from Crunchy Data, which helps manage highly available PostgreSQL clusters in Kubernetes, and I was surprised by the fact that it just works! Here’s what you get after installing the operator:

There’s Operator running in Kubernetes cluster as Pod. Using the command line client utility called pgo you basically send an order to the Operator to create clusters, scale them, create users, etc. Below are instructions of how to try it up in local Kubernetes environment provided by Docker Edge for Mac.

Installation

The first step is deploying a PostgreSQL Operator on Kubernetes, and installing a client that will be used for managing PostgreSQL clusters. Please make sure you have the following pre-requisites:

Docker Edge 18.04 or later (or real Kubernetes cluster). I didn’t test this setup with Minikube, so I can’t tell whether it works the same.
Go (brew install go if you’re running on Mac)
Git

Run this script, which deploys the Operator:

To verify that the Operator is running, run:

kubectl get pod --selector=name=postgres-operator

Now, we can use the pgo client for creating a new PostgreSQL cluster. Create a port forwarding to the Operator API by running the following command in a separate shell window:

kubectl port-forward $(kubectl get pod --selector=name=postgres-operator -o=jsonpath="{.items[0].metadata.name}") 8443:8443

Define environment variables that represent the pgo client configuration:

Verify that pgo client is working:

~$ pgo version
pgo client version 2.6
apiserver version 2.6

Creating PostgreSQL Cluster

To create a new cluster, run:

pgo create cluster pg-test --pgpool

The last parameter tells that we want to add pgpool middleware to the cluster. View cluster status by running:

~$ pgo show cluster all --show-secretscluster : pg-test (centos7-10.3-1.8.2)
        deployment : pg-test
        deployment : pg-test-pgpool
        service : pg-test (10.103.21.84)
        service : pg-test-pgpool (10.111.169.8)
        labels : crunchy_collect=false name=pg-test pg-cluster=pg-test pgpool-secret= crunchy-pgpool=truesecret : pg-test-postgres-secret
        username: postgres
        password: 8ZUYsZziFgsecret : pg-test-primaryuser-secret
        username: primaryuser
        password: mVy2zA2fICsecret : pg-test-testuser-secret
        username: testuser
        password: lNgQ2u2laA

Initially, there’s only leader host running. To add two more replicas, run the following command:

pgo scale pg-test — replica-count=2

Testing the Cluster

Let’s connect using PostgreSQL client, and try playing with it. First, setup port forwarding to port 5432 by running this command in a separate window:

kubectl port-forward $(kubectl get pod --selector=name=pg-test-replica -o=jsonpath="{.items[0].metadata.name}") 5432:5432

Now, we can connect to the pgpoolusing testuser user (currently, this is the only user you can use for connecting until this issue is fixed).

psql -h 127.0.0.1 -U testuser postgres
Password for user testuser:
psql (10.3)
Type "help" for help.postgres=>

Try creating some table with some sample data, and run a query on it:

Failover

Now, let’s try to kill the PostgreSQL leader, and see what happens:

kubectl delete pod $(kubectl get pod --selector=name=pg-test -o=jsonpath="{.items[0].metadata.name}") --cascade=false

Now, if we try to execute any DDL commands we won’t succeed, because there’s no leader:

postgres=> drop table test;
ERROR:  cannot execute DROP TABLE in a read-only transaction

Before electing a new leader, let’s see first who are the candidates:

~$ pgo failover pg-test --queryFailover targets include:
        pg-test-lran
        pg-test-zenx

Elect a new leader:

pgo failover pg-test --target pg-test-zenx

Reconnect, and see that we have a new leader now:

~$ psql -h 127.0.0.1 -U testuser postgres
Password for user testuser:
psql (10.3)
Type "help" for help.postgres=> create table test();
CREATE TABLE
postgres=> drop table test;
DROP TABLE
postgres=>

Conclusion

Lots of different abstraction layers that Kubernetes provides slowly close the gap between the functionality that we get from known Cloud providers and whatever we can build in our own garage. PostgreSQL clustering usually requires tremendous work to set it up and maintain, but Kubernetes Operators concept helps automate most of this hassle.

The PostgreSQL Operators project is relatively new, and one shouldn’t try it in production right away. However it’s very promising, and I would recommend following it at least.

Thanks!