postgresql高可用集群pgpool-II

目录

一、pgpool-II概述

核心功能

架构模式

二、环境信息

三、部署postgresql数据库

下载yum仓库与依赖包

安装postgresql

创建归档目录

初始化主数据库

配置文件postgresql.conf修改

创建数据库用户

配置pg_hba.conf

操作系统配置免密

repl复制用户免密

四、部署pgpool-II

安装依赖包

排除pg仓库的pgpool包

安装pgpool-II

创建pgpool_node_id

pgpool.conf配置文件详解

pgpool.conf配置文件示例

pgpool-II启动与关闭

五、创建测试表

六、pgpool-II使用

pcp_recovery_node 创建备份服务器

负载均衡测试

Failover故障转移

在线恢复

七、问题解决

一、pgpool-II概述

        pgpool-II 是一个用于 PostgreSQL 数据库的中间件工具，提供连接池、负载均衡、自动故障转移和高可用性等功能。它充当客户端和 PostgreSQL 服务器之间的代理，优化数据库性能并增强可靠性。

核心功能

连接池
        pgpool-II 维护一个数据库连接池，复用客户端连接以减少频繁建立和断开连接的开销，适合高并发场景。

负载均衡
        在多个 PostgreSQL 服务器间分发读查询（SELECT 语句），基于配置的权重分配请求，提升整体吞吐量。写操作（INSERT/UPDATE/DELETE）默认发送到主节点。

自动故障转移
        结合流复制或逻辑复制，当主节点故障时，pgpool-II 可自动提升备节点为新主节点，确保服务连续性。需配合 watchdog 模块实现自身高可用。

查询缓存
       可选功能，缓存常用查询结果，减少重复计算和数据库负载，适用于读多写少的场景。

架构模式

主从模式（Streaming Replication）
        pgpool-II 与 PostgreSQL 的流复制结合，实现读写分离和故障转移。主节点处理写操作，从节点处理读操作。

并行查询模式
        通过数据分片（Sharding）将查询分发到多个节点并行执行，适合大规模数据分析。需配合 PostgreSQL 的外表功能（Foreign Data Wrapper）。

二、环境信息

postgresql环境信息

pgpool-II环境信息

三、部署postgresql数据库

# pg62、pg63、pg64服务器执行操作。yum 默认安装postgresql在这个目录下：/var/lib/pgsql

下载yum仓库与依赖包

# postgresql yum仓库 yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm # libzstd依赖包下载安装 wget https://archives.fedoraproject.org/pub/archive/epel/7/x86_64/Packages/l/libzstd-1.5.5-1.el7.x86_64.rpm wget https://archives.fedoraproject.org/pub/archive/epel/7/x86_64/Packages/l/libzstd-devel-1.5.5-1.el7.x86_64.rpm rpm -ivh libzstd-1.5.5-1.el7.x86_64.rpm libzstd-devel-1.5.5-1.el7.x86_64.rpm

安装postgresql

