PostgreSQL HA Cluster in Kubernetes
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 pgpool
using 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!