基于Patroni的PostgreSQL高可用实践

[root@lee ~]# yum -y install https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[root@lee ~]# for i in pgdg10 pgdg11 pgdg12 pgdg13 pgdg14;do yum-config-manager --disable $i;done
[root@lee ~]# yum-config-manager --disable postgresql
[root@lee ~]# yum -y install watchdog patroni patroni-etcd etcd haproxy postgresql15-server

[root@lee ~]# vi /etc/etcd/etcd.conf
[Member]
ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ETCD_LISTEN_PEER_URLS="http://172.16.104.112:2380"
ETCD_LISTEN_CLIENT_URLS="http://172.16.104.112:2379,http://127.0.0.1:2379"
ETCD_NAME="lee"
[Clustering]
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.16.104.112:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://172.16.104.112:2379,http://127.0.0.1:2379"
ETCD_INITIAL_CLUSTER="lee=http://172.16.104.112:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
[root@lee ~]# systemctl enable etcd && systemctl start etcd
[root@lee ~]# systemctl status etcd
[root@lee ~]# etcdctl member list
9227a59dc4fe5c0e: name=lee peerURLs=http://172.16.104.112:2380 clientURLs=http://127.0.0.1:2379,http://172.16.104.112:2379 isLeader=true
[root@lee ~]# etcdctl cluster-health
member 9227a59dc4fe5c0e is healthy: got healthy result from http://127.0.0.1:2379
cluster is healthy

[root@lee ~]# mkdir -p /pgsql/{data01,data02,data03}
[root@lee ~]# chown -R postgres: /pgsql
[root@lee ~]# echo redhat|passwd --stdin postgres
[root@lee ~]# su - postgres
[postgres@lee ~]$ initdb -E UTF8 --locale=en_US.UTF-8 -D /pgsql/data01 -U postgres -W
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password:
Enter it again:

fixing permissions on existing directory /pgsql/data01 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

pg_ctl -D /pgsql/data01 -l logfile start

postgres=# select *from pg_create_physical_replication_slot('lee01');
postgres=# select *from pg_create_physical_replication_slot('lee02');
postgres=# select *from pg_create_physical_replication_slot('lee03');
postgres=# select slot_name,slot_type from pg_replication_slots;
slot_name | slot_type
-----------+-----------
lee01 | physical
lee02 | physical
lee03 | physical

[postgres@lee ~]$ vi /pgsql/data/pg_hba.conf
host all all 0.0.0.0/0 md5
host replication postgres 0.0.0.0/0 md5
host all all 172.16.104.112/32 md5

[root@lee ~]# visudo
postgres ALL=(ALL) NOPASSWD: /usr/sbin/modprobe,/usr/bin/chown

[root@lee ~]# vi /etc/patroni/patroni01.yml
scope: postgres
namespace: /pg_cluster/
#name这个参数的值随便写，建议为主机名，如果在同一台机器上，设置别名即可。
name: lee01

log:
level: INFO
traceback_level: ERROR
#定义patroni的日志路径，此路径对于postgres用户必须可读可写。
dir: /tmp/patroni01
file_num: 10
file_size: 104857600

#restapi的端口后，如果在同一台节点上，设置不同的端口号，在不同的节点使用默认的8008即可。
restapi:
listen: 0.0.0.0:8008
#connect_address为本机的IP加端口号
connect_address: 172.16.104.112:8008

etcd:
host: 172.16.104.112:2379

bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
#Patroni默认使用的是异步流复制，如果使用同步流复制，必须设置以下三个以synchronous开头的参数。
#在多个节点的同步流复制模式，默认只有一个节点是同步流复制，要设置多个同步流复制，必须设置synchronous_node_count参数。
synchronous_mode: true
synchronous_node_count: 2
synchronous_mode_strict: true
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: "on"
logging_collector: 'on'
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
max_connections: 1000
synchronous_commit: on
synchronous_standby_names: '*'

initdb:
- encoding: UTF8
- data-checksums