[root@pg62 yum.repos.d]# yum install -y postgresql15-server Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager This system is not registered with an entitlement server. You can use subscription-manager to register. Resolving Dependencies --> Running transaction check ---> Package postgresql15-server.x86_64 0:15.15-4PGDG.rhel7 will be installed --> Processing Dependency: postgresql15-libs(x86-64) = 15.15-4PGDG.rhel7 for package: postgresql15-server-15.15-4PGDG.rhel7.x86_64 --> Processing Dependency: postgresql15(x86-64) = 15.15-4PGDG.rhel7 for package: postgresql15-server-15.15-4PGDG.rhel7.x86_64 --> Processing Dependency: libpq.so.5()(64bit) for package: postgresql15-server-15.15-4PGDG.rhel7.x86_64 --> Running transaction check ---> Package postgresql15.x86_64 0:15.15-4PGDG.rhel7 will be installed ---> Package postgresql15-libs.x86_64 0:15.15-4PGDG.rhel7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================================================================= Package Arch Version Repository Size ============================================================================================================================================================= Installing: postgresql15-server x86_64 15.15-4PGDG.rhel7 pgdg15 5.9 M Installing for dependencies: postgresql15 x86_64 15.15-4PGDG.rhel7 pgdg15 1.6 M postgresql15-libs x86_64 15.15-4PGDG.rhel7 pgdg15 290 k Transaction Summary ============================================================================================================================================================= Install 1 Package (+2 Dependent packages) Total size: 7.8 M Total download size: 5.9 M Installed size: 34 M Downloading packages: No Presto metadata available for pgdg15 warning: /var/cache/yum/x86_64/7Server/pgdg15/packages/postgresql15-server-15.15-4PGDG.rhel7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 73e3b907: NOKEY Public key for postgresql15-server-15.15-4PGDG.rhel7.x86_64.rpm is not installed postgresql15-server-15.15-4PGDG.rhel7.x86_64.rpm | 5.9 MB 00:00:29 Retrieving key from file:///etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL7 Importing GPG key 0x73E3B907: Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>" Fingerprint: f245 f0bf 96ac 1827 44ca ff2e 64fa ce11 73e3 b907 Package : pgdg-redhat-repo-42.0-38PGDG.noarch (installed) From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL7 Running transaction check Running transaction test Transaction test succeeded Running transaction Warning: RPMDB altered outside of yum. Installing : postgresql15-libs-15.15-4PGDG.rhel7.x86_64 1/3 Installing : postgresql15-15.15-4PGDG.rhel7.x86_64 2/3 Installing : postgresql15-server-15.15-4PGDG.rhel7.x86_64 3/3 Verifying : postgresql15-server-15.15-4PGDG.rhel7.x86_64 1/3 Verifying : postgresql15-15.15-4PGDG.rhel7.x86_64 2/3 Verifying : postgresql15-libs-15.15-4PGDG.rhel7.x86_64 3/3 Installed: postgresql15-server.x86_64 0:15.15-4PGDG.rhel7 Dependency Installed: postgresql15.x86_64 0:15.15-4PGDG.rhel7 postgresql15-libs.x86_64 0:15.15-4PGDG.rhel7 Complete! [root@pg62 yum.repos.d]#

创建归档目录

#创建归档目录，pg62、pg63、pg64服务器执行 [all servers]# su - postgres [all servers]$ mkdir /var/lib/pgsql/archivedir

初始化主数据库

[pg62]# su - postgres
[pg62]$ /usr/pgsql-15/bin/initdb -D $PGDATA

#初始化主数据库，pg62节点操作 su - postgres -bash-4.2$ /usr/pgsql-15/bin/initdb -D $PGDATA 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 database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/lib/pgsql/15/data ... 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 initdb: hint: 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: /usr/pgsql-15/bin/pg_ctl -D /var/lib/pgsql/15/data -l logfile start -bash-4.2$

配置文件postgresql.conf修改

#主服务器pg62编辑配置文件修改参数 vi $PGDATA/postgresql.conf listen_addresses = '*' archive_mode = on archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"' max_wal_senders = 10 max_replication_slots = 10 wal_level = replica hot_standby = on wal_log_hints = on

创建数据库用户

# 创建用户命令 psql -U postgres -p 5432 postgres=# SET password_encryption = 'scram-sha-256'; postgres=# CREATE ROLE pgpool WITH LOGIN; postgres=# CREATE ROLE repl WITH REPLICATION LOGIN; postgres=# \password pgpool postgres=# \password repl postgres=# \password postgres postgres=# GRANT pg_monitor TO pgpool; --执行过程 -bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /var/lib/pgsql/15/data -l logfile start waiting for server to start.... done server started -bash-4.2$ -bash-4.2$ -bash-4.2$ psql -U postgres -p 5432 psql (15.15) Type "help" for help. postgres=# SET password_encryption = 'scram-sha-256'; SET postgres=# CREATE ROLE pgpool WITH LOGIN; CREATE ROLE postgres=# CREATE ROLE repl WITH REPLICATION LOGIN; CREATE ROLE postgres=# \password pgpool Enter new password for user "pgpool": Enter it again: postgres=# \password repl Enter new password for user "repl": Enter it again: postgres=# \password postgres Enter new password for user "postgres": Enter it again: postgres=# GRANT pg_monitor TO pgpool; GRANT ROLE postgres=#

