vitabaks/autobase

psql: error: connection to server at "172.16.9.33", port 5001 failed

Closed this issue · 2 comments

cann't connect to 5001 port remotely:

psql -h 172.16.9.33 -p 5001 -U postgres -d mydb
psql: error: connection to server at "172.16.9.33", port 5001 failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
curl http://172.16.9.30:8008/replica?lag=100MB&tag_datacenter=db-2
[1] 150559
root@ansible:/home/administrator# {"state": "running", "postmaster_start_time": "2024-12-11 06:56:19.133868+05:00", "role": "primary", "server_version": 170002, "xlog": {"location": 1677889372160}, "timeline": 5, "replication": [{"usename": "replicator", "application_name": "db-3", "client_addr": "172.16.9.32", "state": "streaming", "sync_state": "async", "sync_priority": 0}, {"usename": "replicator", "application_name": "db-2", "client_addr": "172.16.9.31", "state": "streaming", "sync_state": "async", "sync_priority": 0}], "dcs_last_seen": 1733927378, "tags": {"datacenter": "db-1"}, "database_system_identifier": "7446320060672743578", "patroni": {"version": "4.0.4", "scope": "postgres-cluster", "name": "db-1"}}
[1]+  Done                    curl http://172.16.9.30:8008/replica?lag=100MB
curl http://172.16.9.31:8008/replica?lag=100MB&tag_datacenter=db-2
[1] 150560
root@ansible:/home/administrator# {"state": "running", "postmaster_start_time": "2024-12-11 09:25:19.389569+05:00", "role": "replica", "server_version": 170002, "xlog": {"received_location": 1677889372160, "replayed_location": 1677889372160, "replayed_timestamp": "2024-12-11 17:26:03.718353+05:00", "paused": false}, "timeline": 5, "replication_state": "streaming", "dcs_last_seen": 1733927390, "tags": {"datacenter": "db-2"}, "database_system_identifier": "7446320060672743578", "patroni": {"version": "4.0.4", "scope": "postgres-cluster", "name": "db-2"}}
[1]+  Done                    curl http://172.16.9.31:8008/replica?lag=100MB
curl http://172.16.9.32:8008/replica?lag=100MB&tag_datacenter=db-2
[1] 150561
root@ansible:/home/administrator# {"state": "running", "postmaster_start_time": "2024-12-11 06:11:03.515908+05:00", "role": "replica", "server_version": 170002, "xlog": {"received_location": 1677889372160, "replayed_location": 1677889372160, "replayed_timestamp": "2024-12-11 17:26:03.718353+05:00", "paused": false}, "timeline": 5, "replication_state": "streaming", "dcs_last_seen": 1733927390, "tags": {"datacenter": "db-3"}, "database_system_identifier": "7446320060672743578", "patroni": {"version": "4.0.4", "scope": "postgres-cluster", "name": "db-3"}}
[1]+  Done                    curl http://172.16.9.32:8008/replica?lag=100MB
ss -ntlp | grep 6432
LISTEN 0      4096         0.0.0.0:6432      0.0.0.0:*    users:(("pgbouncer",pid=1625,fd=7))                      
LISTEN 0      4096         0.0.0.0:6432      0.0.0.0:*    users:(("pgbouncer",pid=1626,fd=7))                      
LISTEN 0      4096         0.0.0.0:6432      0.0.0.0:*    users:(("pgbouncer",pid=1623,fd=7))                      
LISTEN 0      4096         0.0.0.0:6432      0.0.0.0:*    users:(("pgbouncer",pid=1621,fd=7)) 
 psql -h 172.16.9.31 -p 5432 -U postgres -d mydb
Password for user postgres: 
psql -h 172.16.9.31 -p 6432 -U postgres -d mydb
Password for user postgres: 

I have

pgbouncer_processes: 4
pgbouncer_default_pool_mode: "transaction"

Connections to the 5000, 5432, 6432 port are working, but to 5001 no connection!
PostgreSQL version 17.2

Is it due to any misconfiguration?

check haproxy logs, that's the service listening to port 5001

Solved by commenting # balancer_tags="datacenter=db in the inventory