Triển khai PostgreSQL high availability với Patroni trên Ubuntu (Cực kỳ chi tiết)

Đây là tài liệu mà mình đã áp dụng vào thực tế công ty để có thể thiết lập cụm PostgreSQL high availability với Patroni trên Ubuntu (Debian). Thật sự nếu như bạn tìm kiếm các tài liệu trên internet thì không nhiều tài liệu chi tiết như này vì mình đã dành khá nhiều thời gian nghiên cứu từ tài liệu chính thức, tổng hợp các cấu hình hay để tối ưu hệ thống PostgreSql. Và hiện tại đang khởi chạy rất ổn định nên muốn chia sẻ tới mọi người.

Nếu bạn chưa từng làm hệ thống thì sẽ không dễ dàng hiểu nhưng mình sẽ cố gắng viết thật chi tiết để tất cả mọi người đều có thể áp dụng. Thật sự bạn nên lưu lại vì lúc cần thiết bạn sẽ thấy tài liệu này mình viết tâm huyết thế nào.

Điều kiện tiên quyết

Trong hướng dẫn này etcd chạy trên cùng các máy chủ cài Patroni và PostgreSQL và có thêm một máy chủ HAProxy duy nhất. Ngoài ra, etcd cũng có thể chạy trên các node khác nhau
Nếu etcd được triển khai trên cùng một máy chủ với Patroni và PostgreSQL, nên sử dụng hệ thống disk riêng (khác với ổ system) cho etcd và PostgreSQL vì lý do hiệu suất
Đối với hướng dẫn này sẽ sử dụng Ubuntu 22.04 là base OS

Mô hình

Node name	IP	Installed
postgres1	10.128.0.2	Patroni, PostgreSQL, etcd, HAProxy, Keepalived
postgres2	10.128.0.3	Patroni, PostgreSQL, etcd, HAProxy, Keepalived
postgres3	10.128.0.4	Patroni, PostgreSQL, etcd, HAProxy, Keepalived

Version

Package	Version
Patroni	3.3.0
PostgreSQL	15.7
etcd	3.3.25
HAProxy	2.4.24
Keepalived	2.2.4

Thiếp lập ban đầu

Trỏ host trên các node. Thêm nội dung dưới vào cuối file /etc/hosts/ trên ALL node

postgres1 10.128.0.2
postgres2 10.128.0.3
postgres3 10.128.0.4

Mở kết nối

Trong làm thực tế công ty sẽ sử dụng Iptables nên sẽ cần thêm bước này để mở đủ port cho công cụ giao tiếp. Còn có thể môi trường làm Lab bạn sẽ không có firewall này.

iptables -A INPUT -s 10.128.0.2,10.128.0.3,10.128.0.4 -m state --state NEW -m tcp -p tcp -m multiport --dports 5000,5001,7000,5432,2379,2380,8008 -m comment --comment "Postgresql"  -j ACCEPT
iptables -A OUTPUT -d 10.128.0.2,10.128.0.3,10.128.0.4 -m state --state NEW -m tcp -p tcp -m multiport --dports 5000,5001,7000,5432,2379,2380,8008 -m comment --comment "Postgresql"  -j ACCEPT

Cài đặt phần mềm

Chạy các lệnh sau trên node postgres1, postgres2 và postgres3

1. Cài đặt PostgreSQL 15

Bước 1: Cài đặt postgresql-common:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Bước 2: Định cấu hình Apt repository và cài đặt PostgreSQL 15

# Import the repository signing key:
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Update the package lists:
sudo apt update

# Install the latest version of PostgreSQL:
# If you want a specific version, use 'postgresql-16' or similar instead of 'postgresql'
sudo apt -y install postgresql-15

Quá trình cài đặt này đã tự động khởi tạo và khởi động cơ sở dữ liệu mặc định. Có thể kiểm tra trạng thái cơ sở dữ liệu bằng lệnh sau:

sudo systemctl status postgresql.service

Note: Ta nên stop postgresql đi và thiết lập cụm với một data directory khác

