How to Deploy a PostgreSQL cluster on Kubernetes

A PostgreSQL cluster can be thought of as a collection of databases copied across several instances. Each instance can be thought of as an independent node containing all your databases. Normally, we have more than one node per cluster in order to deploy a highly-available database solution.

A PostgreSQL cluster cannot be directly deployed on Kubernetes because it has some specific requirements. These requirements can be fulfilled by a Kubernetes Operator that uses a Custom Resource Definition (CRD) to help manage and deploy PostgreSQL.

Today we'll be taking a look at using the Zalando Postgres Operator to deploy a PostgreSQL cluster using K8s.

Pre-requisites:

1. Have Kubernetes installed (for this blog I'll be using minikube).
2. Have kubectl installed.

Manual Deployment:

Create an empty directory to start working in. First we'll clone the zalando postgres operator repo from github.

git clone https://github.com/zalando/postgres-operator.git
cd postgres-operator

Next we'll apply the YAML manifests in the given order:

kubectl create -f manifests/configmap.yaml  
kubectl create -f manifests/operator-service-account-rbac.yaml 
kubectl create -f manifests/postgres-operator.yaml  
kubectl create -f manifests/api-service.yaml

Next we run a script provided in the repo that helps us set-up an acid-minimal-cluster.

./run_operator_locally.sh

That's it! We now check if the postgres operator is operational.

kubectl get pod -l name=postgres-operator

This should show us the pod in a RUNNING state.

Deploying the UI

Next we'll create our cluster. But first, we'll deploy the UI so that it makes this process easier to visualize. To deploy the UI, run the following command:

kubectl apply -f ui/manifests/

This can take a while to go into the RUNNING state, so check with the following command till the pod is up.

kubectl get pod -l name=postgres-operator-ui

After both the Operator and UI pods are up, we now port-forward to access the interface from our browser.

kubectl port-forward svc/postgres-operator-ui 8081:80

Creating a Cluster

Open up your local browser and access localhost:8081.

You can configure your cluster any way you like. I'll be going forward with the configuration. Press the create cluster button when you're done.

Wait until all the status checks have cleared.

Connecting to the cluster using PSQL

PSQL is a CLI that allows us to query postgres. To use it with our newly deployed cluster, we run port-forward the default 5432 used by Postgres to 6432. We also set up the name of the master pod of our acid-minimal cluster as an environment variable.

export PGMASTER=$(kubectl get pods -o jsonpath={.items..metadata.name} -l application=spilo,cluster-name=acid-minimal-cluster,spilo-role=master -n default)


kubectl port-forward $PGMASTER 6432:5432 -n default

Setup the following environment variables as well (one define the SSLMODE and the other defines the K8s secret when creating acid-minimal-cluster):

export PGPASSWORD=$(kubectl get secret postgres.acid-minimal-cluster.credentials.postgresql.acid.zalan.do -o 'jsonpath={.data.password}' | base64 -d)
export PGSSLMODE=allow

Now connect with PSQL to the default postgres database:

psql -U postgres -h localhost -p 6432

We have successfully deployed our K8s PG cluster. You can now run queries as you like. In the next part we'll take a look at deploying Apache-AGE load balancing using Pg-Pool II for AGE.

References:

1. https://github.com/zalando/postgres-operator/blob/master/docs/quickstart.md