Senin, 23 Desember 2024

Setup High Availability PostgreSQL Cluster - Patroni Cluster

 



Persiapan

Menggunakan 3 vm atau host :
  1. VM1 : hostname: postgresql-1, OS: Rocky Linux 9.2 => ip : 192.168.56.11
  2. VM2 : hostname: postgresql-2, OS: Rocky Linux 9.2 => ip : 192.168.56.12
  3. VM3 : hostname: postgresql-3, OS: Rocky Linux 9.2 => ip : 192.168.56.13
Service atau Aplikasi :
  1. Postgresql-16
  2. Etcd
  3. Keepalive
  4. Haproxy
  5. Patroni
Pembagian IP :
Hostname dan ip berikut bisa ditambahkan di /etc/hosts
  1. 192.168.56.11 postgres-1 etcd-1
  2. 192.168.56.12 postgres-2 etcd-2
  3. 192.168.56.13 postgres-3 etcd-3
  4. 192.168.56.5   ip-vip                         #merupakan ip virtual yang akan diconfig di keepalive
Directory
Disetiap vm kita buatkan directory untuk menyimpan config maupun file yg dibutuhkan, berikut list directory :
  1. mkdir -p /opt/apps/certificate
  2. mkdir -p /opt/apps/etcd
  3. mkdir -p /opt/apps/haproxy
  4. mkdir -p /opt/apps/keepalive
  5. mkdir -p /opt/apps/patroni/log
  6. mkdir -p /var/lib/pgsql/etcd/
Open Port
  1. sudo firewall-cmd --permanent --zone=public --add-port=5432/tcp
  2. sudo firewall-cmd --permanent --zone=public --add-port=2380/tcp
  3. sudo firewall-cmd --permanent --zone=public --add-port=2379/tcp
  4. sudo firewall-cmd --permanent --zone=public --add-port=2376/tcp
  5. sudo firewall-cmd --permanent --zone=public --add-port=8008/tcp
  6. sudo firewall-cmd --permanent --zone=public --add-port=5431/tcp
  7. sudo systemctl reload firewalld && firewall-cmd --list-ports

Installasi Postgresql-16

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install postgresql16-server postgresql16-contrib postgresql16-libs pg_cron_16 pg_checksums_16
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
    NB : installasi postgres cukup sampai initdb saja, N VBkarena kita tidak akan running service postgres

Install ETCD

Generate certificate untuk setup etcd.
    Download dan install cfssl

curl -OL https://pkg.cfssl.org/R1.2/cfssl_linux-amd64 -OL https://pkg.cfssl.org/R1.2/cfssljson_linux-amd64

chmod +x cfssl_linux-amd64 cfssljson_linux-amd64

sudo mv cfssl_linux-amd64 /usr/local/bin/cfssl

sudo mv cfssljson_linux-amd64 /usr/local/bin/cfssljson


    #nano /opt/apps/certificate/ca-config.json

{

  "signing": {

    "default": {

      "expiry": "876000h"

    },

    "profiles": {

      "postgres": {

        "usages": ["signing", "key encipherment", "server auth", "client auth"],

        "expiry": "87600h"

      }

    }

  }

}

    nb : cukup dibuat di salah satu host

    #nano /opt/apps/certificate/ca-csr.json

{

  "CN": "Postgres",

  "key": {

    "algo": "rsa",

    "size": 2048

  },

  "names": [

  {

    "C": "IN",

    "L": "Yogyakarta",

    "O": "Postgres",

    "OU": "CA",

    "ST": "Yoni"

  }

 ]

}

    nb : cukup dibuat di salah satu host

cp -ar ca-csr.json postgres-csr.json

 

#Generate dengan perintah berikut :

cfssl gencert -ca=ca.pem -ca-key=ca-key.pem -config=ca-config.json -hostname=192.168.56.11,192.168.56.12,192.168.56.13,192.168.56.5,127.0.0.1,postgres-1,postgres-2,postgres-3,etcd-1,etcd-2,etcd-3 -profile=postgres postgres-csr.json | cfssljson -bare postgres

 

#Copy kesemua server