2. Cài đặt một số gói Python và gói bổ trợ để hỗ trợ Patroni và etcd

sudo apt install python3-pip python3-dev binutils -y

3. Cài đặt các packages etcd, Patroni, pgBaseRest

sudo apt install patroni \
etcd etcd-server etcd-client \
pgbackrest -y

4. Stop và vô hiệu hóa tất cả các dịch vụ đã cài đặt

sudo systemctl stop {etcd,patroni,postgresql}
systemctl disable {etcd,patroni,postgresql}

5. Mặc dù Patroni có thể sử dụng bản cài đặt PostgreSQL hiện có, nhưng ta hãy xóa thư mục dữ liệu để buộc nó khởi tạo một cụm PostgreSQL mới

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/15/main

Configure etcd distributed store

Distributed configuration store cung cấp khả năng lưu trữ dữ liệu cần được truy cập bởi các hệ thống phân tán quy mô lớn. Việc triển khai phổ biến nhất của Distributed configuration store là etcd. etcd được triển khai dưới dạng cụm để có khả năng chịu lỗi và yêu cầu số lượng thành phần là lẻ (n/2 + 1) để đồng ý cập nhật trạng thái cụm (tức số lượng node cài etcd là lẻ phục vụ cho mục đích vote). Cụm etcd giúp thiết lập sự đồng thuận giữa các node trong quá trình failover (chuyển đổi dự phòng) và quản lý cấu hình cho 3 phiển bản PostgreSQL

Cụm etcd sẽ được thiết lập bằng cách khởi động trên 1 node và sau đó các node tiếp theo được thêm vào node đầu tiên bằng lệnh add

Tạo thư mục lưu data cho etcd

sudo mkdir -p /data/etcd
sudo chown -R etcd. /data/etcd
sudo chmod -R 700 /data/etcd

Configure `postgres1`

Tạo tập tin cấu hình. Ta có thể chỉnh sửa tệp cấu hình mẫu /etc/default/etcd hoặc tạo 1 tệp riêng. Thay thế tên node và địa chỉ IP bằng tên node và địa chỉ IP thực trên node của bạn

ETCD_NAME=postgres1
ETCD_INITIAL_CLUSTER="postgres1=http://10.128.0.2:2380"
ETCD_INITIAL_CLUSTER_TOKEN="PostgreSQL_HA_Cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.128.0.2:2380"
ETCD_DATA_DIR="/data/etcd/postgresql"
ETCD_LISTEN_PEER_URLS="http://10.128.0.2:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.128.0.2:2379,http://localhost:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://10.128.0.2:2379"

Khởi động dịch vụ etcd để áp dụng các thay đổi trên postgres1

sudo systemctl enable --now etcd
sudo systemctl start etcd
sudo systemctl status etcd

Kiểm tra cluster member của cụm etcd trên postgres1:

sudo etcdctl member list

Sample Output:

root@postgres1:/data/etcd# sudo etcdctl member list
8ed9a5f4144cf2b: name=postgres1 peerURLs=http://10.128.0.2:2380 clientURLs=http://10.128.0.2:2379 isLeader=true

Add postgres2 vào cụm etcd. Chạy lệnh sau trên postgres1

sudo etcdctl member add postgres2 http://10.128.0.3:2380

Sample Output:

root@postgres1:/var/lib/etcd# sudo etcdctl member add postgres2 http://10.128.0.3:2380
Added member named postgres2 with ID 4241bed321a82cf5 to cluster

ETCD_NAME="postgres2"
ETCD_INITIAL_CLUSTER="postgres1=http://10.128.0.2:2380,postgres2=http://10.128.0.3:2380"
ETCD_INITIAL_CLUSTER_STATE="existing"
root@postgres1:/var/lib/etcd# sudo etcdc

Configure `postgres2`

Tạo tập tin cấu hình bằng cách chỉnh sửa tệp mẫu /etc/default/etcd và thêm vào nội dung sau

