Introduction

Setup standalone PostgreSQL memiliki risiko Single Point of Failure (SPOF) di mana downtime pada node utama menghentikan seluruh transaksi aplikasi.

Untuk mengatasi limitasi tersebut, arsitektur clustering modern memadukan empat komponen inti:

PostgreSQL: Mesin basis data utama yang berjalan dengan replikasi streaming fisik (physical streaming replication).
etcd: Distributed Consensus Store berbasis algoritma Raft yang bertindak sebagai single source of truth untuk status kluster, leader election, dan health locking.
Patroni: Cluster manager berbasis Python yang memantau PostgreSQL, mengelola replikasi, dan mengeksekusi failover/switchover otomatis secara aman tanpa risiko split-brain.
HAProxy: Reverse proxy dan load balancer yang merutekan traffic Read/Write ke node Primary dan traffic Read-Only ke Replicas menggunakan health check berbasis HTTP ke Patroni REST API.

Configuration

#Topology

## Install & Configuration ETCD

#Step1: Download package & Install package (ALL NODES)

Untuk mengecek versi terbaru bisa dilihat dari link ini https://github.com/etcd-io/etcd

VER=`curl -s https://api.github.com/repos/etcd-io/etcd/releases/latest | grep -Po '"tag_name": "\K.*?(?=")'`
wget https://github.com/etcd-io/etcd/releases/download/$VER/etcd-$VER-linux-amd64.tar.gz
tar xvf etcd-$VER-linux-amd64.tar.gz
cd etcd-$VER-linux-amd64
sudo mv etcd etcdctl etcdutl /usr/local/bin/
sudo useradd --system --home /var/lib/etcd --shell /bin/false etcd
sudo mkdir -p /var/lib/etcd
sudo chown -R etcd:etcd /var/lib/etcd

#Step2: Install dependency untuk etcd

sudo apt install -y python3-etcd3 python3-etcd

#Step3: Membuat File Service & Allow UFW Firewall

Node1 (psql-01)

Membuat service baru dengan nama file/etc/systemd/system/etcd.service

[Unit]
Description=etcd
Documentation=https://github.com/etcd-io/etcd
After=network.target

[Service]
User=etcd
Type=notify
ExecStart=/usr/local/bin/etcd \
  --name etcd-psql-01 \                                      # Edit here
  --data-dir /var/lib/etcd \
  --listen-client-urls http://0.0.0.0:2379 \
  --advertise-client-urls http://10.100.19.71:2379 \         # Edit here
  --listen-peer-urls http://0.0.0.0:2380 \
  --initial-advertise-peer-urls http://10.100.19.71:2380 \
  --initial-cluster "etcd-psql-01=http://10.100.19.71:2380,etcd-psql-02=http://10.100.19.72:2380,etcd-psql-03=http://10.100.19.73:2380" \                          # Edit here
  --initial-cluster-token etcd-cluster \
  --initial-cluster-state new

Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target

Menambahkan konfigurasi untuk allow Firewall (UFW)

sudo ufw allow from 10.100.19.71 to any port 2380 proto tcp
sudo ufw allow from 10.100.19.72 to any port 2380 proto tcp
sudo ufw allow from 10.100.19.73 to any port 2380 proto tcp
sudo ufw allow from 10.100.19.0/24 to any port 2379 proto tcp

Node2 (psql-02)

Membuat service baru dengan nama file /etc/systemd/system/etcd.service

[Unit]
Description=etcd
Documentation=https://github.com/etcd-io/etcd
After=network.target

[Service]
User=etcd
Type=notify
ExecStart=/usr/local/bin/etcd \
  --name etcd-psql-02 \                               # Edit here
  --data-dir /var/lib/etcd \
  --listen-client-urls http://0.0.0.0:2379 \
  --advertise-client-urls http://10.100.19.72:2379 \  # Edit here
  --listen-peer-urls http://0.0.0.0:2380 \
  --initial-advertise-peer-urls http://10.100.19.72:2380 \ #Edit Here
  --initial-cluster "etcd-psql-01=http://10.100.19.71:2380,etcd-psql-02=http://10.100.19.72:2380,etcd-psql-03=http://10.100.19.73:2380" \                   #Edit here
  --initial-cluster-token etcd-cluster \
  --initial-cluster-state new

Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target

