3

I'm running a Postgres database on 5432, and pgBouncer on 6432.

All of my scripts point to 6432, but we have a large legacy code base that points to 5432, which we'd like to have user pgBouncer as well.

The best way we've come up with to do this is to run Postgres on 4432 and pgBouncer on 5432 and 6432. Unfortunately we can only find a way to have pbBouncer listen on one port.

Is it possible to have pgBouncer listen on two ports?

We also tried using IPTables to forward 6432 to 5432 transparently, but it didn't work:

sudo iptables -t nat -I PREROUTING -p tcp --dport 6432 -j REDIRECT --to-ports 5432
sudo iptables -t nat -I OUTPUT -p tcp -o lo --dport 6432 -j REDIRECT --to-ports 5432

Still gives this error:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.6432"?

Any suggestions (aside from "update the legacy code base", which is being reserved as a last resort)?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
user1517922
  • 1,308
  • 3
  • 15
  • 22

3 Answers3

1

Your iptables looks ok to me, but don't forget to enable ip forwarding:

sysctl net.ipv4.ip_forward=1
Chris
  • 11
  • 1
0

You can't have pgbouncer listen on more than one port. So using an OS facility to forward the ports is the right way. I'm not an expert on iptables, to I can't comment on your attempt.

Another approach is to run multiple pgbouncer instances on one host.

Peter Eisentraut
  • 35,221
  • 12
  • 85
  • 90
0

This post is very old, but for what it is worth, in our shop we solved the issue by creating a soft link for the redirected port. So in your case:

/tmp/.s.PGSQL.6432 --> /tmp/.s.PGSQL.5432
DBAYoder
  • 83
  • 1
  • 5