基于patroni的Postgres高可用集群部署

--版本信息

etcd-v3.3.11-linux-amd64.tar.gz
PostgreSQL 12.5 源码编译版本
--patroni及依赖包
python3.6
psycopg2-binary==2.8.6
patroni 2.0.1

--------------------------------------------------------------------------安装------------------------------------------------------------------------------------------
---------------------------etcd的安装只需要在DCS节点中安装，安装PG的容器不需要装，所以不需要镜像，DCS节点上DBA手工安装即可--------------------
--------------------------------------------------------------PG包和patroni安装包需要在PG实例容器上安装-------------------------------------------------------
--安装etcd(root用户)
上传 etcd-v3.3.11-linux-amd64.tar.gz 到/usr/local并解压 tar -xvf etcd-v3.3.11-linux-amd64.tar.gz
tar -zxvf etcd-v3.3.11-linux-amd64.tar.gz
启动etcd:
nohup /usr/local/etcd-v3.3.11-linux-amd64/etcd --config-file=/home/etcd/conf/pg_patroni_etcd.conf.yml >/home/etcd/log/pg_patroni_etcd.log 2>&1 &

 

--etcd配置文件pg_patroni_etcd.yml

# This is the configuration file for the etcd server.

# Human-readable name for this member.
name: 'pg_patroni_etcd01'

# Path to the data directory.
data-dir: /home/etcd/data

# Path to the dedicated wal directory.
wal-dir:

# Number of committed transactions to trigger a snapshot to disk.
snapshot-count: 10000

# Time (in milliseconds) of a heartbeat interval.
heartbeat-interval: 100

# Time (in milliseconds) for an election to timeout.
election-timeout: 1000

# Raise alarms when backend size exceeds the given quota. 0 means use the
# default quota.
quota-backend-bytes: 0

# List of comma separated URLs to listen on for peer traffic.
listen-peer-urls: http://192.168.56.91:2380

# List of comma separated URLs to listen on for client traffic.
listen-client-urls: http://192.168.56.91:2379,http://127.0.0.1:2379

# Maximum number of snapshot files to retain (0 is unlimited).
max-snapshots: 5

# Maximum number of wal files to retain (0 is unlimited).
max-wals: 5

# Comma-separated white list of origins for CORS (cross-origin resource sharing).
cors:

# List of this member's peer URLs to advertise to the rest of the cluster.
# The URLs needed to be a comma-separated list.
initial-advertise-peer-urls: http://192.168.56.91:2380

# List of this member's client URLs to advertise to the public.
# The URLs needed to be a comma-separated list.
#advertise-client-urls: http://192.168.56.91:2379,http://127.0.0.1:2379
advertise-client-urls: http://192.168.56.91:2379

# Discovery URL used to bootstrap the cluster.
discovery:

# Valid values include 'exit', 'proxy'
discovery-fallback: 'proxy'

# HTTP proxy to use for traffic to discovery service.
discovery-proxy:

# DNS domain used to bootstrap initial cluster.
discovery-srv:

# Initial cluster configuration for bootstrapping.
initial-cluster: pg_patroni_etcd01=http://192.168.56.91:2380,pg_patroni_etcd02=http://192.168.56.92:2380,pg_patroni_etcd03=http://192.168.56.93:2380

# Initial cluster token for the etcd cluster during bootstrap.
initial-cluster-token: 'etcd-cluster-token'

# Initial cluster state ('new' or 'existing').
initial-cluster-state: 'new'

# Reject reconfiguration requests that would cause quorum loss.
strict-reconfig-check: false

# Accept etcd V2 client requests
enable-v2: true

# Enable runtime profiling data via HTTP server
enable-pprof: true

# Valid values include 'on', 'readonly', 'off'
proxy: 'off'

# Time (in milliseconds) an endpoint will be held in a failed state.
proxy-failure-wait: 5000

# Time (in milliseconds) of the endpoints refresh interval.
proxy-refresh-interval: 30000

# Time (in milliseconds) for a dial to timeout.
proxy-dial-timeout: 1000

