DocsKnowledge BaseLiveStream
v1.0
Managed Kubernetes
K8s Basics
PMK Onboarding
Networking / Ingress
Storage
Data Services
Automating PMK
Troubleshooting

Postgres

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.

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 in 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.
Bash
Copy

For eg.

Bash
Copy
  • 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
Bash
Copy
  • 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.
Bash
Copy

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 –
Bash
Copy

(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.
Bash
Copy
  • 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

Bash
Copy

Cleanup the HA Postgres setup

helm delete postgresql-ha

References

https://www.postgresql.org/docs

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

  Last updated on Sep 9, 2021
Next to read:
Kafka
On This Page
PostgresPostgres on KubernetesGetting StartedPrerequisitesDeploying Postgres on KubernetesDeploying in testing/Stage/Dev EnvironmentCleanup dev/test/staging postgres setupDeploying PostGres in HA setupCleanup the HA Postgres setupReferences