Setup PostgreSQL

Hardware Requirements

Part	Minimum	Recommendation
CPU	2 cores	4-8 cores
RAM	2GB	8GB+
Storage	20GB SSD	100GB+ SSD(NVMe)
OS	Ubuntu 22.04	Debian 12

Install and Setup

Single Server

Install

sudo apt update

sudo apt install postgresql postgresql-contrib -y

sudo systemctl start postgresql

sudo systemctl enable postgresql

sudo systemctl status postgresql

Setup

Enable Remote Access

sudo nano /etc/postgresql/16/main/postgresql.conf

listen_address = '*'

sudo nano /etc/postgresql/16/main/pg_hba.conf

host    all     all     0.0.0.0/0       md5

sudo systemctl restart postgresql

Create User and Database

sudo -u postgres psql

CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
CREATE DATABASE mydb OWNER myuser;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Cluster

Architecture

+------------------+
|   Client Apps    |
+------------------+
        |
        ▼
+-------------------------+
| VIP: 10.10.10.200       |  <---- Keepalived management VIP
| HAProxy 1 (10.10.10.10) |
| HAProxy 2 (10.10.10.11) |
+-------------------------+
        |
        ▼
+-------------------------------------+
|       PostgreSQL Cluster (Patroni)  |
+-------------------------------------+
| Primary  -> 10.10.10.100 (Master)   |
| Replica1 -> 10.10.10.101 (Follower) |
| Replica2 -> 10.10.10.102 (Follower) |
+-------------------------------------+

Setup for 3 node PostgreSQL

Install PostgreSQL

sudo apt update && sudo apt upgrade -y

sudo apt install -y postgresql postgresql-contrib python3-pip

Create User Postgresql

sudo -i -u postgres

createuser -s patroni

exit

Install Consul

wget https://releases.hashicorp.com/consul/1.15.0/consul_1.15.0_linux_amd64.zip

unzip consul_1.15.0_linux_amd64.zip

sudo mv consul /usr/local/bin/

Configuration Consul

sudo mkdir -p /etc/consul.d /var/lib/consul

sudo useradd -r -d /var/lib/consul -s /bin/false consul

sudo chown -R consul:consul /var/lib/consul

For pg-1

sudo nano /etc/consul.d/config.json

{
  "datacenter": "dc1",
  "node_name": "pg-1",
  "server": true,
  "bootstrap_expect": 3,
  "ui": true,
  "data_dir": "/var/lib/consul",
  "bind_addr": "10.10.10.100",
  "client_addr": "0.0.0.0",
  "retry_join": ["10.10.10.101", "10.10.10.102"]
}

sudo consul agent -config-dir=/etc/consul.d/ -bind=10.10.10.100

For pg-2

sudo nano /etc/consul.d/config.json

{
  "datacenter": "dc1",
  "node_name": "pg-2",
  "server": true,
  "bootstrap_expect": 3,
  "ui": true,
  "data_dir": "/var/lib/consul",
  "bind_addr": "10.10.10.101",
  "client_addr": "0.0.0.0",
  "retry_join": ["10.10.10.100", "10.10.10.102"]
}

sudo consul agent -config-dir=/etc/consul.d/ -bind=10.10.10.101

For pg-3

sudo nano /etc/consul.d/config.json

{
  "datacenter": "dc1",
  "node_name": "pg-3",
  "server": true,
  "bootstrap_expect": 3,
  "ui": true,
  "data_dir": "/var/lib/consul",
  "bind_addr": "10.10.10.102",
  "client_addr": "0.0.0.0",
  "retry_join": ["10.10.10.100", "10.10.10.101"]
}

sudo consul agent -config-dir=/etc/consul.d/ -bind=10.10.10.102

Create Systemd for Consul for each server

sudo nano /etc/systemd/system/consul.service

[Unit]
Description=Consul Server
After=network.target

[Service]
User=root
Group=root
ExecStart=/usr/local/bin/consul agent -config-dir=/etc/consul.d
ExecReload=/bin/kill --signal HUP $MAINPID
KillMode=process
Restart=on-failure
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target

Start Consul

sudo systemctl daemon-reload

sudo systemctl enable consul

sudo systemctl start consul

Check Consul

sudo systemctl status consul

consul members

Install Patroni

sudo apt update && sudo apt upgrade -y

sudo apt install -y python3-pip python3-psycopg2

pip3 install patroni[consul]

Create Folder and User for Patroni

sudo mkdir -p /etc/patroni /var/lib/patroni

sudo useradd -r -s /bin/false patroni

sudo chown -R patroni:patroni /var/lib/patroni

Configuration Patroni

For pg-1

sudo nano /etc/patroni/patroni.yml

scope: pg_cluster
namespace: /service/
name: pg-node1