# Time (in milliseconds) for a write to timeout.
proxy-write-timeout: 5000

# Time (in milliseconds) for a read to timeout.
proxy-read-timeout: 0

client-transport-security:
# Path to the client server TLS cert file.
cert-file:

# Path to the client server TLS key file.
key-file:

# Enable client cert authentication.
client-cert-auth: false

# Path to the client server TLS trusted CA cert file.
trusted-ca-file:

# Client TLS using generated certificates
auto-tls: false

peer-transport-security:
# Path to the peer server TLS cert file.
cert-file:

# Path to the peer server TLS key file.
key-file:

# Enable peer client cert authentication.
client-cert-auth: false

# Path to the peer server TLS trusted CA cert file.
trusted-ca-file:

# Peer TLS using generated certificates.
auto-tls: false

# Enable debug-level logging for etcd.
debug: false

logger: zap

# Specify 'stdout' or 'stderr' to skip journald logging even when running under systemd.
log-outputs: [stderr]

# Force to create a new one member cluster.
force-new-cluster: false

auto-compaction-mode: periodic
auto-compaction-retention: "1"

--PG的安装(root用户)
上传postgres_12.5_centos7_x86_64.tar.gz 解压到 /usr/local目录即可
tar -xvf postgres_12.5_centos7_x86_64.tar.gz
创建postgres用户组，postgres 用户
groupadd postgres
useradd -g postgres postgres
echo 用户的密码 | passwd --stdin postgres
chown -R postgres:postgres /usr/local/postgresql_12.5

--安装patroni(root用户)
安装 python3.6, 新版本patroni依赖python3
ls /usr/bin/python*
yum install -y python36

升级pip版本
wget https://bootstrap.pypa.io/get-pip.py
python3 get-pip.py
/usr/local/bin/pip --version

安装patroni依赖包 psycopg2-binary 2.8.6
/usr/local/bin/pip install psycopg2-binary==2.8.6

上传并解压patroni_etcd_201.tar.gz，先将包中的python-etcd-0.4.5.tar.gz解压并安装python-etcd
tar -xvf python-etcd-0.4.5.tar.gz
cd python-etcd-0.4.5
python3 setup.py install

安装patroni
[root@localhost patroni_etcd_201]# ls
click-7.1.2-py2.py3-none-any.whl prettytable-2.0.0-py3-none-any.whl python-etcd-0.4.5 setuptools-52.0.0-py3-none-any.whl wcwidth-0.2.5-py2.py3-none-any.whl
dnspython-2.1.0-py3-none-any.whl psutil-5.8.0-cp36-cp36m-manylinux2010_x86_64.whl python-etcd-0.4.5.tar.gz six-1.15.0-py2.py3-none-any.whl ydiff-1.2.tar.gz
patroni-2.0.1-py3-none-any.whl python_dateutil-2.8.1-py2.py3-none-any.whl PyYAML-5.4.1-cp36-cp36m-manylinux1_x86_64.whl urllib3-1.26.2-py2.py3-none-any.whl
[root@localhost patroni_etcd_201]#

/usr/local/bin/pip install *.whl

[root@10-84-173-5.test-test.bjht pip_pack]# /usr/local/bin/pip list
Package Version
--------------- -------
click 7.1.2
dnspython 2.1.0
patroni 2.0.1
pip 20.3.3
prettytable 2.0.0
psutil 5.8.0
psycopg2-binary 2.8.6
python-dateutil 2.8.1
python-etcd 0.4.5
PyYAML 5.3.1
setuptools 51.3.3
six 1.15.0
urllib3 1.26.2
wcwidth 0.2.5
wheel 0.36.2
ydiff 1.2
[root@10-84-173-5.test-test.bjht pip_pack]#
python-etcd 0.4.5，psycopg2-binary 2.8.6，PyYAML 5.3.1，patroni 2.0.1 这些包没有一定会报错

[root@10-84-173-5.test-test.bjht pip_pack]# patroni --version
patroni 2.0.1
[root@10-84-173-5.test-test.bjht pip_pack]#

