PostgreSql+Pgpool-II配置高可用集群（超详细）

一.配置示例

1.1. 基本配置示例

1.1.1. 开始

首先，我们必须学习如何在使用复制之前安装和配置 Pgpool-II 和数据库节点。

1.1.1.1. 安装 Pgpool-II

安装 Pgpool-II 非常简单。 在已解压源 tar ball 的目录中， 执行以下命令。

 $ ./configure
 $ make
 $ make install

configure script 收集您的系统信息 并将其用于编译过程。您可以传递命令 line 参数来配置脚本以更改默认行为， 例如安装目录。默认情况下，pgpool-II 将安装到 /usr/local 目录下。

make 命令编译源代码，make install 将安装可执行文件。 您必须对安装目录具有写入权限。 在本教程中，我们将把 Pgpool-II 安装在默认的 /usr/local 目录下。

注意： Pgpool-II 需要 PostgreSQL 7.4 或更高版本的 libpq 库（版本 3 协议）。

如果 configure 脚本显示以下错误消息，则 libpq 库可能未安装，或者它不是版本 3

 configure: error: libpq is not installed or libpq is old

如果库是版本 3，但仍显示上述消息，则 configure 脚本可能无法识别您的 libpq 库。 configure 脚本在 /usr/local/pgsql 下搜索 libpq 库。如果您将 PostgreSQL 安装在 /usr/local/pgsql 以外的目录中，请在使用 --with-pgsql 或 --with-pgsql-includedir 和 --with-pgsql-libdir 命令行选项时 执行 configure。

1.1.1.2. 配置文件

Pgpool-II 的配置参数保存在 pgpool.conf 文件中。该文件每行格式为 “parameter = value”。 当你安装 Pgpool-II 时，pgpool.conf.sample 会自动创建。 我们建议将其复制并重命名为 pgpool.conf，然后编辑 随你所欲。

 $ cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf

Pgpool-II 只接受来自 localhost 的连接 默认使用端口 9999。如果您希望接收来自其他主机的连接， 将 listen_addresses 设置为 ‘*’。

  listen_addresses = 'localhost'
  port = 9999

在本教程中，我们将使用默认参数。

1.1.1.3. 配置 PCP 命令

Pgpool-II 有一个用于管理接口的 用于检索数据库节点信息、通过网络关闭 Pgpool-II 等。要使用 PCP 命令，需要用户身份验证。 此身份验证与 PostgreSQL 的用户身份验证不同。 需要在 pcp.conf 文件中定义用户名和密码。在文件中，用户名和密码在每行中成对列出。 它们用冒号 （😃.密码以 md5 哈希格式加密。

 postgres:e1a41653151e21c69d0506501fb27fc5

当你安装 Pgpool-II 时，pcp.conf.sample 会自动创建。我们建议复制并重命名它 添加到 pcp.conf 中，然后对其进行编辑。

 $ cp /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf

要将你的密码加密成 md5 哈希格式，请使用 pg_md5 命令，它作为 Pgpool-II 的 可执行文件。pg_md5 将文本作为命令行参数， 并显示其 MD5 哈希文本。 例如，将 “postgres” 作为命令行参数， pg_md5 在其标准输出上显示 MD5 哈希文本。

 $ /usr/local/bin/pg_md5 postgres
 e1a41653151e21c69d0506501fb27fc5

PCP 命令通过网络执行，因此必须配置端口号 在 pgpool.conf 文件中pcp_port参数。 在本教程中，我们将使用默认的 9898 作为 pcp_port。

   pcp_port = 9898

1.1.1.4. 准备数据库节点

现在，我们需要为 Pgpool-II 设置后端 PostgreSQL 服务器。这些服务器可以放置在与 Pgpool-II 相同的主机中，或者放在不同的机器上。如果您决定 要将服务器放置在同一主机上，必须分配不同的端口号 对于每个服务器。如果服务器放置在不同的计算机上， 必须正确配置它们，以便它们可以接受网络 来自 Pgpool-II 的连接。 在此示例中，我们创建 3 个 PostgreSQL 服务器，并且 在以下参数中指定 PostgreSQL 信息。

 backend_hostname0 = 'localhost'
 backend_port0 = 5432
 backend_weight0 = 1
 backend_hostname1 = 'localhost'
 backend_port1 = 5433
 backend_weight1 = 1
 backend_hostname2 = 'localhost'
 backend_port2 = 5434
 backend_weight2 = 1

