Single Virtual IP to access DB from client

[IronCarry]

I have already deployed the cluster, and it works fine, i deployed type A with load balancing.
I also added the cluster_vip thinking that with this i can access the db always using this single IP, also if a node die and the master change.
In my case the cluster_vip as been assigned to the node2 (not master) and i also see that if the node die, the vip change correctly.

Cant make psql -U superuser -h 192.168.43.33 -d postgres (the cluste_vip)

psql -U superuser -h 192.168.43.33 -d postgres
psql: error: connection to server at "192.168.43.33", port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

Instead

psql -U superuser -h 192.168.43.32 -d postgres
psql (15.1, server 15.4 (Debian 15.4-1.pgdg120+1))
Type "help" for help.

postgres=# 

I can make that request to the other 3 node, but not on this vip.
i'm missing something? someone already hit this situation?

[vitabaks]

In the "Type A" scheme, the IP address can be run on one of the balancers servers and it will not necessarily be where the database leader role is, in addition, balancers can be separate servers from the database.

Look carefully at the description of the scheme, there are ports for connecting to the database (5000 master, 5001 replicas).

[vitabaks]

I can't reproduce the problem yet

Can you attach an archive of your playbook for analysis?

[vitabaks]

Please show pgbouncer.ini

[IronCarry]

I'm using your playbook, i can give you my vars/main and my inventory, how can i send it to you? do you have an email?

pgbouncer.ini

[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres 

* = host=127.0.0.1 port=5432

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /run/pgbouncer/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = scram-sha-256
auth_user = pgbouncer
auth_dbname = postgres
auth_query = SELECT usename, passwd FROM user_search($1)
admin_users = superuser
stats_users = superuser
ignore_startup_parameters = extra_float_digits,geqo,search_path

pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 10000
default_pool_size = 20
query_wait_timeout = 120
reserve_pool_size = 1
reserve_pool_timeout = 1
max_db_connections = 1000
pkt_buf = 8192
listen_backlog = 4096

log_connections = 0
log_disconnections = 0

# Documentation https://pgbouncer.github.io/config.html

[vitabaks]

[email protected]

[vitabaks]

Fixed here: Compatibility with custom patroni_superuser_username

root@pgnode01:/# psql -h 10.172.0.20 -p 5000 -U superuser -d postgres
Password for user superuser: 
psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))
Type "help" for help.

postgres=# \q
root@pgnode01:/# psql -h 10.172.0.20 -p 5001 -U superuser -d postgres
Password for user superuser: 
psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))
Type "help" for help.

postgres=# \q

Please test the playbook from the 'custom-superuser' branch.

[IronCarry]

Perfect!! Officially done! Ty