High Available Postgres by Patroni

Install Postges trên tất cả các node

Tạo các user trên các node
su postgres /opt/postgres/bin/psql postgres=# CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'x0hDfZv27'; postgres=# CREATE USER postgres WITH SUPERUSER ENCRYPTED PASSWORD 'njk16uAeFNl';

Install watchdog
sudo vi /etc/watchdog.conf ## active dòng này lên, bỏ comment đi: watchdog-device = /dev/watchdog ## thực hiện chuỗi lệnh bên dưới để active software watchdog

sudo mknod /dev/watchdog c 10 130 cat <<EOF >/etc/modules-load.d/softdog.conf softdog EOF

sudo modprobe softdog sudo chown postgres /dev/watchdog systemctl restart watchdog

Install etcd

apt install etcd -y
apt install etcd-server etcd-client -y

Cấu hình /etc/default/etcd trên 3 node

ETCD_NAME="NODE01"
ETCD_DATA_DIR="/var/lib/etcd/node1"
ETCD_LISTEN_PEER_URLS="http://10.10.2.10:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.10.2.10:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.10.2.10:2380,http://127.0.0.1:2380"
ETCD_INITIAL_CLUSTER="node1=http://10.10.2.10:2380,node2=http://10.10.2.11:2380,node2=http://10.10.2.12:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-pgsql"
ETCD_ADVERTISE_CLIENT_URLS="http://10.10.2.10:2379"
ETCD_ENABLE_V2="true"

ETCD_NAME="NODE02"
ETCD_DATA_DIR="/var/lib/etcd/node2"
ETCD_LISTEN_PEER_URLS="http://10.10.2.11:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.10.2.11:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.10.2.11:2380,http://127.0.0.1:2380"
ETCD_INITIAL_CLUSTER="node1=http://10.10.2.10:2380,node2=http://10.10.2.11:2380,node2=http://10.10.2.12:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-pgsql"
ETCD_ADVERTISE_CLIENT_URLS="http://10.10.2.11:2379"
ETCD_ENABLE_V2="true"

ETCD_NAME="NODE03"
ETCD_DATA_DIR="/var/lib/etcd/node3"
ETCD_LISTEN_PEER_URLS="http://10.10.2.12:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.10.2.12:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.10.2.12:2380,http://127.0.0.1:2380"
ETCD_INITIAL_CLUSTER="node1=http://10.10.2.10:2380,node2=http://10.10.2.11:2380,node2=http://10.10.2.12:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-pgsql"
ETCD_ADVERTISE_CLIENT_URLS="http://10.10.2.12:2379"
ETCD_ENABLE_V2="true"

Install patroni
apt install patroni

Trên node01
mkdir -p /etc/patroni nano /etc/patroni/patroni.yml

scope: postgres
namespace: /db/
name: pg01
restapi:
    listen: 10.10.2.10:8008
    connect_address: 10.10.2.10:8008
etcd:
    host: 10.10.2.10:2379
bootstrap:
    dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
            use_pg_rewind: true
            parameters:
              max_connections: 1000
              shared_buffers: 4GB
              effective_cache_size: 12GB
              maintenance_work_mem: 2GB
              checkpoint_completion_target: 0.9
              wal_buffers: 16MB
              default_statistics_target: 500
              random_page_cost: 1.1
              effective_io_concurrency: 200
              work_mem: 524kB
              min_wal_size: 4GB
              max_wal_size: 8GB
              max_worker_processes: 8
              max_parallel_workers_per_gather: 4
              max_parallel_workers: 8
              dynamic_shared_memory_type: mmap 
              log_statement: 'all'
              log_duration: on
              log_min_duration_statement: '100ms'
              log_destination: 'csvlog'
              logging_collector: 'on'
              compute_query_id: 'on'
              log_directory: '/opt/db/postgres/pglog/'
              log_filename: 'postgresql-%Y-%m-%d_%H.log'
              log_truncate_on_rotation: on
              log_rotation_age: 1d                    
              log_rotation_size: 100MB
              log_timezone: 'Asia/Saigon'
              datestyle: 'iso, mdy'
              timezone: 'Asia/Saigon'
              lc_messages: 'en_US.UTF-8'
              lc_monetary: 'en_US.UTF-8'
              lc_numeric: 'en_US.UTF-8'
              lc_time: 'en_US.UTF-8'
              default_text_search_config: 'pg_catalog.english'
              shared_preload_libraries: pg_stat_statements
              pg_stat_statements.track: all       
    initdb:
    - encoding: UTF8
    - data-checksums
    - auth-host: md5
    - auth-local: trust
    #pg_hba:
    #- host replication replicator 127.0.0.1/32 md5
    #- host replication replicator 2.2.2.46/32 trust
    #- host replication replicator 2.2.2.115/32 trust
    #- host replication replicador all md5
    #- host replication replicador 0.0.0.0/0 md5
    #- host all postgres 127.0.0.1/32 trust
    #- host all postgres 2.2.2.46/32 trust
    #- host all postgres 2.2.2.115/32 trust
    #- host all postgres 0.0.0.0/0 md5
    #- host all all 127.0.0.1/32 trust
    #- host all all 0.0.0.0/0 md5
    users:
        admin:
            password: admin
            options:
                - createrole
                - createdb
postgresql:
    listen: 0.0.0.0:5432
    connect_address: 10.10.2.10:5432
    data_dir:  /opt/db/postgres/pgdata
    bin_dir:   /opt/db/postgres/bin
    pgpass: /tmp/pgpass
    authentication:
        replication:
            username: replicator
            password: x0hDfZv27
        superuser:
            username: postgres
            password: njk16uAeFNl
    parameters:
        unix_socket_directories: '.'
watchdog:
    mode: required
    device: /dev/watchdog
    safety_margin: 5
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

Trên node02
mkdir -p /etc/patroni nano /etc/patroni/patroni.yml

scope: postgres
namespace: /db/
name: pg02
restapi:
    listen: 10.10.2.11:8008
    connect_address: 10.10.2.11:8008
etcd:
    host: 10.10.2.11:2379
bootstrap:
    dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
            use_pg_rewind: true
            parameters:
              max_connections: 1000
              shared_buffers: 4GB
              effective_cache_size: 12GB
              maintenance_work_mem: 2GB
              checkpoint_completion_target: 0.9
              wal_buffers: 16MB
              default_statistics_target: 500
              random_page_cost: 1.1
              effective_io_concurrency: 200
              work_mem: 524kB
              min_wal_size: 4GB
              max_wal_size: 8GB
              max_worker_processes: 8
              max_parallel_workers_per_gather: 4
              max_parallel_workers: 8
              dynamic_shared_memory_type: mmap 
              log_statement: 'all'
              log_duration: on
              log_min_duration_statement: '100ms'
              log_destination: 'csvlog'
              logging_collector: 'on'
              compute_query_id: 'on'
              log_directory: '/opt/db/postgres/pglog/'
              log_filename: 'postgresql-%Y-%m-%d_%H.log'
              log_truncate_on_rotation: on
              log_rotation_age: 1d                    
              log_rotation_size: 100MB
              log_timezone: 'Asia/Saigon'
              datestyle: 'iso, mdy'
              timezone: 'Asia/Saigon'
              lc_messages: 'en_US.UTF-8'
              lc_monetary: 'en_US.UTF-8'
              lc_numeric: 'en_US.UTF-8'
              lc_time: 'en_US.UTF-8'
              default_text_search_config: 'pg_catalog.english'
              shared_preload_libraries: pg_stat_statements
              pg_stat_statements.track: all       
    initdb:
    - encoding: UTF8
    - data-checksums
    - auth-host: md5
    - auth-local: trust
    #pg_hba:
    #- host replication replicator 127.0.0.1/32 md5
    #- host replication replicator 2.2.2.46/32 trust
    #- host replication replicator 2.2.2.115/32 trust
    #- host replication replicador all md5
    #- host replication replicador 0.0.0.0/0 md5
    #- host all postgres 127.0.0.1/32 trust
    #- host all postgres 2.2.2.46/32 trust
    #- host all postgres 2.2.2.115/32 trust
    #- host all postgres 0.0.0.0/0 md5
    #- host all all 127.0.0.1/32 trust
    #- host all all 0.0.0.0/0 md5
    users:
        admin:
            password: admin
            options:
                - createrole
                - createdb
