public inbox for [email protected]
help / color / mirror / Atom feedBest Practices for Checking PostgreSQL Server Mode with Multiple Connections
5+ messages / 3 participants
[nested] [flat]
* Best Practices for Checking PostgreSQL Server Mode with Multiple Connections
@ 2024-12-08 12:45 YoungUk Song <[email protected]>
2024-12-08 17:09 ` Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections Adrian Klaver <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: YoungUk Song @ 2024-12-08 12:45 UTC (permalink / raw)
To: psycopg
Hi Team,
I’m currently implementing a solution to establish more than 500 database
connections to a single server to verify whether the PostgreSQL server is
in read-only or read-write mode.
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’m curious if there are any best practices or more efficient methods to
achieve this.
Additionally, I’m not very familiar with Psycopg, so I would greatly
appreciate any advice or suggestions on areas that might need improvement.
Looking forward to your insights!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections
2024-12-08 12:45 Best Practices for Checking PostgreSQL Server Mode with Multiple Connections YoungUk Song <[email protected]>
@ 2024-12-08 17:09 ` Adrian Klaver <[email protected]>
2024-12-09 14:04 ` Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections YoungUk Song <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2024-12-08 17:09 UTC (permalink / raw)
To: YoungUk Song <[email protected]>; psycopg
On 12/8/24 04:45, YoungUk Song wrote:
> Hi Team,
>
> I’m currently implementing a solution to establish more than 500
> 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’m curious if there are any best practices or more efficient methods to
> achieve this.
>
> Additionally, I’m not very familiar with Psycopg, so I would greatly
> appreciate any advice or suggestions on areas that might need improvement.
>
> Looking forward to your insights!
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections
2024-12-08 12:45 Best Practices for Checking PostgreSQL Server Mode with Multiple Connections YoungUk Song <[email protected]>
2024-12-08 17:09 ` Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections Adrian Klaver <[email protected]>
@ 2024-12-09 14:04 ` YoungUk Song <[email protected]>
2024-12-09 14:14 ` Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections Christophe Pettus <[email protected]>
2024-12-09 16:53 ` Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections Adrian Klaver <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: YoungUk Song @ 2024-12-09 14:04 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; psycopg
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’m considering using the target_session_attrs parameter to
solve this problem.
However, I’m unsure how to handle connection errors effectively in this
context.
Specifically, I’d 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년 12월 9일 (월) 오전 9:19, YoungUk Song <[email protected]>님이 작성:
> Thanks for your 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’m wondering if there are any best
> practices for checking all of them.
>
> Also, I’m not sure, but can the target_session_attrs parameter be used in
> this situation?
>
> Thanks!
>
> 2024년 12월 9일 (월) 오전 2:10, Adrian Klaver <[email protected]>님이 작성:
>
>> On 12/8/24 04:45, YoungUk Song wrote:
>> > Hi Team,
>> >
>> > I’m currently implementing a solution to establish more than 500
>> > 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’m curious if there are any best practices or more efficient methods
>> to
>> > achieve this.
>> >
>> > Additionally, I’m not very familiar with Psycopg, so I would greatly
>> > appreciate any advice or suggestions on areas that might need
>> improvement.
>> >
>> > Looking forward to your insights!
>> >
>>
>> --
>> Adrian Klaver
>> [email protected]
>>
>>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections
2024-12-08 12:45 Best Practices for Checking PostgreSQL Server Mode with Multiple Connections YoungUk Song <[email protected]>
2024-12-08 17:09 ` Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections Adrian Klaver <[email protected]>
2024-12-09 14:04 ` Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections YoungUk Song <[email protected]>
@ 2024-12-09 14:14 ` Christophe Pettus <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Christophe Pettus @ 2024-12-09 14:14 UTC (permalink / raw)
To: YoungUk Song <[email protected]>; +Cc: Adrian Klaver <[email protected]>; psycopg
> On Dec 9, 2024, at 06:04, YoungUk Song <[email protected]> wrote:
> Are there any best practices for efficiently handling this task across such a large number of servers?
Out of curiosity, shouldn't something in your infrastructure know this information already? After all, something created all these servers.
That being said, you will need to open connections to the servers to determine if they are in recovery or not. You might be able to do a small optimization in that, when you connect, you also query to see what servers are attached to that server as binary replicas: you can then remove them from the list of servers that need to be checked, since you know they are in recovery. If you have cascaded binary replicas, however, this could end up creating more work than it solves.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections
2024-12-08 12:45 Best Practices for Checking PostgreSQL Server Mode with Multiple Connections YoungUk Song <[email protected]>
2024-12-08 17:09 ` Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections Adrian Klaver <[email protected]>
2024-12-09 14:04 ` Re: Best Practices for Checking PostgreSQL Server Mode with Multiple Connections YoungUk Song <[email protected]>
@ 2024-12-09 16:53 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2024-12-09 16:53 UTC (permalink / raw)
To: YoungUk Song <[email protected]>; psycopg
On 12/9/24 06:04, YoungUk Song wrote:
> 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?
It is a matter of looping over a set of connection settings and
recording the results. To me that points at a Postgres table with the
connections settings and another table that tracks the results of the
connection attempts.
Then it is a matter of writing code that uses psycopg to make
connections using the connection data on whatever schedule you want to
follow. The results of the connection are then written out to the status
table. Where results can be 1) Successful connection and server mode. 2)
Unsuccessful connection and connection error.
>
> Can this be achieved using Psycopg's error-handling features, as
> described here: https://www.psycopg.org/psycopg3/docs/api/errors.html
> <https://www.psycopg.org/psycopg3/docs/api/errors.html;?
Yes use try/except for the connection attempts and capture the error if
there is one.
>
>
> Thanks!
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-12-09 16:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-08 12:45 Best Practices for Checking PostgreSQL Server Mode with Multiple Connections YoungUk Song <[email protected]>
2024-12-08 17:09 ` Adrian Klaver <[email protected]>
2024-12-09 14:04 ` YoungUk Song <[email protected]>
2024-12-09 14:14 ` Christophe Pettus <[email protected]>
2024-12-09 16:53 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox