8.3. Replication Mode and Snapshot Isolation Mode Configuration Example

8.3.3.1. Setting password of postgres user

Run the following command to set the password of postgres user on each server.

[all servers]# passwd postgres
   

8.3.3.2. Configure passwordless SSH login

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.

8.3.3.3. Create .pgpass

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
   

8.3.3.4. Configure firewall

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
   

8.3.4.1. Settings for snapshot isolation mode

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'
   

8.3.5.1. Clustering mode

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'
       

8.3.5.2. listen_addresses

To allow Pgpool-II to accept all incoming connections, we set listen_addresses = '*'.

listen_addresses = '*'
   

8.3.5.3. Health Check

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
   

8.3.5.4. Backend Settings

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'
   

8.3.5.5. Online Recovery

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 = 'postgres'
recovery_password = ''

recovery_1st_stage_command = 'recovery_1st_stage'
recovery_2nd_stage_command = 'recovery_2nd_stage'
   

The sample scripts replication_mode_recovery_1st_stage.sample, replication_mode_recovery_2nd_stage.sample and pgpool_remote_start.sample are installed in /etc/pgpool-II/. Create the scripts used by online recovery command from sample scripts and copy these files to the database cluster directory.

[server1]# cp -p /etc/pgpool-II/sample_scripts/replication_mode_recovery_1st_stage.sample /var/lib/pgsql/14/data/recovery_1st_stage
[server1]# cp -p /etc/pgpool-II/sample_scripts/replication_mode_recovery_2nd_stage.sample /var/lib/pgsql/14/data/recovery_2nd_stage
[server1]# cp -p /etc/pgpool-II/sample_scripts/pgpool_remote_start.sample /var/lib/pgsql/14/data/pgpool_remote_start
[server1]# chown postgres:postgres /var/lib/pgsql/14/data/{recovery_1st_stage,recovery_2nd_stage,pgpool_remote_start}
   

Basically, it should work if you change PGHOME according to PostgreSQL installation directory.

[server1]# vi /var/lib/pgsql/14/data/recovery_1st_stage
...
PGHOME=/usr/pgsql-14
...

[server1]# vi /var/lib/pgsql/14/data/recovery_2nd_stage
...
PGHOME=/usr/pgsql-14
...

[server1]# vi /var/lib/pgsql/14/data/pgpool_remote_start
...
PGHOME=/usr/pgsql-14
...
   

In order to use the online recovery functionality, the functions of pgpool_recovery, pgpool_remote_start, pgpool_switch_xlog are required, so we need to install pgpool_recovery on template1 of PostgreSQL server server1.

[server1]# su - postgres
[server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
   

8.3.5.6. Client Authentication Configuration

Enable client authentication between client and Pgpool-II. When installing with RPM, the Pgpool-II configuration file pool_hba.conf is in /etc/pgpool-II. By default, pool_hba authentication is disabled, set enable_pool_hba = on to enable it.

enable_pool_hba = on
   

The format of pool_hba.conf file follows very closely PostgreSQL's pg_hba.conf format. Set pgpool and postgres user's authentication method to scram-sha-256.

[server1]# vi /etc/pgpool-II/pool_hba.conf
host    all         pgpool           0.0.0.0/0          scram-sha-256
host    all         postgres         0.0.0.0/0          scram-sha-256
   

The default password file name for authentication is pool_passwd. To use scram-sha-256 authentication, the decryption key to decrypt the passwords is required. We create the .pgpoolkey file in Pgpool-II start user postgres's (Pgpool-II 4.1 or later) home directory. (Pgpool-II 4.0 or before, by default Pgpool-II is started as root)

[server1]# su - postgres
[server1]$ echo 'some string' > ~/.pgpoolkey
[server1]$ chmod 600 ~/.pgpoolkey
     

Execute command pg_enc -m -k /path/to/.pgpoolkey -u username -p to register user name and AES encrypted password in file pool_passwd. If pool_passwd doesn't exist yet, it will be created in the same directory as pgpool.conf.

[server1]# su - postgres
[server1]$ pg_enc -m -k ~/.pgpoolkey -u pgpool -p
db password: [pgpool user's password]
[server1]$ pg_enc -m -k ~/.pgpoolkey -u postgres -p
db password: [postgres user's password]

[server1]$ cat /etc/pgpool-II/pool_passwd
pgpool:AESheq2ZMZjynddMWk5sKP/Rw==
postgres:AESHs/pWL5rtXy2IwuzroHfqg==
   

8.3.5.7. PCP password

Since user authentication is required to use the PCP command, we need to specify user name and md5 encrypted password in pcp.conf in format "username:encrypted password".

We use pg_md5 to create the encrypted password entry for pgpool user as below:

[server1]# echo 'pgpool:'`pg_md5 PCP password` >> /etc/pgpool-II/pcp.conf
   

8.3.5.8. Logging

Since Pgpool-II 4.2, the logging collector process has been implemented. In the example, we enable logging collector.

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool_log'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
   

Create the log directory on server1.

[server1]# mkdir /var/log/pgpool_log/
[server1]# chown postgres:postgres /var/log/pgpool_log/
   

8.3.7.1. Create PostgreSQL servers using online recovery

Then, we create PostgreSQL node1 and node2 using online recovery. Ensure that recovery_1st_stage, recovery_2nd_stage and pgpool_remote_start scripts used by pcp_recovery_node command are in the database cluster directory on server1.

[server1]# pcp_recovery_node -h server1 -p 9898 -U pgpool -n 1
Password:
pcp_recovery_node -- Command Successful

[server1]# pcp_recovery_node -h server1 -p 9898 -U pgpool -n 2
Password:
pcp_recovery_node -- Command Successful
   

If pcp_recovery_node has run successfully, verify that the PostgreSQL node0 is started as the main node, and node1 and node2 are started as replicas.

# psql -h server1 -p 9999 -U pgpool postgres -c "show pool_nodes"
Password for user pgpool:
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
 ---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | server1  | 5432 | up     | up        | 0.333333  | main    | main    | 0          | true              | 0                 |                   |                        | 2021-12-02 16:48:21
 1       | server2  | 5432 | up     | up        | 0.333333  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:48:21
 2       | server3  | 5432 | up     | up        | 0.333333  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:48:21
(3 rows)
   

8.3.7.2. Verify replication

Next, let's verify the replication functionality using a benchmark tool pgbench.

[server1]# /usr/pgsql-14/bin/createdb test -U postgres -p 9999
[server1]# /usr/pgsql-14/bin/pgbench -h server1 -U postgres -i -p 9999 test
   

To check if the replication works correctly, directly connect to each PostgreSQL server to see if they return identical results.

[server1]# /usr/pgsql-14/bin/psql -h server1 -U postgres -p 5432 test
test=# \d
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

[server1]# /usr/pgsql-14/bin/psql -h server2 -U postgres -p 5432 test
test=# \d
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

[server1]# /usr/pgsql-14/bin/psql -h server3 -U postgres -p 5432 test
test=# \d
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)
   

server1, server2 and server3 return identical results.

Next, let's run pgbench for a while and check to results.

[server1]# /usr/pgsql-14/bin/pgbench -h server1 -U postgres -p 9999 -T 10 test
   

All PostgreSQL servers return identical results.

[server1]# /usr/pgsql-14/bin/psql -h server1 -U postgres -p 5432 test -c "SELECT sum(abalance) FROM pgbench_accounts"
Password for user postgres:
  sum
--------
 -99710
(1 row)

[server1]# /usr/pgsql-14/bin/psql -h server2 -U postgres -p 5432 test -c "SELECT sum(abalance) FROM pgbench_accounts"
Password for user postgres:
  sum
--------
 -99710
(1 row)

[server1]# /usr/pgsql-14/bin/psql -h server3 -U postgres -p 5432 test -c "SELECT sum(abalance) FROM pgbench_accounts"
Password for user postgres:
  sum
--------
 -99710
(1 row)
   

8.3.7.3. PostgreSQL failure

Next, stop the PostgreSQL main node on server1 and verify the switchover of the main node.

[server1]# su - postgres -c "/usr/pgsql-14/bin/pg_ctl -m i stop"
   

After stopping PostgreSQL on server1, switchover occurs and PostgreSQL on server2 becomes the new main node.

[server1]# psql -h server1 -p 9999 -U pgpool postgres -c "show pool_nodes"
Password for user pgpool:
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | server1  | 5432 | down   | down      | 0.333333  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:57:45
 1       | server2  | 5432 | up     | up        | 0.333333  | main    | main    | 1          | true              | 0                 |                   |                        | 2021-12-02 16:48:21
 2       | server3  | 5432 | up     | up        | 0.333333  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:48:21
(3 rows)
   

8.3.7.4. Online Recovery

Here, we use Pgpool-II online recovery functionality to restore the PostgreSQL node0 on server1.

# pcp_recovery_node -h server1 -p 9898 -U pgpool -n 0
Password:
pcp_recovery_node -- Command Successful
   

Then verify that server1 is started as the main node.

# psql -h server1 -p 9999 -U pgpool postgres -c "show pool_nodes"
Password for user pgpool:
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | server1  | 5432 | up     | up        | 0.333333  | main    | main    | 0          | true              | 0                 |                   |                        | 2021-12-02 16:57:45
 1       | server2  | 5432 | up     | up        | 0.333333  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:48:21
 2       | server3  | 5432 | up     | up        | 0.333333  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:48:21
(3 rows)