scp /opt/apps/certificate/* postgres-2:/opt/apps/certificate/

scp /opt/apps/certificate/* postgres-3:/opt/apps/certificate/



Create config etcd
    #nano /opt/apps/etcd/etcd.conf

ETCD_NAME=etcd-1

ETCD_DATA_DIR="/var/lib/pgsql/etcd/"

ETCD_LISTEN_PEER_URLS="https://192.168.56.11:2380"

ETCD_LISTEN_CLIENT_URLS="https://192.168.56.11:2379,https://127.0.0.1:2379"

 

# [cluster]

ETCD_INITIAL_ADVERTISE_PEER_URLS="https://192.168.56.11:2380"

ETCD_ADVERTISE_CLIENT_URLS="https://192.168.56.11:2379"

ETCD_INITIAL_CLUSTER="etcd-1=https://192.168.56.11:2380,etcd-2=https://192.168.56.12:2380,etcd-3=https://192.168.56.13:2380"

ETCD_INITIAL_CLUSTER_STATE="new"

ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-controller"

ETCD_QUOTA_BACKEND_BYTES="8589934592"

#ETCD_ENABLE_V2="true"

 

#[security]

ETCD_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_PEER_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_PEER_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_CLIENT_CERT_AUTH="true"

    nb : config etcd host postgres-1

ETCD_NAME=etcd-2

ETCD_DATA_DIR="/var/lib/pgsql/etcd/"

ETCD_LISTEN_PEER_URLS="https://192.168.56.12:2380"

ETCD_LISTEN_CLIENT_URLS="https://192.168.56.12:2379,https://127.0.0.1:2379"

 

# [cluster]

ETCD_INITIAL_ADVERTISE_PEER_URLS="https://192.168.56.12:2380"

ETCD_ADVERTISE_CLIENT_URLS="https://192.168.56.12:2379"

ETCD_INITIAL_CLUSTER="etcd-1=https://192.168.56.11:2380,etcd-2=https://192.168.56.12:2380,etcd-3=https://192.168.56.13:2380"

ETCD_INITIAL_CLUSTER_STATE="new"

ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-controller"

ETCD_QUOTA_BACKEND_BYTES="8589934592"

#ETCD_ENABLE_V2="true"

 

#[security]

ETCD_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_PEER_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_PEER_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_CLIENT_CERT_AUTH="true"

    nb : config etcd host postgres-2

ETCD_NAME=etcd-3

ETCD_DATA_DIR="/var/lib/pgsql/etcd/"

ETCD_LISTEN_PEER_URLS="https://192.168.56.13:2380"

ETCD_LISTEN_CLIENT_URLS="https://192.168.56.13:2379,https://127.0.0.1:2379"

 

# [cluster]

ETCD_INITIAL_ADVERTISE_PEER_URLS="https://192.168.56.13:2380"

ETCD_ADVERTISE_CLIENT_URLS="https://192.168.56.13:2379"

ETCD_INITIAL_CLUSTER="etcd-1=https://192.168.56.11:2380,etcd-2=https://192.168.56.12:2380,etcd-3=https://192.168.56.13:2380"

ETCD_INITIAL_CLUSTER_STATE="new"

ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-controller"

ETCD_QUOTA_BACKEND_BYTES="8589934592"

#ETCD_ENABLE_V2="true"

 

#[security]

ETCD_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_PEER_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_PEER_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_CLIENT_CERT_AUTH="true"

    nb : config etcd host postgres-3

Create systemd etcd.
    # nano /etc/systemd/system/etcd.service

[Unit]                                                                                                                                                                                                            

Description=ETCD key-value store                                                                                                                                                            

Documentation=https://github.com/etcd-io/etcd 

After=network.target                                                                                                     

                                                                                                                                                                           

[Service] 

Type=notify 

User=etcd 

Group=etcd 

EnvironmentFile=/opt/apps/etcd/etcd.conf 

ExecStart=/usr/local/bin/etcd 

Restart=always 

RestartSec=10s 

LimitNOFILE=40000 


[Install]                                                                                                                                                                                                         

WantedBy=multi-user.target


Install etcd, metode yang dilakukan download binary etcd.

wget https://github.com/etcd-io/etcd/releases/download/v3.5.17/etcd-v3.5.17-linux-amd64.tar.gz

tar zxvf etcd-v3.5.17-linux-amd64.tar.gz

cp -ar etcd-v3.5.17-linux-amd64/etcd* /usr/local/bin/

groupadd -f -g 1501 etcd

useradd -c "etcd user" -d /opt/apps/etcd -s /bin/false -g etcd -u 1501 etcd

chown -R etcd:etcd /var/lib/pgsql/etcd/ /opt/apps/etcd/ /opt/apps/certificate/

chmod 666 /opt/apps/certificate/

systemctl daemon-reload

systemctl start etcd

    nb : dilakukan disemua host

Pengecekan cluster etcd.

etcdctl endpoint status --write-out=table --endpoints=192.168.56.11:2379,192.168.56.13:2379,192.168.56.13:2379 --cacert=/opt/apps/certificate/ca.pem --cert=/opt/apps/certificate/postgres.pem --key=/opt/apps/certificate/postgres-key.pem


hasil pengecekan cluster etcd

Install Keepalived

Install keepalived

sudo dnf install -y keepalived


Create config keepalived.
    # nano /opt/apps/keepalive/keepalived.conf

! Configuration File for keepalived

global_defs {

}

 

vrrp_script chk_haproxy {

    script "killall -0 haproxy" # check the haproxy process

    interval 2                  # every 2 seconds

    weight 2                    # add 2 points if OK

}

 

vrrp_instance VI_1 {

    interface enp0s3             # interface to monitor

    state MASTER                # MASTER on haproxy1, BACKUP

#    on haproxy2

    virtual_router_id 2         # Set to last digit of IP

    priority 101                # 101 on haproxy1, 100 on

#    haproxy2 etc

    authentication {

        auth_type PASS

        auth_pass Pa55w@rd!

    }

    virtual_ipaddress {

        192.168.56.5             # virtual ip address

    }

    track_script {

        chk_haproxy

    }

}

    nb : config keepalived di host postgres-1

! Configuration File for keepalived

global_defs {

}

 

vrrp_script chk_haproxy {

    script "killall -0 haproxy" # check the haproxy process

    interval 2                  # every 2 seconds

    weight 2                    # add 2 points if OK

}

 

vrrp_instance VI_1 {

    interface enp0s3             # interface to monitor

    state BACKUP                # MASTER on haproxy1, BACKUP

#    on haproxy2

    virtual_router_id 2         # Set to last digit of IP

    priority 100                # 101 on haproxy1, 100 on

#    haproxy2 etc

    authentication {

        auth_type PASS

        auth_pass Pa55w@rd!

    }

    virtual_ipaddress {

        192.168.56.5             # virtual ip address

    }

    track_script {

        chk_haproxy

    }

}

    nb : config keepalived di host postgres-2

! Configuration File for keepalived

global_defs {

}

 

vrrp_script chk_haproxy {

    script "killall -0 haproxy" # check the haproxy process

    interval 2                  # every 2 seconds

    weight 2                    # add 2 points if OK

}

 

vrrp_instance VI_1 {

    interface enp0s3             # interface to monitor

    state BACKUP                # MASTER on haproxy1, BACKUP

#    on haproxy2

    virtual_router_id 2         # Set to last digit of IP

    priority 100                # 101 on haproxy1, 100 on

#    haproxy2 etc

    authentication {

        auth_type PASS

        auth_pass Pa55w@rd!

    }

    virtual_ipaddress {

        192.168.56.5             # virtual ip address

    }

    track_script {

        chk_haproxy

    }

}

    nb : config keepalived di host postgres-3

Create symlink config keepalived

ln -s /opt/apps/keepalived/keepalived.conf /etc/keepalived/

    nb : dilakukan disemua host

Create systemd keepalived.
    #nano /etc/systemd/system/keepalived.service

[Unit]

Description=LVS and VRRP High Availability Monitor

After=network-online.target syslog.target

Wants=network-online.target

 

[Service]

Type=forking

PIDFile=/run/keepalived.pid

KillMode=process

EnvironmentFile=-/etc/sysconfig/keepalived

ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS

ExecReload=/bin/kill -HUP $MAINPID

 

[Install]

WantedBy=multi-user.target

    nb : dilakukan disemua host

Reload systemd dan start keepalived dilakukan disemua host.

systemctl daemon-reload

systemctl start keepalived


Pengecekan ip address apakah ip virtual (192.168.56.5) sudah ke bind, cek disemua host.

Install Patroni

Install package dan patroni.

dnf install python3-pip python3-psycopg2 python3-pip python3 gcc python3-devel python3-etcd -y

 

pip3 install --upgrade setuptools patroni patroni[etcd]


Create config patroni.
    # nano /opt/apps/patroni/patroni.yml

scope: postgres

namespace: /db/postgres16/

name: postgres-1

 

log:

    traceback_level: INFO

    level: INFO

    dir: /opt/apps/patroni/log/

    file_num: 5

    file_size: 104857600

 

restapi:

    listen: postgres-1:8008

    connect_address: postgres-1:8008

 

etcd3:

    hosts: 192.168.56.11:2379,192.168.56.12:2379,192.168.56.13:2379

    protocol: https

    cacert: /opt/apps/certificate/ca.pem

    cert: /opt/apps/certificate/postgres.pem

    key: /opt/apps/certificate/postgres-key.pem

 

bootstrap:

  dcs:

    ttl: 30

    loop_wait: 10

    retry_timeout: 10

    maximum_lag_on_failover: 1048576

    master_start_timeout: 300

    postgresql:

        use_pg_rewind: true

        parameters:

            max_connections: 200

            max_worker_processes: 32

 

postgresql:

    listen: 127.0.0.1,192.168.56.11:5432

    bin_dir: /usr/pgsql-16/bin

    connect_address: 192.168.56.11:5432

    data_dir: /var/lib/pgsql/16/data

    pgpass: /var/lib/pgsql/.pgpass

    authentication:

        replication:

            username: replicate

            password: password123456

        superuser:

            username: patroni

            password: password123456

        parameters:

            unix_socket_directories: '.'

 

tags:

    nofailover: false

    noloadbalance: false

    clonefrom: false

    nosync: false

    nb : config patroni-1

    # nano /opt/apps/patroni/patroni.yml

scope: postgres

namespace: /db/postgres16/

name: postgres-2

 

log:

    traceback_level: INFO

    level: INFO

    dir: /opt/apps/patroni/log/

    file_num: 5

    file_size: 104857600

 

restapi:

    listen: postgres-2:8008

    connect_address: postgres-2:8008

 

etcd3:

    hosts: postgres-1:2379,postgres-2:2379,postgres-3:2379

    protocol: https

    cacert: /opt/apps/certificate/ca.pem

    cert: /opt/apps/certificate/postgres.pem

    key: /opt/apps/certificate/postgres-key.pem

 

bootstrap:

  dcs:

    ttl: 30

    loop_wait: 10

    retry_timeout: 10

    maximum_lag_on_failover: 1048576

    master_start_timeout: 300

    postgresql:

        use_pg_rewind: true

        parameters:

            max_connections: 200

            max_worker_processes: 32

 

postgresql:

    listen: 127.0.0.1,192.168.56.12:5432

    bin_dir: /usr/pgsql-16/bin

    connect_address: 192.168.56.12:5432

    data_dir: /var/lib/pgsql/16/data

    pgpass: /var/lib/pgsql/.pgpass

    authentication:

        replication:

            username: replicate

            password: password123456

        superuser:

            username: patroni

            password: password123456

        parameters:

            unix_socket_directories: '.'

 

tags:

    nofailover: false

    noloadbalance: false

    clonefrom: false

    nosync: false

    nb : config patroni-2

    # nano /opt/apps/patroni/patroni.yml

scope: postgres

namespace: /db/postgres16/

name: postgres-3

 

log:

    traceback_level: INFO

    level: INFO

    dir: /opt/apps/patroni/log/

    file_num: 5

    file_size: 104857600

 

restapi:

    listen: postgres-3:8008

    connect_address: postgres-3:8008

 

etcd3:

    hosts: postgres-1:2379,postgres-2:2379,postgres-3:2379

    protocol: https

    cacert: /opt/apps/certificate/ca.pem

    cert: /opt/apps/certificate/postgres.pem

    key: /opt/apps/certificate/postgres-key.pem

 

bootstrap:

  dcs:

    ttl: 30

    loop_wait: 10

    retry_timeout: 10

    maximum_lag_on_failover: 1048576

    master_start_timeout: 300

    postgresql:

        use_pg_rewind: true

        parameters:

            max_connections: 200

            max_worker_processes: 32

 

postgresql:

    listen: 127.0.0.1,192.168.56.13:5432

    bin_dir: /usr/pgsql-16/bin

    connect_address: 192.168.56.13:5432

    data_dir: /var/lib/pgsql/16/data

    pgpass: /var/lib/pgsql/.pgpass

    authentication:

        replication:

            username: replicate

            password: password123456

        superuser:

            username: patroni

            password: password123456

        parameters:

            unix_socket_directories: '.'

 

tags:

    nofailover: false

    noloadbalance: false

    clonefrom: false

    nosync: false

    nb : config patroni-2

Create systemd patroni.
    #nano /etc/systemd/system/patroni.service

[Unit]

Description=Runners to orchestrate a high-availability PostgreSQL

After=syslog.target network.target

 

[Service]

Type=simple

User=postgres

Group=postgres

ExecStart=/usr/local/bin/patroni /opt/apps/patroni/patroni.yml

KillMode=process

TimeoutSec=30

Restart=no

 

[Install]

WantedBy=multi-user.target

    nb : create semua host

Start patroni host postgres-1

#Nyalakan terlebih dahulu service postgres di host postgres-1, kita perlu membuat user database untuk patroni dan replikasi

systemctl start postgresql-16

su - postgres

psql  # masuk database postgres

create role replicate with REPLICATION LOGIN encrypted password 'password123456';

create role patroni WITH SUPERUSER LOGIN ENCRYPTED PASSWORD 'password123456';

exit

 

#Tambahkan config di pg_hba

nano /var/lib/pgsql/16/data/pg_hba.conf

host    all             all             192.168.56.0/24         md5

host    replication     replicate       192.168.56.0/24         md5

 

#Edit config postgres agar dapat diakses dari host lain, enable dan ubah dari localhost menjadi *

nano /var/lib/pgsql/16/data/postgresql.conf

listen_addresses = '*'

 

#Start patroni di postgres-1

systemctl stop postgresql-16

systemctl daemon-reload

systemctl start patroni

systemctl enable patroni


Start patroni host postgres-2

#hapus data dir postgres pada host postgres-2

rm -rf /var/lib/pgsql/16/data

 

#Start patroni di postgres-2

systemctl daemon-reload

systemctl start patroni

systemctl enable patroni


Start patroni host postgres-3

#hapus data dir postgres pada host postgres-3

rm -rf /var/lib/pgsql/16/data

 

#Start patroni di postgres-3

systemctl daemon-reload

systemctl start patroni

systemctl enable patroni


Pengecekan cluster patroni

patronictl -c /opt/apps/patroni/patroni.yml list















Install haproxy.

Install package dan haproxy disemua host.

#install package

dnf install -y curl gcc openssl-devel libnl3-devel net-snmp-devel

#install haproxy
wget https://www.haproxy.org/download/3.1/src haproxy-3.1.1.tar.gz

tar zxvf haproxy-3.1.1.tar.gz

cd haproxy-3.1.1

make TARGET=linux-glibc USE_OPENSSL=1 USE_ZLIB=1 USE_CRYPT_H=1 USE_LIBCRYPT=1 USE_SYSTEMD=1

make install

groupadd -f -g 1502 haproxy

useradd -c "haproxy user" -d /opt/apps/haproxy/ -s /bin/false -g haproxy -u 1502 haproxy


Create config haproxy disemua host.
    #nano /opt/apps/haproxy/haproxy.cfg

global

    user    haproxy

    group   haproxy

    pidfile /var/run/haproxy.pid

    stats   socket /var/run/haproxy.stats

    maxconn 20480

 

defaults

    mode tcp

    log global

    option                  dontlognull

    option                  redispatch

    retries 2

    timeout client 10m

    timeout connect 4s

    timeout server 10m

    timeout check 5s

 

listen production

    bind 192.168.56.5:5432

    option httpchk OPTIONS/master

    http-check expect status 200

    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

    server db-postgresql-1 192.168.56.11:5432 maxconn 1280 check port 8008

    server db-postgresql-2 192.168.56.12:5432 maxconn 1280 check port 8008

    server db-postgresql-3 192.168.56.13:5432 maxconn 1280 check port 8008

 

listen standby

    bind 192.168.56.5:5431

    option httpchk OPTIONS/replica

    http-check expect status 200

    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

    server db-postgresql-1 192.168.56.11:5432 maxconn 1280 check port 8008

    server db-postgresql-2 192.168.56.12:5432 maxconn 1280 check port 8008

    server db-postgresql-3 192.168.56.13:5432 maxconn 1280 check port 8008


Create systemd haproxy disemua host.
    #nano /etc/systemd/system/haproxy.service

[Unit]

Description=HAProxy Load Balancer

After=network-online.target

Wants=network-online.target

 

[Service]

EnvironmentFile=-/etc/sysconfig/haproxy

Environment="CONFIG=/opt/apps/haproxy/haproxy.cfg" "PIDFILE=/run/haproxy.pid"

ExecStartPre=/bin/sleep 4

ExecStartPre=/usr/local/sbin/haproxy -f $CONFIG -c -q $OPTIONS

ExecStart=/usr/local/sbin/haproxy -Ws -f $CONFIG -p $PIDFILE $OPTIONS

ExecReload=/usr/local/sbin/haproxy -f $CONFIG -c -q $OPTIONS

ExecReload=/bin/kill -USR2 $MAINPID

KillMode=mixed

SuccessExitStatus=143

Type=notify

 

[Install]

WantedBy=multi-user.target


Start haproxy semua host.


systemctl daemon-reload

systemctl start haproxy

systemctl enable haproxy



Testing login postgres menggunakan ip floating atau ip virtual (192.168.56.5).
Coba login postgres sebagai write-read, dan test create database, harusnya berhasil create database.

psql -Upatroni -h192.168.56.5 -p5432 -d postgres



Coba login postgres sebagai read, dan test create database, harusnya gagal create database.

psql -Upatroni -h192.168.56.5 -p5431 -d postgres



di
Label: , , , , ,

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)