这是我参与8月更文挑战的第6天，活动详情查看：8月更文挑战

patroni + etcd

• 需要解决入口点自动更新主库地址

stolon

• stolon测试来看高可用可行，在非云环境下需要增加入口，因stolon自身实现proxy可以用常用的负载均衡完成负载，在不考虑负载均衡情况下，采用pgbouncer实现连接池功能

pgpool-II

• 测试来看效果不好，自己维护状态，切换频繁，不容易实现故障节点自动恢复

Repmgr

• 此方案gitlab用了一段时间，目前切换到patroni+consul，gitlab解决入口点通过patroni开放的api获取leader，更新pgbouncer主库连接地址

目前来看在虚拟机/物理机环境下stolon也不错，上生产前需要多测试

系统环境

• CentOS Linux release 7.8.2003 (Core)

先决条件

• 三台linux主机

mkdir /data/postgresql/data/ -p mkdir -p /data/postgresql/patroni/ 

# /usr/lib/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 #StandardOutput=syslog ExecStart=/usr/local/bin/patroni /etc/patroni.yml ExecReload=/bin/kill -s HUP $MAINPID KillMode=process TimeoutSec=30 Restart=no [Install] WantedBy=multi-user.target systemctl daemon-reload 

实施步骤

1. 安装patroni

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum install -y python36 python36-devel postgresql12-server rm -rf /usr/bin/python ln -sv /usr/bin/python3.6 /usr/bin/python rm -f /usr/bin/pip3 ln -sv /usr/bin/pip3.6 /usr/bin/pip3 curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py python get-pip.py pip3 install patroni[etcd] -i https://mirrors.aliyun.com/pypi/simple/ pip3 install psycopg2-binary -i https://mirrors.aliyun.com/pypi/simple/ pip3 install patroni -i https://mirrors.aliyun.com/pypi/simple/ 

systemctl start patroni.service chown -R postgres:postgres /data/postgresql/  
systemctl start patroni.service

2. 如下是三台patroni配置

[root@pgslave2 ~]# cat /etc/patroni.yml scope: pgsql namespace: /service/ name: pg3 restapi: listen: 0.0.0.0:8008 connect_address: 172.168.1.232:8008 etcd: #Provide host to do the initial discovery of the cluster topology: host: 172.168.1.232: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 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: wal_level: locical hot_standby: "on" wal_keep_segments: 128 max_wal_senders: 10 max_replication_slots: 10 wal_log_hints: "on" archive_mode: "on" hot_standby: on archive_timeout: 1800s initdb: - encoding: UTF8 - locale: C - lc-ctype: zh_CN.UTF-8 - data-checksums pg_hba: - host replication repl 0.0.0.0/0 md5 - host all all 0.0.0.0/0 md5 postgresql: listen: 172.168.1.232:9998 connect_address: 172.168.1.232:9998 data_dir: /data/postgresql/data/ bin_dir: /usr/pgsql-12/bin/ authentication: replication: username: repl password: "123456" superuser: username: postgres password: "postgres" basebackup: max-rate: 100M checkpoint: fast tags: nofailover: false noloadbalance: false clonefrom: false nosync: false 

[root@pgslave1 data]# cat /etc/patroni.yml scope: pgsql namespace: /service/ name: pg2 restapi: listen: 0.0.0.0:8008 connect_address: 172.168.1.234:8008 etcd: #Provide host to do the initial discovery of the cluster topology: host: 172.168.1.234: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 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: wal_level: locical hot_standby: "on" wal_keep_segments: 128 max_wal_senders: 10 max_replication_slots: 10 wal_log_hints: "on" archive_mode: "on" hot_standby: on archive_timeout: 1800s postgresql: listen: 172.168.1.234:9998 connect_address: 172.168.1.234:9998 data_dir: /data/postgresql/data/ bin_dir: /usr/pgsql-12/bin/ pgpass: /data/postgresql/patroni/.pgpass authentication: replication: username: repl password: 123456 superuser: username: postgres password: postgres tags: nofailover: false noloadbalance: false clonefrom: false nosync: false 

[root@pgmaster data]# cat /etc/patroni.yml scope: pgsql namespace: /service/ name: pg1 restapi: listen: 0.0.0.0:8008 connect_address: 172.168.1.235:8008 etcd: #Provide host to do the initial discovery of the cluster topology: host: 172.168.1.235: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 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: wal_level: locical hot_standby: "on" wal_keep_segments: 128 max_wal_senders: 10 max_replication_slots: 10 wal_log_hints: "on" archive_mode: "on" hot_standby: on archive_timeout: 1800s postgresql: listen: 172.168.1.235:9998 connect_address: 172.168.1.235:9998 data_dir: /data/postgresql/data/ bin_dir: /usr/pgsql-12/bin/ pgpass: /data/postgresql/patroni/.pgpass authentication: replication: username: repl password: "123456" superuser: username: postgres password: "postgres" tags: nofailover: false noloadbalance: false clonefrom: false nosync: false