APIO Core

Appearance

PostgreSQL High Availability (HA) Configuration Guide ​

This document outlines the procedure for configuring a High Availability (HA) environment for PostgreSQL. Implementing this setup enables automatic database failover and switchover, ensuring minimal downtime and improved service resilience.

Overview ​

The PostgreSQL HA configuration requires a minimum of three nodes:

  • Database Node 1 - Primary PostgreSQL instance
  • Database Node 2 - Standby PostgreSQL instance
  • Arbiter Node - Responsible for quorum management and split-brain detection in the event of a network partition

This topology provides fault tolerance and ensures continuous database availability.

Prerequisites ​

All nodes must have Docker installed. Refer to the official Docker installation guide for your operating system: Docker Engine Installation Documentation

Configuration Parameters ​

The following credentials and configuration values can be customized within the provided sample files:

Default ValueDescription
password123Database password for connecting to apio_core
adminpasswordAdministrative password for PostgreSQL access

WARNING

Ensure that strong, unique passwords are used in production environments.

Deployment Considerations ​

When deploying multiple Apio Core instances, each instance should be configured to connect to a distinct PostgreSQL node to optimize load distribution and improve fault tolerance.

Configuration of the First Database Node ​

This section describes the procedure to configure the first PostgreSQL node (pg-0) within the High Availability cluster.

Host Configuration ​

Add the IP addresses of the remaining cluster nodes to the /etc/hosts file to ensure proper hostname resolution:

shell
echo "a.b.c.d pg-1" >> /etc/hosts
echo "a.b.c.d pg-2" >> /etc/hosts

INFO

Replace a.b.c.d with the actual IP addresses of the second (pg-1) and third (pg-2) nodes.

Directory Permissions ​

Ensure that the PostgreSQL data directory is writable by the container process:

shell
chmod 777 /data/db

Docker Compose Setup ​

Create a dedicated directory for the PostgreSQL Docker Compose configuration:

shell
mkdir /opt/pg/
cd /opt/pg/
vi docker-compose.yaml

Insert the following configuration into the docker-compose.yaml file:

yaml
services:
  pg-0:
    ports:
      - 5432:5432
    image: bitnamilegacy/postgresql-repmgr:17
    environment:
      POSTGRESQL_POSTGRES_PASSWORD: adminpassword
      POSTGRESQL_USERNAME: apio_core
      POSTGRESQL_PASSWORD: password123
      POSTGRESQL_DATABASE: apio_core
      POSTGRESQL_TIMEZONE: UTC
      REPMGR_PASSWORD: repmgrpassword
      REPMGR_PRIMARY_HOST: pg-0
      REPMGR_PARTNER_NODES: pg-0,pg-1,pg-2
      REPMGR_NODE_NAME: pg-0
      REPMGR_NODE_NETWORK_NAME: pg-0
    restart: unless-stopped
   volumes:
     - /data/db:/bitnami/postgresql

INFO

Update all passwords and network parameters according to your organizational security standards.

Service Deployment ​

Once the configuration file has been created, deploy the services using Docker Compose:

shell
docker compose up -d

Verify that all containers are running successfully:

shell
docker compose ps

Configuration of the Second Database Node ​

This section describes the procedure to configure the second PostgreSQL node (pg-1) within the High Availability cluster.

Host Configuration ​

Add the IP addresses of the remaining cluster nodes to the /etc/hosts file to ensure proper hostname resolution:

shell
echo "a.b.c.d pg-0" >> /etc/hosts
echo "a.b.c.d pg-2" >> /etc/hosts

INFO

Replace a.b.c.d with the actual IP addresses of the first (pg-0) and third (pg-2) nodes.

Directory Permissions ​

Ensure that the PostgreSQL data directory is writable by the container process:

shell
chmod 777 /data/db

Docker Compose Setup ​

Create a dedicated directory for the PostgreSQL Docker Compose configuration:

shell
mkdir /opt/pg/
cd /opt/pg/
vi docker-compose.yaml

Insert the following configuration into the docker-compose.yaml file:

yaml
services:
  pg-1:
    ports:
      - 5432:5432
    image: bitnamilegacy/postgresql-repmgr:17
    environment:
      POSTGRESQL_POSTGRES_PASSWORD: adminpassword
      POSTGRESQL_USERNAME: apio_core
      POSTGRESQL_PASSWORD: password123
      POSTGRESQL_DATABASE: customdatabase
      POSTGRESQL_TIMEZONE: UTC
      REPMGR_PASSWORD: repmgrpassword
      REPMGR_PRIMARY_HOST: pg-0
      REPMGR_PARTNER_NODES: pg-0,pg-1,pg-2
      REPMGR_NODE_NAME: pg-1
      REPMGR_NODE_NETWORK_NAME: pg-1
    restart: unless-stopped
    volumes:
      - /data/db:/bitnami/postgresql

INFO

Update all passwords and network parameters according to your organizational security standards.

Service Deployment ​

Once the configuration file has been created, deploy the services using Docker Compose:

shell
docker compose up -d

Verify that all containers are running successfully:

shell
docker compose ps

Configuration of the Third Database Node (arbiter) ​

This section describes the procedure to configure the Arbiter Node (pg-2) within the High Availability cluster.

Host Configuration ​

Add the IP addresses of the remaining cluster nodes to the /etc/hosts file to ensure proper hostname resolution:

shell
echo "a.b.c.d pg-0" >> /etc/hosts
echo "a.b.c.d pg-1" >> /etc/hosts

INFO

Replace a.b.c.d with the actual IP addresses of the first (pg-0) and second (pg-1) nodes.

Directory Permissions ​

Ensure that the PostgreSQL data directory is writable by the container process:

shell
chmod 777 /data/db

Docker Compose Setup ​

Create a dedicated directory for the PostgreSQL Docker Compose configuration:

shell
mkdir /opt/pg/
cd /opt/pg/
vi docker-compose.yaml

Insert the following configuration into the docker-compose.yaml file:

yaml
services:
  pg-2:
    ports:
      - 5432:5432
    image: bitnamilegacy/postgresql-repmgr:17
    environment:
      POSTGRESQL_POSTGRES_PASSWORD: adminpassword
      POSTGRESQL_USERNAME: apio_core
      POSTGRESQL_PASSWORD: password123
      POSTGRESQL_DATABASE: apio_core
      POSTGRESQL_TIMEZONE: UTC
      REPMGR_PASSWORD: repmgrpassword
      REPMGR_PRIMARY_HOST: pg-0
      REPMGR_PARTNER_NODES: pg-0,pg-1,pg-2
      REPMGR_NODE_NAME: pg-2
      REPMGR_NODE_NETWORK_NAME: pg-2
      REPMGR_NODE_TYPE: witness
    volumes:
      - /data/db:/bitnami/postgresql
    restart: unless-stopped

INFO

Update all passwords and network parameters according to your organizational security standards.

Service Deployment ​

Once the configuration file has been created, deploy the services using Docker Compose:

shell
docker compose up -d

Verify that all containers are running successfully:

shell
docker compose ps

Setup on APIO Core Servers ​

Host Configuration ​

Add the IP addresses of database nodes to the /etc/hosts file to ensure proper hostname resolution:

shell
echo "a.b.c.d pg-0" >> /etc/hosts
echo "a.b.c.d pg-1" >> /etc/hosts

Docker Compose Setup ​

Append the following service configuration into the docker-compose.yaml file of Apio:

yaml
  db:
    image: bitnamilegacy/pgpool:4.6.3
    environment:
      PGPOOL_BACKEND_NODES: "0:pg-0:5432,1:pg-1:5432"
      PGPOOL_SR_CHECK_USER: apio_core
      PGPOOL_SR_CHECK_PASSWORD: password123
      PGPOOL_ENABLE_LDAP: no
      PGPOOL_POSTGRES_USERNAME: postgres
      PGPOOL_POSTGRES_PASSWORD: adminpassword
      PGPOOL_ADMIN_USERNAME: admin
      PGPOOL_ADMIN_PASSWORD: adminpassword
    restart: unless-stopped
    healthcheck:
      test: ["CMD", "/opt/bitnami/scripts/pgpool/healthcheck.sh"]
      interval: 10s
      timeout: 5s
      retries: 5