对于 backend_hostname、backend_port backend_weight，设置节点的主机名、端口号、 和 ratio 进行负载平衡。在每个参数字符串的末尾， 节点 ID 必须通过添加从 0 开始的正整数（即 0、1、2…）来指定。

注意：所有节点的 backend_weight 参数均为 设置为 1，这意味着 SELECT 查询在 三个服务器。

1.1.1.5. 启动/停止 pgpool-II

要启动 Pgpool-II，请执行以下命令 命令。

 $ pgpool

然而，上面的命令没有打印日志消息，因为 Pgpool-II 分离了终端。如果你想显示 Pgpool-II 的日志信息，你可以将 -n 选项传递给 pgpool 命令，这样 Pgpool-II 就会作为非守护进程执行，并且终端不会被分离。

 $ pgpool -n &

日志消息打印在终端上，因此建议使用以下选项。

 $ pgpool -n -d > /tmp/pgpool.log 2>&1 &

-d 选项允许生成调试消息。 上述命令不断将日志消息附加到 /tmp/pgpool.log 。如果需要轮换日志文件，请将日志传递给外部 命令，该命令具有对数轮换功能。 例如，您可以使用 Apache2 中的 rotatelogs：

 $ pgpool -n 2>&1 | /usr/local/apache2/bin/rotatelogs \
 -l -f /var/log/pgpool/pgpool.log.%A 86400 &

这将生成一个名为 “pgpool.log.Thursday” 的日志文件，然后在午夜 00：00 旋转它。如果文件已经 存在。要在轮换之前删除旧的日志文件，您可以使用 cron：

 55 23 * * * /usr/bin/find /var/log/pgpool -type f -mtime +5 -exec /bin/rm -f '{}' \;

请注意，在某些发行版中，rotatelogs 可能以 /usr/sbin/rotatelogs2 的形式存在。-f 选项会在 rotatelogs 启动后立即生成日志文件，并在 apache2 2.2.9 或更高版本中可用。 也可以使用 cronolog。

 $ pgpool -n 2>&1 | /usr/sbin/cronolog \
 --hardlink=/var/log/pgsql/pgpool.log \
 '/var/log/pgsql/%Y-%m-%d-pgpool.log' &

要停止 Pgpool-II，请执行以下命令。

 $ pgpool stop

如果任何客户端仍然连接，Pgpool-II 等待它断开连接，然后终止自身。运行以下命令 命令来代替，如果你想强制关闭 Pgpool-II。

 $ pgpool -m fast stop

1.1.2. 您的第一次复制

复制（请参阅本机复制模式）启用 要复制到多个数据库节点的相同数据。 在本节中，我们将使用我们已经设置的三个数据库节点 up 在 Section 1.1.1 中，并带你一步一步地进入 创建数据库复制系统。 要复制的示例数据将由 pgbench 基准测试程序生成。

1.1.2.1. 配置复制

要启用数据库复制功能，请在 pgpool.conf 文件中将本地复制模式设置为 on。

  replication_mode = true

当本地复制模式开启时，Pgpool-II 会将接收到的查询的副本发送到所有数据库节点。 此外，当 load_balance_mode 设置为 true 时，Pgpool-II 将分发 SELECT 查询 在数据库节点之间。

load_balance_mode = true

在本节中，我们将启用本机复制模式和 load_balance_mode。

1.1.2.2. 检查复制

为了在 pgpool.conf 中反映上述更改，必须重新启动 Pgpool-II。 请参考 “启动/停止 pgpool-II” 第 1.1.1.5 节。 在配置 pgpool.conf 并重启 Pgpool-II 之后，让我们尝试实际的复制 并查看是否一切正常。 首先，我们需要创建一个要复制的数据库。我们将其命名为 “bench_replication”。需要创建此数据库 在所有节点上。通过 Pgpool-II 使用 createdb 命令，数据库将被创建 在所有节点上。

  $ createdb -p 9999 bench_replication

