Overview#

This document provides a step-by-step guide for setting up a highly available (HA) PostgreSQL cluster using GritivaCore. The setup includes a primary node, two replicas, and a connection pooler (pgbouncer), all interconnected via gmesh.

Prerequisites#

To successfully set up the PostgreSQL cluster, ensure you have the following:

• Three virtual machines (VMs) with agents installed and gmesh meshed.

Installation#

Step 1: Create Primary Scope#

On VM-1, execute the following command to create the primary scope:

gritivactl scope create pg-primary --cpu 4 --mem 16G

Step 2: Configure Primary PostgreSQL#

Create a PostgreSQL compose for the primary node with the following parameters:

• wal_level=replica
• archive_mode=on

Step 3: Create Replica Scopes#

On VM-2 and VM-3, create replica scopes and connect each to the pg-primary scope.

Step 4: Initialize Replicas#

Run the following command on each replica to initialize them:

pg_basebackup -h pg-primary.scope-vm-1

Step 5: Add pgbouncer Scope#

On VM-1, create a pgbouncer scope to manage connections to all three nodes.

Step 6: Register the Service#

Execute the following command to register the PostgreSQL service:

gritivactl service create postgres --scope pgbouncer --port 6432

Step 7: Share the Service#

Allow access to the service with the following command:

gritivactl share allow --service pgbouncer:postgres --to my-app

Step 8: Configure Backup#

Set up a backup loop scope that runs pg_basebackup daily to S3.

Step 9: Failover#

In case of a failure, promote a replica using:

pg_promote()

Update the pgbouncer configuration accordingly.

Step 10: Monitor Replication#

Keep an eye on pg_stat_replication for delay and lag thresholds.

Troubleshooting#

If you encounter issues, refer to common troubleshooting tips for PostgreSQL clusters.

Conclusion#

This guide provides a comprehensive approach to setting up a PostgreSQL cluster with high availability. For further insights, refer to the official PostgreSQL documentation.