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, 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