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

Following on from our post of getting Kubernetes stood up atop VMware’s vSphere. The next stage in deploying Zercurity to Kubernetes is to have a reliable backend database. Which can normally be a headache. However, the team over at Crunchy Data have built an awesome tool to deploy and manage a PostgreSQL cluster.

The Crunchy PostgreSQL Operator (pgo) can build PostgreSQL replica sets, manage backups and scale up and scale down additional nodes as well as scale the CPU and memory requirements of running Kubernetes pods. Backups can also be managed and scheduled via the pgo command line tool. Crunchy PostgreSQL also provides a number of add-in pods such as performance metrics via Grafana and Prometheus. As well as pgadmin (a web based GUI to manage your PostgreSQL cluster).

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.

There are two main parts to pgo. There is the management layer (control plane) which provides APIs to the pgo command line tool. Which also exposes a RESTful API to manage your PostgreSQL clusters which is nice. The other part are your deployed database clusters which span a number of other pod types which we’re going to explore.

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.

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 .

https://github.com/helm/helm/releases

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.

Start by checking out the most recent version of Crunchy Data’s PostgreSQL operator. You’ll need git installed (apt install git).

Once cloned we can run there client setup script to download and install the pgo utility for us.

Note: In the next part, for Mac OSX users, you must use ~/.bash_profile instead of ~/.bashrc .

Once the script has finished running you’ll see something like this:

You’ll then need to go ahead and append this into the end of your .bashrc file. All we’re doing here is letting your system know to set some additional environmental variables for each new session you create so you won’t have to enter them in every time. When using the pgo utility.

Simply run the source command which will apply the new configuration to your current session.

You should now be able to run the pgo version command to check everything is working (you’ll get an error about fetching the apirserver version. Which we’re going to setup next).

Installing pgo operator

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

This will take a few minutes to deploy everything and once its done. You’ll be able to query the new pods deployed. To check its running correctly.

You should see a single pod like so:

Lastly, in order for your local pgo client to work correctly we need to port-forward the remote API server to your local machine like so:

You can now re-run pgo version which will now yield an apiserver version:

If you get the following error: Error: Get “https://127.0.0.1:8443/version": dial tcp 127.0.0.1:8443: connect: connection refused it’ll be because your port-forwarding request (shown above) has died and you’ll need to restart it.

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

To deploy support for metrics simply run the following from the root of the postgres operator git repository.

Once helm has finished running you can check everything has been deployed correctly like so:

Once you’ve spun up your first cluster as shown in the next part of this post the dashboard will be automatically populated. To access the metrics dashboard you’ll need to port-forward the Grafana service to your local machine:

The default login for Grafana is admin and use admin again for the password. Your Grafana dashboard will be available from:

http://localhost: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.

  • --replica-count=2
    This will deploy two additional servers to act as replicas. Usually to provide additional redundancy and read throughput. This can be scaled up and down at a later stage using the pgo scale command.
  • --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.

If you have support for the LoadBalancer service type and require external database access. You can add the following parameter: --service-type=LoadBalancer

This command will return your new database credentials. However, the cluster will not yet be live and will take a few minutes to come online whilst it configures itself. You can check the status of your cluster like so (where zercurity is your cluster name):

You can also grab your database credentials at any time with:

If you’ve not opted to use pgbouncer (the flag -pgbouncer ) or you’d like to connect directly your database instances. As the bgbouncer does not have permission to create new databases. You’ll need to request your login using information with this command:

You can also quickly rotate your password too: pgo update pgbouncer zercurity --rotate-password

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:

With the port exposed locally you can now connect in with the psql client or any database management tool.

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:

You can then check the deployment status with (it should only take a few minutes):

After the status has changed to Running you’ll need to once again port forward the remote service to your local machine:

You should then be able to access it from your local machine.

http://localhost: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:

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

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

Backups

To create a backup simply run:

This will automatically create an incremental backup from its last backup point. If you wish to force a full backup you can run:

You can also see what backups have been made with the pgo backup show <zercurity> . Or remove outdated backups with pgo delete backup <zercurity>

Upgrading the Postgres Operator & pgo

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

The last part is just to update the pgo client binary itself. Which can just be downloaded directly from the releases page and replaced with your system binary.

https://github.com/CrunchyData/postgres-operator/releases

Once installed veirfy the versions of both the client and remote server with pgo version.

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.

Real-time security and compliance delivered.