postgresql:
    listen: 0.0.0.0:5432
    connect_address: 10.10.2.11:5432
    data_dir:  /opt/db/postgres/pgdata
    bin_dir:   /opt/db/postgres/bin
    pgpass: /tmp/pgpass
    authentication:
        replication:
            username: replicator
            password: x0hDfZv27
        superuser:
            username: postgres
            password: njk16uAeFNl
    parameters:
        unix_socket_directories: '.'
watchdog:
    mode: required
    device: /dev/watchdog
    safety_margin: 5
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

mkdir -p /dev/data/patroni chown postgres:postgres /dev/data/patroni chmod 700 /dev/data/patroni

Create service patroni

cat <<EOF >/etc/systemd/system/patroni.service
[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target
Wants=network-online.target

[Service]
Type=simple
User=postgres
Group=postgres
PermissionsStartOnly=true
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
ExecReload=/bin/kill -HUP $MAINPID
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
KillMode=process
TimeoutSec=30
Restart=no

[Install]
WantedBy=multi-user.target
EOF

systemctl enable patroni --now

Install và config haproxy trên 2 nodes
apt update apt install haproxy -y
nano /etc/haproxy/haproxy.conf

global
    maxconn 100
defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s
listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /
listen PSQL_MASTER_5000_rw
        bind *:5000
        option httpchk
        http-check expect status 200
        default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
        server postgresql_2.10_5432 10.10.2.10:5432 maxconn 800 check port 8008
        server postgresql_2.11_5432 10.10.2.11:5432 maxconn 800 check port 8008
# Connections to port 5listen PSQL_MASTER_5001_ro
listen PSQL_5001_ro
        bind *:5001
        option httpchk
        http-check expect !status 200
        default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
        server postgresql_2.45_5432 10.10.2.10:5432 maxconn 800 check port 8008
        server postgresql_2.46_5432 10.10.2.11:5432 maxconn 800 check port 8008

systemctl start haproxy systemctl status haproxy

Install và config keepalived trên 2 nodes
apt update apt install keepalived -y
nano /etc/keepalived/keepalived.conf

NODE01
vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 2
}
vrrp_instance VI_1 {
    interface eth0
    virtual_router_id 51
    state MASTER
    priority 102
    virtual_ipaddress {
      10.10.2.15
  }
  track_script {
    chk_haproxy
  }
}

NODE02
vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 2
}
vrrp_instance VI_1 {
    interface eth0
    virtual_router_id 51
    state BACKUP
    priority 101
    virtual_ipaddress {
      10.10.2.15
  }
  track_script {
    chk_haproxy
  }
}

systemctl start keepalived systemctl status keepalived

Kiểm tra hệ thống
etcd
etcdctl member list

438941bcea951af5, started, node1, http://10.10.2.10:2380, http://10.10.2.10:2379, false 6e6ad8e4eb4ca5e6, started, node2, http://10.10.2.11:2380, http://10.10.2.11:2379, false fa858d35ae3aabd4, started, node3, http://10.10.2.12:2380, http://10.10.2.12:2379, false

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

+ Cluster: postgres (7432164371019840658) -----+-----+-----------+
| Member    | Host       |  Role   | State     |  TL | Lag in MB |
+-----------+------------+---------+-----------+-----+-----------+
|    pg01   | 10.10.2.10 | Replica | streaming | 687 |         0 |
|    pg02   | 10.10.2.11 | Leader  | running   | 687 |           |
+-----------+------------+---------+-----------+-----+-----------+