Do not perform tasks for creating database users and user_search() function for pgbouncer on the Standby Cluster leader.
If patroni_standby_cluster.host is defined.
Fixed:
TASK [postgresql-users : Make sure the PostgreSQL users are present] ********************************************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: psycopg2.errors.ReadOnlySqlTransaction: cannot execute ALTER ROLE in a read-only transaction
failed: [10.0.1.2] (item=pgbouncer) => {"ansible_loop_var": "item", "changed": false, "item": {"flags": "LOGIN", "name": "pgbouncer", "password": "fMdGkPgoBhY7cMpXAaoJeip3JY2X4aRc", "role": ""}, "msg": "ERROR: cannot execute ALTER ROLE in a read-only transaction\n"}
...ignoring
TASK [pgbouncer/config : Ensure config directory "/etc/pgbouncer" exist] ****************************************************************************************************************************************
ok: [10.0.1.2]
TASK [pgbouncer/config : Update pgbouncer.ini] ******************************************************************************************************************************************************************
ok: [10.0.1.2] => (item=pgbouncer)
TASK [pgbouncer/config : Create function 'user_search' for pgbouncer 'auth_query' option in all databases] ******************************************************************************************************
fatal: [10.0.1.2]: FAILED! => {"changed": true, "cmd": "for db in $(/usr/lib/postgresql/14/bin/psql -p 5432 -U postgres -d postgres -tAXc \"select datname from pg_catalog.pg_database where datname <> 'template0'\"); do\n /usr/lib/postgresql/14/bin/psql -p 5432 -U postgres -d \"$db\" -tAXc '\n CREATE OR REPLACE FUNCTION user_search(uname TEXT) RETURNS TABLE (usename name, passwd text) AS\n $$\n SELECT usename, passwd FROM pg_shadow WHERE usename=$1;\n $$\n LANGUAGE sql SECURITY DEFINER;\n REVOKE ALL ON FUNCTION user_search(uname TEXT) FROM public;\n GRANT EXECUTE ON FUNCTION user_search(uname TEXT) TO pgbouncer;\n '; done\n", "delta": "0:00:00.021285", "end": "2023-12-13 21:47:34.081207", "msg": "non-zero return code", "rc": 1, "start": "2023-12-13 21:47:34.059922", "stderr": "ERROR: cannot execute CREATE FUNCTION in a read-only transaction\nERROR: cannot execute CREATE FUNCTION in a read-only transaction", "stderr_lines": ["ERROR: cannot execute CREATE FUNCTION in a read-only transaction", "ERROR: cannot execute CREATE FUNCTION in a read-only transaction"], "stdout": "", "stdout_lines": []}
NO MORE HOSTS LEFT **********************************************************************************************************************************************************************************************
PLAY RECAP ******************************************************************************************************************************************************************************************************
10.0.1.2 : ok=123 changed=68 unreachable=0 failed=1 skipped=385 rescued=0 ignored=1
localhost : ok=30 changed=7 unreachable=0 failed=0 skipped=142 rescued=0 ignored=0
Do not perform tasks for creating database users and user_search() function for pgbouncer on the Standby Cluster leader.
If
patroni_standby_cluster.host
is defined.Fixed: