[Impact]
* resource-agents version 4.7.0 does not correctly work with PostgreSQL 11 and above.
* The issue appears on 22.04 and the resource-agents package is affected.
* This issue is caused by the receiver_parent_pids variable, which assigns the wrong name of the wal receiver process.
* The fix is to assign the correct name of the wal receiver process to search for that process and make the wal receiver check compatible with PostgreSQL >= 11.
[Test Plan]
Create a lxd container for the primary postgresql:
$ lxc launch ubuntu:jammy j1
Connect and install packages:
$ lxc shell j1
# apt update && apt install postgresql resource-agents-extra pacemaker-cli-utils -y
Configure postgresql.conf and pg_hba.conf:
# pg_conftool 14 main set listen_addresses '*'
# pg_conftool 14 main set wal_level replica
# echo "host replication replicator all scram-sha-256" >> /etc/postgresql/14/main/pg_hba.conf
Create replication user (choose a password, and remember it, it will be needed again later):
# sudo -u postgres createuser --replication -P -e replicator
restart the primary postgresql:
# systemctl restart postgresql
Back on the host, create lxd container for the secondary postgresql:
$ lxc launch ubuntu:jammy j2
Connect and install packages:
$ lxc shell j2
# apt update && apt install postgresql resource-agents-extra pacemaker-cli-utils -y
Stop postgresql:
# systemctl stop postgresql
Configure postgresql.conf:
# pg_conftool 14 main set listen_addresses '*'
# pg_conftool 14 main set hot_standby on
Cleanup data dir:
# rm -rf /var/lib/postgresql/*/main/*
Perform initial replication as "postgres" user. The pg_basebackup command will prompt for the "replicator" password created earlier on the primary:
# sudo -u postgres -i
$ pg_basebackup -h <IP-of-primary> -D /var/lib/postgresql/14/main -U replicator -P -v -R
$ exit
Start the secondary:
# systemctl start postgresql
Verify replication: list of databases on the secondary does not have a "test" database:
# sudo -u postgres psql -l 2>/dev/null | grep test
#
On the primary, create a test database:
$ lxc shell j1
# sudo -u postgres createdb test
could not change directory to "/root": Permission denied
# sudo -u postgres psql -l 2>/dev/null | grep test
test | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
On the secondary, verify that the test database now exists:
$ lxc shell j2
# sudo -u postgres psql -l 2>/dev/null | grep test
test | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
Check that the secondary does have a "walreceiver" process running:
$ lxc shell j2
# ps axw|grep -E "postgres:.*wal" | grep -v grep
6001 ? Ss 0:06 postgres: 14/main: walreceiver streaming 0/7000780
Now run this long command, one line, on the secondary.
Actual result:
With the bug present, the command will complain that the walreceiver process is NOT running:
# OCF_RESKEY_check_wal_receiver=true OCF_RESKEY_socketdir=/run/postgresql OCF_RESKEY_config=/etc/postgresql/14/main/postgresql.conf OCF_RESKEY_pgctl=/usr/lib/postgresql/14/bin/pg_ctl OCF_RESKEY_pgdata=/var/lib/postgresql/14/main OCF_ROOT=/usr/lib/ocf /usr/lib/ocf/resource.d/heartbeat/pgsql monitor
INFO: Don't check /var/lib/postgresql/14/main during probe
WARNING: wal receiver process is not running
Expected result:
The warning is not present in the output.
[Where problems could occur]
* The patch itself modifies only the heartbeat/pgsql code, so regressions should be limited to the behavior of pgsql.
* Since the code changes affect the pgsql_wal_receiver_status() function, there might be a problem related to the status of the wal receiver and the status of the running processes.
----------------------------original bug report---------------------------
Hello!
Ubuntu 22.04.2 include packages resource-agents version 4.7.0 (release 2020). This version non-correct work with PostgreSQL 11 and above.
resource-agents get fix 'WAL receiver process' in 4.8.0 rc1 (release 2021)
See links
https://github.com/ClusterLabs/resource-agents/blob/baddb06c9720c8df3dadea42ad863d5948a6345f/ChangeLog
Current version resource-agents 4.12.0
Status changed to 'Confirmed' because the bug affects multiple users.