pg_hba:
- host replication postgres 127.0.0.1/32 md5
- host replication postgres 172.16.104.112/32 trust
- host replication postgres 0.0.0.0/0 md5
- 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: 172.16.104.112:5432
data_dir: /pgsql/data01
bin_dir: /usr/pgsql-15/bin
authentication:
replication:
username: postgres
password: redhat
superuser:
username: postgres
password: redhat
parameters:
unix_socket_directories: '/var/run/postgresql'
superuser_reserved_connections: 13
tcp_keepalives_idle: 60
tcp_keepalives_interval: 10
tcp_keepalives_count: 10
shared_buffers: 2048MB
vacuum_cost_delay: 10
bgwriter_delay: 10ms
wal_writer_delay: 10ms
wal_receiver_status_interval: 1s
hot_standby_feedback: on
log_destination: 'csvlog'
logging_collector: on
log_directory: 'pg_log'
log_truncate_on_rotation: on
log_rotation_age: 1d
log_rotation_size: 10MB
log_checkpoints: on
log_connections: on
log_disconnections: on
log_error_verbosity: verbose
log_timezone: 'PRC'
full_page_writes: on

watchdog:
mode: automatic # Allowed values: off, automatic, required
device: /dev/watchdog
safety_margin: 5

tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false

[root@lee ~]# vi /etc/patroni/patroni02.yml
scope: postgres
namespace: /pg_cluster/
name: lee02

log:
level: INFO
traceback_level: ERROR
dir: /tmp/patroni02
file_num: 10
file_size: 104857600

restapi:
listen: 0.0.0.0:8009
connect_address: 172.16.104.112:8009

etcd:
host: 172.16.104.112:2379

bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
synchronous_mode: true
synchronous_node_count: 2
synchronous_mode_strict: true
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: "on"
logging_collector: 'on'
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
max_connections: 1000
synchronous_commit: on
synchronous_standby_names: '*'

initdb:
- encoding: UTF8
- data-checksums

pg_hba:
- host replication postgres 127.0.0.1/32 md5
- host replication postgres 172.16.104.112/32 trust
- host replication postgres 0.0.0.0/0 md5
- host all all 0.0.0.0/0 md5

users:
admin:
password: admin
options:
- createrole
- createdb

postgresql:
listen: 0.0.0.0:5433
connect_address: 172.16.104.112:5433
data_dir: /pgsql/data02
bin_dir: /usr/pgsql-15/bin
authentication:
replication:
username: postgres
password: redhat
superuser:
username: postgres
password: redhat
parameters:
unix_socket_directories: '/var/run/postgresql'
superuser_reserved_connections: 13
tcp_keepalives_idle: 60
tcp_keepalives_interval: 10
tcp_keepalives_count: 10
shared_buffers: 2048MB
vacuum_cost_delay: 10
bgwriter_delay: 10ms
wal_writer_delay: 10ms
wal_receiver_status_interval: 1s
hot_standby_feedback: on
log_destination: 'csvlog'
logging_collector: on
log_directory: 'pg_log'
log_truncate_on_rotation: on
log_rotation_age: 1d
log_rotation_size: 10MB
log_checkpoints: on
log_connections: on
log_disconnections: on
log_error_verbosity: verbose
log_timezone: 'PRC'
full_page_writes: on

watchdog:
mode: automatic # Allowed values: off, automatic, required
device: /dev/watchdog
safety_margin: 5

tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false

[root@lee ~]# vi /etc/patroni/patroni03.yml
scope: postgres
namespace: /pg_cluster/
name: lee03

log:
level: INFO
traceback_level: ERROR
dir: /tmp/patroni03
file_num: 10
file_size: 104857600

restapi:
listen: 0.0.0.0:8010
connect_address: 172.16.104.112:8010

etcd:
host: 172.16.104.112:2379

bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
synchronous_mode: true
synchronous_node_count: 2
synchronous_mode_strict: true
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: "on"
logging_collector: 'on'
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
max_connections: 1000
synchronous_commit: on
synchronous_standby_names: '*'

initdb:
- encoding: UTF8
- data-checksums

pg_hba:
- host replication postgres 127.0.0.1/32 md5
- host replication postgres 172.16.104.112/32 trust
- host replication postgres 0.0.0.0/0 md5
- host all all 0.0.0.0/0 md5

users:
admin:
password: admin
options:
- createrole
- createdb

