Thread: Queries in replica are failing
Hello -
Queries in replica instance are failing with the error "cancelling statement due to conflict with recovery". I was checking two parameters (max_standby_archive_delay and max_standby_streaming_delay) which may allow the queries to run within the time defined in those.
Is it recommended to set those? Is there any other suggestion to tackle this?
Regards
Siraj
Hi,
I would probably use "hot_standby_feedback" rather than change the delay parameters, unless you want to have the read replica (Standby) actually to operate some time behind the primary, for some reason (like having a copy of the data an hour old to fix mistakes on the primary).
https://postgresqlco.nf/doc/en/param/hot_standby_feedback/
The hot_standby_feedback parameter sends feedback to the primary, so the transaction is less likely to be cancelled. The only draw back is that is can cause some bloat on the primary database.
Regards,
Matt
On 16/05/2024 13:01, Siraj G wrote:
Hello -Queries in replica instance are failing with the error "cancelling statement due to conflict with recovery". I was checking two parameters (max_standby_archive_delay and max_standby_streaming_delay) which may allow the queries to run within the time defined in those.Is it recommended to set those? Is there any other suggestion to tackle this?RegardsSiraj
--
On Thu, 2024-05-16 at 17:31 +0530, Siraj G wrote: > Queries in replica instance are failing with the error "cancelling statement due > to conflict with recovery". I was checking two parameters (max_standby_archive_delay > and max_standby_streaming_delay) which may allow the queries to run within the > time defined in those. > > Is it recommended to set those? Is there any other suggestion to tackle this? The only safe way to avoid this error is to set "max_standby_streaming_delay" to -1. See https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/ Yours, Laurenz Albe
I received this error when i set max_standby_streaming_delay to -1 and max_standby_archive_delay to 30 seconds.
| The following processes were terminated on your read replica because they were interfering with the replay of transaction logs: [18218]. |
I was also receiving an error when i set hot_standby_feedback.
Kindly suggest.
On Thu, May 16, 2024 at 8:04 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2024-05-16 at 17:31 +0530, Siraj G wrote:
> Queries in replica instance are failing with the error "cancelling statement due
> to conflict with recovery". I was checking two parameters (max_standby_archive_delay
> and max_standby_streaming_delay) which may allow the queries to run within the
> time defined in those.
>
> Is it recommended to set those? Is there any other suggestion to tackle this?
The only safe way to avoid this error is to set "max_standby_streaming_delay" to -1.
See https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/
Yours,
Laurenz Albe
Have a Good day !!!
Regards
Rohit Sachdeva
Regards
Rohit Sachdeva
On Wed, 2024-05-29 at 22:34 +0530, ROHIT SACHDEVA wrote: > I received this error when i set max_standby_streaming_delay to -1 and max_standby_archive_delay to 30 seconds. > > The following processes were terminated on your read replica because they were interfering with the replay of transactionlogs: [18218]. Ah, then you are recovering from an archive. In that case, set "max_standby_archive_delay" to -1 as well. Yours, Laurenz Albe
Hi ,
If I set both to -1 storage space is increasing rapidly.
Any way out as they are okay with 1 day delay but queries should run smoothly.
Can I pause or schedule the replication process replication at 12 am in Postgres RDS.
On Thu, 30 May, 2024, 2:47 am Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
On Wed, 2024-05-29 at 22:34 +0530, ROHIT SACHDEVA wrote:
> I received this error when i set max_standby_streaming_delay to -1 and max_standby_archive_delay to 30 seconds.
>
> The following processes were terminated on your read replica because they were interfering with the replay of transaction logs: [18218].
Ah, then you are recovering from an archive.
In that case, set "max_standby_archive_delay" to -1 as well.
Yours,
Laurenz Albe
On Thu, 2024-05-30 at 06:59 +0530, ROHIT SACHDEVA wrote: > On Thu, 30 May, 2024, 2:47 am Laurenz Albe, <laurenz.albe@cybertec.at> wrote: > > On Wed, 2024-05-29 at 22:34 +0530, ROHIT SACHDEVA wrote: > > > I received this error when i set max_standby_streaming_delay to -1 and max_standby_archive_delay to 30 seconds. > > > > > > The following processes were terminated on your read replica because they were interfering with the replay of transactionlogs: [18218]. > > > > Ah, then you are recovering from an archive. > > > > In that case, set "max_standby_archive_delay" to -1 as well. > > If I set both to -1 storage space is increasing rapidly. > > Any way out as they are okay with 1 day delay but queries should run smoothly. > > Can I pause or schedule the replication process replication at 12 am in Postgres RDS. Sure, WAL will pile up on the standby if it cannot be replayed right away. You could stop replication, but then WAL would pile up on the primary server. You have to keep the information somewhere. Yours, Laurenz Albe
okay..
So What will be the work around for this.
On Thu, May 30, 2024 at 11:37 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2024-05-30 at 06:59 +0530, ROHIT SACHDEVA wrote:
> On Thu, 30 May, 2024, 2:47 am Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> > On Wed, 2024-05-29 at 22:34 +0530, ROHIT SACHDEVA wrote:
> > > I received this error when i set max_standby_streaming_delay to -1 and max_standby_archive_delay to 30 seconds.
> > >
> > > The following processes were terminated on your read replica because they were interfering with the replay of transaction logs: [18218].
> >
> > Ah, then you are recovering from an archive.
> >
> > In that case, set "max_standby_archive_delay" to -1 as well.
>
> If I set both to -1 storage space is increasing rapidly.
>
> Any way out as they are okay with 1 day delay but queries should run smoothly.
>
> Can I pause or schedule the replication process replication at 12 am in Postgres RDS.
Sure, WAL will pile up on the standby if it cannot be replayed right away.
You could stop replication, but then WAL would pile up on the primary server.
You have to keep the information somewhere.
Yours,
Laurenz Albe
Have a Good day !!!
Regards
Rohit Sachdeva
Regards
Rohit Sachdeva
On Fri, 2024-05-31 at 23:37 +0530, ROHIT SACHDEVA wrote: > On Thu, May 30, 2024 at 11:37 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > Sure, WAL will pile up on the standby if it cannot be replayed right away. > > > > You could stop replication, but then WAL would pile up on the primary server. > > > > You have to keep the information somewhere. > > So What will be the work around for this. Get more disk space, or perform less work on the database. Yours, Laurenz Albe
Can I pause or stop the replication at a particular time in postgres RDS.?
On Fri, May 31, 2024 at 11:47 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2024-05-31 at 23:37 +0530, ROHIT SACHDEVA wrote:
> On Thu, May 30, 2024 at 11:37 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > Sure, WAL will pile up on the standby if it cannot be replayed right away.
> >
> > You could stop replication, but then WAL would pile up on the primary server.
> >
> > You have to keep the information somewhere.
>
> So What will be the work around for this.
Get more disk space, or perform less work on the database.
Yours,
Laurenz Albe
Have a Good day !!!
Regards
Rohit Sachdeva
Regards
Rohit Sachdeva
The correct answer, IMHO, is to not use the replica as a RO query db.
It’s not exactly specifically designed for that, and causes these exact issues.
All operations on Leader must happen first. The Replica cannot and should not be at blame for this. It’s not meant to be optimized.
If you really need a RO for research. DUMP and create a maindb_research.
This can all be automated with a shell script, so every morning you have a fresh research db.
You CAN do what you’re doing, it doesn’t mean you should.
You can cause major issues with a wrong click in the Replica, even in it’s RO status.
From: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
Sent: Friday, May 31, 2024 11:21 AM
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Siraj G <tosiraj.g@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] Re: Queries in replica are failing
Can I pause or stop the replication at a particular time in postgres RDS.?
On Fri, May 31, 2024 at 11:47 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2024-05-31 at 23:37 +0530, ROHIT SACHDEVA wrote:
> On Thu, May 30, 2024 at 11:37 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > Sure, WAL will pile up on the standby if it cannot be replayed right away.
> >
> > You could stop replication, but then WAL would pile up on the primary server.
> >
> > You have to keep the information somewhere.
>
> So What will be the work around for this.
Get more disk space, or perform less work on the database.
Yours,
Laurenz Albe
--
Have a Good day !!!
Regards
Rohit Sachdeva
Hi Team,
There is a requirement of giving select access on few tables and hide other tables in a database of postgres 14 in RDS.
Is it possible or not.
What is the way around for the same.
On Thu, 16 May, 2024, 5:31 pm Siraj G, <tosiraj.g@gmail.com> wrote:
Hello -Queries in replica instance are failing with the error "cancelling statement due to conflict with recovery". I was checking two parameters (max_standby_archive_delay and max_standby_streaming_delay) which may allow the queries to run within the time defined in those.Is it recommended to set those? Is there any other suggestion to tackle this?RegardsSiraj
On Thu, Mar 27, 2025 at 9:10 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:
Hi Team,There is a requirement of giving select access on few tables and hide other tables in a database of postgres 14 in RDS.On Thu, 16 May, 2024, 5:31 pm Siraj G, <tosiraj.g@gmail.com> wrote:Hello -Queries in replica instance are failing with the error "cancelling statement due to conflict with recovery". I was checking two parameters (max_standby_archive_delay and max_standby_streaming_delay) which may allow the queries to run within the time defined in those.
It is very bad list etiquette to reply to an email thread with an entirely new subject. Just send a new email to the list. You should include what you mean by "hide" when you do so.
David J.
Hi David
Hide means only that table will be visible where I will give select access.
On Thu, 27 Mar, 2025, 9:44 pm David G. Johnston, <david.g.johnston@gmail.com> wrote:
On Thu, Mar 27, 2025 at 9:10 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:Hi Team,There is a requirement of giving select access on few tables and hide other tables in a database of postgres 14 in RDS.On Thu, 16 May, 2024, 5:31 pm Siraj G, <tosiraj.g@gmail.com> wrote:Hello -Queries in replica instance are failing with the error "cancelling statement due to conflict with recovery". I was checking two parameters (max_standby_archive_delay and max_standby_streaming_delay) which may allow the queries to run within the time defined in those.It is very bad list etiquette to reply to an email thread with an entirely new subject. Just send a new email to the list. You should include what you mean by "hide" when you do so.David J.
Hidden in what way? What application(s) are the end users using?
On Thu, Mar 27, 2025 at 12:17 PM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:
Hi DavidHide means only that table will be visible where I will give select access.On Thu, 27 Mar, 2025, 9:44 pm David G. Johnston, <david.g.johnston@gmail.com> wrote:On Thu, Mar 27, 2025 at 9:10 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:Hi Team,There is a requirement of giving select access on few tables and hide other tables in a database of postgres 14 in RDS.On Thu, 16 May, 2024, 5:31 pm Siraj G, <tosiraj.g@gmail.com> wrote:Hello -Queries in replica instance are failing with the error "cancelling statement due to conflict with recovery". I was checking two parameters (max_standby_archive_delay and max_standby_streaming_delay) which may allow the queries to run within the time defined in those.It is very bad list etiquette to reply to an email thread with an entirely new subject. Just send a new email to the list. You should include what you mean by "hide" when you do so.David J.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
database users that will be connecting the database via pgadmin tool.
Is that possible as do we not want the other tables/schemas to be exposed to that database users.
On Thu, Mar 27, 2025 at 11:14 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
Hidden in what way? What application(s) are the end users using?On Thu, Mar 27, 2025 at 12:17 PM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:Hi DavidHide means only that table will be visible where I will give select access.On Thu, 27 Mar, 2025, 9:44 pm David G. Johnston, <david.g.johnston@gmail.com> wrote:On Thu, Mar 27, 2025 at 9:10 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:Hi Team,There is a requirement of giving select access on few tables and hide other tables in a database of postgres 14 in RDS.On Thu, 16 May, 2024, 5:31 pm Siraj G, <tosiraj.g@gmail.com> wrote:Hello -Queries in replica instance are failing with the error "cancelling statement due to conflict with recovery". I was checking two parameters (max_standby_archive_delay and max_standby_streaming_delay) which may allow the queries to run within the time defined in those.It is very bad list etiquette to reply to an email thread with an entirely new subject. Just send a new email to the list. You should include what you mean by "hide" when you do so.David J.--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
Have a Good day !!!
Regards
Rohit Sachdeva
Regards
Rohit Sachdeva
I'm not really sure you can hide the existence of an object from a determined client application. After all, they live in pg_class, and every connection must read pg_class (and pg_namespace, pg_aatribute, pg_attrdef, pg_index, etc, etc) to be able to know how to translate table names and column names to oid values.
Having said that, I do not know how the PgAdmin GUI treats schemata and tables that the connected role does not have access to.
But even if the GUI sidebar hides objects the user does not have permission to access, the user should be able to directly query pg_namespace, pg_class, etc to learn the existence of tables, and their structures IF the user is technically savvy enough to use Google., read the docs and StackEExchange.
On Thu, Mar 27, 2025 at 2:43 PM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:
database users that will be connecting the database via pgadmin tool.Is that possible as do we not want the other tables/schemas to be exposed to that database users.On Thu, Mar 27, 2025 at 11:14 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:Hidden in what way? What application(s) are the end users using?On Thu, Mar 27, 2025 at 12:17 PM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:Hi DavidHide means only that table will be visible where I will give select access.On Thu, 27 Mar, 2025, 9:44 pm David G. Johnston, <david.g.johnston@gmail.com> wrote:On Thu, Mar 27, 2025 at 9:10 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:Hi Team,There is a requirement of giving select access on few tables and hide other tables in a database of postgres 14 in RDS.On Thu, 16 May, 2024, 5:31 pm Siraj G, <tosiraj.g@gmail.com> wrote:Hello -Queries in replica instance are failing with the error "cancelling statement due to conflict with recovery". I was checking two parameters (max_standby_archive_delay and max_standby_streaming_delay) which may allow the queries to run within the time defined in those.It is very bad list etiquette to reply to an email thread with an entirely new subject. Just send a new email to the list. You should include what you mean by "hide" when you do so.David J.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!