Platform9 Blog

How to Set Up Postgres on Kubernetes Using Platform9

In this article, you will learn how to set up Postgres on Kubernetes clusters using a free Platform9 Kubernetes account.

Postgres on Kubernetes

Postgres is a relational database that uses SQL to perform queries. It provides features that safely allow users to persist and scale data workloads. It can handle workloads ranging from small single-machine applications to large internet-facing applications with many concurrent users.

Stateful workloads have been shown to run successfully on Kubernetes. Postgres on Kubernetes is a popular choice, especially for organizations that have standardized on Kubernetes as the control plane for their applications. In fact, a recent survey cited Postgres as one of the top-3 technologies running on Docker!

Getting Started

For the sake of simplicity, we will be using a hostPath volume but you can choose any StorageClass you have deployed in the cluster.

To get started, create your free Platform9 Kubernetes account by entering your email below, then select the ‘Deploy Now’ option. Once you have created and verified your account, follow the steps outlined below. 

Step 1: Create your free account

Step 1: Create your free account

Prerequisites

  1. Working Kubernetes cluster 1.16+ with access to kubeconfig.
  2. Access to one of the nodes so we can access the postgres service using a NodePort type.
  3. Helm 2.12+ or Helm 3.0-beta3+ (for HA deployment only)

Deploying Postgres on Kubernetes

Deploying for testing/Stage/Dev Environment

NOTE: This section is for those who want to try PostGres for testing/Stage/Dev environment and deploys PostGres as a deployment object and not as a Statefulset. The storageClass assumed in this section is hostPath which stores the data on the node’s local disk. If the node crashes, the postgres data on the node will not be recoverable.

  • Clone the KoolKubernetes db repository.
$   git clone https://github.com/KoolKubernetes/db.git
  • Create a generic secret so we can specify postgres database Name, postgres username and password. These parameters will be used by postgres deployment for deploying PostgresDB.
$ kubectl create secret generic postgres-secret  --from-literal=POSTGRES_DB='postgresdb' \
--from-literal=POSTGRES_USER=''  --from-literal=POSTGRES_PASSWORD=''

For eg.

$ kubectl create secret generic postgres-secret  --from-literal=POSTGRES_DB='postgresdb' \
--from-literal=POSTGRES_USER=''  --from-literal=POSTGRES_PASSWORD=''
  • Before applying the yaml file that creates Postgres deployment, persistent volume (PV) and its associated persistent volume claim (PVC) along with the nodePort service, here are some of the parameters//fields that you can tweak to choose postgresDB version etc.
  1. This walk-through uses the latest 12.3 postgresDB image but you can choose to select a particular version of PostGres from the available Docker images. You can edit the image by editing the image field on line 52 under the Deployment object ( .spec.template.spec.containers.image field)
  2. You can change the persistent volume type and select any existing StorageClass that you have deployed in your cluster. It can be changed in PersistentVolumeClaim and PersistentVolume objects with the field name storageClassName on lines 10 and 25 (.spec.storageClassName)
  3. You can change the size of the PersistentVolume by editing the field storage under PersistentVolumeClaim and PersistentVolume objects on lines 12 and line 30.
  • Apply the deploy.yaml on the cluster by browsing to the directory where KoolKubernetes repo was cloned and browsing to the subdirectory – /db/postgres/yaml
$ kubectl apply -f deploy.yaml
persistentvolume/postgres-pv-volume created
persistentvolumeclaim/postgres-pv-claim created
deployment.apps/postgres created
service/postgres configured
  • Run the following command to ensure that postgres pod transitions into a ‘Running’ state
$ kubectl get pods -l app=postgres
  • Once the pod is transitioned into a ‘running’ state, you can access this pod by logging onto a worker node and accessing the postgresDB pod by running the psql client and hitting the port 31070 where NodePort service is listening.

(NOTE: You can change the serviceType to loadbalancer or any other type as per your application needs. NodePort type has been choosen here only for simplicity sake.)

$ sudo psql -h localhost -U  --password -p 31070 

If you are following the example mentioned above,

$ sudo psql -h localhost -U postgresadmin --password -p 31070 postgresdb

You will be asked for a password, enter the DBpassword that was specified during Secret creation in Step 2.

  • Once the authentication is complete, you’ll get a postgresDB prompt where you can Create databases,tables etc. as per your application needs.
Password for user postgresadmin:
psql (9.5.21, server 12.3 (Debian 12.3-1.pgdg100+1))
WARNING: psql major version 9.5, server major version 12.
         Some psql features might not work.
Type "help" for help.
postgresdb=#

Cleanup dev/test/staging postgres setup

Run the following command to cleanup the created Kubernetes objects –

$ kubectl delete -f deploy.yaml

All the checks in the above command should complete successfully, before proceeding.

Deploying PostGres in HA setup

NOTE: This section is for those who want to deploy Postgres in an HA setup where loss of one PostGres pod doesn’t result in a data loss. Please note that you need a StorageClass that provides Shared Storage, Replication and redundancy. This guide is using Rook and we’ll be using StatefulSet instead of a deployment object in the earlier section.

  • This tutorial assumes you have Helm installed from where you can access the Kubernetes cluster.
  • Ensure that the default storageClass is set to SharedStorage,Replicated and Redundant or you can specify it while deploying Helm chart. You can do it by passing the parameter –set global.storageClass=
  • Deploy the Helm chart by running the command –
helm repo add bitnami https://charts.bitnami.com/bitnami
helm install postgresql-ha bitnami/postgresql-ha

(NOTE: you can pass additional values referred here if needed)

  • You should be able to observe 2 replicas by default one is the master and the second is standby.
kubectl get pods
NAME                                               READY   STATUS    RESTARTS   AGE
my-release-postgresql-ha-pgpool-768898c659-xlrqn   1/1     Running   0          156m
my-release-postgresql-ha-postgresql-0              1/1     Running   1          147m
my-release-postgresql-ha-postgresql-1              1/1     Running   0          155m
  • Checking the logs on the master pod, here’s what you can observe –
[2020-08-20 08:16:02] [NOTICE] starting monitoring of node "my-release-postgresql-ha-postgresql-2" (ID: 1001)

On the standby pod, you can observe the logs as mentioned below

[2020-08-20 08:14:46] [NOTICE] monitoring cluster primary "my-release-postgresql-ha-postgresql-1" (ID: 1000)
[2020-08-20 08:14:52] [NOTICE] new standby "my-release-postgresql-ha-postgresql-2" (ID: 1001) has connected

Cleanup the HA Postgres setup

helm delete  postgresql-ha

References

https://www.postgresql.org/docs

https://www.datadoghq.com/container-report/

Next Steps

In this blog, we walked through a tutorial on how to set up Postgres on Kubernetes. We hope you found this blog informative and engaging. For more reads like this one, visit our blog page or subscribe for up-to-date news, projects, and related content in real-time.

Check out our additional complete stack projects in our Github repos.

You may also enjoy

How To Set Up Rook To Manage Ceph Within Kubernetes

By Piyush Katira

How To Set Up Kubevirt For Kubernetes Based VM Management

By Peter Fray

The browser you are using is outdated. For the best experience please download or update your browser to one of the following: