基于patroni+etcd的postgresql高可用搭建

1.环境

centos 7.4
 
postgresql 10.5
 
物理ip
 
192.168.199.240  （node1）
 
192.168.199.241  （node2）
 
192.168.199.242  （node3）
 
 
vip
 
192.168.199.248
 
192.168.199.249
 

2.安装patroni和ectd

yum install -y gcc python-devel epel-release
yum install -y etcd
yum install -y python2-pip
pip install --upgrade setuptools
pip install psycopg2-binary
pip install python-etcd
pip install patroni
 3.配置etcd文件

node1
vi /etc/etcd/etcd.conf
 
ETCD_DATA_DIR="/var/lib/etcd/node1.etcd"
ETCD_LISTEN_PEER_URLS="http://192.168.199.240:2380,http://127.0.0.1:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.199.240:2379,http://127.0.0.1:2379"
ETCD_NAME="node1"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.199.240:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.199.240:2379"
ETCD_INITIAL_CLUSTER="node1=http://192.168.199.240:2380,node2=http://192.168.199.241:2380,node3=http://192.168.199.242:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
node2
vi /etc/etcd/etcd.conf
 
ETCD_DATA_DIR="/var/lib/etcd/node2.etcd"
ETCD_LISTEN_PEER_URLS="http://192.168.199.241:2380,http://127.0.0.1:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.199.241:2379,http://127.0.0.1:2379"
ETCD_NAME="node2"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.199.241:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.199.241:2379"
ETCD_INITIAL_CLUSTER="node1=http://192.168.199.240:2380,node2=http://192.168.199.241:2380,node3=http://192.168.199.242:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
node3
vi /etc/etcd/etcd.conf
 
ETCD_DATA_DIR="/var/lib/etcd/node3.etcd"
ETCD_LISTEN_PEER_URLS="http://192.168.199.242:2380,http://127.0.0.1:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.199.242:2379,http://127.0.0.1:2379"
ETCD_NAME="node3"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.199.242:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.199.242:2379"
ETCD_INITIAL_CLUSTER="node1=http://192.168.199.240:2380,node2=http://192.168.199.241:2380,node3=http://192.168.199.242:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
4.安装postgresql

pg数据库参数配置
node1
postgresql.conf
 
max_connections = '100'
max_wal_senders = '10'
port = '5432'
listen_addresses = '0.0.0.0'
synchronous_commit = on
full_page_writes = on
wal_log_hints = on
synchronous_standby_names = '*'
max_replication_slots = 10
wal_level = replica
 
node1
pg_hba.conf
 
host    all             all               192.168.99.0/24        md5
host    replication     replicator        192.168.199.240/32     md5
host    replication     replicator        192.168.199.240/32     md5
host    replication     replicator        192.168.199.240/32     md5
 
创建流复制的用户，创建复制槽
create user replicator replication login encrypted password 'replicator';
alter user postgres with password 'postgres';
select * from pg_create_physical_replication_slot('pg10_node1');
select * from pg_create_physical_replication_slot('pg10_node2');
select * from pg_create_physical_replication_slot('pg10_node3');
 
 
node2
 
pg_basebackup -h 192.168.199.240 -D /opt/data -U replicator -v -P -R
 
vi recovery.conf
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.199.240 port=5432 user=replicator password=replicator'
primary_slot_name = 'pg10_node2'
trigger_file = '/tmp/postgresql.trigger.5432'
 
node3
pg_basebackup -h 192.168.199.240 -D /opt/data -U replicator -v -P -R
 
vi recovery.conf
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.199.240 port=5432 user=replicator password=replicator'
primary_slot_name = 'pg10_node3'
trigger_file = '/tmp/postgresql.trigger.5432'
 5.配置patroni文件

vi /usr/patroni/conf/patroni_postgresql.yml

node1

scope: pg10
namespace: /pgsql/
name: pg10_node1
 
restapi:
  listen: 192.168.199.240:8008
  connect_address: 192.168.199.240:8008
 
etcd:
  host: 192.168.199.240:2379
 
bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a `global configuration`
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "0.0.0.0"
        port: 5432
        wal_level: logical
        hot_standby: "on"
        wal_keep_segments: 1000
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
#        archive_mode: "on"
#        archive_timeout: 1800s
#        archive_command: gzip < %p > /data/backup/pgwalarchive/%f.gz
#      recovery_conf:
#        restore_command: gunzip < /data/backup/pgwalarchive/%f.gz > %p
 
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.199.240:5432
  data_dir: /opt/data
  bin_dir: /opt/pg10/bin
#  config_dir: /etc/postgresql/9.6/main
  authentication:
    replication:
      username: replicator
      password: replicator
    superuser:
      username: postgres
      password: postgres
 
#watchdog:
#  mode: automatic # Allowed values: off, automatic, required
#  device: /dev/watchdog
#  safety_margin: 5
 
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
 node2

scope: pg10
namespace: /pgsql/
name: pg10_node2
 
restapi:
  listen: 192.168.199.241:8008
  connect_address: 192.168.199.241:8008
 
