PostgreSQL高可用集群部署和配置指南

#作者：程宏斌

下载安装

yum install -y gcc gcc-c++ make readline-devel zlib-devel openssl-devel libxml2-devel libxslt-devel bzip2 bison libicu-devel flexwget https://ftp.postgresql.org/pub/source/v18.0/postgresql-18.0.tar.gztar xf postgresql-18.0.tar.gzcd postgresql-18.0

升级OpenSSL步骤

OpenSSL 1.0.2k-fips太旧，不支持TLS 1.3，缺少SSL_CTX_set_ciphersuites需要升级

yum groupinstall -y “Development Tools”yum install -y perl-core zlib-devel

下载并编译 OpenSSL ≥ 1.1.1cd /tmpwget https://www.openssl.org/source/openssl-1.1.1w.tar.gztar -xzf openssl-1.1.1w.tar.gzcd openssl-1.1.1w# 配置安装路径（避免影响系统 OpenSSL）./config --prefix=/usr/local/ssl --openssldir=/usr/local/ssl shared zlib# 编译并安装make -j$(nproc)sudo make install配置环境变量（临时或永久）# 临时生效（当前 shell）export PATH="/usr/local/ssl/bin:$PATH"export LD_LIBRARY_PATH="/usr/local/ssl/lib:$LD_LIBRARY_PATH"export PKG_CONFIG_PATH="/usr/local/ssl/lib/pkgconfig:$PKG_CONFIG_PATH"# 永久生效（可选）echo 'export PATH="/usr/local/ssl/bin:$PATH"' >> ~/.bashrcecho 'export LD_LIBRARY_PATH="/usr/local/ssl/lib:$LD_LIBRARY_PATH"' >> ~/.bashrcecho 'export PKG_CONFIG_PATH="/usr/local/ssl/lib/pkgconfig:$PKG_CONFIG_PATH"' >> ~/.bashrcsource ~/.bashrc#将库路径加入系统动态链接器配置echo "/usr/local/ssl/lib" > /etc/ld.so.conf.d/openssl-1.1.1.confldconfig

编译安装

./configure   --prefix=/usr/local/pgsql   --with-openssl   CPPFLAGS="-I/usr/local/ssl/include"   LDFLAGS="-L/usr/local/ssl/lib"make -j$(nproc)make install

初始化启动

useradd postgres

chown -R postgres:postgres /shaxiang/pg

chown -R postgres:postgres /usr/local/pgsql

su - postgres

/usr/local/pgsql/bin/initdb -D /shaxiang/pg/data --locale=en_US.UTF-8 -E UTF8

/usr/local/pgsql/bin/pg_ctl -D /shaxiang/pg/data -l logfile start #测试启动

修改添加配置

Primary（pg1）上

vim /shaxiang/pg/data/postgresql.conf# 连接与监听listen_addresses = '*'port = 5432# WAL / replicationwal_level = replicamax_wal_senders = 10max_replication_slots = 10                # 如果用 replication slotswal_keep_size = 1GB                        # 取决于 WAL 产生速度与网络延迟archive_mode = onarchive_command = 'test ! -f /data/pgsql/archive/%f && cp %p /data/pgsql/archive/%f'# 热备hot_standby = on# 可选：同步复制策略（见下）synchronous_commit = onsynchronous_standby_names = 'ANY1 (pg2,pg3)'#允许从库连接做复制vim /shaxiang/pg/data/pg_hba.confhost    replication    replicator    192.168.123.0/24    md5# 允许管理/客户端访问（按需）host    all            all     0.0.0.0/0           md5#创建复制用户CREATE USER replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD '123456';重启 Primary/usr/local/pgsql/bin/pg_ctl restart -D /shaxiang/pg/data

在Standby（pg2、pg3）上准备数据

（使用 pg_basebackup）

su - postgres

清理目标目录（如有旧数据）：rm -rf /shaxiang/pg/data/*

/usr/local/pgsql/bin/pg_basebackup -h 192.168.123.70 -U replicator -D /shaxiang/pg/data -Fp -Xs -P -R

查看data下是不是有生成同步的数据

如果配置同步（两个从）

vim postgresql.auto.confprimary_conninfo = 'user=replicator password=123456 host=192.168.123.70 port=5432 application_name=standby1 sslmode=prefer'primary_conninfo = 'user=replicator password=123456 host=192.168.123.70 port=5432 application_name=standby2 sslmode=prefer'

验证复制状态（在Primary上）

SELECT client_addr, application_name, state, sync_state FROM pg_stat_replication;

sync_state为sync表示达到同步复制策略；async为异步。quorum为同步组

state显示streaming表示连接中。

配置systemd管理

/etc/systemd/system/postgresql.service[Unit]Description=PostgreSQL database serverAfter=network.target[Service]Type=forkingUser=postgresGroup=postgresExecStart=/usr/local/pgsql/bin/pg_ctl start -D /shaxiang/pg/data -l /shaxiang/pg/logfileExecStop=/usr/local/pgsql/bin/pg_ctl stop -D /shaxiang/pg/dataExecReload=/usr/local/pgsql/bin/pg_ctl reload -D /shaxiang/pg/dataPIDFile=/shaxiang/pg/data/postmaster.pidRestart=on-failure[Install]WantedBy=multi-user.target

启用并启动：（可能需要提前kill之前启动的pg进程，启动异常请查看/shaxiang/pg/logfile）

systemctl daemon-reloadsystemctl enable postgresqlsystemctl start postgresqlsystemctl status postgresql