Resilient Postgres Infra on Kubernetes

Rounak Saraf
April 2, 2024


In today's dynamic digital landscape, building resilient and scalable architectures is paramount. Particularly, managing a highly available PostgreSQL database cluster, orchestrated by Pgpool and running on Kubernetes, presents a robust solution. This infrastructure entails multiple PostgreSQL pods configured for high availability and data replication, complemented by Pgpool for load balancing and automatic failover. Leveraging Kubernetes StatefulSets, this setup ensures the seamless operation of critical workloads. The PostgreSQL cluster employs Streaming Replication, both Synchronous and Asynchronous, to maintain data consistency across replicas. In this guide, we'll navigate through the intricacies of deploying and managing such a setup, highlighting the pivotal role of Kubernetes in achieving resilience, scalability, and sustainability in database management.

Summary of the Infrastructure


The infrastructure consists of a highly available PostgreSQL database cluster managed by Pgpool, running on Kubernetes. The setup includes multiple PostgreSQL pods for high availability and data replication, and Pgpool for load balancing and automatic failover. The PostgreSQL cluster uses Streaming Replication (Synchronous/Asynchronous) to maintain data consistency across replicas.

Key Points

  • The infrastructure is designed for high availability, fault tolerance, and data redundancy.
  • PostgreSQL StatefulSets ensure that each PostgreSQL pod has its dedicated Persistent Volume Claim (PVC) for data storage.
  • The primary PostgreSQL pod handles read/write operations, and standby pods are continuously replicating data from the primary.
  • Pgpool serves as a load balancer and a connection pool manager to distribute read queries across all available replicas.
  • In case of the primary pod failure, Pgpool automatically promotes a standby pod as the new primary through automatic failover.

Cluster Architecture, Diagram and Description


The cluster architecture consists of the following components:


  • A StatefulSet with multiple replicas for PostgreSQL pods.
  • Each PostgreSQL pod has its Persistent Volume Claim (PVC) for data storage.
  • The primary PostgreSQL pod handles read/write operations, and standby pods replicate data from the primary.
  • ConfigMap and Secrets are used to store configuration files and sensitive data like passwords.


  • A Deployment for Pgpool pod(s) responsible for load balancing and failover management.
  • Pgpool distributes read queries across PostgreSQL replicas.
  • It automatically promotes a standby to primary in case of the primary pod's failure.

Client Pod:

  • A standalone client pod to connect and interact with the PostgreSQL cluster.
  • It is a convenient way to test the setup using psql commands.

Importance and Working

  • Resilience: By leveraging Kubernetes StatefulSets and persistent volumes, the architecture ensures resilience to failures at both the application and infrastructure levels. In case of pod failures, Kubernetes automatically restarts the affected pods, while persistent volumes ensure data integrity and availability.
  • Scalability: The architecture can scale horizontally by adding more instances of PostgreSQL pods and Pgpool instances to handle increasing user loads. Kubernetes manages the scaling process seamlessly, ensuring optimal resource utilization and performance.
  • High Availability: With Pgpool managing connections and load balancing, and PostgreSQL StatefulSets providing data redundancy and failover capabilities, the architecture guarantees high availability of the database cluster. Users experience minimal downtime and uninterrupted access to their data, even during maintenance or infrastructure failures.

Steps to Set Up the Infrastructure and Running on DigitalOcean

Overall Setup - 

  • Create a ConfigMap with pre-stop script for PostgreSQL pods.
  • Create Secrets to store passwords for PostgreSQL and repmgr.
  • Deploy PostgreSQL StatefulSet, Headless Service, and Persistent Volumes.
  • Deploy Pgpool Deployment, Service, and Secrets.
  • Deploy the Client Pod for testing purposes.

Elaborate Steps - 

Prerequisites - Installation of kubectl.

Deploying the infrastructure on Kubernetes cluster on Digital Ocean - 

  • Install doctl
  • Setup a kubernetes cluster with required config in Digital Ocean
  • Configure the API key for authentication (follow documentation)
  • Use the command - doctl kubernetes cluster kubeconfig save <CLUSTER_NAME>
  • Verify that kubectl is now pointing to your DigitalOcean Kubernetes cluster by running:
  • kubectl cluster info

Setting up the infrastructure -

  • Client directory - kubectl apply -f . -n database
  • Pgpool directory - kubectl apply -f . -n database
  • Postgresql directory - kubectl apply -f . -n database
  • Next, retrieve the PostgreSQL password from the secrets to use it later: 

    PASSWORD=$(kubectl get secret postgres-secrets -n database -o  jsonpath="{.data.postgresql-password}" | base64 --decode)
  • Connect to the cluster using psql-client - kubectl exec -it pg-client -n database —-n /bin/bash

You are now inside the PostgreSQL client container. You can connect to the cluster using the following command - psql -h pgpool-svc -p 5432 -U postgres -d postgres -W <<< "$PASSWORD"

  • Alternate way for connecting to the cluster from outside without using pg-client -

