Description
On a deployment where pgbouncer is being used as a role installed on a HAProxy node, the initial deployment setups up the /etc/pgbouncer/pgbouncer.databases.ini correctly with the following:
* = host=127.0.0.1 port=5432 auth_user=pgbouncer_auth_user
From the docs:
By default, PgBouncer listens for connections on port 6432 and, if no pgbouncer_backend is specified, forwards connections to 127.0.0.1:5432 (which may be either Postgres or haproxy, depending on the architecture).
When attempting a switchover however, patroni will kick in and run the on_role_change.sh script which will then set the host to the be the DB node rather then 127.0.0.1, which is incorrect
tpaexec info
# TPAexec v23.42.0 (branch: (HEAD detached at v23.42.0))
tpaexec=/home/<user>/dev/tpa/bin/tpaexec
TPA_DIR=/home/<user>/dev/tpa
PYTHON=/home/<user>/dev/tpa/tpa-venv/bin/python3 (v3.12.3, venv)
TPA_VENV=/home/<user>/dev/tpa/tpa-venv
ANSIBLE=/home/<user>/dev/tpa/tpa-venv/bin/ansible (v2.16.16)
HEAD detached at v23.42.0
nothing to commit, working tree clean
tpaexec configure or config.yml
tpaexec configure ~/dev/tpaclusters/cluster \
--architecture M1 \
--platform bare \
--postgresql 17 \
--failover-manager patroni \
--keyring-backend legacy \
--enable-haproxy \
--no-git
- Name: server1
ip_address: 10.1.2.3
location: main
node: 8
role:
- haproxy
- pgbouncer
vars:
haproxy_backend_servers:
- dbserver1
- dbserver2
- dbserver3
- dbserver4
haproxy_bind_address: 0.0.0.0
- Name: server12
ip_address: 10.1.2.4
location: main
node: 9
role:
- haproxy
- pgbouncer
vars:
haproxy_backend_servers:
- dbserver1
- dbserver2
- dbserver3
- dbserver4
haproxy_bind_address: 0.0.0.0Logs
Reproduction steps
1. Run configure
2. run deploy
3. edit config.yml
4. run deploy
All succeeds and `/etc/pgbouncer/pgbouncer.databases.ini has correct entries
To fail the build, run
1. Switchover (choose any node)
2. Switchback (fails)
Expected result
/etc/pgbouncer/pgbouncer.databases.ini set to
- = host=127.0.0.1 port=5432 auth_user=pgbouncer_auth_user
Actual result
/etc/pgbouncer/pgbouncer.databases.ini set to
- = host=dbserver2 port=5432 auth_user=pgbouncer_auth_user
Comment
~tpa/roles/patroni/config/tasks/config.yml needs logic to detect the fact that pgbouncer needs installing, but to not push the on_role-change script out if this is a HAProxy bound instance of pgbouncer.
Description
On a deployment where pgbouncer is being used as a role installed on a HAProxy node, the initial deployment setups up the
/etc/pgbouncer/pgbouncer.databases.inicorrectly with the following:* = host=127.0.0.1 port=5432 auth_user=pgbouncer_auth_userFrom the docs:
By default, PgBouncer listens for connections on port 6432 and, if no pgbouncer_backend is specified, forwards connections to 127.0.0.1:5432 (which may be either Postgres or haproxy, depending on the architecture).
When attempting a switchover however, patroni will kick in and run the
on_role_change.shscript which will then set the host to the be the DB node rather then 127.0.0.1, which is incorrecttpaexec info
tpaexec configure or config.yml
tpaexec configure ~/dev/tpaclusters/cluster \ --architecture M1 \ --platform bare \ --postgresql 17 \ --failover-manager patroni \ --keyring-backend legacy \ --enable-haproxy \ --no-git - Name: server1 ip_address: 10.1.2.3 location: main node: 8 role: - haproxy - pgbouncer vars: haproxy_backend_servers: - dbserver1 - dbserver2 - dbserver3 - dbserver4 haproxy_bind_address: 0.0.0.0 - Name: server12 ip_address: 10.1.2.4 location: main node: 9 role: - haproxy - pgbouncer vars: haproxy_backend_servers: - dbserver1 - dbserver2 - dbserver3 - dbserver4 haproxy_bind_address: 0.0.0.0Logs
Reproduction steps
Expected result
/etc/pgbouncer/pgbouncer.databases.ini set to
Actual result
/etc/pgbouncer/pgbouncer.databases.ini set to
Comment
~tpa/roles/patroni/config/tasks/config.yml needs logic to detect the fact that pgbouncer needs installing, but to not push the on_role-change script out if this is a HAProxy bound instance of pgbouncer.