然后，我们将使用 -i 选项执行 pgbench。-i 选项使用预定义的表和数据初始化数据库。

  $ pgbench -i -p 9999 bench_replication

下表是表和数据的摘要，它们将由 pgbench -i 创建。如果在所有节点上，列出的表和 数据，复制工作正常。

表 1-1.数据汇总

表名称	行数
pgbench_branches	1
pgbench_tellers	10
pgbench_accounts	100000
pgbench_history	0
让我们使用一个简单的 shell 脚本来检查所有节点上的上述内容。 以下脚本将显示 pgbench_branches 中的行数， 所有节点上的 pgbench_tellers、pgbench_accounts 和 pgbench_history 表（5432、5433、5434）。

 $ for port in 5432 5433 5434; do
 >     echo $port
 >     for table_name in pgbench_branches pgbench_tellers pgbench_accounts pgbench_history; do
 >         echo $table_name
 >         psql -c "SELECT count(*) FROM $table_name" -p $port bench_replication
 >     done
 > done

1.2. pgpool-II + 看门狗设置示例

本节展示了一个使用 Pgpool-II 的流式复制配置示例。在这个例子中，我们使用 3 个 Pgpool-II 服务器来管理 PostgreSQL 服务器，以创建一个健壮的集群系统，避免单点故障或裂脑。

此配置示例中使用 PostgreSQL 16。 所有脚本均已使用 PostgreSQL 10 及更高版本进行了测试。

1.2.1. 要求

我们假设所有的 Pgpool-II 服务器和 PostgreSQL 服务器都在同一个子网中。

1.2.2. 集群系统配置

我们使用三台安装了 Rocky Linux 8 的服务器，并且 这三个服务器的主机名分别为 server1、server2 和 server3。 我们在每台服务器上安装 PostgreSQL 和 Pgpool-II。

注意： Leader、Standby、Primary、Standby 的角色不是固定的，可能会通过进一步的操作来改变。

表 1-2.主机名和 IP 地址

Hostname	IP Address	Virtual IP
server1	192.168.100.51	192.168.100.50
server2	192.168.100.52
server3	192.168.100.53
表 1-3.PostgreSQL 版本和配置

Item	Value	Detail
PostgreSQL Version	16.0	-
port	5432	-
$PGDATA	/var/lib/pgsql/16/data	-
Archive mode	on	/var/lib/pgsql/archivedir
Replication Slots	Enabled	In this configuration example, replication slots are automatically created or deleted in the scripts which are executed during failover or online recovery. These scripts use the hostname specified in backend_hostnameX as the replication slot name.
Async/Sync Replication	Async	-
表 1-4.Pgpool-II 版本和配置

Item	Value	Detail
Pgpool-II Version	4.5.0	-
port	9999	Pgpool-II accepts connections
	9898	PCP process accepts connections
	9000	watchdog accepts connections
	9694	UDP port for receiving Watchdog’s heartbeat signal
Config file	/etc/pgpool-II/pgpool.conf	Pgpool-II config file
User running Pgpool-II	postgres (Pgpool-II 4.1 or later)	Pgpool-II 4.0 or before, the default user running Pgpool-II is root
Running mode	streaming replication mode	-
Watchdog	on	Life check method: heartbeat

Table 1-5. Various sample scripts included in rpm package
表 1-5.rpm 包中包含的各种示例脚本

特征	脚本	细节
故障转移	/etc/pgpool-II/sample_scripts/failover.sh.sample	由 failover_command 运行以执行故障转移
	/etc/pgpool-II/sample_scripts/follow_primary.sh.sample	由 follow_primary_command 运行，以在故障转移后将备用数据库与新的主数据库同步
在线恢复	/etc/pgpool-II/sample_scripts/recovery_1st_stage.sample	Run by recovery_1st_stage_command 以恢复备用节点
	/etc/pgpool-II/sample_scripts/pgpool_remote_start.sample	Run after recovery_1st_stage_command 以启动 Standby 节点
