Thread: Client not able to pick up
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.
> 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 andrunning. Because a client set a connection to read only, then later that pgbouncer -> server connection was assigned to a differentclient. 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
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
> 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 onereporting the error.
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.
How are you using HAProxy??? PostgreSQL can only have one master taking writes. So if you're sending write transactions toHAProxy 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 theone reporting the error.
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.
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 toHAProxy 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!-cUmWeivyjb9sLhqVyIeglhtKuj230PMHCEg0m3xbC0RbLxyHghLO_60OsTj08DhwgwcRaTSTzhZjBx_ULt1xWlvfX1tmGFHxXBbiws$ > 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 theone reporting the error. ---------------------------------------------------------------------- CONFIDENTIALITY NOTICE: If you have received this email in error, please immediately notify the sender by e-mail at the addressshown. This email transmission may contain confidential information. This information is intended only for the useof the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your filesif you are not the intended recipient. Thank you for your compliance. Copyright (c) 2024 Evernorth
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