配置pg_hba.conf

vi /var/lib/pgsql/15/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust host all all 192.168.1.0/24 scram-sha-256 # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust host replication all 192.168.1.0/24 scram-sha-256

操作系统配置免密

        要使用 Pgpool-II 的自动故障转移和在线恢复功能，需要允许所有后端服务器以 Pgpool-II 启动用户（默认为 postgres。Pgpool-II 4.0 或更早版本，默认为 root）和 PostgreSQL 启动用户（默认为 postgres）的身份进行 SSH 公钥认证（无密码 SSH 登录）。

# 配置主机名称解析 echo "192.168.1.62 pg62" >> /etc/hosts echo "192.168.1.63 pg63" >> /etc/hosts echo "192.168.1.64 pg64" >> /etc/hosts

root用户免密配置过程

--root [pg62、pg63、pg64]# mkdir ~/.ssh [pg62、pg63、pg64]# chmod 700 ~/.ssh [pg62、pg63、pg64]# cd ~/.ssh [pg62、pg63、pg64]# ssh-keygen -t rsa -f id_rsa_pgpool [pg62、pg63、pg64]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server1 [pg62、pg63、pg64]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server2 [pg62、pg63、pg64]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server3 -- 执行过程 [root@pg62 .ssh]# ssh-keygen -t rsa -f id_rsa_pgpool Generating public/private rsa key pair. Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in id_rsa_pgpool. Your public key has been saved in id_rsa_pgpool.pub. The key fingerprint is: SHA256:l3qQ3BH5ZR1R+Iq3ciRA9LvhBMUTn/4MiGrQiv5YaP4 root@pg62 The key's randomart image is: +---[RSA 2048]----+ | .ooo. ==| | o+o.+..| | .o..+o. | | o o.=.+ .| | . S =.=.o. | | o o = ooo++ | | + o + . o+ .o| | + o . . . o | | +oE o | +----[SHA256]-----+ [root@pg62 .ssh]# --根据提示输入密码 ssh-copy-id -i id_rsa_pgpool.pub postgres@pg62 ssh-copy-id -i id_rsa_pgpool.pub postgres@pg63 ssh-copy-id -i id_rsa_pgpool.pub postgres@pg64 --验证 [root@pg62 .ssh]# ssh postgres@pg62 -i ~/.ssh/id_rsa_pgpool Last login: Wed Dec 10 16:37:31 2025 from pg62 -bash-4.2$ exit logout Connection to pg62 closed. [root@pg62 .ssh]# ssh postgres@pg63 -i ~/.ssh/id_rsa_pgpool Last login: Wed Dec 10 16:30:31 2025 -bash-4.2$ exit logout Connection to pg63 closed. [root@pg62 .ssh]# ssh postgres@pg64 -i ~/.ssh/id_rsa_pgpool Last login: Wed Dec 10 16:30:31 2025 -bash-4.2$ exit logout Connection to pg64 closed.

postgres用户免密配置过程

# postgres用户配置免密 [pg62、pg63、pg64]# su - postgres [pg62、pg63、pg64]$ mkdir ~/.ssh [pg62、pg63、pg64]$ chmod 700 ~/.ssh [pg62、pg63、pg64]$ cd ~/.ssh [pg62、pg63、pg64]$ ssh-keygen -t rsa -f id_rsa_pgpool [pg62、pg63、pg64]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@pg62 [pg62、pg63、pg64]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@pg63 [pg62、pg63、pg64]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@pg64 --验证 ssh postgres@serverX -i ~/.ssh/id_rsa_pgpool [root@pg62 .ssh]# su - postgres Last login: Wed Dec 10 16:41:47 CST 2025 from pg64 on pts/1 -bash-4.2$ ssh postgres@pg62 -i ~/.ssh/id_rsa_pgpool Last login: Wed Dec 10 16:42:15 2025 -bash-4.2$ hostname pg62 -bash-4.2$ exit logout Connection to pg62 closed. -bash-4.2$ ssh postgres@pg63 -i ~/.ssh/id_rsa_pgpool Last login: Wed Dec 10 16:41:56 2025 from pg64 -bash-4.2$ hostname pg63 -bash-4.2$ exit logout Connection to pg63 closed. -bash-4.2$ ssh postgres@pg64 -i ~/.ssh/id_rsa_pgpool Last login: Wed Dec 10 16:42:06 2025 from pg64 -bash-4.2$ hostname pg64 -bash-4.2$ exit logout Connection to pg64 closed.

