On 2023-06-19 16:09:34 -0500, Ron wrote:
> On 6/19/23 12:15, Peter J. Holzer wrote:
> On 2023-06-19 07:49:49 -0500, Ron wrote:
> On 6/19/23 05:33, Peter J. Holzer wrote:
> So (again, as Francisco already wrote) the best way is probably to write
> a simple proxy which uses the database (not DNS) name for routing. I
> seem to remember that nginx has a plugin architecture for protocols so
> it might make sense to write that as an nginx plugin instead of a
> standalone server, but that's really a judgement call the programmer has
> to make. Another possibility would of course be to extend pgbouncer to
> do what the OP needs.
>
> How would this work with JDBC clients?
>
> Same as with any other client, I guess. Any reason why it should be
> different?
>
>
> That goes to my ultimate point: why would this work, when the point of a
> database client is to connect to a database instance on a specific port like
> 5432, not connect to a web server.
Consider this scenario:
You have several databases scattered across several hosts and ports:
db1 host1.example.com:5432
db2 host1.example.com:5433
db3 host2.example.com:5432
db4 host3.example.com:5432
Then you have your proxy/gateway/bouncer (whatever you want to call it)
listening on proxy.example.com:5432.
The clients all connect to proxy.example.com:5432.
The proxy does the TLS handshake (if necessary) and reads the first
packet. This contains the database name. The proxy then uses the
database name to look up where that database resides (e.g. for db3 it
gets host2.example.com:5432) opens a connection to that port (plus TLS
handshake, if necessary) and forwards the packet. After that it just has
to forward packets in both directions until the connection is closed.
The client never knows that the databases are actually on different
hosts and/or ports. As far as it is concerned, all the databases are on
proxy.example.com:5432.
There is one caveat: All the database names need to be unique.
Such a proxy should be straightforward to write. It only needs to
understand two requests of postgresql protocol (initiate TLS and
connect). It would be much simpler than e.g. pg_bouncer which has to
know about authentication, transactions, etc.[1]. Depending on the
expected number of parallel connections and throughput you might want to
consider what programming language and concurrency model
(multi-threaded, async, state machine, ...) to use.
hp
[1] But of course, if pgbouncer already does what you want, don't
reinvent the wheel.
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"