ETCD_NAME=postgres2
ETCD_INITIAL_CLUSTER="postgres1=http://10.128.0.2:2380,postgres2=http://10.128.0.3:2380"
ETCD_INITIAL_CLUSTER_TOKEN="PostgreSQL_HA_Cluster"
ETCD_INITIAL_CLUSTER_STATE="existing"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.128.0.3:2380"
ETCD_DATA_DIR="/data/etcd/postgresql"
ETCD_LISTEN_PEER_URLS="http://10.128.0.3:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.128.0.3:2379,http://localhost:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://10.128.0.3:2379"

Khởi động dịch vụ etcd để áp dụng các thay đổi trên postgres2

sudo systemctl enable --now etcd
sudo systemctl start etcd
sudo systemctl status etcd

Configure `postgres3`

Add postgres3 vào cụm. Chạy lệnh sau trên postgres1:

sudo etcdctl member add postgres3 http://10.128.0.4:2380

Sample Output:

root@postgres1:/var/lib/etcd# sudo etcdctl member add postgres3 http://10.128.0.4:2380
Added member named postgres3 with ID 68c3d262a236756c to cluster

ETCD_NAME="postgres3"
ETCD_INITIAL_CLUSTER="postgres1=http://10.128.0.2:2380,postgres2=http://10.128.0.3:2380,postgres3=http://10.128.0.4:2380"
ETCD_INITIAL_CLUSTER_STATE="existing"

Tạo tập tin cấu hình bằng cách chỉnh sửa tệp mẫu /etc/default/etcd và thêm vào nội dung sau

ETCD_NAME=postgres3
ETCD_INITIAL_CLUSTER="postgres1=http://10.128.0.2:2380,postgres2=http://10.128.0.3:2380,postgres3=http://10.128.0.4:2380"
ETCD_INITIAL_CLUSTER_TOKEN="PostgreSQL_HA_Cluster"
ETCD_INITIAL_CLUSTER_STATE="existing"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.128.0.4:2380"
ETCD_DATA_DIR="/data/etcd/postgresql"
ETCD_LISTEN_PEER_URLS="http://10.128.0.4:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.128.0.4:2379,http://localhost:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://10.128.0.4:2379"

Khởi động dịch vụ etcd để áp dụng các thay đổi trên postgres3

sudo systemctl enable --now etcd
sudo systemctl start etcd
sudo systemctl status etcd

Kiểm tra ectl cluster member

sudo etcdctl member list

Sample Output:

root@postgres1:/data/etcd# sudo etcdctl member list
8ed9a5f4144cf2b: name=postgres1 peerURLs=http://10.128.0.2:2380 clientURLs=http://10.128.0.2:2379 isLeader=true
4241bed321a82cf5: name=postgres2 peerURLs=http://10.128.0.3:2380 clientURLs=http://10.128.0.3:2379 isLeader=false
68c3d262a236756c: name=postgres3 peerURLs=http://10.128.0.4:2380 clientURLs=http://10.128.0.4:2379 isLeader=false

Configure Patroni

Chạy các lệnh sau trên ALL node

1. Export và tạo các biến môi trường để đơn giản hóa việc tạo tệp cấu hình

Node name:

export NODE_NAME=`hostname -f | cut -d. -f1`

Node IP:

export NODE_IP=`ip addr show eth0 | awk '/inet / {print $2}' | cut -d/ -f1`

Tạo các biến để lưu trữ PATH:

mkdir -p /data/postgresql/15/main
chown -R postgres. /data/postgresql
chmod -R 700 /data/postgresql/15/main

DATA_DIR="/data/postgresql/15/main"
PG_BIN_DIR="/usr/lib/postgresql/15/bin"

Patroni information:

NAMESPACE="prod_env"
SCOPE="cluster1"

2. Tạo thư mục chứa password của user `replicator` trên ALL node

mkdir /opt/secret
chomod -R 700 /opt/secret
chown -R postgres. /opt/secret

3. Tạo tệp cấu hình `/etc/patroni/config.yml` bằng cách chạy lệnh dưới trên ALL node

echo "
namespace: ${NAMESPACE}
scope: ${SCOPE}
name: ${NODE_NAME}

