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
Be the first to comment