agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedReplication Lag
15+ messages / 8 participants
[nested] [flat]
* Replication Lag
@ 2014-03-21 02:42 Chatha, Karan (CMG-Atlanta) <Karan.Chatha@coxinc.com>
0 siblings, 1 reply; 15+ messages in thread
From: Chatha, Karan (CMG-Atlanta) @ 2014-03-21 02:42 UTC (permalink / raw)
To: pgsql-admin
We are having replication lag issues in our production environment. We are on postgres 9.015.
We have one master and 8 slaves. We don't see any loads or io on the slaves. All we see is replication lag which we measure in megs. We can reproduce
this by doing transactions on the master and we see that transactions are not coming over to slave.
Is there any way we are hitting a bug?
KARAN CHATHA | Manager, Data Services | CMG Technology
karan.chatha@coxinc.com<mailto:karan.chatha@coxinc.com> | p: 678-645-4083| m: 404-713-1368
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication Lag
@ 2014-03-21 19:13 Steve Crawford <scrawford@pinpointresearch.com>
parent: Chatha, Karan (CMG-Atlanta) <Karan.Chatha@coxinc.com>
0 siblings, 1 reply; 15+ messages in thread
From: Steve Crawford @ 2014-03-21 19:13 UTC (permalink / raw)
To: Chatha, Karan (CMG-Atlanta) <Karan.Chatha@coxinc.com>; pgsql-admin
On 03/20/2014 07:42 PM, Chatha, Karan (CMG-Atlanta) wrote:
>
> We are having replication lag issues in our production environment.
> We are on postgres 9.015.
>
Er. 9.0.15?
>
> We have one master and 8 slaves. We don't see any loads or io on the
> slaves. All we see is replication lag which we measure in megs. We
> can reproduce
>
> this by doing transactions on the master and we see that transactions
> are not coming over to slave.
>
Are you seeing a *lag* in replication or no replication at all?
>
> Is there any way we are hitting a bug?
>
Possibly but I'm going to guess that the most likely location of the bug
is somewhere in your configuration. You need to provide more
information. This page is a good guide:
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
In particular, I'd like to know for starters:
0. What form of replication are you using? Bucardo? Slony? Pgpool?
Londiste? Mammoth? Hot-standby? Warm-standby? ...
1. Did it ever work?
2. If so, what changed? (configuration, upgrades, network, ???)
3. Are all machines on the same version?
4. Have you done any upgrades? If so, did you follow all the special
notes regarding each upgrade? Occasionally minor upgrades require steps
beyond simply replacing the binary and at times those have involved
replication issues.
5. Anything of interest in the logs on the master or any of the
standbys? Be sure sufficient logging is enabled.
Cheers,
Steve
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication Lag
@ 2014-03-21 19:43 Chatha, Karan (CMG-Atlanta) <Karan.Chatha@coxinc.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 15+ messages in thread
From: Chatha, Karan (CMG-Atlanta) @ 2014-03-21 19:43 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; pgsql-admin
1) It was working until March 8
2) We upgraded Postgres from 9.03 to 9.015 on Feb 19
3) Streaming Replication
4) Right now we have master on 9.015 and 7 slaves on 9.015 and one slave on 9.0.16
5) We have full logging enable to syslog
6) What we see is that there are no loads or io but archives get stuck on one archive. We have
7) max_standby_archive_delay = 60000 # max delay before canceling queries
max_standby_streaming_delay = 60000
It is almost like these values are not being honored.
Thx
KARAN CHATHA | Manager, Data Services | CMG Technology
karan.chatha@coxinc.com | p: 678-645-4083| m: 404-713-1368
From: Steve Crawford [mailto:scrawford@pinpointresearch.com]
Sent: Friday, March 21, 2014 3:13 PM
To: Chatha, Karan (CMG-Atlanta); pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Replication Lag
On 03/20/2014 07:42 PM, Chatha, Karan (CMG-Atlanta) wrote:
We are having replication lag issues in our production environment. We are on postgres 9.015.
Er. 9.0.15?
We have one master and 8 slaves. We don't see any loads or io on the slaves. All we see is replication lag which we measure in megs. We can reproduce
this by doing transactions on the master and we see that transactions are not coming over to slave.
Are you seeing a *lag* in replication or no replication at all?
Is there any way we are hitting a bug?
Possibly but I'm going to guess that the most likely location of the bug is somewhere in your configuration. You need to provide more information. This page is a good guide: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
In particular, I'd like to know for starters:
0. What form of replication are you using? Bucardo? Slony? Pgpool? Londiste? Mammoth? Hot-standby? Warm-standby? ...
1. Did it ever work?
2. If so, what changed? (configuration, upgrades, network, ???)
3. Are all machines on the same version?
4. Have you done any upgrades? If so, did you follow all the special notes regarding each upgrade? Occasionally minor upgrades require steps beyond simply replacing the binary and at times those have involved replication issues.
5. Anything of interest in the logs on the master or any of the standbys? Be sure sufficient logging is enabled.
Cheers,
Steve
Click here<https://www.mailcontrol.com/sr/n5zdpbVp7GPGX2PQPOmvUq6rRgRshdaJRpnmJk2o6cLsyL4wvU6ebKFo9FCU0sXrzYkFM...; to report this email as spam.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication Lag
@ 2014-03-22 00:44 Steve Crawford <scrawford@pinpointresearch.com>
parent: Chatha, Karan (CMG-Atlanta) <Karan.Chatha@coxinc.com>
0 siblings, 0 replies; 15+ messages in thread
From: Steve Crawford @ 2014-03-22 00:44 UTC (permalink / raw)
To: Chatha, Karan (CMG-Atlanta) <Karan.Chatha@coxinc.com>; pgsql-admin
On 03/21/2014 12:43 PM, Chatha, Karan (CMG-Atlanta) wrote:
>
> 1)It was working until March 8
>
And then what changed? *Anything* that might have happened. Config
change, unclean reboot, out of disk, firewall updates, network changes,
anything at all...
>
> 2)We upgraded Postgres from 9.03 to 9.015 on Feb 19
>
There are a few items that require special handling between 9.03 and
9.0.15. Did you read all the release notes and make sure that the extra
steps were completed or didn't apply to you? (I'm not sure that any
directly impact replication but haven't been running anything earlier
than 9.1 for quite a while.)
>
> 3)Streaming Replication
>
> 4)Right now we have master on 9.015 and 7 slaves on 9.015 and one
> slave on 9.0.16
>
> 5)We have full logging enable to syslog
>
What do the logs tell you? Have you thoroughly examined them both for
current messages and anything unusual around the time that the issue
appeared?
>
> 6)What we see is that there are no loads or io but archives get stuck
> on one archive. We have
>
> 7)max_standby_archive_delay = 60000 # max delay before
> canceling queries
>
> max_standby_streaming_delay = 60000
>
> It is almost like these values are not being honored.
>
Cheers,
Steve
^ permalink raw reply [nested|flat] 15+ messages in thread
* Replication lag
@ 2025-04-17 05:51 Wasim Devale <wasimd60@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Wasim Devale @ 2025-04-17 05:51 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-admin
Hi everyone,
We have a setup of primary and replica database. We are using the replica
as read only purpose. But the queries are long running queries that takes
30 minutes to complete.
Do we have any settings in place that will not show replication lag and the
queries also executes on replica database without competition on WAL reply?
The settings:
Hot standby is off
And maximum streaming delay is set to -1
Thanks,
Wasim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication lag
@ 2025-04-17 12:14 Wasim Devale <wasimd60@gmail.com>
parent: Wasim Devale <wasimd60@gmail.com>
0 siblings, 2 replies; 15+ messages in thread
From: Wasim Devale @ 2025-04-17 12:14 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-admin
Hi All
Does wal_level = logical can resolve the issue of replication lag?
On Thu, 17 Apr, 2025, 11:21 am Wasim Devale, <wasimd60@gmail.com> wrote:
> Hi everyone,
>
> We have a setup of primary and replica database. We are using the replica
> as read only purpose. But the queries are long running queries that takes
> 30 minutes to complete.
>
> Do we have any settings in place that will not show replication lag and
> the queries also executes on replica database without competition on WAL
> reply?
>
> The settings:
> Hot standby is off
> And maximum streaming delay is set to -1
>
> Thanks,
> Wasim
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication lag
@ 2025-04-17 20:28 Kellyn Pot'Vin-Gorman <dbakevlar@gmail.com>
parent: Wasim Devale <wasimd60@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Kellyn Pot'Vin-Gorman @ 2025-04-17 20:28 UTC (permalink / raw)
To: Wasim Devale <wasimd60@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-admin
Hey Wasim,
You've already checked the lag information in pg_stat_replication,
pg_stat_statements and pg_stat_activity?
Is there any delay in the setup that might be causing the lag?
max_standby_streaming_delay and/or max_standby_archive_delay
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication lag
@ 2025-04-17 22:04 Gaspare Boscarino, P.Eng. <gaspare.boscarino@theoremasystems.com>
parent: Wasim Devale <wasimd60@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Gaspare Boscarino, P.Eng. @ 2025-04-17 22:04 UTC (permalink / raw)
To: Wasim Devale <wasimd60@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-admin
Hello Wasim,
If I understand your problem correctly, you are trying to use the replica
to run queries for some kind of report. For those cases, I recommend
setting up a logical replication which will allow you to have a replica
that can be modified based on your needs. For instance, on the target
database (replica) you could create indices to improve the performance of
your query. An analysis of the execution plan would be necessary, of course.
Regards,
Gaspare
On Thu, Apr 17, 2025 at 5:15 AM Wasim Devale <wasimd60@gmail.com> wrote:
> Hi All
>
> Does wal_level = logical can resolve the issue of replication lag?
>
> On Thu, 17 Apr, 2025, 11:21 am Wasim Devale, <wasimd60@gmail.com> wrote:
>
>> Hi everyone,
>>
>> We have a setup of primary and replica database. We are using the replica
>> as read only purpose. But the queries are long running queries that takes
>> 30 minutes to complete.
>>
>> Do we have any settings in place that will not show replication lag and
>> the queries also executes on replica database without competition on WAL
>> reply?
>>
>> The settings:
>> Hot standby is off
>> And maximum streaming delay is set to -1
>>
>> Thanks,
>> Wasim
>>
>
--
Gaspare Boscarino, P.Eng., M.Eng., MASc.
Founder and CEO
*Theorema Systems Inc.*
www.theoremasystems.com | +1 604-765-0121
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication lag
@ 2025-04-18 06:48 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Gaspare Boscarino, P.Eng. <gaspare.boscarino@theoremasystems.com>
0 siblings, 1 reply; 15+ messages in thread
From: Laurenz Albe @ 2025-04-18 06:48 UTC (permalink / raw)
To: Gaspare Boscarino, P.Eng. <gaspare.boscarino@theoremasystems.com>; Wasim Devale <wasimd60@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-admin
> On Thu, Apr 17, 2025 at 5:15 AM Wasim Devale <wasimd60@gmail.com> wrote:
> > Does wal_level = logical can resolve the issue of replication lag?
> >
> > > We have a setup of primary and replica database. We are using the replica as
> > > read only purpose. But the queries are long running queries that takes 30 minutes
> > > to complete.
> > >
> > > Do we have any settings in place that will not show replication lag and the
> > > queries also executes on replica database without competition on WAL reply?
> > >
> > > The settings:
> > > Hot standby is off
> > > And maximum streaming delay is set to -1
In short: no.
A more detailed discussion:
If I understand correctly, you are fighting with replication conflicts, and you
want no replay delay and no canceled queries.
The only way you can have that is if you don't have replication conflicts, and
that is something you can guarantee. However, you can reduce the frequency of
replication conflicts:
- Setting "hot_standby_feedback = on" will probably get rid of the majority of
replication conflicts, but the price is that long-running queries on the standby
can bloat the tables and indexes on the primary.
- Setting "vacuum_truncate = off" (available from v18 on) will get rid of another
set of replication conflicts. Before v18, you'd have to disable VACUUM truncation
on each table individually.
You will probably still get some buffer pin replication conflicts, and commands
like TRUNCATE, ALTER TABLE or VACUUM (FULL) will always cause them.
Changing "wal_level" has no impact on all that, except that if you set it to
"minimal", you cannot have replication any more, which would get rid of replication
conflicts.
Similarly, setting "hot_standby = off" on the standby would immediately get rid of
all replication conflicts, because you could no longer connect to the standby and
run queries there.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication lag
@ 2025-04-18 10:51 Wasim Devale <wasimd60@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 15+ messages in thread
From: Wasim Devale @ 2025-04-18 10:51 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Gaspare Boscarino, P.Eng. <gaspare.boscarino@theoremasystems.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-admin
So finally long running on a replica won't minimise replication lag to zero
in any scenario? Correct?
On Fri, 18 Apr, 2025, 12:18 pm Laurenz Albe, <laurenz.albe@cybertec.at>
wrote:
> > On Thu, Apr 17, 2025 at 5:15 AM Wasim Devale <wasimd60@gmail.com> wrote:
> > > Does wal_level = logical can resolve the issue of replication lag?
> > >
> > > > We have a setup of primary and replica database. We are using the
> replica as
> > > > read only purpose. But the queries are long running queries that
> takes 30 minutes
> > > > to complete.
> > > >
> > > > Do we have any settings in place that will not show replication lag
> and the
> > > > queries also executes on replica database without competition on WAL
> reply?
> > > >
> > > > The settings:
> > > > Hot standby is off
> > > > And maximum streaming delay is set to -1
>
> In short: no.
>
> A more detailed discussion:
>
> If I understand correctly, you are fighting with replication conflicts,
> and you
> want no replay delay and no canceled queries.
>
> The only way you can have that is if you don't have replication conflicts,
> and
> that is something you can guarantee. However, you can reduce the
> frequency of
> replication conflicts:
>
> - Setting "hot_standby_feedback = on" will probably get rid of the
> majority of
> replication conflicts, but the price is that long-running queries on the
> standby
> can bloat the tables and indexes on the primary.
>
> - Setting "vacuum_truncate = off" (available from v18 on) will get rid of
> another
> set of replication conflicts. Before v18, you'd have to disable VACUUM
> truncation
> on each table individually.
>
> You will probably still get some buffer pin replication conflicts, and
> commands
> like TRUNCATE, ALTER TABLE or VACUUM (FULL) will always cause them.
>
> Changing "wal_level" has no impact on all that, except that if you set it
> to
> "minimal", you cannot have replication any more, which would get rid of
> replication
> conflicts.
>
> Similarly, setting "hot_standby = off" on the standby would immediately
> get rid of
> all replication conflicts, because you could no longer connect to the
> standby and
> run queries there.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication lag
@ 2025-04-18 11:08 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Wasim Devale <wasimd60@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Laurenz Albe @ 2025-04-18 11:08 UTC (permalink / raw)
To: Wasim Devale <wasimd60@gmail.com>; +Cc: Gaspare Boscarino, P.Eng. <gaspare.boscarino@theoremasystems.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-admin
On Fri, 2025-04-18 at 16:21 +0530, Wasim Devale wrote:
> So finally long running on a replica won't minimise replication lag to zero in any scenario? Correct?
I am not sure I understand that sentence correctly.
Yes, if you are running long-running queries on a standby server, that
won't minimize replication lag. But I am surprised that anyone could
imagine it would.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 15+ messages in thread
* Replication lag
@ 2025-05-23 07:13 Wasim Devale <wasimd60@gmail.com>
0 siblings, 3 replies; 15+ messages in thread
From: Wasim Devale @ 2025-05-23 07:13 UTC (permalink / raw)
To: pgsql-admin; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello,
Reply wal and query execution on replica can coexists?
Golden gate in oracle has this feature that they can coexists but in
postgresql do we have any provision like this.
Please assist.
Thanks,
Wasim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication lag
@ 2025-05-23 08:08 David Okeamah <whitneykiss741@gmail.com>
parent: Wasim Devale <wasimd60@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: David Okeamah @ 2025-05-23 08:08 UTC (permalink / raw)
To: Wasim Devale <wasimd60@gmail.com>; pgsql-admin; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Query Execution and Reply WAL Coexistence in PostgreSQL
Wasim,
Thanks for your question.
Yes, PostgreSQL does support concurrent WAL replay and read query execution on replicas through its hot standby feature. By setting hot_standby = on, a replica can serve read-only queries while applying WAL files from the primary via streaming replication.
However, there are a few caveats:
* Read queries on the standby may be canceled if they conflict with recovery operations. This behavior can be tuned using parameters like max_standby_streaming_delay and hot_standby_feedback.
* Unlike Oracle GoldenGate, PostgreSQL’s native logical replication is more limited in terms of conflict resolution and cross-version replication, though tools like pglogical or Debezium can bridge those gaps for more complex use cases.
Best regards,
David Okeamah
DAVID OKEAMAH,DEVELOPER
________________________________
From: Wasim Devale <wasimd60@gmail.com>
Sent: Friday, May 23, 2025 8:13:10 AM
To: pgsql-admin <pgsql-admin@postgresql.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Replication lag
Hello,
Reply wal and query execution on replica can coexists?
Golden gate in oracle has this feature that they can coexists but in postgresql do we have any provision like this.
Please assist.
Thanks,
Wasim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication lag
@ 2025-05-23 08:31 Dionysios-Charalampos Vythoulkas <dcvythoulkas@gmail.com>
parent: Wasim Devale <wasimd60@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Dionysios-Charalampos Vythoulkas @ 2025-05-23 08:31 UTC (permalink / raw)
To: pgsql-admin; Pgsql-admin <pgsql-admin@lists.postgresql.org>; +Cc: Wasim Devale <wasimd60@gmail.com>
On streaming replication yes, you can perform read-only queries.
On logical replication, as far as I know you can also run write queries, but you have to be
careful to keep the data consistent.
On Παρασκευή, 23 Μαΐου 2025 10:13:10 Π.Μ. EEST Wasim Devale wrote:
> Hello,
>
> Reply wal and query execution on replica can coexists?
>
> Golden gate in oracle has this feature that they can coexists but in
> postgresql do we have any provision like this.
>
> Please assist.
>
> Thanks,
> Wasim
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Replication lag
@ 2025-05-23 09:46 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Wasim Devale <wasimd60@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Laurenz Albe @ 2025-05-23 09:46 UTC (permalink / raw)
To: Wasim Devale <wasimd60@gmail.com>; pgsql-admin; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, 2025-05-23 at 12:43 +0530, Wasim Devale wrote:
> Reply wal and query execution on replica can coexists?
Yes, you can have both.
But there is the possibility of replication conflicts, which can
either delay replay of the WAL or lead to cacneled queries on the
standby.
To see why this is unavoidable in some cases, consider the following
scenario:
- on the standby, there is a long-running query on table A
- on the primary, somebody executes "DROP TABLE A"
The change gets replicated to the standby, but it clearly cannot be
replayed while the query is still running.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2025-05-23 09:46 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-03-21 02:42 Replication Lag Chatha, Karan (CMG-Atlanta) <Karan.Chatha@coxinc.com>
2014-03-21 19:13 ` Steve Crawford <scrawford@pinpointresearch.com>
2014-03-21 19:43 ` Chatha, Karan (CMG-Atlanta) <Karan.Chatha@coxinc.com>
2014-03-22 00:44 ` Steve Crawford <scrawford@pinpointresearch.com>
2025-04-17 05:51 Replication lag Wasim Devale <wasimd60@gmail.com>
2025-04-17 12:14 ` Re: Replication lag Wasim Devale <wasimd60@gmail.com>
2025-04-17 20:28 ` Re: Replication lag Kellyn Pot'Vin-Gorman <dbakevlar@gmail.com>
2025-04-17 22:04 ` Re: Replication lag Gaspare Boscarino, P.Eng. <gaspare.boscarino@theoremasystems.com>
2025-04-18 06:48 ` Re: Replication lag Laurenz Albe <laurenz.albe@cybertec.at>
2025-04-18 10:51 ` Re: Replication lag Wasim Devale <wasimd60@gmail.com>
2025-04-18 11:08 ` Re: Replication lag Laurenz Albe <laurenz.albe@cybertec.at>
2025-05-23 07:13 Replication lag Wasim Devale <wasimd60@gmail.com>
2025-05-23 08:08 ` Re: Replication lag David Okeamah <whitneykiss741@gmail.com>
2025-05-23 08:31 ` Re: Replication lag Dionysios-Charalampos Vythoulkas <dcvythoulkas@gmail.com>
2025-05-23 09:46 ` Re: Replication lag Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox