Re: Question: Multiple pg clusters on one server can be reached with the standard port.

Michael:

On Fri, 16 Jun 2023 at 20:26, Michael Weiller <michael(at)weiller(dot)eu> wrote:

> Because with iptables or netfilter I can't forward TCP packets based on the DNS alias name. Or is that possible?

The dns alias name does not reach the listening tcp socket, it is
mapped to the target record, then finally to the A record and this is
what is used to start the tcp connection.

> I have the same problem with nginx.

In nginx you cannot use the dns alias, but if you are using http the
"normal" clients send it as host when they open an url. But there is
no "host" parameter in the pg protocol. There is a dbaname, which can
be used with the adequate software, like pgbouncer, but you said you
do not like it.

You may try to write a small program which parses the startup message
and redirects the connection based on the info there. I'm not sure why
pgbouncer has not this option, but it may be because it has a lot more
option. It does not seem to be that difficult, receive the packet,
parse it, connect to remote, send it the packet and from there on just
forward traffic blindly. Parsing startup seems quite simple,
maintaining a socket pool and forwarding is simple, you could probably
prototype that in an afternoon ( if no guru replies me "but this won't
work because .... ).

> I just looked in the documentation again but I can't find a way to distinguish which cluster to forward to based on the DNS alias.

As I asaid above, DNS alias is not avalilable to the listener. On any
protocol. Things like http work because the clients send the dns alias
on some place on the default usage, but you can write an http client
which sends Host: from the uri given but connects to a different IP
address.

Francisco Olarte.