Patroni是Zalando开发的数据库高可用管理软件，用于编排和自动化PostgreSQL集群的管理过程。Patroni 需要一系列其他组件的支持，通过利用第三方分布式一致性软件，组建并实现数据库高可用方案。
Patroni架构及功能
Patroni架构具有以下特点：
● 结合选主软件进行集群管理。
● 通过yml内容识别集群配置。
● 采用分布式一致性软件（ZooKeeper、etcd、Consul）。
● 支持自动故障转移和手动切换。
● 支持级联复制。
● 支持云环境部署。
Patroni需要etcd集群支持，etcd集群作为Patroni的DCS（分布式配置存储），保存集群成员元数据信息及状态信息。Patroni部署在每个PostgreSQL节点上，负责每个节点的初始化、启动、同步及集群内节点选主等。etcd集群的搭建请参考 etcd集群搭建 。
Partroni集群搭建
本篇将介绍在Rocky Linux 9上用Patroni搭建一个三节点的PostgreSQL集群，三个节点分别为db01、db02、db03，首先在三个节点上分别安装好PostgreSQL(安装方法可参考 PostgreSQL 16源码安装 )，设置好相关环境变量及参数（本例中PostgreSQL用户为postgres）。
一、初始化数据库(仅需在db1节点上进行)
initdb -D$PGDATA-E utf8 -U postgres -W# 创建复制角色create role replica login replication encrypted password 'xxxxxxxx'; # 创建复制槽select * from pg_create_physical_replication_slot('db01'); select * from pg_create_physical_replication_slot('db02'); select * from pg_create_physical_replication_slot('db03'); 检查 postgres=# select slot_name,slot_type from pg_replication_slots; slot_name | slot_type ---------------+----------- db01 | physical db02 | physical db03 | physical (3 rows)
在进行流复制时，注意replication slots的使用，其主要作用是避免备库未应用的WAL日志被移除。replication slots在PostgreSQL 9.4时引入，主要提供一种自动化的方法来确保主机在所有的备机收到WAL段之前不会移除它们，并且主机也不会移除可能导致恢复冲突的行，即使备机断开也是如此。在没有启用 replication slots 的环境中，如果碰到ERROR：requested WAL segment xxxx has already been removed 错误，则解决办法是提前开启归档，或者重做备节点，另外还需要在master上设置 wal_keep_segments 为更大的值。需要注意的是，流复制并不会默认打开replication slots，需要手动配置。
二、安装和配置Patroni
2.1 安装Python（可选）
最新版的patroni需要python3.7及以上版本，请检查系统中的python是否符合要求，如果python版本过低，可自行编译安装，方法如下：
# 编译前需要检查是否有安装openssl-dev包，如果未安装则dnf install libopenssl-devel wget https://www.python.org/ftp/python/3.7.17/Python-3.7.17.tar.xz tar Jxvf Python-3.7.17.tar.xz cd Python-3.7.17 ./configure --prefix=/usr/local/python37 --with-openssl=/usr/local/openssl --enable-sharedCFLAGS=-fPICmakemake install # 升级pip3/usr/local/python37/bin/python3.7 -m pip install --upgrade pip
2.2 安装Patroni
/usr/local/python37/pip3 install psycopg2_binary /usr/local/python37/pip3 install patroni[etcd]
2.3 配置Patroni
注意：以下操作均在postgres用户下进行
在db01、db02、db03三个节点上创建.pgpass文件
su – postgres vi .pgpass db01:5432:*:replica:xxxxxxxx chmod0600 .pgpass
分别在三个节点配置patroni，配置文件保存在postgres用户etc目录下
# db01配置scope: pg_patroni # 集群名namespace: /service/ name: pg_patroni1 # 节点名，三个节点不能相同restapi: listen: db01:8008 # IP地址或者主机名，根据自己的情况设置 connect_address: db01:8008 # IP地址或者主机名，根据自己的情况设置etcd3: hosts: #需自行设定，请勿照抄- etcd01:2379 - etcd02:2379 - etcd03:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 postgresql: use_pg_rewind: true use_slots: true parameters: listen_addresses: '*' port: 5432 max_connections: 1000 superuser_reserved_connections: 3 unix_socket_directories: '.' unix_socket_permissions: 0700 tcp_keepalives_idle: 60 tcp_keepalives_interval: 10 tcp_keepalives_count: 10 shared_buffers: 4GB maintenance_work_mem: 2GB dynamic_shared_memory_type: posix vacuum_cost_delay: 0 bgwriter_delay: 10ms bgwriter_lru_maxpages: 1000 bgwriter_lru_multiplier: 10.0 bgwriter_flush_after: 0 max_worker_processes: 8 max_parallel_workers_per_gather: 12 old_snapshot_threshold: -1 backend_flush_after: 0 synchronous_commit: off full_page_writes: on wal_buffers: 128MB wal_writer_delay: 10ms wal_writer_flush_after: 0 checkpoint_timeout: 30min max_wal_size: 8GB min_wal_size: 2GB checkpoint_completion_target: 0.05 checkpoint_flush_after: 0 max_wal_senders: 8 random_page_cost: 1.3 parallel_tuple_cost: 0 parallel_setup_cost: 0 effective_cache_size: 2GB log_destination: 'csvlog' logging_collector: on log_truncate_on_rotation: on log_checkpoints: on log_connections: on log_disconnections: on log_error_verbosity: verbose log_timezone: 'PRC' vacuum_defer_cleanup_age: 0 wal_level: hot_standby hot_standby: on hot_standby_feedback: off wal_writer_delay: 10ms max_wal_senders: 10 max_standby_archive_delay: 300s max_standby_streaming_delay: 300s autovacuum: on log_autovacuum_min_duration: 0 autovacuum_max_workers: 4 autovacuum_naptime: 45s autovacuum_vacuum_scale_factor: 0.1 autovacuum_analyze_scale_factor: 0.1 autovacuum_freeze_max_age: 1600000000 autovacuum_multixact_freeze_max_age: 1600000000 vacuum_freeze_table_age: 1500000000 vacuum_multixact_freeze_table_age: 1500000000 datestyle: 'iso, mdy' timezone: 'PRC' lc_messages: 'C' lc_monetary: 'C' lc_numeric: 'C' lc_time: 'C' default_text_search_config: 'pg_catalog.english' shared_preload_libraries: 'pg_stat_statements' jit: on jit_provider: 'llvmjit' jit_above_cost: 100000 jit_inline_above_cost: 500000 jit_optimize_above_cost: 500000 max_replication_slots: 10archive_command: /data/pgsql16/data/archive.sh %p %f archive_mode: true initdb: - encoding: UTF8 - data-checksums # 以下请根据自己的情况设置IP地址 pg_hba: - host replication replica x.x.x.0/24 md5 - host replication replica x.x.x.0/24 md5 - host all all 0.0.0.0/0 md5 - host all all 127.0.0.1/32 md5 users: admin: password: admin% options: - createrole - createdb postgresql: listen: 0.0.0.0:5432 connect_address: db01,127.0.0.1 # IP地址或者主机名，根据自己的情况设置 data_dir: /data/pgsql14/data pgpass: /home/postgres/.pgpass authentication: replication: username: replica password: xxxxxxxxx superuser: username: postgres password: xxxxxxxxx parameters: unix_socket_directories: '..'#没有vip，暂时屏蔽#callbacks:#on_start: /home/postgres/sbin/patroni_callback.sh#on_stop: /home/postgres/sbin/patroni_callback.sh#on_role_change: /home/postgres/sbin/patroni_callback.sh use_unix_socket: truetags: nofailover: false noloadbalance: false clonefrom: false nosync: false
其余两个节点的配置文件绝大部分都一样，注意修改以上配置中有注释的内容。
2.4 启动Patroni
分别在三个节点上启动Patroni
/usr/local/python37/bin/patroni ~/etc/postgres-pg01.yml > ~/log/patroni.log 2>&1 &
启动后查看日志文件，看是否有错，并用ps命令检查系统是否有patroni进程。
等三个节点的patroni都启动成功后，可使用patronictl命令查看集群状态。此时db02、db03只是安装了PostgreSQL，还没有做初始化数据库等操作，这些事情都可以使用patroni为我们做：
/usr/local/python37/bin/patronictl -c ~/etc/postgres-pg01.yml reinit [集群名] [数据库节点] # 集群名、数据库节点名分别对应上面配置文件的scope和name字段
三、Patroni日常使用
/usr/local/python37/bin/patronictl -c ~/etc/postgres-pg01.yml –help Usage: patronictl [OPTIONS] COMMAND [ARGS]... Options: -c, --config-file TEXT Configuration file -d, --dcs-url, --dcs TEXT The DCS connect url -k, --insecure Allow connections to SSL sites without certs --help Show this message and exit. Commands: dsn Generate a dsn for the provided member, defaults to a dsn of... edit-config Edit cluster configuration failover Failover to a replica flush Discard scheduled events history Show the history of failovers/switchovers list List the Patroni members for a given Patroni pause Disable auto failover query Query a Patroni PostgreSQL member reinit Reinitialize cluster member reload Reload cluster member configuration remove Remove cluster from DCS restart Restart cluster member resume Resume auto failover show-config Show cluster configuration switchover Switchover to a replica topology Prints ASCII topology for given cluster version Output version of patronictl command or a running Patroni...
日常尽量使用patronictl来维护数据库，比如修改配置、reload配置、重启数据库节点、主备切换等操作。
