pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedClient not able to pick up
9+ messages / 3 participants
[nested] [flat]
* Client not able to pick up
@ 2024-07-02 05:57 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rajesh Kumar @ 2024-07-02 05:57 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi we use patroni 3.1 , pgbouncer, openshift 4.1 , postgres 15.6
Recently we upgraded openshift platform to 4.13 and postgres server is up
and running and now we are witnessing frequent restarts (I can see timeline
added in patronictl history) or whenever failover happened or whenever
system automatically restarted for some reason (etcd logs says "DCS
communication error", postgres log says "received fast shutdown request"
for the same time), those times, fron the client side they are getting
error "cannot execute update in read only transaction" and is stucked in
this msg.
We have a common hostname grocerydb-primary that resolved both master and
standby.
Now, I want to understand two things 1. What could be the reason for
frequent restarts or shutdown and startups 2. Why client is stucked with
the message " cannot execute update in read only transaction" , eventhough
master is up and running.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Client not able to pick up
@ 2024-07-02 12:42 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Ribe @ 2024-07-02 12:42 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Jul 1, 2024, at 11:57 PM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> 2. Why client is stucked with the message " cannot execute update in read only transaction" , eventhough master is up and running.
Because a client set a connection to read only, then later that pgbouncer -> server connection was assigned to a different client. You have to either:
- reset connections in pgbouncer when they are reassigned, which has its own downsides--see the docs
- fix the client so it doesn't leave connections in read only state
- have those clients connect directly to PG
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Client not able to pick up
@ 2024-07-02 13:25 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rajesh Kumar @ 2024-07-02 13:25 UTC (permalink / raw)
To: scott_ribe@elevated-dev.com; +Cc: pgsql-admin@lists.postgresql.org
Let's ignore pgbouncer. I am getting the same error for client who are
connected directly
On Tue, 2 Jul 2024, 18:12 Scott Ribe, <scott_ribe@elevated-dev.com> wrote:
> > On Jul 1, 2024, at 11:57 PM, Rajesh Kumar <rajeshkumar.dba09@gmail.com>
> wrote:
> >
> > 2. Why client is stucked with the message " cannot execute update in
> read only transaction" , eventhough master is up and running.
>
> Because a client set a connection to read only, then later that pgbouncer
> -> server connection was assigned to a different client. You have to either:
>
> - reset connections in pgbouncer when they are reassigned, which has its
> own downsides--see the docs
> - fix the client so it doesn't leave connections in read only state
> - have those clients connect directly to PG
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Client not able to pick up
@ 2024-07-02 13:51 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Ribe @ 2024-07-02 13:51 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Jul 2, 2024, at 7:25 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> Let's ignore pgbouncer. I am getting the same error for client who are connected directly
Principle is the same, something is setting the read only state.
- Either the database is read only, as for a hot standby for instance;
- Or the user is set to default to read only;
- Or the client is setting read only and not subsequently setting read write.
Ignoring pg bouncer just means excluding the possibility that the read only state was set by some client other than the one reporting the error.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Client not able to pick up
@ 2024-07-03 17:27 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rajesh Kumar @ 2024-07-03 17:27 UTC (permalink / raw)
To: scott_ribe@elevated-dev.com; +Cc: pgsql-admin@lists.postgresql.org
Can this problem due to issues with HAproxy?
On Tue, 2 Jul 2024, 19:22 Scott Ribe, <scott_ribe@elevated-dev.com> wrote:
> > On Jul 2, 2024, at 7:25 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com>
> wrote:
> >
> > Let's ignore pgbouncer. I am getting the same error for client who are
> connected directly
>
> Principle is the same, something is setting the read only state.
>
> - Either the database is read only, as for a hot standby for instance;
> - Or the user is set to default to read only;
> - Or the client is setting read only and not subsequently setting read
> write.
>
> Ignoring pg bouncer just means excluding the possibility that the read
> only state was set by some client other than the one reporting the error.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Client not able to pick up
@ 2024-07-03 18:42 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Scott Ribe @ 2024-07-03 18:42 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
How are you using HAProxy??? PostgreSQL can only have one master taking writes. So if you're sending write transactions to HAProxy to split among master & replicas, then yeah, there's your problem.
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
> On Jul 3, 2024, at 11:27 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> Can this problem due to issues with HAproxy?
>
> On Tue, 2 Jul 2024, 19:22 Scott Ribe, <scott_ribe@elevated-dev.com> wrote:
> > On Jul 2, 2024, at 7:25 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
> >
> > Let's ignore pgbouncer. I am getting the same error for client who are connected directly
>
> Principle is the same, something is setting the read only state.
>
> - Either the database is read only, as for a hot standby for instance;
> - Or the user is set to default to read only;
> - Or the client is setting read only and not subsequently setting read write.
>
> Ignoring pg bouncer just means excluding the possibility that the read only state was set by some client other than the one reporting the error.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Client not able to pick up
@ 2024-07-03 18:49 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 9+ messages in thread
From: Rajesh Kumar @ 2024-07-03 18:49 UTC (permalink / raw)
To: scott_ribe@elevated-dev.com; +Cc: pgsql-admin@lists.postgresql.org
Patronictl and etcd is not enough for autofailover right....there must be
HAproxy setup
On Thu, 4 Jul 2024, 00:13 Scott Ribe, <scott_ribe@elevated-dev.com> wrote:
> How are you using HAProxy??? PostgreSQL can only have one master taking
> writes. So if you're sending write transactions to HAProxy to split among
> master & replicas, then yeah, there's your problem.
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> https://www.linkedin.com/in/scottribe/
>
>
>
> > On Jul 3, 2024, at 11:27 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com>
> wrote:
> >
> > Can this problem due to issues with HAproxy?
> >
> > On Tue, 2 Jul 2024, 19:22 Scott Ribe, <scott_ribe@elevated-dev.com>
> wrote:
> > > On Jul 2, 2024, at 7:25 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com>
> wrote:
> > >
> > > Let's ignore pgbouncer. I am getting the same error for client who are
> connected directly
> >
> > Principle is the same, something is setting the read only state.
> >
> > - Either the database is read only, as for a hot standby for instance;
> > - Or the user is set to default to read only;
> > - Or the client is setting read only and not subsequently setting read
> write.
> >
> > Ignoring pg bouncer just means excluding the possibility that the read
> only state was set by some client other than the one reporting the error.
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: [EXTERNAL] Re: Client not able to pick up
@ 2024-07-03 18:53 Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 9+ messages in thread
From: Wetmore, Matthew (CTR) @ 2024-07-03 18:53 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
My guess is that in their pg_admin config, they are still connecting to the server, not the HAPROXY.
If you connect to the proxy, you will always get the leader. If you connect to the server in pg_admin, you'll get that.
-----Original Message-----
From: Scott Ribe <scott_ribe@elevated-dev.com>
Sent: Wednesday, July 3, 2024 11:43 AM
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: Client not able to pick up
How are you using HAProxy??? PostgreSQL can only have one master taking writes. So if you're sending write transactions to HAProxy to split among master & replicas, then yeah, there's your problem.
--
Scott Ribe
scott_ribe@elevated-dev.com
https://urldefense.com/v3/__https://www.linkedin.com/in/scottribe/__;!!GFE8dS6aclb0h1nkhPf9!-cUmWeiv...
> On Jul 3, 2024, at 11:27 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> Can this problem due to issues with HAproxy?
>
> On Tue, 2 Jul 2024, 19:22 Scott Ribe, <scott_ribe@elevated-dev.com> wrote:
> > On Jul 2, 2024, at 7:25 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
> >
> > Let's ignore pgbouncer. I am getting the same error for client who are connected directly
>
> Principle is the same, something is setting the read only state.
>
> - Either the database is read only, as for a hot standby for instance;
> - Or the user is set to default to read only;
> - Or the client is setting read only and not subsequently setting read write.
>
> Ignoring pg bouncer just means excluding the possibility that the read only state was set by some client other than the one reporting the error.
----------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this email in error, please immediately notify the sender by e-mail at the address shown. This email transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance. Copyright (c) 2024 Evernorth
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Client not able to pick up
@ 2024-07-03 19:03 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Ribe @ 2024-07-03 19:03 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Ah, OK, using it in conjunction with Patroni to get failover is legit.
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
> On Jul 3, 2024, at 12:49 PM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> Patronictl and etcd is not enough for autofailover right....there must be HAproxy setup
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-07-03 19:03 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-02 05:57 Client not able to pick up Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-07-02 12:42 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-07-02 13:25 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-07-02 13:51 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-07-03 17:27 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-07-03 18:42 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-07-03 18:49 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-07-03 19:03 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-07-03 18:53 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox