在CentOS上配置PostgreSQL的高可用性，可以使用多種方法，其中最常見的是使用Patroni和Pgpool-II。以下是使用Patroni和Pgpool-II配置高可用性的基本步驟：
首先，確保你的CentOS系統已經安裝了PostgreSQL、Patroni和Pgpool-II。
sudo yum install postgresql-server patroni pgpool2
編輯PostgreSQL的配置文件/var/lib/pgsql/data/postgresql.conf和/var/lib/pgsql/data/pg_hba.conf，確保它們支持復制和高可用性。
postgresql.conf
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64
hot_standby = on
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host replication replicator 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5
在主節點上初始化復制。
sudo systemctl start postgresql
sudo -u postgres psql -c "CREATE USER replicator WITH REPLICATION PASSWORD 'your_password' LOGIN;"
sudo -u postgres psql -c "ALTER USER replicator WITH SUPERUSER;"
在從節點上，停止PostgreSQL服務并編輯/var/lib/pgsql/data/recovery.conf文件。
sudo systemctl stop postgresql
echo "standby_mode = 'on'" | sudo tee /var/lib/pgsql/data/recovery.conf
echo "primary_conninfo = 'host=master_ip port=5432 user=replicator password=your_password'" | sudo tee -a /var/lib/pgsql/data/recovery.conf
echo "restore_command = 'cp /var/lib/pg_xlog/archive/%f %p'" | sudo tee -a /var/lib/pgsql/data/recovery.conf
將主節點的數據目錄復制到從節點。
sudo rsync -avz /var/lib/pgsql/data/ /var/lib/pgsql/data/
啟動從節點上的PostgreSQL服務。
sudo systemctl start postgresql
在所有節點上安裝Patroni。
sudo yum install python3-pip
sudo pip3 install patroni
創建Patroni配置文件/etc/patroni.yml。
master節點
scope: postgres
name: master
namespace: /db/
restapi:
listen: 0.0.0.0:8008
connect_address: master_ip:8008
etcd:
host: etcd_ip:2379
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
max_wal_senders: 10
wal_keep_segments: 64
hot_standby: on
data_dir: /var/lib/pgsql/data
replication:
listen: 0.0.0.0:5432
connect_address: master_ip:5432
replication_role: replica
triggers:
- name: pg_hba_reload
type: WALReceiver
trigger: 'pg_hba_reload()'
module_path: custom
module_call: reload_hba
slots: 1
authentication:
replication:
username: replicator
password: your_password
superuser:
username: postgres
password: your_password
standby節點
scope: postgres
name: standby
namespace: /db/
restapi:
listen: 0.0.0.0:8008
connect_address: standby_ip:8008
etcd:
host: etcd_ip:2379
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
max_wal_senders: 10
wal_keep_segments: 64
hot_standby: on
data_dir: /var/lib/pgsql/data
replication:
listen: 0.0.0.0:5432
connect_address: master_ip:5432
replication_role: standby
triggers:
- name: pg_hba_reload
type: WALReceiver
trigger: 'pg_hba_reload()'
module_path: custom
module_call: reload_hba
slots: 1
authentication:
replication:
username: replicator
password: your_password
superuser:
username: postgres
password: your_password
在所有節點上啟動Patroni。
sudo patroni /etc/patroni.yml
在所有節點上安裝Pgpool-II。
sudo yum install pgpool2
創建Pgpool-II配置文件/etc/pgpool2/pgpool.conf。
backend_hostname0 = 'master_ip'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'standby_ip'
backend_port1 = 5432
backend_weight1 = 1
listen_addresses = '*'
port = 9999
auth_type = md5
auth_provider = 1
auth_file = '/etc/pgpool2/pool_passwd'
load_balance_mode = on
failover_command = '/usr/bin/pg_ctl promote -D /var/lib/pgsql/data'
recovery_command = '/usr/bin/pg_ctl promote -D /var/lib/pgsql/data'
創建密碼文件/etc/pgpool2/pool_passwd。
'master_ip' 'replicator' 'your_password'
'standby_ip' 'replicator' 'your_password'
設置密碼文件權限。
sudo chmod 600 /etc/pgpool2/pool_passwd
啟動Pgpool-II。
sudo systemctl start pgpool2
確保所有節點上的Patroni和Pgpool-II都在運行，并且PostgreSQL集群正常工作。
sudo systemctl status patroni
sudo systemctl status pgpool2
通過訪問Pgpool-II的監聽地址和端口（例如http://your_ip:9999），你可以查看集群的狀態和健康狀況。
通過以上步驟，你可以在CentOS上配置一個高可用的PostgreSQL集群，使用Patroni管理集群狀態，使用Pgpool-II進行負載均衡和故障轉移。
