PG 14 + Pgpool-II + Watchdog 实现高可用

0    653    2


👉 本文共约10945个字,系统预计阅读时间或需42分钟。


Pgpool-II + Watchdog配置示例

This section shows an example of streaming replication configuration using Pgpool-II. In this example, we use 3 Pgpool-II servers to manage PostgreSQL servers to create a robust cluster system and avoid the single point of failure or split brain.

PostgreSQL 14 is used in this configuration example. All scripts have been tested with PostgreSQL 10 and later.

1. Requirements

We assume that all the Pgpool-II servers and the PostgreSQL servers are in the same subnet.

2. Cluster System Configuration

We use 3 servers with CentOS 7.9 installed. Let these servers be server1 server2, server3. We install PostgreSQL and Pgpool-II on each server.

Figure 8-1. Cluster System Configuration

PG 14 + Pgpool-II + Watchdog 实现高可用

Note: The roles of Active, Standby, Primary, Standby are not fixed and may be changed by further operations.

Table 8-2. Hostname and IP address

HostnameIP AddressVirtual IP

Table 8-3. PostgreSQL version and Configuration

PostgreSQL Version14.0-
Archive modeon/var/lib/pgsql/archivedir
Replication SlotsEnable-
Start automaticallyEnable-

Table 8-4. Pgpool-II version and Configuration

Pgpool-II Version4.3.0-
port9999Pgpool-II accepts connections
9898PCP process accepts connections
9000watchdog accepts connections
9694UDP port for receiving Watchdog's heartbeat signal
Config file/etc/pgpool-II/pgpool.confPgpool-II config file
Pgpool-II start userpostgres (Pgpool-II 4.1 or later)Pgpool-II 4.0 or before, the default startup user is root
Running modestreaming replication mode-
WatchdogonLife check method: heartbeat
Start automaticallyEnable-

Table 8-5. Various sample scripts included in rpm package

Failover/etc/pgpool-II/ by failover_command to perform failover
/etc/pgpool-II/ by follow_primary_command to synchronize the Standby with the new Primary after failover.
Online recovery/etc/pgpool-II/recovery_1st_stage.sampleRun by recovery_1st_stage_command to recovery a Standby node
/etc/pgpool-II/pgpool_remote_start.sampleRun after recovery_1st_stage_command to start the Standby node
Watchdog/etc/pgpool-II/ by wd_escalation_command to switch the Active/Standby Pgpool-II safely

The above scripts are included in the RPM package and can be customized as needed.

3. Installation

In this example, we install Pgpool-II and PostgreSQL RPM packages with YUM.

Install PostgreSQL from PostgreSQL YUM repository.

Since Pgpool-II related packages are also included in PostgreSQL YUM repository, add the "exclude" settings to /etc/yum.repos.d/pgdg-redhat-all.repo so that Pgpool-II is not installed from PostgreSQL YUM repository.

The following is a setting example of /etc/yum.repos.d/pgdg-redhat-all.repo.

Install Pgpool-II from Pgpool-II YUM repository.

4. Before Starting

Before you start the configuration process, please check the following prerequisites.

  • Set up PostgreSQL streaming replication on the primary server. In this example, we use WAL archiving.

    First, we create the directory /var/lib/pgsql/archivedir to store WAL segments on all servers. In this example, only Primary node archives WAL locally.

    Then we edit the configuration file $PGDATA/postgresql.conf on server1 (primary) as follows. Enable wal_log_hints to use pg_rewind. Since the Primary may become a Standby later, we set hot_standby = on.

    We use the online recovery functionality of Pgpool-II to setup standby server after the primary server is started.

  • Because of the security reasons, we create a user repl solely used for replication purpose, and a user pgpool for streaming replication delay check and health check of Pgpool-II.

    Table 8-6. Users

    User NamePasswordDetail
    replreplPostgreSQL replication user
    pgpoolpgpoolPgpool-II health check (health_check_user) and replication delay check (sr_check_user) user
    postgrespostgresUser running online recovery

    If you want to show "replication_state" and "replication_sync_state" column in SHOW POOL NODES command result, role pgpool needs to be PostgreSQL super user or or in pg_monitor group (Pgpool-II4.1 or later). Grant pg_monitor to pgpool:

    Note: If you plan to use detach_false_primary(Pgpool-II 4.0 or later), role "pgpool" needs to be PostgreSQL super user or or in "pg_monitor" group to use this feature.

    Assuming that all the Pgpool-II servers and the PostgreSQL servers are in the same subnet and edit pg_hba.conf to enable scram-sha-256 authentication method.

  • To use the automated failover and online recovery of Pgpool-II, the settings that allow passwordless SSH to all backend servers between Pgpool-II execution user (default root user) and postgresuser and between postgres user and postgres user are necessary. Execute the following command on all servers to set up passwordless SSH. The generated key file name is id_rsa_pgpool.

    After setting SSH, use ssh postgres@serverX -i ~/.ssh/id_rsa_pgpool command to make sure that you can log in without entering a password. Edit /etc/ssh/sshd_config if necessary and restart sshd.

  • To allow repl user without specifying password for streaming replication and online recovery, and execute pg_rewind using postgres, we create the .pgpass file in postgres user's home directory and change the permission to 600 on each PostgreSQL server.

  • When connect to Pgpool-II and PostgreSQL servers, the target port must be accessible by enabling firewall management softwares. Following is an example for CentOS/RHEL7.

    PG 14 + Pgpool-II + Watchdog 实现高可用后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!


Avatar photo





  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部