Pg jdbc DNS failover

From: Nikolay Prokofiev <nprokofiev(at)gmail(dot)com>
To: pgsql-jdbc(at)lists(dot)postgresql(dot)org
Subject: Pg jdbc DNS failover
Date: 2023-11-21 09:07:30
Message-ID: CABwh0FDqOiHh2Y_Qqq8iTOLoKDwxZrgia97wZnWk0n1M_6cJvg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi. According to pgjdbc documantation
<https://jdbc.postgresql.org/documentation/use/#connection-fail-over>, the
driver supports failover when ip addresses and ports of host are comma
separated, like this:
jdbc:postgresql://my-db-host-slave-1:5432,my-db-host-master-1:5432/database?targetServerType=primary
.

But it's not convenient, because we use k8s for pg, and there may be more
slaves added and master ip may be changed.

I want to store all my hosts in DNS and to specify one hostname like this:
jdbc:postgresql://my-db-host.local:5432/database?targetServerType=primary

I have a dns record with 2 ip addresses:

$ nslookup my-db-host.local
Server: 192.168.1.1
Address: 192.168.1.1#53

Non-authoritative answer:
Name: my-db-host-slave-1
Address: 192.168.10.21
Name: my-db-host-master-1
Address: 192.168.11.25

and I tried to specify it like this:
jdbc:postgresql://my-db-host.local:5432/database?targetServerType=primary.

But this way it only picks the first ip - slave and fails with
org.postgresql.util.PSQLException:
Could not find a server with specified targetServerType: primary

Is there any workaround for using dns failover? May be some options in
connection pools if driver does not support that?

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2023-11-21 13:07:46 Re: Pg jdbc DNS failover
Previous Message Dave Cramer 2023-11-20 15:19:19 Re: Mapping of timestamptz to TIMESTAMP vs. TIMESTAMP_WITH_TIMEZONE