The cluster architecture consists of the following components:

PostgreSQL:

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

Pgpool:

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

‍

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

‍

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 -

• Clone the repo or download and extract the zip from - https://github.com/scriptcamp/kubernetes-postgresql.git
• Move into the directory using 
• Create namespace - kubectl create namespace database
• Next step is to apply all the yaml files present in all the subdirectories -

• 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> = 34.138.59.54 and <NODEPORT ADDR> = 32000. The password for this infra setup is WbrTpN3g7q .You are ready for testing !! 

‍

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 - 

CREATE DATABASE testdb;

\c testdb;

CREATE TABLE test_table (id SERIAL PRIMARY KEY, name VARCHAR(50));

• 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 10.244.0.193 -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 10.244.0.193 -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 10.244.0.193 -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 

failover.

• 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;"