High Availability
Set up High Availability using Pgpool-II
This guide continues from the previous streaming replication setup.
System Configuration [All]
Pgpool-II uses some additional network ports for communication, open the ports:
sudo firewall-cmd --permanent --zone=public --add-port=9999/tcp --add-port=9898/tcp --add-port=9000/tcp --add-port=9694/udp
sudo firewall-cmd --reload
Creating the pgpool User [db01]
Create the database user that will be used for the Pgpool-II health and replication delay checks.
Switch to the postgres user and log into the database:
su postgres
Now log in:
psql
Create the pgpool user:
SET password_encryption = 'scram-sha-256';
CREATE ROLE pgpool WITH LOGIN;
\password pgpool
\password postgres
Eventually these are the users that are needed. make sure they are created with the right passwords.
| User | Password | Detail |
|---|---|---|
| replicator | password | PostgreSQL replication user |
| pgpool | password | Pgpool-II health check (health_check_user) and replication delay check (sr_check_user) user |
| postgres | password | User running online recovery |
To show replication_state and replication_sync_state grant the pg_monitor role to the pgpool user:
GRANT pg_monitor TO pgpool;
pg_hba? and pool_passwd pg_md5
Configure Password-less Authentication [All]
Postgres User
The postgres user on each node needs password-less ssh access to the other nodes.
To do this an SSH public/private key will be generated and the public key will be added to the other nodes.
su - postgres
Generate the key:
mkdir -p ~/.ssh && chmod 700 ~/.ssh && cd ~/.ssh
ssh-keygen -t rsa -f ~/.ssh/id_rsa_pgpool
Copy the public key manually to the other systems or use ssh-copy-id.
To use ssh-copy-id the postgres user will need to have a password set.
As root (on all node):
passwd postgres
Switch back to the postgres user (all nodes):
su - postgres
Now copy the keys:
ssh-copy-id -i ~/.ssh/id_rsa_pgpool.pub postgres@db01.nomadesk.org
ssh-copy-id -i ~/.ssh/id_rsa_pgpool.pub postgres@db02.nomadesk.org
ssh-copy-id -i ~/.ssh/id_rsa_pgpool.pub postgres@db03.nomadesk.org
Streaming Replication [All]
To allow replicator user without specifying password for streaming replication and online recovery, and execute pg_rewind using postgres, create the .pgpass file in postgres user's home directory and change the permission to 600 on each PostgreSQL server.
This file allows replicator user and postgres user without providing a password for streaming replication and fail-over.
Switch to the postgres user:
su - postgres
Now add the .pgpass file, make sure to change the passwords:
cat << EOF > /var/lib/pgsql/.pgpass
db01.nomadesk.org:5432:replication:replicator:<replicator user password>
db02.nomadesk.org:5432:replication:replicator:<replicator user password>
db03.nomadesk.org:5432:replication:replicator:<replicator user password>
db01.nomadesk.org:5432:postgres:postgres:<postgres user password>
db02.nomadesk.org:5432:postgres:postgres:<postgres user password>
db03.nomadesk.org:5432:postgres:postgres:<postgres user password>
EOF
chmod 600 /var/lib/pgsql/.pgpass
PCP Authentication [All]
To use PCP commands PCP user names and MD5 encrypted passwords must be declared in pcp.conf in format username:<encrypted password>.
echo 'pgpool:'`pg_md5 <password>` >> /etc/pgpool-II/pcp.conf
Each server must be able to run pcp commands password-less, to do this create .pcppass in postgres home directory on each server.
The format of .pcppass is hostname:port:username:<password>:
su - postgres
Now add the password to .pcppass file:
cat << EOF > ~/.pcppass
localhost:9898:pgpool:<password>
EOF
chmod 600 ~/.pcppass
Configure PGPool [db01]
Configure Pgpool-II on the primary (db01) node, in a later step the configuration and scripts will be copied over to the other nodes.
Copy the configuration sample files:
cp /etc/pgpool-II/pgpool.conf.sample /etc/pgpool-II/pgpool.conf
vim /etc/pgpool-II/pgpool.conf
Add the following:
Change the hostnames and names to match your own setup.
Update the interface name in the if_up_cmd, if_down_cmd and arping_cmd line.
Fail-over Scripts [db01]
Pgpool-II comes with sample scripts, copy those to the script names configured above:
cp -p /etc/pgpool-II/failover.sh.sample /etc/pgpool-II/failover.sh
cp -p /etc/pgpool-II/escalation.sh.sample /etc/pgpool-II/escalation.sh
chown postgres:postgres /etc/pgpool-II/{failover.sh,escalation.sh}
chmod +x /etc/pgpool-II/{failover.sh,escalation.sh}
Update the escalation script:
sudo sed -i 's/PGPOOLS=.*/PGPOOLS=(db01.nomadesk.org db02.nomadesk.org db03.nomadesk.org)/' /etc/pgpool-II/escalation.sh
sudo sed -i 's/VIP=.*/VIP=192.168.122.20/' /etc/pgpool-II/escalation.sh
sudo sed -i 's/DEVICE=.*/DEVICE=enp1s0/' /etc/pgpool-II/escalation.sh
Recovery [db01]
Configure the required parameters to perform online recovery.
Because Superuser privilege in PostgreSQL is required for performing online recovery, we specify postgres user in recovery_user.
cp -p /etc/pgpool-II/recovery_1st_stage.sample /var/lib/pgsql/16/data/recovery_1st_stage
cp -p /etc/pgpool-II/pgpool_remote_start.sample /var/lib/pgsql/16/data/pgpool_remote_start
chown postgres:postgres /var/lib/pgsql/16/data/{recovery_1st_stage,pgpool_remote_start}
chmod +x /var/lib/pgsql/16/data/{recovery_1st_stage,pgpool_remote_start}
sudo sed -i 's/REPLUSER=repl/REPLUSER=replicator/' /var/lib/pgsql/16/data/recovery_1st_stage
Note add commands to check and remove postgresql.auto.conf or copy this script recovery_1st_stage
In order to use the online recovery functionality, the functions of pgpool_recovery, pgpool_remote_start, pgpool_switch_xlog are required.
These are available in the pgpool_recovery on template1 of PostgreSQL server.
To install the template:
su - postgres
Now install the extension:
psql template1 -c "CREATE EXTENSION pgpool_recovery"
Client Authentication on PgPool-II [db01]
Enable scram-sha-256
Allow scram-sha-256 authentication for the postgres and pgpool users for Pgpool-II:
cp -p /etc/pgpool-II/pool_hba.conf.sample /etc/pgpool-II/pool_hba.conf
cat << EOF >> /etc/pgpool-II/pool_hba.conf
host all pgpool 192.168.122.0/24 scram-sha-256
host all postgres 192.168.122.0/24 scram-sha-256
host all replicator 192.168.122.0/24 scram-sha-256
EOF
scram-sha-256 Decryption key [db01]
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.
To create one, a .pgpoolkey file in postgres user's home directory should be created.
Switch to the postgres user:
su - postgres
Create the key:
echo `uuidgen` > ~/.pgpoolkey
chmod 600 ~/.pgpoolkey
Copy the file to the other nodes as well:
scp -i ~/.ssh/id_rsa_pgpool ~/.pgpoolkey postgres@db02.nomadesk.org:~
scp -i ~/.ssh/id_rsa_pgpool ~/.pgpoolkey postgres@db03.nomadesk.org:~
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.
As root, create the Pgpool-II passwd file:
touch /etc/pgpool-II/pool_passwd
chown -R postgres:postgres /etc/pgpool-II/
Now switch back to the postgres user:
su - postgres
//ToDo: figure out how to get this working only plain text passwords seem to work for some reason
Add the encrypted passwords:
pg_enc -m -k ~/.pgpoolkey -u pgpool -p
pg_enc -m -k ~/.pgpoolkey -u postgres -p
pg_enc -m -k ~/.pgpoolkey -u replicator -p
Create the follow_primary.sh script
Create the Script
This script is run when a fail-over happens and needs to make sure that standby nodes follow the new primary.
Create the following script:
touch /etc/pgpool-II/follow_primary.sh
chmod +x /etc/pgpool-II/follow_primary.sh
The following is different from the provided sample script:
- Remove the
postgresql.auto.confif present upon fail-over
Replication User
The script above uses the replicator user as user name for the replication, if a different name is used, update it using the following command:
sudo sed -i 's/REPLUSER=.*/REPLUSER=<username>/' /etc/pgpool-II/follow_primary.sh
Syncing the Pgpool-II Configuration [db01]
Now that the configuration is complete, copy it to the other servers:
rsync -vva /etc/pgpool-II/ db02.nomadesk.org:/etc/pgpool-II/
rsync -vva /etc/pgpool-II/ db03.nomadesk.org:/etc/pgpool-II/
Node Identification [All]
There are three database servers, a primary and two standby nodes, each is identified by a number.
Add this identification to the each machine:
On the primary (db01):
echo "0" > /etc/pgpool-II/pgpool_node_id
On the first standby node (db02):
echo "1" > /etc/pgpool-II/pgpool_node_id
On the second standby node (db03):
echo "2" > /etc/pgpool-II/pgpool_node_id
WatchDog [All]
Since root privilege is required to execute if_up/down_cmd or arping_cmd command, use setuid on these command or allow postgres user (the user Pgpool-II is running as) to run sudo command without a password.
Note: If Pgpool-II is installed using RPM, the postgres user has been configured to run ip/arping via sudo without a password.
visudo
postgres ALL=NOPASSWD: /sbin/ip
postgres ALL=NOPASSWD: /usr/sbin/arping
Create the directory the logs will be stored in:
mkdir -p /var/log/pgpool_log/
chown -R postgres:postgres /var/log/pgpool_log/
Starting Pgpool-II [All]
Now that the configuration on all servers is the same, it's time to start Pgpool-II.
First restart PostgreSQL on all nodes:
systemctl restart postgresql-16
Now start Pgpool-II on all nodes:
systemctl restart pgpool-II
Setting Primary and StandBy Nodes
Start by seeing if the virtual IP is being assigned by just doing a ping:
ping 192.168.122.20
If it's not being assigned automatically, on db01, what we're going to use as primary, run the following:
/usr/bin/sudo /sbin/ip addr add 192.168.122.20/24 dev enp1s0 label enp1s0:0
/usr/bin/sudo /usr/sbin/arping -U 192.168.122.20 -w 1 -I enp1s0
Note: replace the interface names and IP where needed
Before doing anything, connect to Pgpool-II using the virtual IP and view the status:
psql -h 192.168.122.20 -p 9999 -U pgpool postgres -c 'show pool_nodes'
This will show you the current status:
[$]# psql -h 192.168.122.20 -p 9999 -U pgpool postgres -c 'show pool_nodes'
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 | db01.nomadesk.org | 5432 | up | up | 0.333333 | primary | primary | 0 | false | 0 | | | 2024-07-25 15:28:25
1 | db02.nomadesk.org | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2024-07-25 15:28:25
2 | db03.nomadesk.org | 5432 | up | up | 0.333333 | standby | standby | 0 | true | 0 | | | 2024-07-25 15:28:25
FAQ
ERROR: backend response with kind 'E' when expecting 'R'
If when logging into PostgreSQL using the Pgpool-II interface and you can the backend response with kind 'E' when expecting 'R' error.
The cause of this is most likely incorrectly configured credentials.
Log into the postgres database directly and update the user's credentials using scram-sha-256.
su - postgres
psql
Now update the password(s):
SET password_encryption = 'scram-sha-256';
\password <user>
Try to log in again.