etcd:
  host: 192.168.199.241:2379
 
bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a `global configuration`
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "0.0.0.0"
        port: 5432
        wal_level: logical
        hot_standby: "on"
        wal_keep_segments: 1000
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
#        archive_mode: "on"
#        archive_timeout: 1800s
#        archive_command: gzip < %p > /data/backup/pgwalarchive/%f.gz
#      recovery_conf:
#        restore_command: gunzip < /data/backup/pgwalarchive/%f.gz > %p
 
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.199.241:5432
  data_dir: /opt/data
  bin_dir: /pg10/postgres10/bin
#  config_dir: /etc/postgresql/9.6/main
  authentication:
    replication:
      username: replicator
      password: replicator
    superuser:
      username: postgres
      password: postgres
 
#watchdog:
#  mode: automatic # Allowed values: off, automatic, required
#  device: /dev/watchdog
#  safety_margin: 5
 
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
 node3

scope: pg10
namespace: /pgsql/
name: pg10_node3
 
restapi:
  listen: 192.168.199.242:8008
  connect_address: 192.168.199.242:8008
 
etcd:
  host: 192.168.199.242:2379
 
bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a `global configuration`
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "0.0.0.0"
        port: 5432
        wal_level: logical
        hot_standby: "on"
        wal_keep_segments: 1000
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
#        archive_mode: "on"
#        archive_timeout: 1800s
#        archive_command: gzip < %p > /data/backup/pgwalarchive/%f.gz
#      recovery_conf:
#        restore_command: gunzip < /data/backup/pgwalarchive/%f.gz > %p
 
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.199.242:5432
  data_dir: /opt/data
  bin_dir: /opt/pg10/bin
#  config_dir: /etc/postgresql/9.6/main
  authentication:
    replication:
      username: replicator
      password: replicator
    superuser:
      username: postgres
      password: postgres
 
#watchdog:
#  mode: automatic # Allowed values: off, automatic, required
#  device: /dev/watchdog
#  safety_margin: 5
 
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
 6.启动patroni

手动启动 patroni,注意这里需要切换到postgres用户下启动

node1、node2、node3 三个节点依次启动

[postgres@node1 ~]$patroni /usr/patroni/conf/patroni_postgresql.yml
查看集群信息

[root@node1 ~]# patronictl -c /usr/patroni/conf/patroni_postgresql.yml list
+ Cluster: pg10 (6940821055749364910) ---+---------+----+-----------+
| Member     | Host            | Role    | State   | TL | Lag in MB |
+------------+-----------------+---------+---------+----+-----------+
| pg10_node1 | 192.168.199.240 | Leader  | running |  1 |           |
| pg10_node2 | 192.168.199.241 | Replica | running |  1 |       0.0 |
| pg10_node3 | 192.168.199.242 | Replica | running |  1 |       0.0 |
+------------+-----------------+---------+---------+----+-----------+
查看etcd状态

查看etcd状态
 
[root@node1 ~]# etcdctl ls /pgsql/pg10
/pgsql/pg10/leader
/pgsql/pg10/config
/pgsql/pg10/optime
/pgsql/pg10/members
/pgsql/pg10/initialize
为了方便开机自启，故配置成 patroni.service，3个node都需要进行配置，配置好patroni.service后就可以直接在root用户下切换Leader以及重启postgres节点等操作

[root@localhost data]# vi /etc/systemd/system/patroni.service
[root@localhost data]# cat /etc/systemd/system/patroni.service
[Unit]
Description=patroni - a high-availability PostgreSQL
Documentation=https://patroni.readthedocs.io/en/latest/index.html
After=syslog.target network.target etcd.target
Wants=network-online.target
 
[Service]
Type=simple
User=postgres
Group=postgres
PermissionsStartOnly=true
ExecStart=/usr/bin/patroni /usr/patroni/conf/patroni_postgresql.yml
ExecReload=/bin/kill -HUP $MAINPID
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
RestartSec=30s
TimeoutSec=0
 
[Install]
WantedBy=multi-user.target
 

 7.常用命令

# 启动服务(会自动启动 pg 实例)
systemctl start patroni
# 关闭服务(会自动关闭 pg 实例)
systemctl stop patroni
# 查看集群状态
patronictl -c /etc/patroni.yml list
# 可以查看命令的使用说明
patronictl --help
# 查看版本号
patronictl -c /etc/patroni.yml version
# 查看所有成员信息
patronictl -c /etc/patroni.yml list
# 重新加载配置
patronictl -c /etc/patroni.yml reload
# 移除集群，重新配置的时候使用
patronictl -c /etc/patroni.yml remove postgres
# 重启数据库集群
patronictl -c /etc/patroni.yml restart postgres
# 切换 Leader，将一个 slave 切换成 leader。
patronictl -c /etc/patroni.yml switchover
 

参考：https://blog.csdn.net/andou2693/article/details/101549161/ 

          https://mp.weixin.qq.com/s/edvWkTb-WF7YyVAFz5GCfw
https://blog.csdn.net/silenceray/article/details/116144139