Re: PostgreSQL 9.2 high replication lag

[Lucas]

‐‐‐ Original Message ‐‐‐
On Friday, August 20th, 2021 at 4:56 PM, Ben Madin  wrote:

> I realise you may have other complications, but we have upgraded numerous 
> v9.6 servers (on AWS) to v12 in the last year, with basically no problems in 
> the upgrade, and many less problems with streaming. It might take less time 
> to complete the upgrade??

Hi Ben. Thanks for your reply.

I have a Bucardo replication between my EC2 instance PG 9.2 database and RDS 
running PG 13. We have completed a couple of tests already (including a 
stress/load test). But the business thinks is too risky to move all customers 
to RDS at once. They wanted to do it gradually which complicates my life as I 
would need a bidirectional replication in place.

So, I'm still trying to convince them to migrate all customers at once :(

Lucas

publickey - root@sud0.nz - 0xC5E964A1.asc
Description: application/pgp-keys


signature.asc
Description: OpenPGP digital signature

Re: PostgreSQL 9.2 high replication lag

[Lucas]

‐‐‐ Original Message ‐‐‐

On Friday, August 20th, 2021 at 5:29 PM, Laurenz Albe laurenz.a...@cybertec.at 
wrote:

> On Fri, 2021-08-20 at 01:33 +, Lucas wrote:
> 

> > After setting max_standby_streaming_delay to 120s it got a lot better.
> > 

> > But the replication delay is still happening quite often, except this time 
> > goes up to 120s only.
> 

> That's exactly what this parameter should do.
> 

> If you don't want the delays, either reduce the value (and get more canceled 
> queries)
> 

> or try to reduce the number of conflicts, for example by setting 
> "hot_standby_feedback = on".

Yes, I already have the hot_standby_feedback = on set to on on all slaves.

> Note that you will never be able to completely get rid of replication 
> colflicts;
> 

> for example, there are buffer pin conflicts or lock conflicts caused by 
> autovacuum
> 

> truncation.
> 

> See this article for more:
> 

> https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/
> 

> If you want a standby that has no apply delays and no canceled queries is 
> usually
> 

> not possible. Consider using two standby servers for these two purposes.

Thanks for sharing this. I feel relief a bit to know that replication conflicts 
will always "be there". Since I started this email thread, we have deployed a 
couple of extra slaves to share the load between them. This has helped a lot 
with the replication delay, but it is still there... 

I think I'll end up lowering max_standby_streaming_delay and dealing with 
conflits when they happen. Let me ask you; Is there a way to know what kind of 
conflicts are being responsible for the replication delay? How could I check 
this?

Thanks
Lucas

publickey - root@sud0.nz - 0xC5E964A1.asc
Description: application/pgp-keys


signature.asc
Description: OpenPGP digital signature