Menambahkan konfigurasi untuk allow Firewall (UFW)

sudo ufw allow from 10.100.19.71 to any port 2380 proto tcp
sudo ufw allow from 10.100.19.73 to any port 2380 proto tcp
sudo ufw allow from 10.100.19.73 to any port 2380 proto tcp
sudo ufw allow from 10.100.19.0/24 to any port 2379 proto tcp

Node3 (psql-03)

Membuat service baru dengan nama file /etc/systemd/system/etcd.service

[Unit]
Description=etcd
Documentation=https://github.com/etcd-io/etcd
After=network.target

[Service]
User=etcd
Type=notify
ExecStart=/usr/local/bin/etcd \
  --name etcd-psql-03 \                                          # Edit here
  --data-dir /var/lib/etcd \
  --listen-client-urls http://0.0.0.0:2379 \
  --advertise-client-urls http://10.100.19.73:2379 \             # Edit here
  --listen-peer-urls http://0.0.0.0:2380 \
  --initial-advertise-peer-urls http://10.100.19.73:2380 \
  --initial-cluster "etcd-psql-01=http://10.100.19.71:2380,etcd-psql-02=http://10.100.19.72:2380,etcd-psql-03=http://10.100.19.73:2380" \                              # Edit here
  --initial-cluster-token etcd-cluster \
  --initial-cluster-state new

Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target

Menambahkan konfigurasi untuk allow Firewall (UFW)

sudo ufw allow from 10.100.19.71 to any port 2380 proto tcp
sudo ufw allow from 10.100.19.72 to any port 2380 proto tcp
sudo ufw allow from 10.100.19.73 to any port 2380 proto tcp
sudo ufw allow from 10.100.19.0/24 to any port 2379 proto tcp

#Step4: Restart & Enable service (ALL NODES)

sudo systemctl daemon-reload
sudo systemctl enable etcd
sudo systemctl restart etd

#Step5: Checking Service ETCD

Check member list

sudo etcdctl --endpoints=http://10.100.19.71:2379 \
  member list

Check Health

sudo etcdctl --endpoints=http://10.100.19.71:2379 \
  endpoint health

Check Endpoint Status

sudo etcdctl \
  --endpoints=http://10.100.19.71:2379,http://10.100.19.72:2379,http://10.100.19.73:2379 \
  endpoint status --write-out=table

#Instalation Postgresql & Patroni

#Step1: Update & Install repo untuk postgres (ALL NODES)

sudo apt update && sudo apt upgrade -y
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

#Step2: Install Postgresql 18 & patroni (ALL NODES)

sudo apt update -y && sudo apt upgrade -y
sudo apt install -y postgresql-18 patroni jq 
sudo systemctl disable --now postgresql

#Step3: Membuat lib baru untuk data postgresql (ALL NODES)

sudo mkdir -p /var/lib/postgresql/data
sudo chmod 700 /var/lib/postgresql/data/
sudo chown postgres:postgres /var/lib/postgresql/data

#Configure Patroni

#Step1: Membuat konfigurasi file & Allow Firewall (UFW)

Node1 (psql-01)

Membuat config baru dengan nama file /etc/patroni/config.yml

scope: postgresql-cluster
namespace: /service/
name: psql-01                         # Hostname Node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.100.19.71:8008  # IP Node1

etcd3:
  hosts: 10.100.19.71:2379,10.100.19.72:2379,10.100.19.73:2379   # ALL NODES
  protocol: http

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      parameters:
        ssl: 'off'
      pg_hba:
      - host postgres postgres 127.0.0.1/32 scram-sha-256
      - host replication replicator 127.0.0.1/32 scram-sha-256
      - host replication replicator 10.100.19.71/32 scram-sha-256  # Node1
      - host replication replicator 10.100.19.72/32 scram-sha-256  # Node2
      - host replication replicator 10.100.19.73/32 scram-sha-256  # Node3
  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.100.19.71:5432              # IP Node1
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/18/bin             # Binary directory for PostgreSQL 18
  authentication:
    superuser:
      username: postgres
      password: aSv5n9FTcBQzIaLPKsxUZBbCBNls58fa  # Superuser password - be sure to change
    replication:
      username: replicator
      password: Q2crBPkMab3TkqvLqbmqwb6OIiKNLwqJ  # Replication password - be sure to change
  parameters:
    max_connections: 100
    shared_buffers: 256MB
    logging_collector: 'on'
    log_directory: '/var/log/postgresql'
    log_filename: 'postgresql-%a.log'

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