restapi:
    listen: 0.0.0.0:8008
    connect_address: ${NODE_IP}:8008

etcd3:
    host: ${NODE_IP}:2379

bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  dcs:
      ttl: 30
      loop_wait: 10
      retry_timeout: 10
      maximum_lag_on_failover: 1048576
      slots:
          repl_slot:
            type: physical

      postgresql:
          use_pg_rewind: true
          use_slots: true
          parameters:
              wal_level: replica
              hot_standby: "on"
              wal_keep_segments: 10
              max_wal_senders: 5
              max_replication_slots: 10
              wal_log_hints: "on"
              logging_collector: 'on'
              max_connections: 512

  # some desired options for 'initdb'
  initdb: # Note: It needs to be a list (some options need values, others are switches)
      - encoding: UTF8
      - data-checksums

  pg_hba: # Add following lines to pg_hba.conf after running 'initdb'
      - host replication replicator 127.0.0.1/32 trust
      - host replication replicator 0.0.0.0/0 md5
      - host all all 0.0.0.0/0 md5
      - host all all ::0/0 md5

postgresql:
    cluster_name: cluster_1
    listen: 0.0.0.0:5432
    connect_address: ${NODE_IP}:5432
    data_dir: ${DATA_DIR}
    bin_dir: ${PG_BIN_DIR}
    pgpass: /opt/secret/pgpass
    authentication:
        replication:
            username: replicator
            password: replPasswd
        superuser:
            username: postgres
            password: aMu3c9QKKUGYQZK
    parameters:
        unix_socket_directories: "/var/run/postgresql/"
    create_replica_methods:
        - basebackup
    basebackup:
        checkpoint: 'fast'

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
" | sudo tee -a /etc/patroni/config.yml

4. Khởi động dịch vụ Patroni trên ALL node

Ta cần chạy lệnh sau để khởi động Patroni trên tất cả các node nhưng không nên thực hiện song song. Trước tiên, hãy bắt đầu với postgres1 đợi dịch vụ hoạt động sau đó mới tiếp tục với các node khác, luôn chờ chúng đồng bộ hóa với node chính

sudo systemctl enable --now patroni
sudo systemctl restart patroni

Khi Patroni khởi động, nó sẽ khởi tạo DB PostgreSQL (vì dịch vụ PostgreSQL hiện không chạy và thư mục dữ liệu trống) theo các khai báo trong tệp YAML trên

Verify

1. Kiểm tra dịch vụ xem có lỗi không

sudo journalctl -fu patroni

Note: Việc thay đổi tệp /etc/patroni/config.yml và khởi động lại dịch vụ sẽ không có bất kỳ tác dụng nào ở đây vì phần bootstrap chỉ định cấu hình sẽ áp dụng khi PostgreSQL được khởi tạo lần đầu trên node. Nó sẽ không lặp lại quá trình này ngay cả khi tệp cấu hình Patroni được sửa đổi và dịch vụ được khởi động lại

Check Cluster

patronictl -c /etc/patroni/config.yml list $SCOPE

Sample Output:

root@postgres1:/data/etcd# patronictl -c /etc/patroni/config.yml list $SCOPE
+ Cluster: cluster1 (7384061089845509481) ---+----+-----------+
| Member    | Host     | Role    | State     | TL | Lag in MB |
+-----------+----------+---------+-----------+----+-----------+
| postgres1 | 10.128.0.2 | Leader  | running   |  9 |           |
| postgres2 | 10.128.0.3 | Replica | streaming |  9 |         0 |
| postgres3 | 10.128.0.4 | Replica | streaming |  9 |         0 |
+-----------+----------+---------+-----------+----+-----------+

Check Replica Slot

Nếu Patroni đã khởi động đúng cách, ta sẽ có thể kết nối cục bộ với node PostgreSQL bằng lệnh sau:

sudo psql -U postgres

Kiểm tra Replica Slot đã được tạo hay chưa bằng lệnh sau:

SELECT * FROM pg_replication_slots;

Sample Output:

