MIME-Version: 1.0
From: Feike Steenbergen <feikesteenbergen@gmail.com>
Date: Tue, 13 Aug 2019 11:50:18 +0200
Message-ID: 
 <CAK_s-G2_3S09_EA+nRxxefMW+0-UwKE=Uj6bCdBpPncPVRpM_g@mail.gmail.com>
Subject: Feature: Use DNS SRV records for connecting
To: PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk

Hi all,

I'd like to get some feedback on whether or not implementing a DNS SRV feature
for connecting to PostgreSQL would be desirable/useful.

The main use case is to have a DNS SRV record that lists all the possible
primaries of a given replicated PostgreSQL cluster. With auto failover
solutions like patroni, pg_auto_failover, stolon, etc. any of these endpoints
could be serving the primary server at any point in time.

Combined with target_session_attrs a connection string to a highly-available
cluster could be something like:

   psql "dnssrv=mydb.prod.example.com target_session_attr=read_write"

Which would then resolve the SRV record _postgresql._tcp.mydb.prod.example.com
and using the method described in RFC 2782 connect to the host/port combination
one by one until it finds the primary.

A benefit of using SRV records would be that the port is also part of the DNS
record and therefore a single IP could be used to serve many databases on
separate ports. When working with a cloud environment or containerized setup
(or both) this would open up some good possibilities.

Note: We currently can already do this somehow by specifying multiple
hosts/ports in the connection string, however it would be useful if we could
refer to a single SRV record instead, as that would have a list of hosts
and ports to connect to.

DNS SRV is described in detail here:
https://tools.ietf.org/html/rfc2782

I'd love to hear some support/dissent,

regards,

Feike