--启动patroni
mkdir -p /home/service/var/pgdata
chown postgres:postgres -R /home/service/var/pgdata
chmod -R 700 /home/service/var/pgdata

--环境变量
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/postgresql_12.5/bin/
export PATH
export LD_LIBRARY_PATH=/usr/local/postgresql_12.5/lib
export PATRONICTL_CONFIG_FILE=/home/postgres/patroni/patroni.yml

patroni /home/postgres/patroni/patroni.yml > /home/postgres/patroni/pg_patroni.log 2>&1 &

 

patroni配置文件patroni.yml:

scope: pgsql
namespace: /service/
name: pg01

restapi:
listen: 192.168.56.94:8008
connect_address: 192.168.56.94:8008
# certfile: /etc/ssl/certs/ssl-cert-snakeoil.pem
# keyfile: /etc/ssl/private/ssl-cert-snakeoil.key
# authentication:
# username: username
# password: password

# ctl:
# insecure: false # Allow connections to SSL sites without certs
# certfile: /etc/ssl/certs/ssl-cert-snakeoil.pem
# cacert: /etc/ssl/certs/ssl-cacert-snakeoil.pem

etcd:
#Provide host to do the initial discovery of the cluster topology:
hosts: 192.168.56.91:2379,192.168.56.92:2379,192.168.56.93:2379
#Or use "hosts" to provide multiple endpoints
#Could be a comma separated string:
#hosts: host1:port1,host2:port2
#or an actual yaml list:
#hosts:
#- host1:port1
#- host2:port2
#Once discovery is complete Patroni will use the list of advertised clientURLs
#It is possible to change this behavior through by setting:
#use_proxies: true

#raft:
# data_dir: .
# self_addr: 127.0.0.1:2222
# partner_addrs:
# - 127.0.0.1:2223
# - 127.0.0.1:2224

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: 10
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
# master_start_timeout: 300
# synchronous_mode: false
#standby_cluster:
#host: 127.0.0.1
#port: 1111
#primary_slot_name: patroni
postgresql:
# use_pg_rewind: true
# use_slots: true
parameters:
# max_connections:88 不能在这里配置max_connections，否则show-config会不显示
# wal_level: hot_standby
# hot_standby: "on"
# wal_keep_segments: 8
# max_wal_senders: 10
# max_replication_slots: 10
# wal_log_hints: "on"
# archive_mode: "on"
# archive_timeout: 1800s
# archive_command: mkdir -p ../wal_archive && test ! -f ../wal_archive/%f && cp %p ../wal_archive/%f
# recovery_conf:
# restore_command: cp ../wal_archive/%f %p

# 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'
# For kerberos gss based connectivity (discard @.*$)
#- host replication replicator 127.0.0.1/32 gss include_realm=0
#- host all all 0.0.0.0/0 gss include_realm=0
- host replication repl 0.0.0.0/0 md5
- host all all 0.0.0.0/0 md5
# - hostssl all all 0.0.0.0/0 md5

# Additional script to be launched after initial cluster creation (will be passed the connection URL as parameter)
# post_init: /usr/local/bin/setup_cluster.sh

# Some additional users users which needs to be created after initializing new cluster
# users:
# admin:
# password: admin
# options:
# - createrole
# - createdb

postgresql:
listen: 0.0.0.0:54322
connect_address: 192.168.56.94:54322
data_dir: /home/service/var/pgdata
bin_dir: /usr/local/postgresql_12.5/bin
# bin_dir:
# config_dir:
pgpass: /tmp/pgpassfile
authentication:
replication:
username: repl
password: "oracle"
superuser:
username: postgres
password: "oracle"
# Server side kerberos spn
# krbsrvname: postgres
parameters:
# Fully qualified kerberos ticket file for the running user
# same as KRB5CCNAME used by the GSS
# krb_server_keyfile: /var/spool/keytabs/postgres
#unix_socket_directories: '.'
# Additional fencing script executed after acquiring the leader lock but before promoting the replica
#pre_promote: /path/to/pre_promote.sh
callbacks:
on_role_change: /bin/bash /home/postgres/pg_role_change.sh

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

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