postgresql:
listen: 0.0.0.0:5434
connect_address: 172.16.104.112:5434
data_dir: /pgsql/data03
bin_dir: /usr/pgsql-15/bin
authentication:
replication:
username: postgres
password: redhat
superuser:
username: postgres
password: redhat
parameters:
unix_socket_directories: '/var/run/postgresql'
superuser_reserved_connections: 13
tcp_keepalives_idle: 60
tcp_keepalives_interval: 10
tcp_keepalives_count: 10
shared_buffers: 2048MB
vacuum_cost_delay: 10
bgwriter_delay: 10ms
wal_writer_delay: 10ms
wal_receiver_status_interval: 1s
hot_standby_feedback: on
log_destination: 'csvlog'
logging_collector: on
log_directory: 'pg_log'
log_truncate_on_rotation: on
log_rotation_age: 1d
log_rotation_size: 10MB
log_checkpoints: on
log_connections: on
log_disconnections: on
log_error_verbosity: verbose
log_timezone: 'PRC'
full_page_writes: on

watchdog:
mode: automatic # Allowed values: off, automatic, required
device: /dev/watchdog
safety_margin: 5

tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false

[root@lee ~]# cd /usr/lib/systemd/system
[root@lee system]# vi patroni01.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. It is recommended to use systemd
# "dropin" feature; i.e. create file with suffix .conf under
# /etc/systemd/system/patroni.service.d directory overriding the
# unit's defaults. You can also use "systemctl edit patroni"
# Look at systemd.unit(5) manual page for more info.

[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target

[Service]
Type=simple

User=postgres
Group=postgres

# Read in configuration file if it exists, otherwise proceed
EnvironmentFile=-/etc/patroni_env.conf

# WorkingDirectory=/var/lib/pgsql

# Where to send early-startup messages from the server
# This is normally controlled by the global default set by systemd
#StandardOutput=syslog

# Pre-commands to start watchdog device
# Uncomment if watchdog is part of your patroni setup
ExecStartPre=-/usr/bin/sudo /sbin/modprobe softdog soft_noboot=1
ExecStartPre=-/usr/bin/sudo /bin/chown postgres:postgres /dev/watchdog

# Start the patroni process
ExecStart=/usr/bin/patroni /etc/patroni/patroni01.yml

# Send HUP to reload from patroni.yml
ExecReload=/usr/bin/kill -s HUP $MAINPID

# only kill the patroni process, not it's children, so it will gracefully stop postgres
KillMode=process

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=30

# Do not restart the service if it crashes, we want to manually inspect database on failure
Restart=no

[Install]
WantedBy=multi-user.target
#以下直接复制并修改启动程序里对应的配置文件文件路径即可。
[root@lee system]# cp patroni01.service patroni02.service
[root@lee system]# cp patroni01.service patroni03.service
[root@lee ~]# systemctl daemon-reload

[root@lee ~]# for i in `patroni01 patroni02 patroni03`;do systemctl enable $i;done
[root@lee ~]# for i in `patroni01 patroni02 patroni03`;do systemctl start $i;done
[root@lee ~]# echo "alias patronictl='patronictl -c /etc/patroni/patroni01.yml'" >>/etc/profile
[root@lee ~]# source /etc/profile

[root@lee ~]# vi /etc/haproxy/haproxy.cfg
global
maxconn 1000
pidfile /var/run/haproxy.pid
maxconn 5000
user root
group root
daemon
nbproc 2

defaults
mode tcp
log global
option tcplog
option dontlognull
option redispatch
retries 3
maxconn 1000
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout check 5s

listen stats
mode http
bind *:7000
log global
stats enable
stats refresh 30s
stats uri /
stats realm Private lands
stats auth admin:admin

listen primary
bind *:5000
mode tcp
option tcplog
balance roundrobin
option httpchk /primary
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server lee01 172.16.104.112:5432 maxconn 1000 check port 8008
server lee02 172.16.104.112:5433 maxconn 1000 check port 8009
server lee03 172.16.104.112:5434 maxconn 1000 check port 8010

listen standbys
balance roundrobin
bind *:5001
mode tcp
option tcplog
option httpchk /replica
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server lee01 172.16.104.112:5432 maxconn 1000 check port 8008
server lee02 172.16.104.112:5433 maxconn 1000 check port 8009
server lee03 172.16.104.112:5434 maxconn 1000 check port 8010

[root@lee ~]# systemctl enable haproxy && systemctl start haproxy
[root@lee ~]# systemctl status haproxy

[postgres@lee ~]$ psql "host=172.16.104.112 port=5000 password=redhat" -c 'select inet_server_addr(),inet_server_port(),pg_is_in_recovery()'
[postgres@lee ~]$ psql "host=172.16.104.112 port=5001 password=redhat" -c 'select inet_server_addr(),inet_server_port(),pg_is_in_recovery()'