CODEWITHSUNDEEP
linuxpostgresqlportiptablespgbouncer
user1517922
user1517922

Reputation: 1468

pgBouncer on Multiple Ports?

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)?

Upvotes: 3

Views: 1656

Answers (3)

DBAYoder
DBAYoder

Reputation: 93

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

Upvotes: 0

Chris
Chris

Reputation: 11

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

sysctl net.ipv4.ip_forward=1

Upvotes: 1

Peter Eisentraut
Peter Eisentraut

Reputation: 36759

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.

Upvotes: 0

Related Questions