0

I have postgresql installed on vultr "Ubuntu 20.04". I installed Pgbouncer for connection pooling and configure it as bellow, but but pgbouncer is rejecting connection with the vultr ip address. it is only working with "localhost (127.0.0.1)". I have added both port "6432 and 5432" to the firewall rules but still cant connect, the command I tried was "sudo psql -h 139..22*.* -U myuser -d mydb -p 6432". I keep on getting the error "psql: error: could not connect to server: Connection refused. Is the server running on host "139..22*.*" and accepting TCP/IP connections on port 6432?"

# postgresql.conf file bellow:
listen_addresses = '*'

port = 5432

# pgbouncer.ini file bellow:
 [databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres

mydb = host=127.0.0.1 port=5432 dbname=mydb

 [pgbouncer]

listen_addr = *

listen_port = 6432

# pg_hba.conf file below:
IPv4 local connections:

host    all             all             0.0.0.0/0            md5

IPv6 local connections:

host    all             all             ::1/128                 md5
Johndev247
  • 91
  • 6
  • I suspect some kind of user error, like the ini file you have created is not the one actually being used, or the host which pgbouncer is running on doesn't have the IP address you think it does. What happens if you try connecting to 5432 instead? – jjanes May 20 '23 at 15:51
  • @jjanes If i use 5432, it will connect but that would be bypassing pgBouncer right? – Johndev247 May 20 '23 at 16:46
  • Right. But the question is, does it work (when used from the other machine)? This is a debugging experiment, not a proposed solution. – jjanes May 20 '23 at 18:38
  • @jjanes yes it works with my Front end (React native app) and also able to connect is with pgAdmin 4. both works well on the server ip address and port 5432. Thanks for helping me debug this. I cant just get it work on the pgbouncer port 6432. – Johndev247 May 20 '23 at 21:36
  • In this case where you can reach 5432 but not 6432 from the remote but you can reach 6432 from localhost, I would say your don't really have listen_addr set correctly. It looks good in what you show, but maybe the real file has a typo, or you didn't restart pgbouncer after making the edit, or it is the wrong ini file altogether, something like that. – jjanes May 21 '23 at 01:06
  • @jjanes Thank you very much, you are very right about listen address. I checked the pgbouncer.ini file and found out that at the tail end of it, there was a deceleration of listen_addr and port again, which the adress was set to 127.0.0.1 I have removed those lines, save and restart pgbouncer and now everything works well. Thank you very much. – Johndev247 May 21 '23 at 01:32

1 Answers1

0

To anyone who may likely face the same issue as me in the future, because I have seen someone posted exact issue like mine here some years ago but unfortunately did not get an answer. its probably because there is no way someone can know where the little error is, especially when all looks good.

Look out

In my own case, In pgbouncer.ini file

[pgbouncer]

I have set

listen_addr = *

and

listen_port = 6432

for it to accept connection from anywhere but I was not aware that somewhere inside the pgbouncer.ini file there was another declaration of listen_addr which was set to "127.0.0.1" and listen_port to "6432". So its advised to check well, if found another one then clear the wrong one, save the file and restart pgbouncer.

Johndev247
  • 91
  • 6