Menambahkan konfigurasi untuk allow Firewall (UFW)

sudo ufw allow from 10.100.19.101 to any port 8008 proto tcp  
sudo ufw allow from 10.100.19.102 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.71 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.72 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.73 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.71 to any port 5432 proto tcp
sudo ufw allow from 10.100.19.72 to any port 5432 proto tcp
sudo ufw allow from 10.100.19.73 to any port 5432 proto tcp

Restart service patroni

sudo systemctl restart patroni
sudo systemctl enable patroni

Node2 (pgsql-02)

Membuat config baru dengan nama file /etc/patroni/config.yml

scope: postgresql-cluster
namespace: /service/
name: psql-02                       # Hostname Node2

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.100.19.72:8008  # IP Node2

etcd3:
  hosts: 10.100.19.71:2379,10.100.19.72:2379,10.100.19.73:2379   # ALL NODES
  protocol: http

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      parameters:
        ssl: 'off'
      pg_hba:
      - host postgres postgres 127.0.0.1/32 scram-sha-256
      - host replication replicator 127.0.0.1/32 scram-sha-256
      - host replication replicator 10.100.19.71/32 scram-sha-256  # Node1
      - host replication replicator 10.100.19.72/32 scram-sha-256  # Node2
      - host replication replicator 10.100.19.73/32 scram-sha-256  # Node3
  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.100.19.72:5432              # IP Node2
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/18/bin             # Binary directory for PostgreSQL 18
  authentication:
    superuser:
      username: postgres
      password: aSv5n9FTcBQzIaLPKsxUZBbCBNls58fa  # Superuser password - be sure to change
    replication:
      username: replicator
      password: Q2crBPkMab3TkqvLqbmqwb6OIiKNLwqJ  # Replication password - be sure to change
  parameters:
    max_connections: 100
    shared_buffers: 256MB
    logging_collector: 'on'
    log_directory: '/var/log/postgresql'
    log_filename: 'postgresql-%a.log'

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

Menambahkan konfigurasi untuk allow Firewall (UFW)

sudo ufw allow from 10.100.19.101 to any port 8008 proto tcp  
sudo ufw allow from 10.100.19.102 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.71 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.72 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.73 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.71 to any port 5432 proto tcp
sudo ufw allow from 10.100.19.72 to any port 5432 proto tcp
sudo ufw allow from 10.100.19.73 to any port 5432 proto tcp

Restart service patroni

sudo systemctl restart patroni
sudo systemctl enable patroni

Node3 (psql-03)

Membuat config baru dengan nama file /etc/patroni/config.yml

scope: postgresql-cluster
namespace: /service/
name: psql-03                       # Hostname Node3

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.100.19.73:8008  # IP Node3

etcd3:
  hosts: 10.100.19.71:2379,10.100.19.72:2379,10.100.19.73:2379   # ALL NODES
  protocol: http

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      parameters:
        ssl: 'off'
      pg_hba:
      - host postgres postgres 127.0.0.1/32 scram-sha-256
      - host replication replicator 127.0.0.1/32 scram-sha-256
      - host replication replicator 10.100.19.71/32 scram-sha-256  # Node1
      - host replication replicator 10.100.19.72/32 scram-sha-256  # Node2
      - host replication replicator 10.100.19.73/32 scram-sha-256  # Node3
  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.100.19.73:5432              # IP Node3
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/18/bin             # Binary directory for PostgreSQL 18
  authentication:
    superuser:
      username: postgres
      password: aSv5n9FTcBQzIaLPKsxUZBbCBNls58fa  # Superuser password - be sure to change
    replication:
      username: replicator
      password: Q2crBPkMab3TkqvLqbmqwb6OIiKNLwqJ  # Replication password - be sure to change
  parameters:
    max_connections: 100
    shared_buffers: 256MB
    logging_collector: 'on'
    log_directory: '/var/log/postgresql'
    log_filename: 'postgresql-%a.log'

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

Menambahkan konfigurasi untuk allow Firewall (UFW)