看门狗	/etc/pgpool-II/sample_scripts/escalation.sh.sample	可选配置。由 wd_escalation_command 运行以安全地切换 Leader/Standby Pgpool-II

上述脚本包含在 RPM 包中，可以根据需要进行自定义。

1.2.3. 安装

在此示例中，我们使用 YUM 安装 Pgpool-II 和 PostgreSQL RPM 包。

从 PostgreSQL YUM 存储库安装 PostgreSQL。

[all servers]# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[all servers]# dnf -qy module disable postgresql
[all servers]# dnf install -y postgresql16-server

由于 Pgpool-II 相关的包也包含在 PostgreSQL YUM 仓库中， 将 “exclude” 设置添加到 /etc/yum.repos.d/pgdg-redhat-all.repo 中，这样 Pgpool-II 就不会从 PostgreSQL YUM 仓库安装。

[all servers]# vi /etc/yum.repos.d/pgdg-redhat-all.repo

以下是 /etc/yum.repos.d/pgdg-redhat-all.repo 的设置示例。

[pgdg-common]
...
exclude=pgpool*

[pgdg16]
...
exclude=pgpool*

[pgdg15]
...
exclude=pgpool*

[pgdg14]
...
exclude=pgpool*

[pgdg13]
...
exclude=pgpool*

[pgdg12]
...
exclude=pgpool*

[pgdg11]
...
exclude=pgpool*

从 Pgpool-II YUM 仓库安装 Pgpool-II。

[all servers]# dnf install -y https://www.pgpool.net/yum/rpms/4.5/redhat/rhel-8-x86_64/pgpool-II-release-4.5-1.noarch.rpm
[all servers]# dnf install -y pgpool-II-pg16-*

1.2.4. 开始之前

在开始配置过程之前，请检查以下先决条件。

1.2.4.1. 在 Primary 上设置流复制

在主服务器上设置 PostgreSQL 流复制。 在此示例中，我们使用 WAL 存档。

首先，我们创建目录 /var/lib/pgsql/archivedir 以在所有服务器上存储 WAL 段。在此示例中，只有主节点在本地存档 WAL。

[all servers]# su - postgres
[all servers]$ mkdir /var/lib/pgsql/archivedir

在主服务器上初始化 PostgreSQL。

[server1]# su - postgres
[server1]$ /usr/pgsql-16/bin/initdb -D $PGDATA

然后我们在 server1（主）上编辑配置文件 $PGDATA/postgresql.conf，如下所示。启用 wal_log_hints 以使用 pg_rewind。 由于 Primary 稍后可能会成为 Standby ，因此我们将 hot_standby = on。

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

在 server1 上启动 PostgreSQL 主服务器。

[server1]# su - postgres
[server1]$ /usr/pgsql-16/bin/pg_ctl start -D $PGDATA

1.2.4.2. 在 Standby 上设置 Streaming Replication

有多种方法可以设置备用服务器，例如：

使用 pg_basebackup 从备用数据库备份主数据库的数据目录。

使用 Pgpool-II 的在线恢复功能 （第 5.11 节）自动 设置备用服务器。

在这个例子中，我们使用 Pgpool-II 的 在线恢复以设置备用服务器 在 Section 1.2.1.2 中，在 Pgpool-II 的配置完成后。

1.2.4.3. 设置 PostgreSQL 用户

PostgreSQL 用户需要使用 Pgpool-II 的 运行状况检查和复制延迟检查功能。 出于安全原因，我们创建了一个名为 pgpool 的专用用户，用于流复制延迟检查和 运行状况检查。 并创建一个名为 repl 的专用用户进行复制。 由于在线恢复功能需要超级用户权限，因此 我们在这里使用 postgres user。

从 Pgpool-II 4.0 开始，支持 scram-sha-256 身份验证。 此配置示例使用 scram-sha-256 身份验证方法。 首先，设置 password_encryption = ‘scram-sha-256’，然后创建用户。

表 1-6.用户

用户名 密码 细节
举报 举报 PostgreSQL 复制用户
pgpool pgpool Pgpool-II 健康检查 （health_check_user） 和复制延迟检查 （sr_check_user） 用户
postgres postgres 运行联机恢复的用户

[server1]# 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