Deploy Cloud Native PostgreSQL on Kubernetes

In this blog, we’ll show you how to set up a highly available PostgreSQL database cluster and test their software against a PostgreSQL database on their local machine environment using the CloudNativePG PostgresSQL operator for Kubernetes.

PostgreSQL

PostgreSQL is a popular open-source relational database management system, and cloud-native practices focus on leveraging cloud services and containerization to build scalable, resilient, and agile systems.

Kubernetes Postgres Operator

Cloud Native PostgreSQL (CNP) is a Kubernetes Postgres operator designed by EDB to deploy and manage your PostgreSQL clusters in production environments.

The Cloud Native PostgreSQL has one of the biggest advantages to using the operator, which is that it takes many things off your hands.

Although Cloud Native PostgreSQL is primarily designed to work with containerized applications that run in the same Kubernetes cluster and rely on a PostgreSQL database as their backend, you can also use it with applications that are not in a container.

The pods and associated database-related problems automatically grow as you scale the deployment. The CloudNativePG operator, or Kubernetes Postgres Operator, can also be used in hobby and small business settings where low expenses are required but high availability is required, as it is free and open source.

Advantages of Cloud Native PostgreSQL (CNP):

Scalability: Cloud-native architectures should be designed to scale horizontally or vertically as demand changes. PostgreSQL can be scaled by adding more nodes, using read replicas, or leveraging auto-scaling features provided by cloud platforms.

Containerization: Using containerization technologies like Docker, PostgreSQL instances can be packaged into containers. This allows for consistent deployment across different environments, making moving databases between development, testing, and production easier.

Backup and Restore Strategies: Cloud-native PostgreSQL deployments typically include robust backup and restore strategies. This may involve automated backups to cloud storage, snapshots, and point-in-time recovery mechanisms.

Orchestration: Orchestration tools, such as Kubernetes, can be used to manage and scale PostgreSQL containers. Kubernetes provides features like automated deployment, scaling, and monitoring, ensuring high availability and fault tolerance.

Security: Security is paramount in any database deployment. Cloud-native PostgreSQL implementations should follow the best database security practices, including proper access controls, encryption, and regular security audits.

Microservices Architecture: In a cloud-native environment, applications are often developed as a collection of microservices. Each microservice may have its own database, and PostgreSQL can be a suitable choice for these databases.

Monitoring and Logging: Cloud-native PostgreSQL deployments benefit from effective monitoring and logging solutions. Tools like Prometheus and Grafana can be used for monitoring, while centralized logging systems capture and analyze database-related logs.

Also Read: IoT Security Challenges and Solutions

Setup the Local Kubernetes

Minikube

Minikube is a tool that makes it easy to run Kubernetes locally. For users who want to experiment with Kubernetes or work with it daily, Minikube operates a single-node Kubernetes cluster within a virtual machine (VM) on your laptop.

To install the latest minikube stable release on Linux:

curl -LO https://storage.googleapis.com/minikube/releases/latest/minikube-linux-amd64
sudo install minikube-linux-amd64 /usr/local/bin/minikube

When you installed it, run the command to create a minikube cluster:

minikube start

This will create the Kubernetes cluster, and you will be ready to use it. Verify that it works with the command:

kubectl get nodes

You will see one node called Minikube.

To stop the minikube cluster

minikube stop

Delete all of the minikube clusters.

minikube delete --all

Installing Cloud-Native PostgreSQL Operator

Steps:

1. To install the cloud native-pg operator

kubectl apply -f \
https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/release-1.21/releases/cnpg-1.21.1.yaml

2. You can verify that with:

kubectl get deployment -n cnpg-system cnpg-controller-manager

Deploying a minimal PostgreSQL Cluster

Steps:

1. The cluster-example.yaml sample file defines a simple cluster:

# Example of PostgreSQL cluster
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example
spec:
  instances: 3
  # Example of rolling update strategy:
  # - unsupervised: automated update of the primary once all
  #                 replicas have been upgraded (default)
  # - supervised: requires manual supervision to perform
  #               the switchover of the primary
  primaryUpdateStrategy: unsupervised
  # Require 1Gi of space
  storage:
    size: 1Gi

2. Deploy a PostgreSQL 15.3 cluster

kubectl apply -f cluster-example.yaml

3. Check that the pods are being created

$ kubectl get pod
NAME                                      READY   STATUS    RESTARTS   AGE
cluster-example-1                         1/1     Running     0                   50m
cluster-example-2                         1/1     Running     0                   51m
cluster-example-3                         1/1     Running     0                   51m
cnpg-controller-manager-c466f8dcb-497d2   1/1     Running     0                   52m

4. Accessing a Pod with Kubectl

kubectl exec -it <Cluster NAME> -- bash

Postgres Commands

1. User to access the PostgreSQL database server

psql -U postgres

2. Postgres DB User

\du

3. Postgres list databases 

\l

4. Connect to a specific database:

\c database_name;

5. Show tables in Postgres

\dt

6. Postgres inserts a new row into a table:

INSERT INTO table(column1,column2,...)
VALUES(value_1,value_2,...);

7. Check postgres version

$ kubectl exec -it cluster-example-1 bash
kubectl exec [POD] [COMMAND] is DEPRECATED and will be removed in a future version. Use kubectl exec [POD] -- [COMMAND] instead.
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
postgres@cluster-example-1:/$ psql -U postgres
psql (16.0 (Debian 16.0-1.pgdg110+1))
Type "help" for help.
postgres=#

Services Provide by Cloudnative-PG Cluster

Cloud Native PostgreSQL automatically provides 4 services for each cluster:

$ service/cluster-example-r
service/cluster-example-ro
service/cluster-example-rw
service/cnpg-webhook-service

You can utilize the cluster-example-rw service, which will manage read and write traffic if you need to work with the primary server.

Alternatively, you may simply offload traffic to cluster-example-ro to use replicas or to cluster-example-r to use both the primary server and the replicas if all you need to do is read from the database.

Secrets Provide by Cloudnative-PG Cluster

What about your credentials? Just look into the generated secrets:

$ kubectl get secrets -o name
secret/cluster-example-app
secret/cluster-example-ca
secret/cluster-example-replication
secret/cluster-example-server
secret/cnpg-ca-secret
secret/cnpg-webhook-cert

Generally, PostgreSQL can be accessed without superuser credentials as an application. A new database named app owned by a user name “app“ has already been created for you, and you can access it using the credentials you will find in the cluster-example-app secret.

The following command will dump your credentials, encoded in base64:

$ kubectl get secret cluster-example-app -oyaml -o=jsonpath={.data}
{“password”:”REDACTED”,”pgpass”:”REDACTED”,”username”:”REDACTED”}

When you deploy your application inside the same Kubernetes cluster, you will not need to do that, since you can directly use that secret inside the Deployment of the stateless application.

Postgres vs Mysql

Open-source relational database management systems (RDBMS) that have been in widespread usage for many years are PostgreSQL and MySQL. PostgreSQL and MySQL have unique characteristics even though they have certain things in common. 

The choice between PostgreSQL and MySQL often depends on specific requirements, preferences, and the nature of the project. Both databases are capable and widely used, and the decision should be based on factors such as licensing, SQL compliance, performance considerations, and community support.

Conclusion

As you can see, it’s quite simple, light, and quick to set up a local Kubernetes environment with a PostgreSQL cluster. Most importantly, it is self-contained, which means it can be easily turned down at the end of your work:

Delete all of the minikube clusters.

minikube delete --all

Also Read: Narrowband IoT: Everything You Need to Know