PGPASSWORD=$PASSWORD psql -h <IP ADDR> -p <NODEPORT ADDR> -U postgresRemember to replace <IP ADDR> with the public IP address of one of your nodes and <NODEPORT ADDR> with the NodePort address of the pgpool-svc-nodeport service. In this case <IP ADDR> = and <NODEPORT ADDR> = 32000. The password for this infra setup is WbrTpN3g7q .You are ready for testing !! 

Steps to Test Resilience

Replication Testing:

  • Connect to the primary PostgreSQL pod using the Client Pod.
  • Create a new database and some test tables.
  • Verify that the data is replicated to the standby pods.

Elaborate Steps - 

  • Connect to the primary database - PGPASSWORD=${PASSWORD} psql -h pgpool-svc -p 5432 -U postgres
  • Create a database and a table - 


\c testdb;


  • Insert some data into the table -

INSERT INTO test_table (name) VALUES ('Data 1'), ('Data 2'), ('Data 3');

  • Check the data in the primary database -

SELECT * FROM test_table;

  • Exit out of the primary node - \q. Connect to one of the standby nodes and check if the data is replicated - 

PGPASSWORD=${PASSWORD} psql -h <standby-node-ip> -p <standby-node-port> -U postgres

Replace <standby-node-ip> and <standby-node-port> with the appropriate values for your setup.

Example - PGPASSWORD=${PASSWORD} psql -h -p 5432 -U postgres

  • Once connected to the standby node, check the data in the test_table - 

\c testdb; 

SELECT * FROM test_table;

  • To check for databases replicated/present in the standby pod, you can use the psql command and the following query:

PGPASSWORD=${PASSWORD} psql -h -p 5432 -U postgres -c "SELECT datname FROM pg_database;"

This should list the databases that are available in the standby nodes. If testdb is present in the list, it would mean that it has been replicated. 

  • To get into the testdb of the standby node - 

PGPASSWORD=${PASSWORD} psql -h -p 5432 -U postgres -d testdb

The test_table table data can be checked using - SELECT * FROM test_table;

Failover Testing:

  • Simulate a failure on the primary PostgreSQL pod (e.g., delete the pod).
  • Observe that Pgpool automatically promotes a standby pod as the new primary.
  • Verify that read/write operations are now directed to the new primary.

Elaborate Steps - 

  • Find the details of the primary and the secondary nodes -

PGPASSWORD=WbrTpN${PASSWORD} 3g7q psql -h pgpool-svc -p 5432 -U postgres -c "SELECT * FROM pg_stat_replication;"

Look for the node with state = streaming in the result. Note the client_addr and client_port values 

for this node. This is the standby node. 

No. of entries you should see = No. of replicas of Postgres running minus 1. (2 in this case).

  • Scale down the primary node (stimulate failure) - 

kubectl scale statefulset postgres-sts --replicas=0

Wait for a few moments to allow the cluster to detect the primary node failure and promote a new primary.

  • Check the data in the new primary node (it should persist)

PGPASSWORD=${PASSWORD} psql -h pgpool-svc -p 5432 -U postgres

\c testdb; 

SELECT * FROM test_table;

The data in the test_table should still be available on the new primary node, indicating successful 


  • Alternatively if kubectl scale statefulset postgres-sts --replicas=0, Identify the name of the primary pod (postgres-sts-0) and delete it:

kubectl delete pod postgres-sts-0 -n database

  • After deleting the primary pod, observe the status of the remaining pods to see which one becomes the new primary:

kubectl get pods -n database   

One of the standby pods should now take over as the new primary, and the PostgreSQL cluster 

should continue to function with the new primary pod.

The output of kubectl get pods -n database should contain a primary node that is 

younger than the previous standby pods (just born), this is because the standby pod is promoted 

to the primary pod. 

Sample logs of follower pod when the primary pod goes down:

  • The replication status can be verified as before - 
PGPASSWORD=${PASSWORD} psql -h pgpool-svc -p 5432 -U postgres -c "SELECT * FROM pg_stat_replication;"

Importance and Working

Pg-Pool for Postgres

Pg pool is a middleware component that sits in front of the Postgres servers and acts as a gatekeeper to the cluster.
It mainly serves two purposes: Load balancing & Limiting the requests.

  • Load Balancing: Pg pool takes connection requests and queries. It analyzes the query to decide where the query should be sent.
  • Read-only queries can be handled by read-replicas. Write operations can only be handled by the primary server. In this way, it load balances the cluster.
  • Limits the requests: Like any other system, Postgres has a limit on no. of concurrent connections it can handle gracefully.
  • Pg-pool limits the no. of connections it takes up and queues up the remaining. Thus, gracefully handling the overload.

RepMgr Inside PostgreSQL Servers

RepMgr is an open-source tool shipped with Postgres which serves two purposes: Replication & Failover.

  • Replication: It replicates data from the primary server to all the replicas. This helps in reducing the load on the servers by distributing read & write queries.

Failover: It can handle failovers in the cluster i.e. it can promote a read-only server to a primary server when required.