postgres=# SELECT * FROM pg_replication_slots;
 slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase 
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+-----------
 repl_slot |        | physical  |        |          | f         | f      |            |      |              | 0/173F2E0   |                     | reserved   |               | f
 postgres2 |        | physical  |        |          | f         | t      |      23982 |      |              | 0/5CAA6F0   |                     | reserved   |               | f
 postgres3 |        | physical  |        |          | f         | t      |      23990 |      |              | 0/5CAA6F0   |                     | reserved   |               | f
(3 rows)

Configure HAProxy

HAProxy là bộ cân bằng tải và là điểm truy cập duy nhất vào cụm PostgreSQL của bạn cho ứng dụng khách. Ứng dụng khách truy cập URL HAProxy và gửi yêu cầu đọc/ghi của nó tới đó. HAProxy định tuyến write request tới Primary node và read request tới các Standby node theo round-robin. Để thực hiện điều này, ta sẽ cung cấp các cổng khác nhau trong tệp cấu hình HAProxy. Trong quá trình triển khai này, thao tác write được định tuyến đến port 5000 và read tới port 5001

Bằng cách này, ứng dụng khách không thể biết được node nào trong cụm bên dưới là Primary node. HAProxy gửi kết nối đến 1 node healthy (miễn là có sẵn 1 node healthy) và đảm bảo rằng các yêu cầu ứng dụng khách không bao giờ bị từ chối

1. Do tài nguyên có hạn nên hướng dẫn này sẽ cài đặt `HAProxy` trên `postgres3`

sudo apt install haproxy -y

2. Đường dẫn tệp cấu hình `HAProxy` là `/etc/haproxy/haproxy.cfg`. Chỉ định cấu hình sau trong tệp này:

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 primary
    bind *:5000
    option httpchk /primary 
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgres1 10.128.0.2:5432 maxconn 100 check port 8008
    server postgres2 10.128.0.3:5432 maxconn 100 check port 8008
    server postgres3 10.128.0.4:5432 maxconn 100 check port 8008

listen standbys
    balance roundrobin
    bind *:5001
    option httpchk /replica 
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgres1 10.128.0.2:5432 maxconn 100 check port 8008
    server postgres2 10.128.0.3:5432 maxconn 100 check port 8008
    server postgres3 10.128.0.4:5432 maxconn 100 check port 8008

HAProxy sẽ sử dụng API REST do Patroni lưu trữ dể kiểm tra trạng thái hoạt động của từng node PostgreSQL và định tuyến các yêu cầu một cách thích hợp

3. Restart & Enable HAProxy

sudo systemctl restart haproxy
sudo systemctl enable haproxy

4. Kiểm tra log HAProxy nếu có lỗi:

sudo journalctl -u haproxy.service -n 100 -f

5. Verify

root@postgres2:/data/postgresql/15/main# psql -U postgres -h 10.128.0.3 -p 5000
Password for user postgres: 
psql (15.7 (Ubuntu 15.7-1.pgdg22.04+1))
Type "help" for help.

postgres=# create database tubui;
CREATE DATABASE
postgres=# \q

root@postgres2:/data/postgresql/15/main# psql -U postgres -h 10.128.0.3 -p 5001
Password for user postgres: 
psql (15.7 (Ubuntu 15.7-1.pgdg22.04+1))
Type "help" for help.

postgres=# create database tu;
ERROR:  cannot execute CREATE DATABASE in a read-only transaction
postgres=#

Change Config Default

Thực hiện thay đổi config để tối ưu DB, thực hiện trên 1 node ví dụ postgres1:

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

loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    checkpoint_completion_target: 0.9
    datestyle: iso, mdy
    default_statistics_target: 100
    default_text_search_config: pg_catalog.english
    dynamic_shared_memory_type: posix
    effective_cache_size: 12GB
    effective_io_concurrency: 200
    hot_standby: true
    lc_messages: en_US.UTF-8
    lc_monetary: en_US.UTF-8
    lc_numeric: en_US.UTF-8
    lc_time: en_US.UTF-8
    log_destination: stderr
    log_directory: /var/log/postgresql
    log_filename: postgresql.log
    log_line_prefix: '%m [%p]'
    log_rotation_age: 3d
    log_rotation_size: 0
    log_timezone: Asia/Ho_Chi_Minh
    log_truncate_on_rotation: true
    logging_collector: 'on'
    maintenance_work_mem: 1GB
    max_connections: 512
    max_parallel_maintenance_workers: 2
    max_parallel_workers: 4
    max_parallel_workers_per_gather: 2
    max_replication_slots: 10
    max_wal_senders: 5
    max_wal_size: 4GB
    min_wal_size: 1GB
    random_page_cost: 1.1
    timezone: Asia/Ho_Chi_Minh
    use_pg_rewind: true
    use_slots: true
    wal_keep_segments: 10
    wal_level: replica
    wal_log_hints: true
    work_mem: 4MB
retry_timeout: 10
slots:
  percona_cluster_1:
    type: physical
ttl: 30

Tải lại các cấu hình đã thay đổi:

patronictl -c /etc/patroni/config.yml reload $SCOPE

Install Keepalived

1. Mở kết nối

iptables -A INPUT -s 10.128.0.2,10.128.0.3,10.128.0.4 -d 224.0.0.0/8 -i eth0 -p vrrp -m comment --comment "Keepalived_vrrp" -j ACCEPT
iptables -A INPUT -s 10.128.0.2,10.128.0.3,10.128.0.4 -d 224.0.0.0/8 -i eth0 -p ah -m comment --comment "Keepalived_vrrp" -j ACCEPT

iptables -A OUTPUT -s 10.128.0.2,10.128.0.3,10.128.0.4 -d 224.0.0.0/8 -o eth0 -p vrrp -m comment --comment "Keepalived_vrrp" -j ACCEPT
iptables -A OUTPUT -s 10.128.0.2,10.128.0.3,10.128.0.4 -d 224.0.0.0/8 -o eth0 -p ah -m comment --comment "Keepalived_vrrp" -j ACCEPT

2. Cài đặt Keepalived

sudo apt install keepalived -y

Thêm đoạn config sau vào file /etc/keepalived/keepalived.conf trên ALL node

Trên node postgres1:

global_defs {
}
vrrp_script chk_haproxy { # Requires keepalived-1.1.13
    script "killall -0 haproxy" # widely used idiom
    interval 2 # check every 2 seconds
    weight 2 # add 2 points of prio if OK
}
vrrp_instance VI_1 {
    interface eth0
    state MASTER # or "BACKUP" on backup
    priority 101 # 101 on master, 100 on backup
    virtual_router_id 51
    authentication {
        auth_type PASS
        auth_pass prod_env_93
    }
    virtual_ipaddress {
        10.1.0.10
    }
    track_script {
        chk_haproxy
    }
}

Trên node postgres2:

global_defs {
}
vrrp_script chk_haproxy { # Requires keepalived-1.1.13
    script "killall -0 haproxy" # widely used idiom
    interval 2 # check every 2 seconds
    weight 2 # add 2 points of prio if OK
}
vrrp_instance VI_1 {
    interface eth0
    state BACKUP # or "BACKUP" on backup
    priority 100 # 101 on master, 100 on backup
    virtual_router_id 51
    authentication {
        auth_type PASS
        auth_pass prod_env_93
    }
    virtual_ipaddress {
        10.1.0.10
    }
    track_script {
        chk_haproxy
    }
}

Trên node postgres3:

global_defs {
}
vrrp_script chk_haproxy { # Requires keepalived-1.1.13
    script "killall -0 haproxy" # widely used idiom
    interval 2 # check every 2 seconds
    weight 2 # add 2 points of prio if OK
}
vrrp_instance VI_1 {
    interface eth0
    state BACKUP # or "BACKUP" on backup
    priority 99 # 101 on master, 99 on backup
    virtual_router_id 51
    authentication {
        auth_type PASS
        auth_pass prod_env_93
    }
    virtual_ipaddress {
        10.1.0.10
    }
    track_script {
        chk_haproxy
    }
}

