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 1tKeNq-009yXC-Sz for psycopg@arkaria.postgresql.org; Mon, 09 Dec 2024 14:05:11 +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 1tKeNo-008yYO-Dm for psycopg@arkaria.postgresql.org; Mon, 09 Dec 2024 14:05:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tKeNn-008yY7-Ul for psycopg@lists.postgresql.org; Mon, 09 Dec 2024 14:05:09 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tKeNl-001uT7-EP for psycopg@postgresql.org; Mon, 09 Dec 2024 14:05:07 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-a9a977d6cc7so634373966b.3 for ; Mon, 09 Dec 2024 06:05:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=g-skku-edu.20230601.gappssmtp.com; s=20230601; t=1733753103; x=1734357903; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=SJIlHHvmW/cMV3Hz9zvEX6FuBUV7vKe790otPQ1C43E=; b=YYrf0vvx2owO0e3R06w0p2yotZK6s93J77ivlNtuFobuSeQNQlAhk+BT/AZ0SLTHtV LKy0HPyWMk2R/1k7bsJqWPuGAcqvS77dcFx31VjNnxr55iXSp2kWM8BxjSEnx4Fr3DAb 6BkJqgIk46gxytX1LiRmGg4AFRkOvi/OeuqP56yaTQu93GSPRjOzAWzTeSZ2LHOPDPI7 +l4RXgUQJRHX+YLoxWm7SLVIqSfisRteNiyLJE15ySZCdENkVUIHoVIVH75P5Kgg71Sq btOzTfU5Y5Q6/zxUnP4LiYLvClMXhYQOQxMb+OQLXQP0MmecaVF7QSv5FrwYHM6u6ggm 7svw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1733753103; x=1734357903; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=SJIlHHvmW/cMV3Hz9zvEX6FuBUV7vKe790otPQ1C43E=; b=UJ0GrAxidGRg6UO127itwO8Mlfc4G6bEJbstc0UN8868p3nvxEx7+rUnL1COX4z+54 ypfTF3DMWV+upclKufGUVoWxXcneVOwVtNkia1oLzGtncGUch6CJOmtXpsOYcIm8dtFD qOP41R+TQ6absqJX86rjAiP2am5PbbWpCX2tt2vMp5Am7pQYpFKBpK2q8S6fdWQxidGO Fw39G4V7vF6EmILkTV20K/ysoeFrNnwwpadZls7RMseaY7mnJSglzNuGRNzK9sRXNl0X TDQ1FkAi62k5zU04eF1CMeCYojR8cY3TAaa1uhHp7QNH5xypOzqvavJGZQL5nozcSpCJ BQQQ== X-Forwarded-Encrypted: i=1; AJvYcCXrooEa1115TVJKq+monlwJX/xVp8J6y74bJ1OD0Hn0U7Wyo5KM5tGSig0dF5z6EhIWLJfxyicH@postgresql.org X-Gm-Message-State: AOJu0YwXEpYchUXSSjXrIpC34zULO7amMVmp4U1YeHlMDZjjaPmef27v Vmj7GJFaHVTdqepZYqoptE7fyBK5ul5GYUvZGZFdgZ0z6cPiu8m4Uuf+xZCn8KMnte+xV7ahHBA pb0l907u/lByKWos7edsi4zwJtws3oTK8sQ4MNpYTXh/qAK6eo4BXuA== X-Gm-Gg: ASbGncuZ+X6AYeclvAkviytt/6evwlpjCnN4+awpAFIPOIEVBdetzdaXS/3fyOVZ906 I81WaGfIx3X2Hga3r0mrZoq9odFzQUw== X-Google-Smtp-Source: AGHT+IFPhkjbAiKdVhgDpmLB9Th7HCh6gHGjp8Ra9+WQiiY+hMXkj6v8oizT21yu20LDyVGs1vJtxrnDWmp9WGNVAC0= X-Received: by 2002:a05:6402:1e90:b0:5d0:abb8:7a3 with SMTP id 4fb4d7f45d1cf-5d4184dced6mr1740793a12.6.1733753102676; Mon, 09 Dec 2024 06:05:02 -0800 (PST) MIME-Version: 1.0 References: <5ed40ad0-5012-48fa-b5c0-61abf7713080@aklaver.com> In-Reply-To: From: YoungUk Song Date: Mon, 9 Dec 2024 23:04:51 +0900 Message-ID: Subject: Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections To: Adrian Klaver , psycopg@postgresql.org Content-Type: multipart/alternative; boundary="000000000000d058ba0628d6d9f9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d058ba0628d6d9f9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you for your response! I apologize for the confusion in my earlier message. I need to check whether more than 500 different database servers (not just one) are operating in read-only or read-write mode using Psycopg. Are there any best practices for efficiently handling this task across such a large number of servers? Additionally, I=E2=80=99m considering using the target_session_attrs parame= ter to solve this problem. However, I=E2=80=99m unsure how to handle connection errors effectively in = this context. Specifically, I=E2=80=99d like to differentiate between connection failures= and situations where the server is not in standby mode. Can this be achieved using Psycopg's error-handling features, as described here: https://www.psycopg.org/psycopg3/docs/api/errors.html? Thanks! 2024=EB=85=84 12=EC=9B=94 9=EC=9D=BC (=EC=9B=94) =EC=98=A4=EC=A0=84 9:19, Y= oungUk Song =EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1: > Thanks for your reply! > > Actually, I made a mistake in my previous message. I need to check whethe= r > more than 500 different database servers (not just one) are in read-only = or > read-write mode using psycopg. I=E2=80=99m wondering if there are any bes= t > practices for checking all of them. > > Also, I=E2=80=99m not sure, but can the target_session_attrs parameter be= used in > this situation? > > Thanks! > > 2024=EB=85=84 12=EC=9B=94 9=EC=9D=BC (=EC=9B=94) =EC=98=A4=EC=A0=84 2:10,= Adrian Klaver =EB=8B=98=EC=9D=B4 =EC=9E=91=EC= =84=B1: > >> On 12/8/24 04:45, YoungUk Song wrote: >> > Hi Team, >> > >> > I=E2=80=99m currently implementing a solution to establish more than 5= 00 >> > database connections to a single server to verify whether the >> PostgreSQL >> > server is in read-only or read-write mode. >> The server as whole is either in recovery or it is not, that takes one >> connection to determine. What are the remaining 499 connections for? >> >> > >> > My current approach is to create individual database connections and >> > execute the |pg_is_in_recovery()| function for each connection to >> > determine the mode. >> > >> > I=E2=80=99m curious if there are any best practices or more efficient = methods >> to >> > achieve this. >> > >> > Additionally, I=E2=80=99m not very familiar with Psycopg, so I would g= reatly >> > appreciate any advice or suggestions on areas that might need >> improvement. >> > >> > Looking forward to your insights! >> > >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com >> >> --000000000000d058ba0628d6d9f9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Thank you for your response!=C2=A0

