Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r5QUC-00DRZU-Mo for pgsql-jdbc@arkaria.postgresql.org; Tue, 21 Nov 2023 13:08:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r5QUB-00Gfbn-Am for pgsql-jdbc@arkaria.postgresql.org; Tue, 21 Nov 2023 13:08:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r5QUB-00Gfab-1T for pgsql-jdbc@lists.postgresql.org; Tue, 21 Nov 2023 13:08:15 +0000 Received: from pgintl.fastcrypt.com ([149.56.129.164]) by magus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r5QU7-007baA-Op for pgsql-jdbc@lists.postgresql.org; Tue, 21 Nov 2023 13:08:14 +0000 Received: from mail-lf1-f43.google.com (mail-lf1-f43.google.com [209.85.167.43]) by pgintl.fastcrypt.com (Postfix) with ESMTPSA id CB479202BC for ; Tue, 21 Nov 2023 08:08:08 -0500 (EST) Received: by mail-lf1-f43.google.com with SMTP id 2adb3069b0e04-5094727fa67so7738054e87.3 for ; Tue, 21 Nov 2023 05:08:08 -0800 (PST) X-Gm-Message-State: AOJu0YyYvS/LkYEDMJjHoEcc58n8+GvizPWwPcHgXFa4Y/TlOv0yis2m skgh1NNATKba5VKTd05pivmHBlyafxJH2+uJrX8= X-Google-Smtp-Source: AGHT+IH/W65mkKFN/UHQWneNWZrBy12FWbvj8bZRJDWQy3mHE+6KPepy/EzyTbIjO/xLVVsJdV/EW9y/5+zVrp1UWfo= X-Received: by 2002:a05:6512:3b2a:b0:50a:aa7d:2c18 with SMTP id f42-20020a0565123b2a00b0050aaa7d2c18mr7938130lfv.61.1700572084051; Tue, 21 Nov 2023 05:08:04 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Dave Cramer Date: Tue, 21 Nov 2023 08:07:46 -0500 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Pg jdbc DNS failover To: Nikolay Prokofiev Cc: pgsql-jdbc@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000fc27e8060aa94a7f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000fc27e8060aa94a7f Content-Type: text/plain; charset="UTF-8" On Tue, 21 Nov 2023 at 04:07, Nikolay Prokofiev wrote: > Hi. According to pgjdbc documantation > , > 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? > Currently no. It would be a useful addition however. Dave --000000000000fc27e8060aa94a7f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable



<= div class=3D"gmail_quote">
On Tue, 21 = Nov 2023 at 04:07, Nikolay Prokofiev <nprokofiev@gmail.com> wrote:

Hi. According to=C2=A0pgjdbc documantation, the driver s= upports failover when ip addresses and ports of host are comma separated, l= ike this:=C2=A0jdbc:postgresql://my-db-host-slave-1:5432,my-db-host-master-1:5432/d= atabase?targetServerType=3Dprimary.

But it's not conveni= ent, because we use k8s for pg, and there may be more slaves added and mast= er ip may be changed.

I want to store all my hosts in DNS and to sp= ecify one hostname like this:=C2=A0jdbc:postgresql://my-db-host.local:5432/database= ?targetServerType=3Dprimary

I have a dns record with 2 ip ad= dresses:

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

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

and I tried to specify it like this:=C2=A0jdbc:postgresql://my-db= -host.local:5432/database?targetServerType=3Dprimary.

But th= is way it only picks the first ip - slave and fails with=C2=A0org.postgresql.util= .PSQLException: Could not find a server with specified targetServerType: pr= imary

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


Currently no.=C2=A0 It would be a useful addition h= owever.

Dave
--000000000000fc27e8060aa94a7f--