3. Restart & Enable Keepalived

sudo systemctl restart keepalived
sudo systemctl enable keepalived

Tunning kernel

Tạo tệp /etc/sysctl.d/90-postgresql.conf trên tất cả các server

touch /etc/sysctl.d/90-postgresql.conf

Thêm các cấu hình sau vào tệp /etc/sysctl.d/90-postgresql

# kernel tuning parameters for postgresql
# shared memory
kernel.shmmax=4831838208
kernel.shmall=4831838208

# swappiness
vm.swappiness=1

# memory overcommit
vm.overcommit_memory=2
vm.overcommit_ratio=50

# memory dirty pages
vm.dirty_background_ratio=15
vm.dirty_ratio=20

# backlog
net.core.somaxconn = 6553

Thực hiện áp dụng cấu hình

sysctl -w --system

PgBouncer

Giới thiệu về PgBouncer

PgBouncer là một công cụ proxy kết nối cho PostgreSQL. Nó giúp quản lý và tối ưu hóa các kết nối giữa các ứng dụng và cơ sở dữ liệu PostgreSQL, PgBouncer hoạt động như một proxy trung gian giữa ứng dụng và cơ sở dữ liệu PostgreSQL, cung cấp các tính năng như connection pooling, load balancing và retry cho các kết nối thất bại

Các tính năng chính của PgBouncer

1. Connection Pooling: Giải pháp giúp tối ưu hóa việc sử dụng kết nối đến cơ sở dữ liệu bằng cách duy trì một tập hợp các kết nối sẵn sàng để sử dụng lại, thay vì tạo một kết nối cho mỗi request từ ứng dụng

2. Load Balancing: Phân phối các kết nối đến nhiều máy chủ cơ sở dữ liệu (primary và standby) để cải thiện hiệu suất và phân phối tải

3. Transaction Management: Hỗ trợ các chế độ khác nhau cho việc xử lý giao dịch, giúp kiểm soát và quản lý các kết nối đến cơ sở dữ liệu

4. Query Rewriting: Cung cấp các tùy chọn để sửa đổi hoặc điều chỉnh các truy vấn SQL

5. Automatic Failover: Khi kết nối đến cơ sở dữ liệu bị lỗi, PgBouncer có thể tự động kết nối lại với một máy chỉ khác nếu có sẵn

Cách hoạt động của PgBouncer

PgBouncer hoạt động như một proxy giữa ứng dụng và PostgreSQL. Dưới đây là cách PgBouncer xử lý các kết nối và truy vấn:

1. Ứng dụng kết nối đến PgBouncer: Ứng dụng gửi các truy vấn SQL đến PgBouncer thay vì trực tiếp đến PostgreSQL

2. PgBouncer kết nối đến PostgreSQL: PgBouncer duy trì một hoặc nhiều kết nối tới các máy chủ PostgreSQL. Những kết nối này được sử dụng lại để phục vụ nhiều yêu cầu từ ứng dụng

3. Quản lý kết nối: PgBouncer sẽ quản lý các kết nối ứng dụng đến cơ sở dữ liệu, bao gồm việc tạo mới, giữ kết nối và tái sử dụng kết nối

4. Phân phối truy vấn: PgBouncer có thể phân phối các truy vấn đến nhiều máy chủ cơ sở dữ liệu dựa trên các cấu hình load balancing

5. Xử lý truy vấn: PgBouncer gửi truy vấn đến PostgreSQL và nhận kết quả để trả về cho ứng dụng

Các chế độ kết nối của PgBouncer

PgBouncer hỗ trợ 3 node connection chính:

1. Session Pooling: Mỗi kết nối từ ứng dụng tương ứng với một kết nối cơ sở dữ liệu. Kết nối được mở và giữ suốt thời gian làm việc

2. Transaction Pooling: Mỗi kết nối từ ứng dụng tương ứng với một kết nối cơ sở dữ liệu cho mỗi giao dịch. Kết nối sẽ được giải phóng về pool khi giao dịch kết thúc