I apologize fo= r the confusion in my earlier message.=C2=A0

I need to check whether = more than 500 different database servers (not just one) are operating in re= ad-only or read-write mode using Psycopg.=C2=A0

Are there any best pr= actices for efficiently handling this task across such a large number of se= rvers?


Additionally, I=E2=80=99m considering using the target_session_attrs parameter to solve this problem.=C2=A0

However, I=E2=80=99m unsure how to handle connection errors effectively in= this context.=C2=A0

Specifically, I=E2=80=99d like to differentiate = between connection failures and situations where the server is not in stand= by mode.=C2=A0

Can this be achieved using Psycopg's error-handlin= g features, as described here: https://www.psycopg.org/psycopg3/doc= s/api/errors.html?


Thanks!


20= 24=EB=85=84 12=EC=9B=94 9=EC=9D=BC (=EC=9B=94) =EC=98=A4=EC=A0=84 9:19, You= ngUk Song <pidaoh@g.skku.edu>= ;=EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1:

Thanks for you= r reply!

Actually, I made a mistake in my previous message. I need to= check whether more than 500 different database servers (not just one) are = in read-only or read-write mode using psycopg. I=E2=80=99m wondering if the= re are any best practices for checking all of them.

Also, I=E2=80=99m= not sure, but can the target_session_attrs parameter be used = in this situation?

Thanks!


2024=EB=85=84 12=EC=9B=94 9=EC=9D=BC (= =EC=9B=94) =EC=98=A4=EC=A0=84 2:10, Adrian Klaver <adrian.klaver@aklaver.com>= =EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1:
On 12/8/24 04:45, YoungUk Song wrote:
> Hi Team,
>
> I=E2=80=99m currently implementing a solution to establish more than 5= 00
> database connections to a single server to verify whether the PostgreS= QL
> server is in read-only or read-write mode.
The server as whole is either in recovery or it is not, that takes one
connection to determine. What are the remaining 499 connections for?

>
> My current approach is to create individual database connections and <= br> > execute the |pg_is_in_recovery()| function for each connection to
> determine the mode.
>
> I=E2=80=99m curious if there are any best practices or more efficient = methods to
> achieve this.
>
> Additionally, I=E2=80=99m not very familiar with Psycopg, so I would g= reatly
> appreciate any advice or suggestions on areas that might need improvem= ent.
>
> Looking forward to your insights!
>

--
Adrian Klaver
adrian.klave= r@aklaver.com

--000000000000d058ba0628d6d9f9--