# Backend DCS using Consul
consul:
  host: 10.10.10.100:8500
  register_service: true

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.10.10.100:8008

etcd: {}

postgresql:
  listen: 10.10.10.100:5432
  connect_address: 10.10.10.100:5432
  data_dir: /var/lib/patroni
  bin_dir: /usr/lib/postgresql/14/bin
  config_dir: /etc/postgresql/14/main
  authentication:
    superuser:
      username: postgres
      password: mypassword
    replication:
      username: replicator
      password: replpassword
  parameters:
    max_connections: 200
    shared_buffers: 256MB
    wal_level: replica
    synchronous_commit: "on"
    archive_mode: "on"
    archive_command: "cp %p /var/lib/patroni/archive/%f"

For pg-2

sudo nano /etc/patroni/patroni.yml

scope: pg_cluster
namespace: /service/
name: pg-node2

consul:
  host: 10.10.10.100:8500
  register_service: true

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.10.10.101:8008

etcd: {}

postgresql:
  listen: 10.10.10.101:5432
  connect_address: 10.10.10.101:5432
  data_dir: /var/lib/patroni
  bin_dir: /usr/lib/postgresql/14/bin
  config_dir: /etc/postgresql/14/main
  authentication:
    superuser:
      username: postgres
      password: mypassword
    replication:
      username: replicator
      password: replpassword
  parameters:
    max_connections: 200
    shared_buffers: 256MB
    wal_level: replica
    synchronous_commit: "on"
    archive_mode: "on"
    archive_command: "cp %p /var/lib/patroni/archive/%f"s

For pg-3

sudo nano /etc/patroni/patroni.yml

scope: pg_cluster
namespace: /service/
name: pg-node3

consul:
  host: 10.10.10.100:8500
  register_service: true

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.10.10.102:8008

etcd: {}

postgresql:
  listen: 10.10.10.102:5432
  connect_address: 10.10.10.102:5432
  data_dir: /var/lib/patroni
  bin_dir: /usr/lib/postgresql/14/bin
  config_dir: /etc/postgresql/14/main
  authentication:
    superuser:
      username: postgres
      password: mypassword
    replication:
      username: replicator
      password: replpassword
  parameters:
    max_connections: 200
    shared_buffers: 256MB
    wal_level: replica
    synchronous_commit: "on"
    archive_mode: "on"
    archive_command: "cp %p /var/lib/patroni/archive/%f"

Create Systemd for Patroni foreach server

[Unit]
Description=Patroni PostgreSQL HA Cluster
After=network.target

[Service]
User=patroni
Group=patroni
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
Restart=on-failure
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target

Start Patroni Service

sudo systemctl daemon-reload

sudo systemctl enable patroni

sudo systemctl start patroni

Check Patroni Service

sudo systemctl status patroni

patronictl -c /etc/patroni/patroni.yml list

Install HAProxy

sudo apt update && sudo apt upgrade -y

sudo apt install -y haproxy

Configuration HAProxy

HAProxy 1 and HAProxy 2

sudo nano /etc/haproxy/haproxy.cfg

global
    log /dev/log local0
    maxconn 4000

defaults
    log global
    option redispatch
    retries 3
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms

# Frontend nhận kết nối PostgreSQL
frontend postgresql
    bind *:5432
    mode tcp
    default_backend patroni

# Backend chuyển hướng đến các node PostgreSQL trong Patroni Cluster
backend patroni
    mode tcp
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-node1 10.10.10.100:5432 check port 8008
    server pg-node2 10.10.10.101:5432 check port 8008
    server pg-node3 10.10.10.102:5432 check port 8008

Start HAProxy Service

sudo systemctl daemon-reload

sudo systemctl enable haproxy

sudo systemctl start haproxy

Check HAProxy Service

sudo systemctl status haproxy

Install Keepalived

sudo apt update && sudo apt upgrade -y

sudo apt install -y keepalived

Configuration Keepalived

HAProxy 1 is MASTER

sudo nano /etc/keepalived/keepalived.conf

vrrp_instance VI_1 {
    state MASTER
    interface eth0  # Change actual network interface
    virtual_router_id 51
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass haproxy
    }
    virtual_ipaddress {
        10.10.10.200
    }
}

HAProxy 2 is BACKUP

sudo nano /etc/keepalived/keepalived.conf

vrrp_instance VI_1 {
    state BACKUP
    interface eth0  # Change actual network interface
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass haproxy
    }
    virtual_ipaddress {
        10.10.10.200
    }
}

Start Keepalived Service

sudo systemctl daemon-reload

sudo systemctl enable keepalived

sudo systemctl start keepalived

Check Keepalived Serivce

sudo systemctl status keepalived

ip a | grep 10.10.10.200