Overview

Bitnami's PostgreSQL High Availability (HA) solution leverages the PostgreSQL replication manager (repmgr) to manage replication and failover processes within PostgreSQL clusters. It ensures data redundancy, load balancing, and automatic failover to maintain database availability.

Architecture

PostgreSQL Nodes:

• Primary Node: Handles all write operations.

• Replica Nodes: Handle read operations and replicate data from the primary node.

Pgpool-II:

• Acts as a proxy and load balancer for PostgreSQL nodes.

• Routes write queries to the primary node and read queries to replica nodes.

• Monitors node health and facilitates failover during a primary node failure.

Replication Manager (repmgr):

• Manages replication and automates failover.

• Promotes a replica to the primary role during failures.

Deployment Steps

1. Add the Bitnami Helm Repository:

helm repo add bitnami https://charts.bitnami.com/bitnami

2. Install the PostgreSQL-HA Chart:

helm install access-postgresql-ha bitnami/postgresql-ha \
  --namespace access \
  --create-namespace \
  -f values.yaml

3. Configure Values:

Customize the values.yaml file to set the desired configuration. Examples:

Option 1: Basic Initialization

postgresql:
  username: PA_NON_ROOT_USER
  password: postgres
  database: pbsworks
  postgresPassword: '$POSTGRS_PASSWORD'

Option 2: Custom Initialization Scripts

postgresql:
  initdbScripts: |
    CREATE USER myuser WITH PASSWORD 'mypassword';
    CREATE DATABASE mydatabase;
    GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

High Availability Failover Demo

Cluster Status:

To check the cluster status before failure:

kubectl exec -it access-postgresql-ha-postgresql-2 -n access \
  -- /opt/bitnami/scripts/postgresql-repmgr/entrypoint.sh repmgr -f /opt/bitnami/repmgr/conf/repmgr.conf cluster show

Example Output:

ID | Name                           | Role    | Status    | Upstream        | Timeline
1  | access-postgresql-ha-postgresql-0 | Primary | Running   |                 | 9
2  | access-postgresql-ha-postgresql-1 | Replica | Running   | access-postgresql-ha-postgresql-0 | 9

Inducing Failover:

Manually fail the primary node and observe the failover process:

kubectl delete pod access-postgresql-ha-postgresql-0 -n access

Check the cluster status again to confirm the new primary:

kubectl exec -it access-postgresql-ha-postgresql-2 -n access \
  -- /opt/bitnami/scripts/postgresql-repmgr/entrypoint.sh repmgr -f /opt/bitnami/repmgr/conf/repmgr.conf cluster show

Within 10-20 seconds, a replica should be promoted to the primary role.

Post-Deployment Configuration

Pgpool-II Configuration:

Pgpool-II manages query routing and failover. To access Pgpool-II:

kubectl port-forward svc/access-postgresql-ha-pgpool 5432:5432 -n access

Accessing the Database:

Use the following credentials from the values.yaml configuration:

psql -h localhost -p 5432 -U <username> -d <database>

Scaling Nodes:

To add replica nodes:

helm upgrade access-postgresql-ha bitnami/postgresql-ha \
  --namespace access \
  --set postgresql.replicaCount=<desired_count>

Troubleshooting

Common Issues:

1. Node Connectivity Errors:

   • Verify network policies and ensure all nodes can communicate.

2. Cluster Not Ready:

   • Check logs for Pgpool-II or replication manager.

3. Failover Delays:

   • Verify health checks and timeout settings in values.yaml.

Log Access:

kubectl logs <pod_name> -n access

Monitoring

Enable monitoring to track cluster health and performance. Add the following to values.yaml:

metrics:
  enabled: true

Access metrics via Prometheus or other monitoring tools.

Conclusion

Deploying Bitnami PostgreSQL-HA on K3s provides a robust, highly available database solution. By following the steps above, you can ensure minimal downtime and maintain consistent database performance in your Kubernetes environment.