合 PG 14 + Pgpool-II + Watchdog 实现高可用
Tags: PG高可用Pgpool-IIPG 14自动转移单点故障Watchdog
- 1. Requirements
- 2. Cluster System Configuration
- 3. Installation
- 4. Before Starting
- 5. Create pgpool_node_id
- 6. Pgpool-II Configuration
- 6.1. Clustering mode
- 6.2. listen_addresses
- 6.3. port
- 6.4. Streaming Replication Check
- 6.5. Health Check
- 6.6. Backend Settings
- 6.7. Failover configuration
- 6.8. Pgpool-II Online Recovery Configurations
- 6.9. Client Authentication Configuration
- 6.10. Watchdog Configuration
- 6.11. Logging
- 7. /etc/sysconfig/pgpool Configuration
- 8. Starting/Stopping Pgpool-II
- 9. How to use
- 9.1. Set up PostgreSQL standby server
- 9.2. Switching active/standby watchdog
- 9.3. Failover
- 9.4. Online Recovery
参考:https://www.pgpool.net/docs/latest/en/html/example-cluster.html
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
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
Hostname | IP Address | Virtual IP |
---|---|---|
server1 | 192.168.137.101 | 192.168.137.150 |
server2 | 192.168.137.102 | 192.168.137.150 |
server3 | 192.168.137.103 | 192.168.137.150 |
Table 8-3. PostgreSQL version and Configuration
Item | Value | Detail |
---|---|---|
PostgreSQL Version | 14.0 | - |
port | 5432 | - |
$PGDATA | /var/lib/pgsql/14/data | - |
Archive mode | on | /var/lib/pgsql/archivedir |
Replication Slots | Enable | - |
Start automatically | Enable | - |
Table 8-4. Pgpool-II version and Configuration
Item | Value | Detail |
---|---|---|
Pgpool-II Version | 4.3.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 |
Pgpool-II start user | postgres (Pgpool-II 4.1 or later) | Pgpool-II 4.0 or before, the default startup user is root |
Running mode | streaming replication mode | - |
Watchdog | on | Life check method: heartbeat |
Start automatically | Enable | - |
Table 8-5. Various sample scripts included in rpm package
Feature | Script | Detail |
---|---|---|
Failover | /etc/pgpool-II/failover.sh.sample | Run by failover_command to perform failover |
/etc/pgpool-II/follow_primary.sh.sample | Run by follow_primary_command to synchronize the Standby with the new Primary after failover. | |
Online recovery | /etc/pgpool-II/recovery_1st_stage.sample | Run by recovery_1st_stage_command to recovery a Standby node |
/etc/pgpool-II/pgpool_remote_start.sample | Run after recovery_1st_stage_command to start the Standby node | |
Watchdog | /etc/pgpool-II/escalation.sh.sample | Run 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.
1 2 | [all servers]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm [all servers]# yum install -y postgresql14-server |
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.
1 | [all servers]# vi /etc/yum.repos.d/pgdg-redhat-all.repo |
The following is a setting example of /etc/yum.repos.d/pgdg-redhat-all.repo
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | [pgdg-common] ... exclude=pgpool* [pgdg14] ... exclude=pgpool* [pgdg13] ... exclude=pgpool* [pgdg12] ... exclude=pgpool* [pgdg11] ... exclude=pgpool* [pgdg10] ... exclude=pgpool* [pgdg96] ... exclude=pgpool* |
Install Pgpool-II from Pgpool-II YUM repository.
1 2 3 | [all servers]# yum install -y https://www.pgpool.net/yum/rpms/4.3/redhat/rhel-7-x86_64/pgpool-II-release-4.3-1.noarch.rpm [all servers]# yum install -y pgpool-II-pg14-* |
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.12[all servers]# su - postgres[all servers]$ mkdir /var/lib/pgsql/archivedirThen we edit the configuration file
$PGDATA/postgresql.conf
onserver1
(primary) as follows. Enablewal_log_hints
to usepg_rewind
. Since the Primary may become a Standby later, we sethot_standby = on
.123456789listen_addresses = '*'archive_mode = onarchive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'max_wal_senders = 10max_replication_slots = 10wal_level = replicahot_standby = onwal_log_hints = onWe 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 userpgpool
for streaming replication delay check and health check of Pgpool-II.Table 8-6. Users
User Name Password Detail repl repl PostgreSQL replication user pgpool pgpool Pgpool-II health check (health_check_user) and replication delay check (sr_check_user) user postgres postgres User running online recovery 12345678[server1]# psql -U postgres -p 5432postgres=# SET password_encryption = 'scram-sha-256';postgres=# CREATE ROLE pgpool WITH LOGIN;postgres=# CREATE ROLE repl WITH REPLICATION LOGIN;postgres=# \password pgpoolpostgres=# \password replpostgres=# \password postgresIf 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 inpg_monitor
group (Pgpool-II4.1 or later). Grantpg_monitor
topgpool
:12GRANT 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 enablescram-sha-256
authentication method.123host all all samenet scram-sha-256host replication all samenet scram-sha-256To 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
postgres
user and betweenpostgres
user andpostgres
user are necessary. Execute the following command on all servers to set up passwordlessSSH
. The generated key file name isid_rsa_pgpool
.12345678910111213[all servers]# cd ~/.ssh[all servers]# ssh-keygen -t rsa -f id_rsa_pgpool[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server1[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server2[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server3[all servers]# su - postgres[all servers]$ cd ~/.ssh[all servers]$ ssh-keygen -t rsa -f id_rsa_pgpool[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server1[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server2[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server3After 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 usingpostgres
, we create the.pgpass
file inpostgres
user's home directory and change the permission to600
on each PostgreSQL server.12345678910[all servers]# su - postgres[all servers]$ vi /var/lib/pgsql/.pgpassserver1:5432:replication:repl:<repl user password>server2:5432:replication:repl:<repl user password>server3:5432:replication:repl:<repl user password>server1:5432:postgres:postgres:<postgres user password>server2:5432:postgres:postgres:<postgres user password>server3:5432:postgres:postgres:<postgres user password>[all servers]$ chmod 600 /var/lib/pgsql/.pgpassWhen 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.
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!