repl复制用户免密

       为了允许 repl 用户在不指定密码的情况下进行流复制和在线恢复，并使用 postgres 执行 pg_rewind，我们在 postgres 用户的主目录中创建 .pgpass 文件，并将每个 PostgreSQL 服务器上的权限更改为 600。

[pg62、pg63、pg64]$ vi /var/lib/pgsql/.pgpass pg62:5432:replication:repl:repl pg63:5432:replication:repl:repl pg64:5432:replication:repl:repl pg62:5432:postgres:postgres:postgres pg63:5432:postgres:postgres:postgres pg64:5432:postgres:postgres:postgres [pg62、pg63、pg64]$ chmod 600 /var/lib/pgsql/.pgpass

四、部署pgpool-II

# pg62、pg63、pg64服务器执行操作。

安装依赖包

libmemcached-1.0.16-5.el7.x86_64 <<< 基础包需要安装 mount /dev/cdrom /mnt yum install -y libmemcached

排除pg仓库的pgpool包

# 修改pgdg仓库文件 vi /etc/yum.repos.d/pgdg-redhat-all.repo [pgdg-common] ... exclude=pgpool* [pgdg15] ... exclude=pgpool* [pgdg14] ... exclude=pgpool* [pgdg13] ... exclude=pgpool* [pgdg12] ... exclude=pgpool* [pgdg11] ... exclude=pgpool* [pgdg10] ... exclude=pgpool* #拷贝修改的文件至其它服务器 scp /etc/yum.repos.d/pgdg-redhat-all.repo 192.168.1.63:/etc/yum.repos.d/pgdg-redhat-all.repo scp /etc/yum.repos.d/pgdg-redhat-all.repo 192.168.1.64:/etc/yum.repos.d/pgdg-redhat-all.repo

安装pgpool-II

[root@pg62 yum.repos.d]# yum install -y https://www.pgpool.net/yum/rpms/4.4/redhat/rhel-7-x86_64/pgpool-II-release-4.4-1.noarch.rpm Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager This system is not registered with an entitlement server. You can use subscription-manager to register. pgpool-II-release-4.4-1.noarch.rpm | 5.7 kB 00:00:00 Examining /var/tmp/yum-root-IXf77b/pgpool-II-release-4.4-1.noarch.rpm: pgpool-II-release-4.4-1.noarch Marking /var/tmp/yum-root-IXf77b/pgpool-II-release-4.4-1.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package pgpool-II-release.noarch 0:4.4-1 will be installed --> Finished Dependency Resolution pgdg-common/7Server/x86_64/signature | 665 B 00:00:00 pgdg-common/7Server/x86_64/signature | 2.9 kB 00:00:00 !!! pgdg12/7Server/x86_64/signature | 665 B 00:00:00 pgdg12/7Server/x86_64/signature | 3.6 kB 00:00:00 !!! pgdg13/7Server/x86_64/signature | 665 B 00:00:00 pgdg13/7Server/x86_64/signature | 3.6 kB 00:00:00 !!! pgdg14/7Server/x86_64/signature | 665 B 00:00:00 pgdg14/7Server/x86_64/signature | 3.6 kB 00:00:00 !!! pgdg15/7Server/x86_64/signature | 665 B 00:00:00 pgdg15/7Server/x86_64/signature | 3.6 kB 00:00:00 !!! Dependencies Resolved ============================================