| pgpool-II 4.3.5 Documentation | |||
|---|---|---|---|
| Prev | Up | Chapter 8. Configuration Examples | Next |
This section shows an example of how to configure Pgpool-II replication mode and snapshot isolation mode.
In streaming replication mode described in Section 8.2, replication is performed by PostgreSQL's streaming replication functionality. However, in native replication mode, Pgpool-II performs replication by routing write queries to all PostgreSQL servers.
Snapshot isolation mode is similar to native replication mode except it adds the visibility consistency among nodes.
PostgreSQL 14 is used in this configuration example. All scripts have been tested with PostgreSQL 10 and later.
In this example, we use one Pgpool-II and three PostgreSQL servers to describe how to configure and use Pgpool-II's replication.
In this example we use 3 servers with CentOS 7.9 installed. Let these servers be server1, server2, server3. We install PostgreSQL on all servers and Pgpool-II on server1.
In this example we use the minimum settings to configure replication. In a production environment, it is recommended to enable Watchdog to avoid single points of failure. For more details about Watchdog configurations, please refer to Section 8.2.6.10.
Table 8-7. Hostname and IP address
| Hostname | IP Address | Virtual IP |
|---|---|---|
| server1 | 192.168.137.101 | PostgreSQL node0, Pgpool-II |
| server2 | 192.168.137.102 | PostgreSQL node1 |
| server3 | 192.168.137.103 | PostgreSQL node2 |
Table 8-8. 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 |
Table 8-9. 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 | |
| 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 |
| Clustering mode | native replication mode | - |
| snapshot isolation mode | - |
In this example, we install Pgpool-II and PostgreSQL RPM packages with YUM.
Install PostgreSQL from PostgreSQL YUM repository.
[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.
[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.
[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 using Pgpool-II YUM repository.
[all servers]# yum install -y https://www.pgpool.net/yum/rpms/4.3/redhat/rhel-7-x86_64/pgpool-II-release-4.3-2.noarch.rpm [all servers]# yum install -y pgpool-II-pg14-*
Before you start the configuration process, please check the following prerequisites.
Run the following command to set the password of postgres user on each server.
[all servers]# passwd postgres
To use the online recovery of Pgpool-II, the settings that allow passwordless SSH to all servers are required. Execute the following command on all servers to set up passwordless SSH. The generated key file name is id_rsa_pgpool.
[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@server3
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 to execute online recovery scripts without specifying password, we create the .pgpass file in postgres user's home directory and change the permission to 600 on each server.
[all servers]# su - postgres [all servers]$ vi /var/lib/pgsql/.pgpass server1: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/.pgpass
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.
[all servers]# firewall-cmd --permanent --zone=public --add-service=postgresql [all servers]# firewall-cmd --permanent --zone=public --add-port=9999/tcp --add-port=9898/tcp [all servers]# firewall-cmd --reload
If Watchdog is enabled, you also need to open port 9000 and 9694.
[all servers]# firewall-cmd --permanent --zone=public --add-port=9000/tcp --add-port=9694/udp
This section describes how to create and configure a PostgreSQL server.
In this example, we use WAL archiving. First, we create the directory /var/lib/pgsql/archivedir to store WAL segments on all servers.
[all servers]# su - postgres [all servers]$ mkdir /var/lib/pgsql/archivedir
Create only one PostgreSQL server on server1. The other two PostgreSQL servers are created by using Pgpool-II's online recovery functionality in Section 8.3.8.1.
Run the following command to create a PostgreSQL database cluster on server1.
[server1]# su - postgres [server1]$ /usr/pgsql-14/bin/initdb -E UTF8 --no-locale
Then edit $PGDATA/postgresql.conf on server1.
[server1]$ vi $PGDATA/postgresql.conf listen_addresses = '*' archive_mode = on archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'
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.
[server1]$ vi $PGDATA/pg_hba.conf host all all samenet scram-sha-256 host replication all samenet scram-sha-256
Run the following command to start PostgreSQL server.
[server1]$ /usr/pgsql-14/bin/pg_ctl start
Create PostgreSQL users.
Table 8-10. PostgreSQL users
| User Name | Password | Detail |
|---|---|---|
| repl | repl | PostgreSQL replication user |
| pgpool | pgpool | User performing health check (health_check_user) |
| postgres | postgres | User performing online recovery |
[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
Snapshot isolation mode is only available when PostgreSQL's transaction isolation level is "repeatable read". If you want to use snapshot isolation mode, set default_transaction_isolation ='repeatable read' in postgresql.conf.
[server1]$ vi $PGDATA/postgresql.conf default_transaction_isolation = 'repeatable read'
When installing Pgpool-II using RPM, the Pgpool-II configuration sample files are in /etc/pgpool-II.
First, specify Pgpool-II clustering mode in backend_clustering_mode.
Native replication mode
backend_clustering_mode = 'native_replication'
Snapshot isolation mode
backend_clustering_mode = 'snapshot_isolation'
To allow Pgpool-II to accept all incoming connections, we set listen_addresses = '*'.
listen_addresses = '*'
Enable health check to allow Pgpool-II to detect PostgreSQL failure. Also, if the network is unstable, the health check fails even though the backend is running properly, failover or degenerate operation may occur. In order to prevent such incorrect detection of health check, we set health_check_max_retries = 3. Specify health_check_user and health_check_password. In this example, we leave sr_check_password empty, and create the entry in pool_passwd. See Section 8.3.5.6 for how to create the entry in pool_passwd. From Pgpool-II 4.0, if these parameters are left blank, Pgpool-II will first try to get the password for that specific user from pool_passwd file before using the empty password.
health_check_period = 5 health_check_timeout = 30 health_check_user = 'pgpool' health_check_password = '' health_check_max_retries = 3
Specify the PostgreSQL backend information. Multiple backends can be specified by adding a number at the end of the parameter name.
# - Backend Connection Settings - backend_hostname0 = 'server1' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/var/lib/pgsql/14/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = 'server2' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/pgsql/14/data' backend_flag1 = 'ALLOW_TO_FAILOVER' backend_hostname2 = 'server3' backend_port2 = 5432 backend_weight2 = 1 backend_data_directory2 = '/var/lib/pgsql/14/data' backend_flag2 = 'ALLOW_TO_FAILOVER'
Next, in order to perform online recovery we specify the PostgreSQL user name and online recovery command recovery_1st_stage_command and recovery_2nd_stage_command. Because Superuser privilege in PostgreSQL is required for performing online recovery, we specify postgres user in recovery_user.
recovery_user