pgsql集群搭建主机ip01–192.168.171.4 02–192.168.171.5 03–192.168.171.61.放行必要端口sudo firewall-cmd --permanent --zonepublic \ --add-port5432/tcp \ --add-port6432/tcp \ --add-port2379/tcp \ --add-port8008/tcp \ --add-port2380/tcp # 重载防火墙 sudo firewall-cmd --reload # 查看放行 sudo firewall-cmd --list-ports2.禁用thpecho never /sys/kernel/mm/transparent_hugepage/enabled echo echo never /sys/kernel/mm/transparent_hugepage/enabled /etc/rc.local chmod x /etc/rc.local为什么要禁用 THPTHP 是什么Linux 内核的内存管理特性自动将普通 4KB 内存页合并为 2MB 大页目的是减少 TLB转译后备缓冲器缺失提升内存访问性能为什么数据库尤其是 PostgreSQL要禁用问题说明内存分配延迟THP 的内存合并/拆分是异步的可能在运行时触发导致不可预测的延迟 spikes写放大即使只修改大页中的一小部分数据整个 2MB 页面都会被标记为脏页增加 I/O 负担内存碎片THP 可能导致内存碎片化反而降低性能fork 延迟PostgreSQL 使用多进程架构fork 子进程时 THP 可能增加延迟3.安装 etcd 与 PgBouncerCentOS 7 的官方基础源或 EPEL 源中包含了这两个组件3.1安装 EPEL 源PgBouncer 通常在此源中yum install -y epel-release3.2 安装 etcd 和 pgbounceryum install -y etcd pgbouncer4.安装 Python 3 及 PatroniCentOS 7 自带的 python3 (3.6) 可能会在安装最新版 Patroni 时遇到依赖问题。推荐通过以下方式安装4.1安装 Python3 及开发依赖工具yum install -y python3 python3-devel gcc pidof4.2 升级 pip 并通过 pip 安装 psycopg2 和 patronipip3 install --upgrade pip -i https://pypi.tuna.tsinghua.edu.cn/simple pip3 install psycopg2-binary patroni[etcd3] -i https://pypi.tuna.tsinghua.edu.cn/simple5.部署 etcd 集群5.1修改每台机器的/etc/etcd/etcd.yml。注意将 IP 替换为当前节点的实际内网 IPvim /etc/etcd/etcd.yml 192.168.171.4(注意修改为自己的ip)name: node1>name: node2>name: node3>5.2修改/usr/lib/systemd/system/etcd.servicevim /usr/lib/systemd/system/etcd.service 进去后按ggdG清空原有数据粘贴下面数据[Unit] DescriptionEtcd Server Afternetwork.target [Service] Typenotify ExecStart/usr/bin/etcd --config-file/etc/etcd/etcd.yml Restarton-failure [Install] WantedBymulti-user.target5.3找到并编辑配置文件在 CentOS/RHEL 系统中配置文件通常位于/etc/etcd/etcd.conf你需要修改以下两个参数vim /etc/etcd/etcd.conf参数名当前错误配置建议修改为说明ETCD_LISTEN_CLIENT_URLShttp://localhost:2379http://0.0.0.0:23790.0.0.0表示监听所有网卡允许外部访问ETCD_ADVERTISE_CLIENT_URLShttp://localhost:2379http://本机IP:2379告诉集群其他成员通过这个 IP 来连接我5.4 重启服务修改完三台机器的配置后依次执行systemctl daemon-reload systemctl restart etcd 启动并验证 etcdsystemctl enable etcd systemctl start etcd 验证 etcd 健康状态 (CentOS 7 默认是 v2 API指定 v3 执行验证)ETCDCTL_API3 etcdctl endpoint health --endpoints192.168.171.4:2379,192.168.171.5:2379,192.168.171.6:23796.配置并启动 Patroni 管理 PG14在各个节点创建配置文件例如放于/etc/patroni/patroni.ymlmkdir /etc/patroni vim /etc/patroni/patroni.yml 192.168.171.4修改为自己的ipscope:pg-ha-clusternamespace:/servicename:pg-node-1# 修改为自己集群的ipetcd3:hosts:-192.168.171.4:2379-192.168.171.5:2379-192.168.171.6:2379restapi:listen:0.0.0.0:8008# 修改connect_address:192.168.171.4:8008bootstrap:dcs:ttl:30loop_wait:10retry_timeout:10maximum_lag_on_failover:1048576synchronous_mode:truepostgresql:use_pg_rewind:trueuse_slots:trueinitdb:-encoding:UTF8-data-checksumspg_hba:-host replication replicator 0.0.0.0/0 md5-host all all 0.0.0.0/0 md5users:admin:password:AdminSecurePassword123options:-superuser-createdbpostgresql:listen:0.0.0.0:5432connect_address:192.168.171.4:5432data_dir:/var/lib/pgsql/14/databin_dir:/usr/pgsql-14/binpgpass:/var/lib/pgsql/.pgpassauthentication:replication:username:replicatorpassword:ReplicaPassword123superuser:# 你的pgsql的超级用户和密码username:postgrespassword:123456parameters:password_encryption:md5ssl:offshared_buffers:128MBeffective_cache_size:32GBwork_mem:64MBmaintenance_work_mem:1GBmax_wal_size:2GBmin_wal_size:1GBcheckpoint_completion_target:0.9checkpoint_timeout:15minmax_worker_processes:3max_parallel_workers_per_gather:2max_parallel_workers:3scope:pg-ha-clusternamespace:/servicename:pg-node-2etcd3:hosts:-192.168.171.4:2379-192.168.171.5:2379-192.168.171.6:2379restapi:listen:0.0.0.0:8008connect_address:192.168.171.5:8008bootstrap:dcs:ttl:30loop_wait:10retry_timeout:10maximum_lag_on_failover:1048576synchronous_mode:truepostgresql:use_pg_rewind:trueuse_slots:trueinitdb:-encoding:UTF8-data-checksumspg_hba:-host replication replicator 0.0.0.0/0 md5-host all all 0.0.0.0/0 md5users:admin:password:AdminSecurePassword123options:-superuser-createdbpostgresql:listen:0.0.0.0:5432connect_address:192.168.171.5:5432data_dir:/var/lib/pgsql/14/databin_dir:/usr/pgsql-14/binpgpass:/var/lib/pgsql/.pgpassauthentication:replication:username:replicatorpassword:ReplicaPassword123superuser:username:postgrespassword:123456parameters:password_encryption:md5ssl:offshared_buffers:128MBeffective_cache_size:32GBwork_mem:64MBmaintenance_work_mem:1GBmax_wal_size:2GBmin_wal_size:1GBcheckpoint_completion_target:0.9checkpoint_timeout:15minmax_worker_processes:3max_parallel_workers_per_gather:2max_parallel_workers:3scope:pg-ha-clusternamespace:/servicename:pg-node-3etcd3:hosts:-192.168.171.4:2379-192.168.171.5:2379-192.168.171.6:2379restapi:listen:0.0.0.0:8008connect_address:192.168.171.6:8008bootstrap:dcs:ttl:30loop_wait:10retry_timeout:10maximum_lag_on_failover:1048576synchronous_mode:truepostgresql:use_pg_rewind:trueuse_slots:trueinitdb:-encoding:UTF8-data-checksumspg_hba:-host replication replicator 0.0.0.0/0 md5-host all all 0.0.0.0/0 md5users:admin:password:AdminSecurePassword123options:-superuser-createdbpostgresql:listen:0.0.0.0:5432connect_address:192.168.171.6:5432data_dir:/var/lib/pgsql/14/databin_dir:/usr/pgsql-14/binpgpass:/var/lib/pgsql/.pgpassauthentication:replication:username:replicatorpassword:ReplicaPassword123superuser:username:postgrespassword:123456parameters:password_encryption:md5ssl:offshared_buffers:128MBeffective_cache_size:32GBwork_mem:64MBmaintenance_work_mem:1GBmax_wal_size:2GBmin_wal_size:1GBcheckpoint_completion_target:0.9checkpoint_timeout:15minmax_worker_processes:3max_parallel_workers_per_gather:2max_parallel_workers:3 2.确保存储目录及权限正确不需要再生成 SSL 证书mkdir -p /var/lib/pgsql/14/data /etc/patroni chown -R postgres:postgres /var/lib/pgsql/ chmod 700 /var/lib/pgsql/14/data 3.配置 CentOS 7 Systemd 服务来管理 Patronivim /etc/systemd/system/patroni.service[Unit] DescriptionPatroni Orchestrator for PostgreSQL Afternetwork.target etcd.service [Service] Typesimple Userpostgres Grouppostgres ExecStart/usr/local/bin/patroni /etc/patroni/patroni.yml ExecReload/bin/kill -HUP $MAINPID KillModeprocess TimeoutSec30 Restarton-failure RestartSec10 [Install] WantedBymulti-user.target 4.启动 Patronisystemctl daemon-reload systemctl enable patroni systemctl start patroni 5.切换到 postgres 用户执行查看su - postgres -c patronictl -c /etc/patroni/patroni.yml list 查看日志journalctl -u patroni -n 100 --no-pager
pgsql集群搭建（Patroni + etcd ）
pgsql集群搭建主机ip01–192.168.171.4 02–192.168.171.5 03–192.168.171.61.放行必要端口sudo firewall-cmd --permanent --zonepublic \ --add-port5432/tcp \ --add-port6432/tcp \ --add-port2379/tcp \ --add-port8008/tcp \ --add-port2380/tcp # 重载防火墙 sudo firewall-cmd --reload # 查看放行 sudo firewall-cmd --list-ports2.禁用thpecho never /sys/kernel/mm/transparent_hugepage/enabled echo echo never /sys/kernel/mm/transparent_hugepage/enabled /etc/rc.local chmod x /etc/rc.local为什么要禁用 THPTHP 是什么Linux 内核的内存管理特性自动将普通 4KB 内存页合并为 2MB 大页目的是减少 TLB转译后备缓冲器缺失提升内存访问性能为什么数据库尤其是 PostgreSQL要禁用问题说明内存分配延迟THP 的内存合并/拆分是异步的可能在运行时触发导致不可预测的延迟 spikes写放大即使只修改大页中的一小部分数据整个 2MB 页面都会被标记为脏页增加 I/O 负担内存碎片THP 可能导致内存碎片化反而降低性能fork 延迟PostgreSQL 使用多进程架构fork 子进程时 THP 可能增加延迟3.安装 etcd 与 PgBouncerCentOS 7 的官方基础源或 EPEL 源中包含了这两个组件3.1安装 EPEL 源PgBouncer 通常在此源中yum install -y epel-release3.2 安装 etcd 和 pgbounceryum install -y etcd pgbouncer4.安装 Python 3 及 PatroniCentOS 7 自带的 python3 (3.6) 可能会在安装最新版 Patroni 时遇到依赖问题。推荐通过以下方式安装4.1安装 Python3 及开发依赖工具yum install -y python3 python3-devel gcc pidof4.2 升级 pip 并通过 pip 安装 psycopg2 和 patronipip3 install --upgrade pip -i https://pypi.tuna.tsinghua.edu.cn/simple pip3 install psycopg2-binary patroni[etcd3] -i https://pypi.tuna.tsinghua.edu.cn/simple5.部署 etcd 集群5.1修改每台机器的/etc/etcd/etcd.yml。注意将 IP 替换为当前节点的实际内网 IPvim /etc/etcd/etcd.yml 192.168.171.4(注意修改为自己的ip)name: node1>name: node2>name: node3>5.2修改/usr/lib/systemd/system/etcd.servicevim /usr/lib/systemd/system/etcd.service 进去后按ggdG清空原有数据粘贴下面数据[Unit] DescriptionEtcd Server Afternetwork.target [Service] Typenotify ExecStart/usr/bin/etcd --config-file/etc/etcd/etcd.yml Restarton-failure [Install] WantedBymulti-user.target5.3找到并编辑配置文件在 CentOS/RHEL 系统中配置文件通常位于/etc/etcd/etcd.conf你需要修改以下两个参数vim /etc/etcd/etcd.conf参数名当前错误配置建议修改为说明ETCD_LISTEN_CLIENT_URLShttp://localhost:2379http://0.0.0.0:23790.0.0.0表示监听所有网卡允许外部访问ETCD_ADVERTISE_CLIENT_URLShttp://localhost:2379http://本机IP:2379告诉集群其他成员通过这个 IP 来连接我5.4 重启服务修改完三台机器的配置后依次执行systemctl daemon-reload systemctl restart etcd 启动并验证 etcdsystemctl enable etcd systemctl start etcd 验证 etcd 健康状态 (CentOS 7 默认是 v2 API指定 v3 执行验证)ETCDCTL_API3 etcdctl endpoint health --endpoints192.168.171.4:2379,192.168.171.5:2379,192.168.171.6:23796.配置并启动 Patroni 管理 PG14在各个节点创建配置文件例如放于/etc/patroni/patroni.ymlmkdir /etc/patroni vim /etc/patroni/patroni.yml 192.168.171.4修改为自己的ipscope:pg-ha-clusternamespace:/servicename:pg-node-1# 修改为自己集群的ipetcd3:hosts:-192.168.171.4:2379-192.168.171.5:2379-192.168.171.6:2379restapi:listen:0.0.0.0:8008# 修改connect_address:192.168.171.4:8008bootstrap:dcs:ttl:30loop_wait:10retry_timeout:10maximum_lag_on_failover:1048576synchronous_mode:truepostgresql:use_pg_rewind:trueuse_slots:trueinitdb:-encoding:UTF8-data-checksumspg_hba:-host replication replicator 0.0.0.0/0 md5-host all all 0.0.0.0/0 md5users:admin:password:AdminSecurePassword123options:-superuser-createdbpostgresql:listen:0.0.0.0:5432connect_address:192.168.171.4:5432data_dir:/var/lib/pgsql/14/databin_dir:/usr/pgsql-14/binpgpass:/var/lib/pgsql/.pgpassauthentication:replication:username:replicatorpassword:ReplicaPassword123superuser:# 你的pgsql的超级用户和密码username:postgrespassword:123456parameters:password_encryption:md5ssl:offshared_buffers:128MBeffective_cache_size:32GBwork_mem:64MBmaintenance_work_mem:1GBmax_wal_size:2GBmin_wal_size:1GBcheckpoint_completion_target:0.9checkpoint_timeout:15minmax_worker_processes:3max_parallel_workers_per_gather:2max_parallel_workers:3scope:pg-ha-clusternamespace:/servicename:pg-node-2etcd3:hosts:-192.168.171.4:2379-192.168.171.5:2379-192.168.171.6:2379restapi:listen:0.0.0.0:8008connect_address:192.168.171.5:8008bootstrap:dcs:ttl:30loop_wait:10retry_timeout:10maximum_lag_on_failover:1048576synchronous_mode:truepostgresql:use_pg_rewind:trueuse_slots:trueinitdb:-encoding:UTF8-data-checksumspg_hba:-host replication replicator 0.0.0.0/0 md5-host all all 0.0.0.0/0 md5users:admin:password:AdminSecurePassword123options:-superuser-createdbpostgresql:listen:0.0.0.0:5432connect_address:192.168.171.5:5432data_dir:/var/lib/pgsql/14/databin_dir:/usr/pgsql-14/binpgpass:/var/lib/pgsql/.pgpassauthentication:replication:username:replicatorpassword:ReplicaPassword123superuser:username:postgrespassword:123456parameters:password_encryption:md5ssl:offshared_buffers:128MBeffective_cache_size:32GBwork_mem:64MBmaintenance_work_mem:1GBmax_wal_size:2GBmin_wal_size:1GBcheckpoint_completion_target:0.9checkpoint_timeout:15minmax_worker_processes:3max_parallel_workers_per_gather:2max_parallel_workers:3scope:pg-ha-clusternamespace:/servicename:pg-node-3etcd3:hosts:-192.168.171.4:2379-192.168.171.5:2379-192.168.171.6:2379restapi:listen:0.0.0.0:8008connect_address:192.168.171.6:8008bootstrap:dcs:ttl:30loop_wait:10retry_timeout:10maximum_lag_on_failover:1048576synchronous_mode:truepostgresql:use_pg_rewind:trueuse_slots:trueinitdb:-encoding:UTF8-data-checksumspg_hba:-host replication replicator 0.0.0.0/0 md5-host all all 0.0.0.0/0 md5users:admin:password:AdminSecurePassword123options:-superuser-createdbpostgresql:listen:0.0.0.0:5432connect_address:192.168.171.6:5432data_dir:/var/lib/pgsql/14/databin_dir:/usr/pgsql-14/binpgpass:/var/lib/pgsql/.pgpassauthentication:replication:username:replicatorpassword:ReplicaPassword123superuser:username:postgrespassword:123456parameters:password_encryption:md5ssl:offshared_buffers:128MBeffective_cache_size:32GBwork_mem:64MBmaintenance_work_mem:1GBmax_wal_size:2GBmin_wal_size:1GBcheckpoint_completion_target:0.9checkpoint_timeout:15minmax_worker_processes:3max_parallel_workers_per_gather:2max_parallel_workers:3 2.确保存储目录及权限正确不需要再生成 SSL 证书mkdir -p /var/lib/pgsql/14/data /etc/patroni chown -R postgres:postgres /var/lib/pgsql/ chmod 700 /var/lib/pgsql/14/data 3.配置 CentOS 7 Systemd 服务来管理 Patronivim /etc/systemd/system/patroni.service[Unit] DescriptionPatroni Orchestrator for PostgreSQL Afternetwork.target etcd.service [Service] Typesimple Userpostgres Grouppostgres ExecStart/usr/local/bin/patroni /etc/patroni/patroni.yml ExecReload/bin/kill -HUP $MAINPID KillModeprocess TimeoutSec30 Restarton-failure RestartSec10 [Install] WantedBymulti-user.target 4.启动 Patronisystemctl daemon-reload systemctl enable patroni systemctl start patroni 5.切换到 postgres 用户执行查看su - postgres -c patronictl -c /etc/patroni/patroni.yml list 查看日志journalctl -u patroni -n 100 --no-pager