3. Statement Pooling: Mỗi CÂU LỆNH SQL từ ứng dụng tương ứng với một kết nối từ cơ sở dữ liệu. Đây là chế độ tối ưu nhất nhưng có thể không tương thích với tất cả các ứng dụng

Cài đặt PgBouncer

sudo apt-get update
sudo apt-get install pgbouncer

Cấu hình PgBouncer

File cấu hình chính của PgBouncer là pgbouncer.ini. Dưới đây là file cấu hình cơ bản

[databases]
* = host=10.128.0.10 port=5000

[pgbouncer]
listen_addr = *
listen_port = 6432

auth_file = /etc/pgbouncer/userlist.txt
auth_type = hba
auth_hba_file = /etc/pgbouncer/pg_hba.conf

default_pool_size = 20
min_pool_size = 5
max_client_conn = 512
pool_mode = transaction

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

admin_users = postgres
stats_users = stats

server_lifetime = 300
server_idle_timeout = 60

Thiết lập người dùng

File userlist.txt chứa thông tin xác thực người dùng dạng MD5

Ta có thể tạo hash MD5 bằng cách sử dụng lệnh sau:

echo "md5"$(echo -n 'postgres:aMu3c9QKKUGYQZK' | md5sum | awk ' { print $1 } ')

Sample Output:

root@patroni1:/etc/pgbouncer# echo "md5"$(echo -n 'postgres:aMu3c9QKKUGYQZK' | md5sum | awk ' { print $1 } ')
md5c4388e3e198885c4b0349a38ce5a09d7

Đối với phiên bản PostgreSQL 15 trở lên, sử dụng scram-sha-256 để xác thực

psql -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"

Sample Output:

root@patroni1:/etc/pgbouncer# psql -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"
"postgres" "SCRAM-SHA-256$4096:Xyx5X5KUBAQleSlufcHx3w==$mJbMJnXJKIQlS2YPRNttB3OeBY3pbxpLmueZLxyUBOY=:jq+axTh6YpIklgzY5QkAoHdxjGKAmlxhVFUS2hgtWKE="
"replicator" "SCRAM-SHA-256$4096:udeQUwQBA+0Kvl14OQoD0g==$qKjt27yU7MNESoqAPjDD+++qq2dkYMKNnR7j5o5B7k8=:/9cEKHxr66aH2ZBpaOxM1Yc4sYo7yKkQuAXJSlywAVE="

Điền giá trị sau vào file userlist.txt với định dạng

"username" "<md5_passwordhash>"

hoặc

"username" "<scram-sha-256_passwordhash>"

Resart và enable Pgboucer

sudo systemctl restart pgbouncer
sudo systemctl enable pgbouncer

Test Performance

Tạo database phục vụ cho mục đích test hiệu năng

create database testdb;

Thử với port 5432

root@patroni1:/etc/pgbouncer# pgbench -h 10.128.0.10 -U postgres -c 500 -T 60 testdb -p 5432
Password: 
pgbench (15.7 (Ubuntu 15.7-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 500
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 34338
number of failed transactions: 0 (0.000%)
latency average = 783.697 ms
initial connection time = 7702.715 ms
tps = 638.001406 (without initial connection time)

Thử với port 6432

root@patroni1:/etc/pgbouncer# pgbench -h 10.128.0.10 -U postgres -c 500 -T 60 testdb -p 6432
Password: 
pgbench (15.7 (Ubuntu 15.7-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 500
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 44192
number of failed transactions: 0 (0.000%)
latency average = 624.219 ms
initial connection time = 5510.564 ms
tps = 801.001164 (without initial connection time)

Vậy là chúng ta đã setup thành công PostgreSQL high availability trong thực tế. Mình vẫn dám khẳng định lại là bạn hoàn toàn có thể áp dụng vào thực tế công ty vì chính mình dùng tốt mình mới đi chia sẻ cho mọi người. Nếu thấy hữu ích thì bạn có thể Bookmark lại vào tài khoản sau dễ tìm. Và chia sẻ cho người khác để họ dùng nhé.