sudo ufw allow from 10.100.19.101 to any port 8008 proto tcp  
sudo ufw allow from 10.100.19.102 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.71 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.72 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.73 to any port 8008 proto tcp
sudo ufw allow from 10.100.19.71 to any port 5432 proto tcp
sudo ufw allow from 10.100.19.72 to any port 5432 proto tcp
sudo ufw allow from 10.100.19.73 to any port 5432 proto tcp
sudo ufw allow from 10.100.19.101 to any port 5432 proto tcp
sudo ufw allow from 10.100.19.102 to any port 5432 proto tcp

Restart service patroni

sudo systemctl restart patroni
sudo systemctl enable patroni

#Step2: Check Health

curl -s -k http://10.100.19.71:8008/primary | jq .
curl -s -k http://10.100.19.72:8008/primary | jq .
curl -s -k http://10.100.19.73:8008/primary | jq .

### Step3: Check config menggunakan patronictl

sudo patronictl -c /etc/patroni/config.yml show-config
sudo patronictl -c /etc/patroni/config.yml list

## Konfigurasi Haproxy

#Step1: Tambahkan pada baris konfigurasi Haproxy `/etc/haproxy/haproxy.cfd`

frontend postgres_frontend
    bind *:5432
    mode tcp
    default_backend postgres_backend

backend postgres_backend
    mode tcp
    option tcp-check
    option httpchk GET /primary  # patroni provides an endpoint to check node roles
    http-check expect status 200  # expect 200 for the primary node
    timeout connect 5s
    timeout server 30s
    server psql-01 10.100.19.71:5432 check port 8008
    server psql-02 10.100.19.72:5432 check port 8008
    server psql-03 10.100.19.73:5432 check port 8008

“

Untuk konfigurasi ini akan melakukan health check pada status primary aktif. Jika primary tidak aktif, maka nodes lainnya akan down

#Step2: Restart service haproxy & Check status pada haprxy

sudo systemctl restart haproxy

Kita bisa chcek menggunakan stats monitor

## Konfigurasi pg_hba di patroni

Tadi kita sudah membuat konfigurasi patroni, tetapi kita masih blm membuat allow list untuk all user agar bisa akses dari luar. Saatnya kita akan membuat konfigurasi pg_hba via patroni.

#Step1: Jalankan command patroni edit

sudo patronictl -c /etc/patroni/config.yml edit-config

#Step2: Tambahkan code dibawah

  - host all postgres 0.0.0.0/0 reject
  - host all all 0.0.0.0/0 scram-sha-256

### Step3: Reload patroni config

sudo patronictl -c /etc/patroni/config.yml reload postgresql-cluster

## Membuat Superuser

Superuser bawaan dari postgres itu hanya bisa diakses dari lokal server, yang artinya kita harus membuat superuser agar kita bisa remote

#Step1: Login dari primary nodes

psql -U postgres -h 127.0.0.1

#Step2: Create user dan kasih akses privileges

CREATE ROLE adminpostgres WITH SUPERUSER LOGIN PASSWORD 'password_rahasia';
\du

### Step3: Login menggunakan IP address dari haproxy menggunakan user yang sudah dibuat

Selesai

Introduction

Configuration

#Topology

#Step1: Download package & Install package (ALL NODES)

#Step2: Install dependency untuk etcd

#Step3: Membuat File Service & Allow UFW Firewall

#Step4: Restart & Enable service (ALL NODES)

#Step5: Checking Service ETCD

#Instalation Postgresql & Patroni

#Step1: Update & Install repo untuk postgres (ALL NODES)

#Step2: Install Postgresql 18 & patroni (ALL NODES)

#Step3: Membuat lib baru untuk data postgresql (ALL NODES)

#Configure Patroni

#Step1: Membuat konfigurasi file & Allow Firewall (UFW)

#Step2: Check Health

#Step1: Tambahkan pada baris konfigurasi Haproxy /etc/haproxy/haproxy.cfd

#Step2: Restart service haproxy & Check status pada haprxy

#Step1: Jalankan command patroni edit

#Step2: Tambahkan code dibawah

#Step1: Login dari primary nodes

#Step2: Create user dan kasih akses privileges

#Step1: Tambahkan pada baris konfigurasi Haproxy `/etc/haproxy/haproxy.cfd`