From Karan.Chatha@coxinc.com Fri Mar 21 13:00:50 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQz46-0005ii-8D for pgsql-admin@arkaria.postgresql.org; Fri, 21 Mar 2014 13:00:50 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WQz44-0006kT-Q4 for pgsql-admin@arkaria.postgresql.org; Fri, 21 Mar 2014 13:00:48 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQpQ4-0000yi-0u for pgsql-admin@postgresql.org; Fri, 21 Mar 2014 02:42:52 +0000 Received: from obmail6.coxinc.com ([66.6.145.166] helo=coxinc.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQpQ0-0001ib-9w for pgsql-admin@postgresql.org; Fri, 21 Mar 2014 02:42:50 +0000 Received: from ([10.211.24.104]) by EATL3MS006.coxinc.com with ESMTP with TLS id JWSJWG1.289974606; Thu, 20 Mar 2014 22:42:43 -0400 Received: from CMGATLPMS2003.cmg.int ([169.254.3.177]) by CMGATLPMS2004.cmg.int ([169.254.4.192]) with mapi id 14.01.0355.002; Thu, 20 Mar 2014 22:42:43 -0400 From: "Chatha, Karan (CMG-Atlanta)" To: "pgsql-admin@postgresql.org" Subject: Replication Lag Thread-Topic: Replication Lag Thread-Index: Ac9ErzvBoQ6W+VTPSjSnRkTKAPLmSQ== Date: Fri, 21 Mar 2014 02:42:43 +0000 Message-ID: <378D940FF9AE0145BA853EE92442D64334660540@CMGATLPMS2003.cmg.int> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.211.24.5] Content-Type: multipart/alternative; boundary="_000_378D940FF9AE0145BA853EE92442D64334660540CMGATLPMS2003cm_" MIME-Version: 1.0 X-Pg-Spam-Score: 0.8 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_000_378D940FF9AE0145BA853EE92442D64334660540CMGATLPMS2003cm_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 slave= s. All we see is replication lag which we measure in megs. We can reprodu= ce this by doing transactions on the master and we see that transactions are n= ot coming over to slave. Is there any way we are hitting a bug? KARAN CHATHA | Manager, Data Services | CMG Technology karan.chatha@coxinc.com | p: 678-645-4083| = m: 404-713-1368 --_000_378D940FF9AE0145BA853EE92442D64334660540CMGATLPMS2003cm_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

We are having replication lag issues in our producti= on 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 wh= ich 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 | p: 678-645-4083| = m: 404-713-1368

 

--_000_378D940FF9AE0145BA853EE92442D64334660540CMGATLPMS2003cm_-- From scrawford@pinpointresearch.com Fri Mar 21 19:13:17 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WR4sW-0002tt-Sf for pgsql-admin@arkaria.postgresql.org; Fri, 21 Mar 2014 19:13:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WR4sW-0003Xt-DS for pgsql-admin@arkaria.postgresql.org; Fri, 21 Mar 2014 19:13:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WR4sV-0003Xn-Kt for pgsql-admin@postgresql.org; Fri, 21 Mar 2014 19:13:15 +0000 Received: from cerberus.pinpointresearch.com ([66.7.238.130] helo=polaris.pinpointresearch.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WR4sR-0002Zx-HG for pgsql-admin@postgresql.org; Fri, 21 Mar 2014 19:13:15 +0000 Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 740ABE00EC82; Fri, 21 Mar 2014 12:13:08 -0700 (PDT) Message-ID: <532C8F44.4030301@pinpointresearch.com> Date: Fri, 21 Mar 2014 12:13:08 -0700 From: Steve Crawford User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.3.0 MIME-Version: 1.0 To: "Chatha, Karan (CMG-Atlanta)" , "pgsql-admin@postgresql.org" Subject: Re: Replication Lag References: <378D940FF9AE0145BA853EE92442D64334660540@CMGATLPMS2003.cmg.int> In-Reply-To: <378D940FF9AE0145BA853EE92442D64334660540@CMGATLPMS2003.cmg.int> Content-Type: multipart/alternative; boundary="------------050300050008000703080602" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------050300050008000703080602 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------050300050008000703080602 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
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

--------------050300050008000703080602-- From Karan.Chatha@coxinc.com Mon Mar 24 13:31:51 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WS4yk-0003OE-RT for pgsql-admin@arkaria.postgresql.org; Mon, 24 Mar 2014 13:31:51 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WS4yk-0008Fy-7Z for pgsql-admin@arkaria.postgresql.org; Mon, 24 Mar 2014 13:31:50 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WR5MA-0002aG-Cw for pgsql-admin@postgresql.org; Fri, 21 Mar 2014 19:43:54 +0000 Received: from obmail5.coxinc.com ([66.6.145.182] helo=coxinc.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WR5M6-0003D5-C8 for pgsql-admin@postgresql.org; Fri, 21 Mar 2014 19:43:53 +0000 Received: from ([10.211.24.102]) by ceiatlpms005.coxinc.com with ESMTP with TLS id j041111477.170386908; Fri, 21 Mar 2014 15:43:46 -0400 Received: from CMGATLPMS2003.cmg.int ([169.254.3.177]) by CMGATLPMS2002.cmg.int ([169.254.2.14]) with mapi id 14.01.0355.002; Fri, 21 Mar 2014 15:43:45 -0400 From: "Chatha, Karan (CMG-Atlanta)" To: Steve Crawford , "pgsql-admin@postgresql.org" Subject: Re: Replication Lag Thread-Topic: [ADMIN] Replication Lag Thread-Index: Ac9ErzvBoQ6W+VTPSjSnRkTKAPLmSQAq+MoAAAdw1nA= Date: Fri, 21 Mar 2014 19:43:45 +0000 Message-ID: <378D940FF9AE0145BA853EE92442D64334660CF3@CMGATLPMS2003.cmg.int> References: <378D940FF9AE0145BA853EE92442D64334660540@CMGATLPMS2003.cmg.int> <532C8F44.4030301@pinpointresearch.com> In-Reply-To: <532C8F44.4030301@pinpointresearch.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.211.24.5] Content-Type: multipart/alternative; boundary="_000_378D940FF9AE0145BA853EE92442D64334660CF3CMGATLPMS2003cm_" MIME-Version: 1.0 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_000_378D940FF9AE0145BA853EE92442D64334660CF3CMGATLPMS2003cm_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 sla= ve 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 =3D 60000 # max delay befor= e canceling queries max_standby_streaming_delay =3D 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 slave= s. All we see is replication lag which we measure in megs. We can reprodu= ce this by doing transactions on the master and we see that transactions are n= ot 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. Thi= s 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 si= mply replacing the binary and at times those have involved replication issu= es. 5. Anything of interest in the logs on the master or any of the standbys? B= e sure sufficient logging is enabled. Cheers, Steve Click here to report this= email as spam. --_000_378D940FF9AE0145BA853EE92442D64334660CF3CMGATLPMS2003cm_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

1)&n= bsp;     It was working= until March 8

2)&n= bsp;     We upgraded Po= stgres from 9.03 to 9.015 on Feb 19

3)&n= bsp;     Streaming Repl= ication

4)&n= bsp;     Right now we h= ave master on 9.015 and 7 slaves on 9.015 and one slave on 9.0.16

5)&n= bsp;     We have full l= ogging enable to syslog

6)&n= bsp;     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 =3D 60000  &nbs= p;            # max = delay before canceling queries

max_standby_streaming_delay =3D 60000

 

It is almost like these values are not being = honored.

 

Thx

 

KARAN CHATHA | Manager, Data Services | CMG Technology<= /o:p>

karan.chatha@coxinc.co= m | 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 producti= on 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 wh= ich 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 replicatio= n 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. Thi= s page is a good guide: htt= p://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 si= mply replacing the binary and at times those have involved replication issu= es.

5. Anything of interest in the logs on the master or any of the standbys? B= e sure sufficient logging is enabled.

Cheers,
Steve


Click here to report this email as spam.

--_000_378D940FF9AE0145BA853EE92442D64334660CF3CMGATLPMS2003cm_-- From scrawford@pinpointresearch.com Sat Mar 22 00:44:16 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WRA2q-0000NP-8E for pgsql-admin@arkaria.postgresql.org; Sat, 22 Mar 2014 00:44:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WRA2p-0000g4-Nj for pgsql-admin@arkaria.postgresql.org; Sat, 22 Mar 2014 00:44:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WRA2o-0000er-7S for pgsql-admin@postgresql.org; Sat, 22 Mar 2014 00:44:14 +0000 Received: from cerberus.pinpointresearch.com ([66.7.238.130] helo=polaris.pinpointresearch.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WRA2k-000075-OU for pgsql-admin@postgresql.org; Sat, 22 Mar 2014 00:44:13 +0000 Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id D8F10E00EC82; Fri, 21 Mar 2014 17:44:08 -0700 (PDT) Message-ID: <532CDCD8.30200@pinpointresearch.com> Date: Fri, 21 Mar 2014 17:44:08 -0700 From: Steve Crawford User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.3.0 MIME-Version: 1.0 To: "Chatha, Karan (CMG-Atlanta)" , "pgsql-admin@postgresql.org" Subject: Re: Replication Lag References: <378D940FF9AE0145BA853EE92442D64334660540@CMGATLPMS2003.cmg.int> <532C8F44.4030301@pinpointresearch.com> <378D940FF9AE0145BA853EE92442D64334660CF3@CMGATLPMS2003.cmg.int> In-Reply-To: <378D940FF9AE0145BA853EE92442D64334660CF3@CMGATLPMS2003.cmg.int> Content-Type: multipart/alternative; boundary="------------090307000208010808090907" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------090307000208010808090907 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------090307000208010808090907 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
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
--------------090307000208010808090907-- From wasimd60@gmail.com Thu Apr 17 05:51:30 2025 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5IA8-00FOS1-9j for pgsql-admin@arkaria.postgresql.org; Thu, 17 Apr 2025 05:51:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u5IA5-00FhKo-RP for pgsql-admin@arkaria.postgresql.org; Thu, 17 Apr 2025 05:51:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5IA5-00FhKg-F4 for pgsql-admin@lists.postgresql.org; Thu, 17 Apr 2025 05:51:46 +0000 Received: from mail-ej1-x62e.google.com ([2a00:1450:4864:20::62e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u5IA4-000Uag-0c for pgsql-admin@postgresql.org; Thu, 17 Apr 2025 05:51:45 +0000 Received: by mail-ej1-x62e.google.com with SMTP id a640c23a62f3a-ac41514a734so53530366b.2 for ; Wed, 16 Apr 2025 22:51:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744869103; x=1745473903; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=rVZVx9Oy8ez+ZZT1QMR4WcQX8Fnml9UxKxkymIa1WVA=; b=XkYnCmgQC6Uj5JDP7Jug9UbuQaqCH2GDuvAHbVNAkey/IFnpi3pRS8InG2e6p1ksPJ 1ZnGl04QIGNhsXN58iGsQKpvAkIx3y5OuEDOvjZ7VVvfWGo7S0rv98mgS1OuSRJiKL+t u7R9bOnoU2pOTVSCEi60rBurGB8lAEdQ6ahObXrjkULRf9cfwIGnI6g/Q1onk/dSyKfs F7fvdmyFilwnAaBTmlMupqURcf2BAR5uexTVylae99ShiD+j4oSB0cwxLVj1deUu/i4d 5kTQX3qDOHbzZJuCA1w6ftLayLzU0xdz4gfu1mDK1nvADWTmKD+M/wlbzxCYQZ0Zp2Ot jS0w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744869103; x=1745473903; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=rVZVx9Oy8ez+ZZT1QMR4WcQX8Fnml9UxKxkymIa1WVA=; b=nd6GfCMx615f8WKjf5JYJSzxdjeGQSEjKujGjhx/QUiznPgT21NWsRn8IMqrzFFuCd Bzv9tJ5UtXLMGlvepX4r3FEsF5eawMcCfa2dyjSNILK+sygjKJE4BJ5W141jYqY1jPRK dYvkdCJICzHS2AuU77P7PT6lHh7DDYuL861eWrShuvz+OUYp9DZ2KUUAkzG/wKP7Luu8 6rPNVRWR1IqknQ0qG2GdVzO4PnF7V15b6NdhPJNWwsGFiRZJRWzaLm9jv8LSi2TQcyBI hXYRHq+Pb0ZgQDBlSKeljxSk/DBW17MsigUCMA1x/aWY8rVW70jTJ2RN6+w1P3cgoaXy ys+w== X-Forwarded-Encrypted: i=1; AJvYcCVApw1FNGgMa7WOrQHOwsVnwbFPAG0xedzgu09KQxxd2QAhWEWvHSputwK52aZL5bjEAyCMn9VXYBP0Ow==@postgresql.org X-Gm-Message-State: AOJu0YySzD39/bOYqaaP9obM7N9a0DFcbsafbJzfKFsApfxG42GUpqoA UTG0y+v6TM6jPez/G32lAvYlCoXDWaEP6H+VwjIsJ8o7WQOTVsUj2AFMRzvAVjH4C/MhSpyMQCv XafG/MJkKaZ67PKWRZjBhAI9dER9ZX5BF X-Gm-Gg: ASbGncupNoUY3LZO0RiKJHsHOPbdVUyhWWpYjlT7yjJO50h/t7kLUcwffTDcgTlXNkk YWCqUqD4m02SEv89h8aDNOvNSEiCxlGdc3Fa4VeIahXAdBONRM7Qwyk3e2nPbByGU6NUj4AtYzu l63tfkJMRrSfW7J77DgVeN0js= X-Google-Smtp-Source: AGHT+IH+oLvvfebeqlzyUGQesOymLPV5IqY1HP8c4bmf4hOY4GHTLISp6a71yvxHZEGtil5R6QG/Zf+boCZF+134QMc= X-Received: by 2002:a17:906:443:b0:ac7:150b:57b2 with SMTP id a640c23a62f3a-acb42aed3e8mr367717166b.41.1744869102855; Wed, 16 Apr 2025 22:51:42 -0700 (PDT) MIME-Version: 1.0 From: Wasim Devale Date: Thu, 17 Apr 2025 11:21:30 +0530 X-Gm-Features: ATxdqUH6EBgiJL-LXGPV3NFhAFcRTGD4bZDKclEJ29cJYfxEJBqgwD4SPzLbYpU Message-ID: Subject: Replication lag To: Pgsql-admin , pgsql-admin Content-Type: multipart/alternative; boundary="0000000000000e2d710632f2ffe2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000e2d710632f2ffe2 Content-Type: text/plain; charset="UTF-8" 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 --0000000000000e2d710632f2ffe2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 3= 0 minutes to complete.

D= o 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 streami= ng delay is set to -1

Th= anks,
Wasim=C2=A0
--0000000000000e2d710632f2ffe2-- From wasimd60@gmail.com Thu Apr 17 12:14:32 2025 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5O8p-00H84g-QJ for pgsql-admin@arkaria.postgresql.org; Thu, 17 Apr 2025 12:14:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u5O8n-006HZO-Hb for pgsql-admin@arkaria.postgresql.org; Thu, 17 Apr 2025 12:14:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5O8n-006HZ8-3Y for pgsql-admin@lists.postgresql.org; Thu, 17 Apr 2025 12:14:49 +0000 Received: from mail-ej1-x635.google.com ([2a00:1450:4864:20::635]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u5O8k-000Z30-21 for pgsql-admin@lists.postgresql.org; Thu, 17 Apr 2025 12:14:49 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-ac2bdea5a38so103947166b.0 for ; Thu, 17 Apr 2025 05:14:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744892086; x=1745496886; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=Cn/maopuIzVhV0G5bVG3DhRo71J5jGa/fRKOcpENdmc=; b=gazoMK3xUvT/GwbzaB45CLU3ppNZT1dMvuV64fdwD6c+LXJzBNSulhkjBZJ6p6XO3l H1D5bCxDjP5VP/uB72GqcABnbRs7j1uNxXYNDgdk/SGxcCYBCc8aZfDPuWbblIwFbT9Y Z4OP6nQuz6OiXwi4nyejsqQWQPR8w/rw+uQMyF2WuJN6+6lvUZN2LW2flZHccMqjVNrj +gXzmb1VZA/ZXsnNYqzRitYMvHSqVlJGlKmR1g0j83lKporBjf8sarzV3r3oJRGcphFt pwdELhVmFmHE1aJUJebg5yKp6+jKiAh2TtEHVeNsh5hjnT+LFnTlPOUmIWEUzQIUDsaJ Lfdg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744892086; x=1745496886; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Cn/maopuIzVhV0G5bVG3DhRo71J5jGa/fRKOcpENdmc=; b=QJvlQQI+HcNsfHlPAlw3ZzaE9jSwXMukNLwnfEc1bP6h2PRfA1h4CgbXyo0bdfovYD plSHm4ovYvdFl7ew7sfYDoX2whKuVl7pBgm6FtwFwFxdVFAdwzrdBdXd0KBSTGsXzH2+ Ol6tFm94O2HnDWhTjrBtLwc2Enkf73RSqPxA/GxujsdiqDVCjPCdRb4HJOVNto16uEzp s+oBnJ+aUuvVLiYW1hrinFICZs9ZKIXRNc00qAjvCeLcyylRPknSRXC/mH7vvZd6xq6K exc25Z1P1BgnH0qrHMvlQ4NbirM4s46zzegqscAamtfMyAbuPWqP6VwK1ztU3bZD9GsB 30XQ== X-Gm-Message-State: AOJu0Yxyq2j2NjK48vy/tdOmDCr0ZVid/RkDCMiSRrlrySiGBWznq8Bb 9KvXYtqhsqsvxsaD8nuxsd16B1jeXSu1UgT+5cYQVp8vFY4rN4b/gaTgE6YJ6OA+P5TrsF6PATQ e7myMdJKsP+Reg6TtQdSNpTUqTziYgA== X-Gm-Gg: ASbGncuVGDxSOx+zARj2xeHW8Bu0yRnipBRVruXR1eE+D+6rGHnybH5/ya7am6bf/KT 61FNpfLpnicNT9Mz8++6J3IqQrFweWEkNHoYiud1dSkolQ8PRxtp8LZAavIr/eCXreJnG6xh2w0 JfaqmKanWy+xQdq6QVeSAbejQ= X-Google-Smtp-Source: AGHT+IHGiqXA6s79iWUwGHDhsNxvuw9ANQ/i5plNuqdNUUKJRbOgob6XURGLwSxG/gXO4WZHzuaoVLDkDz+JSCEOkIY= X-Received: by 2002:a17:906:d542:b0:aca:a687:a409 with SMTP id a640c23a62f3a-acb428fdf62mr582483566b.17.1744892086150; Thu, 17 Apr 2025 05:14:46 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Wasim Devale Date: Thu, 17 Apr 2025 17:44:32 +0530 X-Gm-Features: ATxdqUEqpV5bBP6nqcDIfqwhUOWMYv4sFpiHQ27Ax9U7fas2AhaU-Wf2tQ-wXFk Message-ID: Subject: Re: Replication lag To: Pgsql-admin , pgsql-admin Content-Type: multipart/alternative; boundary="000000000000f76c8c0632f8582b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f76c8c0632f8582b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi All Does wal_level =3D logical can resolve the issue of replication lag? On Thu, 17 Apr, 2025, 11:21=E2=80=AFam Wasim Devale, w= rote: > 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 > --000000000000f76c8c0632f8582b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi All=C2=A0

Does wal_level =3D logical can resolve the issue of replication= lag?

=
On Thu, 17 Apr, 2025, 11:21=E2=80=AFa= m Wasim Devale, <wasimd60@gmail.co= m> wrote:
= Hi everyone,

We have a setup o= f primary and replica database. We are using the replica as read only purpo= se. But the queries are long running queries that takes 30 minutes to compl= ete.

Do we have any sett= ings in place that will not show replication lag and the queries also execu= tes on replica database without competition on WAL reply?

The settings:
Hot = standby is off
And maximum streaming delay is set to= -1

Thanks,
Wasim=C2=A0
--000000000000f76c8c0632f8582b-- From dbakevlar@gmail.com Thu Apr 17 20:28:37 2025 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5fxd-004KSq-2L for pgsql-admin@arkaria.postgresql.org; Fri, 18 Apr 2025 07:16:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u5fxa-008bSc-Ud for pgsql-admin@arkaria.postgresql.org; Fri, 18 Apr 2025 07:16:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5fxa-008bSR-HW for pgsql-admin@lists.postgresql.org; Fri, 18 Apr 2025 07:16:27 +0000 Received: from mail-lj1-x22d.google.com ([2a00:1450:4864:20::22d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u5fxY-000hge-0m for pgsql-admin@lists.postgresql.org; Fri, 18 Apr 2025 07:16:27 +0000 Received: by mail-lj1-x22d.google.com with SMTP id 38308e7fff4ca-3105ef2a070so16651251fa.2 for ; Fri, 18 Apr 2025 00:16:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744960584; x=1745565384; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=UnKTKM8NdeC63XKmnSgDkShGlrwMhnM1CV+sjj5RjE4=; b=QOZhZgXaj9MS4DBddZJ4VMNWpbKUZKMC2RXD+Ao2fsLDvQTTwsCDwb2/Cp/ecLnNT4 lfFB761Y21oWrg4rNAgWffWgg2qraA+n5RVWzlvy1jSIVmzHeYedPvnTMGs7eObuuSHe ThfSqPOLFG3LzCiwoMu9U4IQBQjvjuL7z/4j92caPs/PLjaW+e4U0V+328dr/Sy72E/k 2URw4QeP3V6BnIMlGuxTkmq8PZ/ekn1DeYePuvYCFIzKPGIrUw5FAyTS+1Mb7TZ5JgF8 BKzygTQrtD7/qc3dlcmUPudaNMU4m+QnA80FoSOH9qdPuGUBq7fEHyPHErS5i5XvmLWM LWfw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744960584; x=1745565384; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=UnKTKM8NdeC63XKmnSgDkShGlrwMhnM1CV+sjj5RjE4=; b=L4vQCKCqAyEk/HY53mCD7UMh9qbRb1TWdd9a+sXblZFvYiqL/jSEmrzSFeEbWDMWZX WJsJ56QSe89Wct+MrXsSs59hUrRI7Ah5VHEgElPd7hvEDj+wiZREDMy3M0Pn6LaU7a1u u7XTNDzxAycY7cNc/g8BU5oPgLfg21RwAFjUPbmSRix28x9GHPWIMulnfa7xmAo6bU3W 4CejzzBBG8VcDBS0cE0N85y1KhpTkjRfTzT86FlVOB/dECuVilcApcxJrhG+TWOErp1E amGhdd10oBIZ64pu3GNqgA+NBZbzC0+1qfDhff02jE6bLgHbLSxqK6vmrv8P3hyr1zmP 0QRg== X-Gm-Message-State: AOJu0YwSeXIYlsIw0SvDnKrEW1UFBfVpbpJ0en9mabmfZnMAEvIB2oln dWJi8e3KOaAqZoR9kGERoHuQEMz4p/vzzFQVoF7TR06QzQeu26i0NVKYYCrrvIuJHT1C/TbHHGK XVNEe8+qX3CDP/dZhYo/BYLSfi3vFgg== X-Gm-Gg: ASbGnct8vMSY0jPAlSbYjPy0oLf62Es/tAPHFe5U+lzzaEVxPaYzl2506O3Nu3eI7q3 Q7HDNaCbJqlwXueQchBU3AJ000kNjT5APmQ/LVwIJpjrPnKLlPF27pFnDSgAmHFRgAc8b22uVx0 tpP0C3yqXHFjf2Lclsdn+VxZ6HzkCQ31qG1yxNdf3jAUj6GSbfp7RM6xNQBRI3AloC X-Google-Smtp-Source: AGHT+IGX9Ahzv5/ajZO9lGsIeItBg2xpBYdksli2E+I8kbXWbDWmJTEliLDY6ZEtPB1eVdFODnwzlRJ0ScKzxCGspIM= X-Received: by 2002:a05:6512:1313:b0:54c:a49:d3ee with SMTP id 2adb3069b0e04-54d6e61c9f3mr68264e87.3.1744921797588; Thu, 17 Apr 2025 13:29:57 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "Kellyn Pot'Vin-Gorman" Date: Thu, 17 Apr 2025 13:28:37 -0700 X-Gm-Features: ATxdqUGOOtffmDLw13wm5Suq-4ukdv0GxhcYr9S-8-KlrEp-_inAHh0jsmPgyM4 Message-ID: Subject: Re: Replication lag To: Wasim Devale Cc: Pgsql-admin , pgsql-admin Content-Type: multipart/alternative; boundary="000000000000e7fd6e0632ff43ff" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e7fd6e0632ff43ff Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 From gaspare.boscarino@theoremasystems.com Thu Apr 17 22:04:52 2025 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5XM5-0027XH-Fk for pgsql-admin@arkaria.postgresql.org; Thu, 17 Apr 2025 22:05:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u5XM3-001ajl-C4 for pgsql-admin@arkaria.postgresql.org; Thu, 17 Apr 2025 22:05:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5XM2-001afl-R0 for pgsql-admin@lists.postgresql.org; Thu, 17 Apr 2025 22:05:07 +0000 Received: from mail-pj1-x102a.google.com ([2607:f8b0:4864:20::102a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u5XLz-000dZ0-2G for pgsql-admin@lists.postgresql.org; Thu, 17 Apr 2025 22:05:07 +0000 Received: by mail-pj1-x102a.google.com with SMTP id 98e67ed59e1d1-306b6ae4fb2so1311730a91.3 for ; Thu, 17 Apr 2025 15:05:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=theoremasystems-com.20230601.gappssmtp.com; s=20230601; t=1744927502; x=1745532302; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=R+CThM5jMwD6hHS66helb4c9K9U4p/ccKeqG8DJ23vo=; b=zoztlKvDz6D+V3cbxRWr5IfCiTEdUCXV0GM7TmE5a9DiI/VjFxzCAnOdfrl31Fpcwz 0k8Fo4WbORWUG1TXKf5HcgbfGqmohbwiZclg8x1nt5xqX5lBsbtNIsK5fDxQejHQTj0e 1dxyFXNzH6471DjZu+OeyBasPQFM0NjXbZ3PW13d2NnjpJqJpufouDx8WZ6lylRmsP6p jPhZ8hxCH6oUU8TeNDszH9iiXxxM4GDV10Bz/Pcsjn8QHPiTos5VzLkXA4R9z43mYKgE 0f9V/PpIqu9tR1pCXZtFw5oB9cfvOCyVHABXtv2UMJEsWhbEJQMRFH69/o+TEy6AaHep AiEA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744927502; x=1745532302; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=R+CThM5jMwD6hHS66helb4c9K9U4p/ccKeqG8DJ23vo=; b=qNGhcne5yMtELZOZ7vNFqNnr+L79nOiGKQqt+pUkdJ0fG5xeS52GGnAJax+LQmiNUL ftdZqw0OOnUYwh+dfIOZ+LZHPAgTDt4QHfGeaBGEZKmSwSHD4jwE8qeswTcauhzNIIYe D6QkxX/7Ub75PhLpLhSIOJJ0KmifCynW5ys+e90Naekjg4J75bg3LNza+0NAi7vBmEsi Af7Oh1UBwVxyWLXPDGUpw+uiZxhlNASuzbLceLl2H9xXrkTG2/6+egOQArNbM7Sj1WcI OxQdQicgZB3YEDLXssH4PIvIk863ldtD/9JZ/guwaqgMONiv9VddoivujOSB/HEOEPss QRuQ== X-Gm-Message-State: AOJu0Yxaf/YoH0jznQ/Y16x1sKLlYr0pdA7dUKH2nzwjBOdMYr2goVqT 323E+V4m0fghrR1AdgIwGauBNVLg+mj/xFCRCJOr+L+0vGWswvaxpwGDiRVul7rKw/G9+zsJUW1 Dj7wIJx9ttWg8GRAxeZvm6EIiOKe0LwBtlDkFoDTXl+41Oqrx X-Gm-Gg: ASbGnctZppYAUYtVy5cXrXiyGrWwXQ9ezRF51y6H3zzdn28syMspG9y7ekeHs0rdn07 7zzIf8TWxXH7XdMcotY3B+/cApKEEWpDa8e4uT6JxGcxZaHSlMXy6sCHlT+j3PrHTo1sGxMPdxt pLxf9J6HyvlkP4podA5O4i X-Google-Smtp-Source: AGHT+IHQo6RUJqtSPn8+Qp00TP3KvieS71OQlFLZGSmOM1XC4yXBsOqKpj7y7avndBX2umkBzhNwo99Hel9o/zgW12Q= X-Received: by 2002:a17:90b:384c:b0:2ff:4bac:6fba with SMTP id 98e67ed59e1d1-3087bbc29d0mr854029a91.24.1744927502097; Thu, 17 Apr 2025 15:05:02 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "Gaspare Boscarino, P.Eng." Date: Thu, 17 Apr 2025 15:04:52 -0700 X-Gm-Features: ATxdqUG-7yrD4RQgoY_LlqJ_yjv0W-5tO4BCF8q4QeI9jz9BOwiAEbtstoJcrQ8 Message-ID: Subject: Re: Replication lag To: Wasim Devale Cc: Pgsql-admin , pgsql-admin Content-Type: multipart/alternative; boundary="000000000000ec04c606330097b6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ec04c606330097b6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Wasim Devale w= rote: > Hi All > > Does wal_level =3D logical can resolve the issue of replication lag? > > On Thu, 17 Apr, 2025, 11:21=E2=80=AFam Wasim Devale, = wrote: > >> Hi everyone, >> >> We have a setup of primary and replica database. We are using the replic= a >> as read only purpose. But the queries are long running queries that take= s >> 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 >> > --=20 Gaspare Boscarino, P.Eng., M.Eng., MASc. Founder and CEO *Theorema Systems Inc.* www.theoremasystems.com | +1 604-765-0121 --000000000000ec04c606330097b6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Wasim,

If I understand= your problem correctly, you are trying to use the replica to run queries f= or some kind of report. For those cases, I recommend setting up a logical r= eplication which will allow you to have a replica that can be modified base= d on your needs. For instance, on the target database (replica) you could c= reate indices to improve the performance of your query. An analysis of the = execution plan would be necessary, of course.

= Regards,

=C2=A0=C2=A0 Gaspare

On Thu, Apr 17, 2025 at 5:15=E2=80=AFAM Wasim Devale <wasimd60@gmail.com> wrote:
Hi All= =C2=A0

Does wal_level = =3D logical can resolve the issue of replication lag?

On Thu, 17 = Apr, 2025, 11:21=E2=80=AFam Wasim Devale, <wasimd60@gmail.com> wrote:
Hi everyone,=

We have a setup of primary an= d 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 plac= e that will not show replication lag and the queries also executes on repli= ca database without competition on WAL reply?

The settings:
Hot standby is o= ff
And maximum streaming delay is set to -1

Thanks,
W= asim=C2=A0


--
Gaspare Boscarino, P.Eng., M.Eng., MASc.
Founder and CEO=
Theorema Systems Inc.
<= a href=3D"http://www.theoremasystems.com" target=3D"_blank">www.theoremasys= tems.com | +1 604-765-0121
--000000000000ec04c606330097b6-- From laurenz.albe@cybertec.at Fri Apr 18 06:48:55 2025 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5fX4-004DCD-8A for pgsql-admin@arkaria.postgresql.org; Fri, 18 Apr 2025 06:49:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u5fX2-0085OT-2A for pgsql-admin@arkaria.postgresql.org; Fri, 18 Apr 2025 06:49:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5fX1-0085JO-Ko for pgsql-admin@lists.postgresql.org; Fri, 18 Apr 2025 06:49:00 +0000 Received: from mail-wm1-x32a.google.com ([2a00:1450:4864:20::32a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u5fX0-000fOh-0L for pgsql-admin@postgresql.org; Fri, 18 Apr 2025 06:48:59 +0000 Received: by mail-wm1-x32a.google.com with SMTP id 5b1f17b1804b1-43cf034d4abso8036445e9.3 for ; Thu, 17 Apr 2025 23:48:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec.at; s=google; t=1744958937; x=1745563737; darn=postgresql.org; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:cc:to:from:subject:message-id:from:to:cc:subject :date:message-id:reply-to; bh=J/mEtoPx03qN/aw8vMg72DK83PyXEojIUMVeyLzYyls=; b=TFyJQyLV/r27SrZlyQPizLcW+t3zugtoHoOyYSxK85uU1TiUrSjDp5CcmosiD09PYj N3Xjv2wLyq9xKpiblEZ7fwqLwjgwPgFjYMJNWDrcbMFA+0KxwShmknw9wOW8ehzg3mLu 4vRbiBsUU7BzuEdJ3OWsH+THX41wkXjW/i4YXki/8uuNObPtSUY02RcgpQQHXyuH0vYy W+Y0KSftWgXZkxnUq+tpttTyq4BkPMoXHwuRQg7iYAXpyA59o5I7T/T/l8vpfbOHfdsL RLw8erxGmVEcAmQy1YM3P8pjmyIE3CuR1zu4uREFImb1/JlaCKTlhX7ZQiNEQYPCD3/x 0+kQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744958937; x=1745563737; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:cc:to:from:subject:message-id:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=J/mEtoPx03qN/aw8vMg72DK83PyXEojIUMVeyLzYyls=; b=Li7bNcW0cu1iGUivdnNxzj3LJkUuhHSKkubNznrbe/RhI7k7E+lK8znDrd6RxxcabM Nm2XL7kR1kqUFwH7owdTCkKiPV+178kZB2iQ3BUtI01KSyRN4A4go+Evw5czzwzQ5CHE oFZdR7iPYU7twWd9ehD8SQyMKR8rObvB+dYBv7ZvkLoKIfiSOazM6m3wydXjBQoek/M1 l4w2KlCfE73Cv+09Tfx9+JNQMxiHDXedaLwzItr2zmH8fAUvaV1RlolOvMX/tbYRbqhS LEUhPO8OfEKrHwP0g/JphsKGyiYMQ8lTDL3sy5PgsBVzLskVhVB8giKNQYLinHVR5Mpb wYpA== X-Forwarded-Encrypted: i=1; AJvYcCWg76velRZLZDCfIdK9gllvX2c+xlEDGJq+Qb7WnxumMZbeYNgn07OZiiG6ivUZc468udJHXbbHoU7Vew==@postgresql.org X-Gm-Message-State: AOJu0YxCzZsh+WLb+cIn0tyyKp8TrscoHxCQC15N4PXYeJZ5jJsOWcBn P2KaFD+fzOGP+mveJ1+gwnvklSo5jJ0yS4RVfjRE5t5ImtTodbotXM54VKl9M0SAMGuwgZBwF+I W X-Gm-Gg: ASbGncs+7c9m9GJ12cpiK8LyXjfcho9veM3gaUAKpcuWdMGLSsj1reOLKz9ogq2VkOP tHEqfS/2sel3GqNGBV4m/HyHs/1qs1y1ia3sXyYtOq5T/Jk9SSH/l9VqugvLRzXKf0vEYn6xb3S MEvgtN+wcZtATBNHQuNVysLgNAbWfFwwH4qZdLczws083Z8OTcClgggw+hGCCukhWaMTqxEuhk/ Y9U8yNKnBZ3ZQ+tMa2fTa3FeZY7IhxGgQfwaKq/b14DWUABoheOmGqCURRZtH5EIjIfQ46cERDw 6blzyEvZP6zzMZmnIFdi+r7R5zjh1c4LMxMmPwuYpwZcLMJ4ES8KH6FeITr2 X-Google-Smtp-Source: AGHT+IHGvLTx9teKG/pte/nkX6CLLNzIzdSQW7vbBnGQxaKAThMJHtT8XsKhkowxg4wizKKvVbILvw== X-Received: by 2002:a05:600c:3583:b0:43c:ed61:2c26 with SMTP id 5b1f17b1804b1-4406abb2407mr11534365e9.17.1744958936734; Thu, 17 Apr 2025 23:48:56 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:5373:9d9a:af04:78c5:75aa]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-39efa4a4856sm1777322f8f.81.2025.04.17.23.48.56 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 17 Apr 2025 23:48:56 -0700 (PDT) Message-ID: Subject: Re: Replication lag From: Laurenz Albe To: "Gaspare Boscarino, P.Eng." , Wasim Devale Cc: Pgsql-admin , pgsql-admin Date: Fri, 18 Apr 2025 08:48:55 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On Thu, Apr 17, 2025 at 5:15=E2=80=AFAM Wasim Devale = wrote: > > Does wal_level =3D logical can resolve the issue of replication lag? > >=20 > > > We have a setup of primary and replica database. We are using the rep= lica as > > > read only purpose. But the queries are long running queries that take= s 30 minutes > > > to complete. > > >=20 > > > Do we have any settings in place that will not show replication lag a= nd the > > > queries also executes on replica database without competition on WAL = reply? > > >=20 > > > 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 =3D on" will probably get rid of the majori= ty 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 =3D off" (available from v18 on) will get rid of= another set of replication conflicts. Before v18, you'd have to disable VACUUM t= runcation on each table individually. You will probably still get some buffer pin replication conflicts, and comm= ands 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 t= o "minimal", you cannot have replication any more, which would get rid of rep= lication conflicts. Similarly, setting "hot_standby =3D off" on the standby would immediately g= et rid of all replication conflicts, because you could no longer connect to the stand= by and run queries there. Yours, Laurenz Albe From wasimd60@gmail.com Fri Apr 18 10:51:20 2025 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5jJp-004x6X-O3 for pgsql-admin@arkaria.postgresql.org; Fri, 18 Apr 2025 10:51:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u5jJn-00Bnz9-MN for pgsql-admin@arkaria.postgresql.org; Fri, 18 Apr 2025 10:51:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5jJn-00BnyT-3z for pgsql-admin@lists.postgresql.org; Fri, 18 Apr 2025 10:51:36 +0000 Received: from mail-ed1-x52b.google.com ([2a00:1450:4864:20::52b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u5jJl-000h72-27 for pgsql-admin@lists.postgresql.org; Fri, 18 Apr 2025 10:51:34 +0000 Received: by mail-ed1-x52b.google.com with SMTP id 4fb4d7f45d1cf-5e6ff035e9aso3239973a12.0 for ; Fri, 18 Apr 2025 03:51:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744973493; x=1745578293; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=iLteA45xmuHPWOIhNe8UpIwpA3GHE68e+YCSAtQY0PA=; b=LhfqhVFyy2mVWrHdAyv5CU9KPGEzS0R9du8QRiKLx0kISacpVjxm7dGjoHw1247MIi eLRZ3wpt5/TR17jrsuyTxm0Ruz7hsxYQb2eIHeiZ/p7XfO9ySk4bOFPyHTc7wjGgCIv3 CHVSQmGuwHku1FUQUlzB6zTozvM3nWgHw9/L3gKWrCCOMh7mdzSFE9mtcy26/r9OvqLf rBb44NV9Cd0E8djYulKwlbzmQjSLbrH/nLFLQwWi9PR/9cYcSJZ8HyVJCikeEnDk6EJk WjYNYGfBca3ai5zTryPIlfS4ftwsT2Ig38KD9cLtzawNsmWOFH06lrp/baSLcT6sUOZH JE0w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744973493; x=1745578293; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=iLteA45xmuHPWOIhNe8UpIwpA3GHE68e+YCSAtQY0PA=; b=qDvSYhxXQlmZ7GX7vY/eZneeN2qtgKoYQ2i1aeVNUyV73TH1JhJxpQ+wfMAWQugdYP tDTuLrZOYP7lKMn8oOX+lNS18Ma/JTY4Ngig0cXbPlPp9QJpyEZ3IataolN7Sos9L1gM MXsYi6TRV6jko8JNO8PBSJQqehWrh56BLr3UdMURO7XFVbL+TCvsYv9uXjJvRBCA4R24 Qj+RMIDTTL9w9845fRI/zb0hD11p6LpzyATMq75TsY0+Do437Ip91yP0xZXXC5Mg/6B/ el6WCr7JzSvBh/HnzY44Tp4Cf3ztMevnuO9ASdKRuWhzJUD2FcxUQBl68M2MKJtEc9Ik wcCQ== X-Forwarded-Encrypted: i=1; AJvYcCVCjorJNaQzvBfwngsH0gZyifTObHCqyZLZIpG7r6afbF9yvbEQEKaMSOihl73NsAwBGUBqFxFZa6Vazw==@lists.postgresql.org X-Gm-Message-State: AOJu0YwXT0Y0evIf4zxkGOqPAUE1McK2c5yI11kIs7ZxYPkxWhm/0B25 FTSReN/82Y0DCJ652NiHGlFy314ImGxWmdEHnXVdp9Ul5ee6t/ruseHmFWMyC6oi5JzW1gLV7cc VhDO6TPpx/Le1fZMtG8lYqvPJMTs= X-Gm-Gg: ASbGncsZlqh0lBz7IeluRsGs+TxEG30l0QbvWxPUHtA2fHUTkieREeMAtGYEh5U7vKY XgimuQAgyZqqj9q5aA24OroSKdqiYwBcE/EAMRj3gQ3VW9PJMsrpNlsI9LP+uVg31ektSRLwZaf nYTr9d/UdYX8EyAf37ZTxvbhA= X-Google-Smtp-Source: AGHT+IHd/+J3R7IL6+MkwzT8fFQV5bMyIeBF4WazBodJRybGd9XCNWuliC7wDyFO66DkLJ8uR0SqG0L0wlAb8DDZdI8= X-Received: by 2002:a17:907:d1b:b0:ac3:4487:6a99 with SMTP id a640c23a62f3a-acb74db7e5emr201937666b.47.1744973492476; Fri, 18 Apr 2025 03:51:32 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Wasim Devale Date: Fri, 18 Apr 2025 16:21:20 +0530 X-Gm-Features: ATxdqUGKiBBH_2wI_kSkYcY7HbAbnDM19sc_vPPM6r9XAgQsWeg5t6xhKpOHbHA Message-ID: Subject: Re: Replication lag To: Laurenz Albe Cc: "Gaspare Boscarino, P.Eng." , Pgsql-admin , pgsql-admin Content-Type: multipart/alternative; boundary="0000000000002965c406330b4df8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002965c406330b4df8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFpm Laurenz Albe, wrote: > > On Thu, Apr 17, 2025 at 5:15=E2=80=AFAM Wasim Devale wrote: > > > Does wal_level =3D 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 WA= L > 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 =3D on" will probably get rid of the > majority of > replication conflicts, but the price is that long-running queries on th= e > standby > can bloat the tables and indexes on the primary. > > - Setting "vacuum_truncate =3D 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 =3D 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 > --0000000000002965c406330b4df8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

So finally long running on a replica won't minimise repl= ication lag to zero in any scenario? Correct?


On Fri, 18 Apr, 2025, 12:18=E2=80=AFpm Laurenz Albe, <laurenz.albe@cybertec.at> = wrote:
> On Thu, Apr 17, 2025 at= 5:15=E2=80=AFAM Wasim Devale <wasimd60@gmail.com> wrote:
> > Does wal_level =3D logical can resolve the issue of replication l= ag?
> >
> > > We have a setup of primary and replica database. We are usin= g 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 replicat= ion lag and the
> > > queries also executes on replica database without competitio= n 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 conflic= ts, and
that is something you can guarantee.=C2=A0 However, you can reduce the freq= uency of
replication conflicts:

- Setting "hot_standby_feedback =3D on" will probably get rid of = the majority of
=C2=A0 replication conflicts, but the price is that long-running queries on= the standby
=C2=A0 can bloat the tables and indexes on the primary.

- Setting "vacuum_truncate =3D off" (available from v18 on) will = get rid of another
=C2=A0 set of replication conflicts.=C2=A0 Before v18, you'd have to di= sable VACUUM truncation
=C2=A0 on each table individually.

You will probably still get some buffer pin replication conflicts, and comm= ands
like TRUNCATE, ALTER TABLE or VACUUM (FULL) will always cause them.

Changing "wal_level" has no impact on all that, except that if yo= u set it to
"minimal", you cannot have replication any more, which would get = rid of replication
conflicts.

Similarly, setting "hot_standby =3D off" on the standby would imm= ediately get rid of
all replication conflicts, because you could no longer connect to the stand= by and
run queries there.

Yours,
Laurenz Albe
--0000000000002965c406330b4df8-- From laurenz.albe@cybertec.at Fri Apr 18 11:08:50 2025 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5jab-0050bE-Oj for pgsql-admin@arkaria.postgresql.org; Fri, 18 Apr 2025 11:08:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u5jaZ-00C7rA-OO for pgsql-admin@arkaria.postgresql.org; Fri, 18 Apr 2025 11:08:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5jaZ-00C7qv-Bf for pgsql-admin@lists.postgresql.org; Fri, 18 Apr 2025 11:08:56 +0000 Received: from mail-wr1-x42f.google.com ([2a00:1450:4864:20::42f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u5jaW-000jMS-2N for pgsql-admin@postgresql.org; Fri, 18 Apr 2025 11:08:55 +0000 Received: by mail-wr1-x42f.google.com with SMTP id ffacd0b85a97d-39c1ef4acf2so1105272f8f.0 for ; Fri, 18 Apr 2025 04:08:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec.at; s=google; t=1744974532; x=1745579332; darn=postgresql.org; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:cc:to:from:subject:message-id:from:to:cc:subject :date:message-id:reply-to; bh=b9APN0EhgufrTGPZ5SHLIFnU8RJ3Pk0Su/FRtfkMnNQ=; b=MuRDq1z3q6gi2F8f7rVE6JFn1sPuW9p58qD9uVj+l8kAM1h2t3DOZ5RI0WAmczJ/Ni +yuzQpHt0ITVyVBmY0tzTbyIHmvFKdlKS4p6hm3QjL/XhqbHHKCEQrnXxJNmRhJnCYpS dt3aJsBf5KgdfoCeQFrDakKUvDW6k2ly6xm9cufzhx+V/ttWqzttAX9JGtakNBBnL4L3 h4VxXwJM5AqJj4Dl0aSE4J8+L8Yj5LaFnaux3XK+XfY+aZmBPP43OMBTjnZYOVInWcjv buyHzTSxgAF4v0xQIU6MFmOLqircsXhMt0ZJvN0xquHV8as/DGwm7xG8/FXFwrxsaaUa g5wQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744974532; x=1745579332; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:cc:to:from:subject:message-id:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=b9APN0EhgufrTGPZ5SHLIFnU8RJ3Pk0Su/FRtfkMnNQ=; b=YOiSVXMx1G47WVPNDorm1CPZCVHMd44s8z9MF8aSQwOEoLxOC/qQjHDkTXQDF/NPIM QoksqkgrxISw4LgMmydTGHlJrxzDqG68MMrSYSGO6bIExy6Kw/sX7NxwHXjU4LQpSUQP HtZLhOPGEZyoLu6u6lPSVdUcqQZj8xtDTE9d1mPlcXKfW0E2X6S6V2BEyu4Y3Dj1cJ3m GIRUyxi+a13kyv7Co3VYWsb2BWqnk2sM/CKkov2gGKUfRWdiIgAPnBzv0qoU67BPBkY+ rwzqgChyAHlcScUetVvtRtg8SggYTg4T+sWB6KG3kpcClo4y2vV7G1ZTs0rN86cdaj6c ZHeg== X-Forwarded-Encrypted: i=1; AJvYcCWIPk92DezmDn55F+s8rtuUmCtRFfDuAZgRFlRfuKwn/diWmRl3T9wJY2dXJ2B5wU444ruNyilmbveDYw==@postgresql.org X-Gm-Message-State: AOJu0YxK1/L9ixfR6oipbcQ9bhAMsh0iuCEuXZJqoXTjXq1FhUBrrMdz 39xNCgNV3zy7sb5PBf/42d/a/4Xm43OccojEY7t8EV0SdwtpC0ui8bhhT8+sISc= X-Gm-Gg: ASbGncsm48A04J71eBd/JZnmSqAFX6XJC+c9YmCBKq2qK/AmKwCeHp7yBcO+Kr39X2u QJHMGWe/YtEhq6xPlp0ztDfUyKJ/VToX/ibpuGj+X4AU10i0/+cjkEh8HBIbrj5DESjnL/FppN1 5SATADJ73leVULEr4Zz4p5L34qUc9bFZW9pYqkRcoBRJLGdHQ+aGDBt4nwgt51VBYFi9w7zGXRm vy/U8no8erllsly+UCtfuLq3uE44e4bXDAhJs6hzFl/Eomi9GbOYYpo1wHstpWlBZ5NpB7ziOWm qN2u6vzdsl+5DzUokNt8eFCd72NSn2cpBIo3N1hK0fK1NsLWqmxaJ09ijSVP X-Google-Smtp-Source: AGHT+IFQppZnEWNvUUrwvRnfeMJ3882DaII1UR7A2X/g1NY7CT8TouVHhdAZxUbMFVjW32OLkpZlHw== X-Received: by 2002:a5d:64ae:0:b0:391:ba6:c066 with SMTP id ffacd0b85a97d-39efba5dba8mr1993610f8f.35.1744974531609; Fri, 18 Apr 2025 04:08:51 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:5373:9d9a:af04:78c5:75aa]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-39efa493446sm2417870f8f.74.2025.04.18.04.08.51 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 18 Apr 2025 04:08:51 -0700 (PDT) Message-ID: <6e85abdb00db4ec0cd8220373b4610838d8a3455.camel@cybertec.at> Subject: Re: Replication lag From: Laurenz Albe To: Wasim Devale Cc: "Gaspare Boscarino, P.Eng." , Pgsql-admin , pgsql-admin Date: Fri, 18 Apr 2025 13:08:50 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 ze= ro 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 From wasimd60@gmail.com Fri May 23 07:13:10 2025 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uIMas-003469-FL for pgsql-admin@arkaria.postgresql.org; Fri, 23 May 2025 07:13:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uIMar-00G1LG-DA for pgsql-admin@arkaria.postgresql.org; Fri, 23 May 2025 07:13:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uIMar-00G1L7-0d for pgsql-admin@lists.postgresql.org; Fri, 23 May 2025 07:13:24 +0000 Received: from mail-ej1-x632.google.com ([2a00:1450:4864:20::632]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uIMao-000TgK-1X for pgsql-admin@lists.postgresql.org; Fri, 23 May 2025 07:13:24 +0000 Received: by mail-ej1-x632.google.com with SMTP id a640c23a62f3a-acae7e7587dso1295397666b.2 for ; Fri, 23 May 2025 00:13:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1747984401; x=1748589201; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=Fphwuhdjl3kkwuSVuVFJIH4qr0KbQzJpnICilBq9pSQ=; b=URE8GWSpVgH/0i+rlwDaZ87KOMsLcMaBGoDhll2o9eN2ky8blY9U/NxoUAFUYIhufh 4IPbp8un14KzhRfI9TSNtoqFDwFXsOsRNvIHz3VVekGStgJC/lAvK9PeKb69UYQ7JJHi eb5lplH8fGa/0gI/PvMGJ+z+nL0uPGIdimKG7UpBLUhCp0xkxXKntd0zY9cNBUe51Djd 6Au2yukGgYJRuYLrIrSYddzvpGFq8zuvAhYH4ty426f96FgKvVTpznaonHuDcZDFb7oR 2DWLOmV7p7jTKH384YEuivd/MaMAjJ8FZAuKbtHXIUKM+BRvQKUPC32/22qpM0YhQB7L 0rSA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1747984401; x=1748589201; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=Fphwuhdjl3kkwuSVuVFJIH4qr0KbQzJpnICilBq9pSQ=; b=j3tABbmsRHW26ReI417A6EJ3iMcEy66ROUUEsb3wtB0EKePlLd2qlJgnlfkTArH4Sy GQUHVhSSEE6jSPgVphMC/DZO22h+wojU4m22WHVdx/A28nNGDjTtFFRLeQdFvdjvI73o hWjgaX7wPXIiVihrvDGjoXjQNeQ1Zdbh/1oNpw64ZWvj3F+3KdEDa3wbEmLA/ZFx+wGw B9LNhgqNbRA4lwV/RQuWxrvNau9Ramjo/KemSw7Goa4UiEiq05sGvZH/jMkgVH6KO5WJ miagdpLR0OZ6r3856aXrha4f/RhS7Rcq/LHqdBGANWGZwmLLSa3zu7X4RURdiyGauWZk 30yQ== X-Forwarded-Encrypted: i=1; AJvYcCU8RbZue7huuSxGHyb2sWSKmELt1NMTDuU5v82XMciK2G+VVn2P3nREyc+Lmrg0neJV/xmpmreqlSwcqA==@lists.postgresql.org X-Gm-Message-State: AOJu0Yy6qkhth61Z9zqGn5eSQXJwWWzYeZvNu/Vpo7KeUrDstVKcI71v jr55Bs+2kLhVLorlIHQjrUxrbyBcp0ygSNvqSuHEnqNs3himSi2nFqp5lWND3jUu5N+UUTjmPE9 o5Cv1OdWJrGhs/BPjF0zOXnC00y1UHowAog== X-Gm-Gg: ASbGnctcSfeRFqlkdzRHB8JT4ZALDX11Z+xzthldid8g/sePvE1Ck5vAtMaosJ3bMc1 Avg4JzNk7+JVIPwXcGmVXvUHKI7mNfEMd954yNj7SbqOW0a2T0FCtKrcBtbRypizioDv0RUGAsc 5kmS4XWJ0OhfSZsE6LVMPsn/JgTXGvxx8YYYguVA0SyTvn X-Google-Smtp-Source: AGHT+IF1ppmK9WQNt+I/RuRoi2pnZEHm5Mw+NcyWa5PJJvruhzr72bXLM/9xPrl8FQxzd6e7KIgRXMBK4D/JIpPbb84= X-Received: by 2002:a17:906:f249:b0:ad5:b221:540c with SMTP id a640c23a62f3a-ad71c14500cmr131098366b.58.1747984401153; Fri, 23 May 2025 00:13:21 -0700 (PDT) MIME-Version: 1.0 From: Wasim Devale Date: Fri, 23 May 2025 12:43:10 +0530 X-Gm-Features: AX0GCFve1Eblgs9AEB7xSvRqWg9XZwL0AFY15-QINsBT390Iqns5KZ7Yz_iMyP4 Message-ID: Subject: Replication lag To: pgsql-admin , Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000004dc6b20635c85501" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004dc6b20635c85501 Content-Type: text/plain; charset="UTF-8" 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 --0000000000004dc6b20635c85501 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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
--0000000000004dc6b20635c85501-- From whitneykiss741@gmail.com Fri May 23 08:08:33 2025 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uINSK-003Mdz-Ti for pgsql-admin@arkaria.postgresql.org; Fri, 23 May 2025 08:08:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uINSJ-00GdyW-59 for pgsql-admin@arkaria.postgresql.org; Fri, 23 May 2025 08:08:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uINSI-00GdyO-J2 for pgsql-admin@lists.postgresql.org; Fri, 23 May 2025 08:08:38 +0000 Received: from mail-yw1-x1129.google.com ([2607:f8b0:4864:20::1129]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uINSG-000Tds-0l for pgsql-admin@postgresql.org; Fri, 23 May 2025 08:08:37 +0000 Received: by mail-yw1-x1129.google.com with SMTP id 00721157ae682-70cb3121db3so62953397b3.3 for ; Fri, 23 May 2025 01:08:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1747987716; x=1748592516; darn=postgresql.org; h=mime-version:content-language:accept-language:in-reply-to :references:message-id:date:thread-index:thread-topic:subject:to :from:from:to:cc:subject:date:message-id:reply-to; bh=k/LDKFxGIWXl5iKzRM6EwlzkgRhe8Hd4RZyiFZLQrR4=; b=l12G2unJ8P8R6J5R7NCULECRE+P1wjOPGfKApWsIKrl+jQM4xlGXJfp4bUlTu8cliu YRbeZbGylfe46uDKDimxmpRZvJrGJKgTjs75+ggVhK2VjxCnDwwl81kk7DXJBUyt4rw6 UoHVn2GTJ60fCD4cl/ur3M/hQn67CRCsQyOko4r4OP9HVYBpDo3prB/q87ahUZOg4l7r RDeVdZxkG8kuds27DgeFJu3CLMPQ/tfOihj5UujXOLPyn6JqJOLeHSucLhtVRDDcV1OT mcCSUk2epZZljp+OTIRa5yRERebWPe617Pb2pZd0sQb9WoeYqOhlxfGSF5d/IjeLV1g4 gtHw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1747987716; x=1748592516; h=mime-version:content-language:accept-language:in-reply-to :references:message-id:date:thread-index:thread-topic:subject:to :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=k/LDKFxGIWXl5iKzRM6EwlzkgRhe8Hd4RZyiFZLQrR4=; b=Pw51c18xJc3DeRR+D5L0ftB16C8LCBeT1SmZS0Z0aU7H22eJJEU93J3V6XFNaFSe73 GSf7yXX2l3jRs7H2a8JH82/LmcaU72jGmNl/5SkeGpwU+w/LGTHM5EWH3Ez4zW6RuibA xaHnLay6FhEmKqzqAXZpzvjGsSd0EM5dE8jY/anGeOhYZrztBF+XoFzCFQRqBgk02sea NG6EtlmSWept1J0d2OTjWosEc+DKuouAouq1LZ6tkYT2MlFtXSNoB8AbfRudRDsL5o06 SXTyhRXv3/jNm4hkyMISYeTfAzU8bSibUv9CCUI+AtXFv08VsAVYOlFWuknA+6V1u6JZ 3Low== X-Forwarded-Encrypted: i=1; AJvYcCXkVOiFpZNRc2cn95NQYC2cMWngVlmzgR7vHLjk0ck9zpVvlMBbWpYEVkR54JYFFjDQ4IrBJInakD1gCg==@postgresql.org X-Gm-Message-State: AOJu0YzyECmyY5ASAZLxdiPqlxOaZQnV/6D3HR9/ZaEWPqILVpguSAGz p6eGoJq1yA0b7xQu1agsUHBQjM8YZ1rbquqlxlOdEEZkNyrRAdO2dAz6 X-Gm-Gg: ASbGncvRK2Qde4x34GhlFyLDnWOXUwyN6R3t5X7dWAu9qp1CMzc+LxSuIBNriWaXOfK WU6RAIoRcWIAssQtArb0YET41Ot9VAbJB/XkZi6KQLC6WMMarb8cjNqlxycXhV6juZjuaZV4Wgt iriXUg5LxiK7umNl0U+JwKIDamxArBnjzCTxp+wBRrscO9l86sw+pG3SXHJh+4TTGfMsXtrdhHw Xop9CmTge23FzeVzyi+sJycJV7tciiYE242LQyOh0Xydoi7p0DxpQmvpQHagm5yKMAuEvu9hOoP BFRhB90VtallprPOvzzSGMqCb6iT+c3QxhKcMyP2rJsWfGA3nkGXD2m89rSwYiAYeX7tmffOs0s UjLtOcrRlUDdt3jD264XiXqVlH6+vKRL80HocDqtu7eFGKaLnDrVcSA== X-Google-Smtp-Source: AGHT+IGBZga76soOIpB6Uvmzvu5ImRYrpfIXwd+fhMqrkEMXYStsbly253RMT91Ecd3HSgzdmWDaaA== X-Received: by 2002:a05:690c:4d49:b0:70e:16a3:ce77 with SMTP id 00721157ae682-70e16a3d565mr46205707b3.6.1747987715576; Fri, 23 May 2025 01:08:35 -0700 (PDT) Received: from AS4PR03MB8506.eurprd03.prod.outlook.com ([2603:1026:c03:7052::5]) by smtp.gmail.com with ESMTPSA id 00721157ae682-70e07dc91b9sm5885627b3.39.2025.05.23.01.08.34 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 23 May 2025 01:08:35 -0700 (PDT) From: David Okeamah To: Wasim Devale , pgsql-admin , Pgsql-admin Subject: Re: Replication lag Thread-Topic: Replication lag Thread-Index: AVFXdm5VH7LGsQN0MNH6nmnT1TDFaJTohoKY X-MS-Exchange-MessageSentRepresentingType: 1 Date: Fri, 23 May 2025 08:08:33 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-GB X-MS-Has-Attach: X-MS-Exchange-Organization-SCL: -1 X-MS-TNEF-Correlator: X-MS-Exchange-Organization-RecordReviewCfmType: 0 x-ms-reactions: allow Content-Type: multipart/alternative; boundary="_000_AS4PR03MB8506AFDDDE719261B0E1ADDDF798AAS4PR03MB8506eurp_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_AS4PR03MB8506AFDDDE719261B0E1ADDDF798AAS4PR03MB8506eurp_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable 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 =3D on= , a replica can serve read-only queries while applying WAL files from the p= rimary via streaming replication. However, there are a few caveats: * Read queries on the standby may be canceled if they conflict with rec= overy operations. This behavior can be tuned using parameters like max_stan= dby_streaming_delay and hot_standby_feedback. * Unlike Oracle GoldenGate, PostgreSQL=92s native logical replication i= s more limited in terms of conflict resolution and cross-version replicatio= n, though tools like pglogical or Debezium can bridge those gaps for more c= omplex use cases. Best regards, David Okeamah DAVID OKEAMAH,DEVELOPER ________________________________ From: Wasim Devale Sent: Friday, May 23, 2025 8:13:10 AM To: pgsql-admin ; Pgsql-admin 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 postgr= esql do we have any provision like this. Please assist. Thanks, Wasim --_000_AS4PR03MB8506AFDDDE719261B0E1ADDDF798AAS4PR03MB8506eurp_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable

Query Execution and Reply WAL Coexistence in PostgreSQL


 Wasim,





  • Read queries on the standby may be canceled if they conflict with recovery = operations. This behavior can be tuned using parameters like max_standby_st= reaming_delay and hot_standby_feedback.
  • Unlike Oracle GoldenGate, PostgreSQL=92s native logical replication is more= limited in terms of conflict resolution and cross-version replication, tho= ugh tools like pglogical or Debezium can bridge those gaps for more complex= use cases.






DAVID OKEAMAH,DEVELOPER 
From: Wasim Devale <wasi= md60@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 coex= ists but in postgresql do we have any provision like this.

Please assist.

Thanks,
Wasim
--_000_AS4PR03MB8506AFDDDE719261B0E1ADDDF798AAS4PR03MB8506eurp_-- From dcvythoulkas@gmail.com Fri May 23 08:31:19 2025 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uINoO-003Tzg-Lp for pgsql-admin@arkaria.postgresql.org; Fri, 23 May 2025 08:31:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uINoM-00GwP6-Ee for pgsql-admin@arkaria.postgresql.org; Fri, 23 May 2025 08:31:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uINoM-00GwOx-3x for pgsql-admin@lists.postgresql.org; Fri, 23 May 2025 08:31:25 +0000 Received: from mail-yw1-x112f.google.com ([2607:f8b0:4864:20::112f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uINoJ-000Ttr-0b for pgsql-admin@lists.postgresql.org; Fri, 23 May 2025 08:31:25 +0000 Received: by mail-yw1-x112f.google.com with SMTP id 00721157ae682-70dec158cbcso33607157b3.2 for ; Fri, 23 May 2025 01:31:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1747989082; x=1748593882; darn=lists.postgresql.org; h=content-transfer-encoding:mime-version:references:in-reply-to :message-id:date:subject:cc:to:from:from:to:cc:subject:date :message-id:reply-to; bh=fBs6WdplK/N8EW+OAICtdjhex0eEn3LhQRHHHkrbDt0=; b=WXkUyni4IKes4r0oNX/ywDLVx1CmMiHBR39gzHMA0/1iP/qPO6EVwSY33SzMur3Lj3 9J/cZcmWDrsRBwskDJdOp9x/+ve6K9s39mtDggIXmM6AvhPAyVPf1mZG54KxTAhmPZSz W7L17LY629cCBCEJD3Jfx6WDSmbQOw6mUMW5yG/fs9a/XTb42R3ICCihfpp7BqkwOrK0 gzhz4bMiZDFcjl/jUs9yNhZ96GWRRKwqsk36hYua1bA7+PaBY7mNaIf1MIB1NgPtjSXm GMafpKGlBMLHAy4Wc9xfUVeGQMEzv7RfpR4M3MX5EOwUU+Yn3F63JBndS3NQXMGAzAzE dqXA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1747989082; x=1748593882; h=content-transfer-encoding:mime-version:references:in-reply-to :message-id:date:subject:cc:to:from:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=fBs6WdplK/N8EW+OAICtdjhex0eEn3LhQRHHHkrbDt0=; b=Ki5YDp5alsqyYR68CJNYAymZUkoejeI5DfEU8y0u6s3tcfPyWtVw5zCkVBlEzvTrQi 1PO92vXLNLe4is0xACnU0x6WP5UeXNHSRZ+DxvlJ5/PFT2y4JTnl2jdqVmiV0gwWkoBZ nVYb7AvQjmjiWLoU+xOI0mH+ArmKwywRw0ZlrtNd1kMV9gluYiuKeNZgMdurYeAKZ60j 9kSnsqSRHRUnwkOEmkiaFAZIeRVB4PG2ivg2ij3BPIhIrR9jKaVNqiM6A0eeSZrSGQTd 0KER1OIs2KW9YBl8wZDcgSJ+Qa3ksSvfv01CIX+zkujFadkZTt4534irJPw3CQpOtVGg Il/Q== X-Forwarded-Encrypted: i=1; AJvYcCXUHoK7+D9S7LVg3Wvuo+/H3n7558dFL3gB72GoBsNYxfyXXGvpPNG8FSwatmJaWOXPfTG3tSfVtusmBg==@lists.postgresql.org X-Gm-Message-State: AOJu0YzCP9Avt66FoT8wQS1fy5wLGEwt+8RoY45yBSjjRr4dYL6cBQir PhzyBVREv/p+dy3PrFTsmz6KkwQD8MaXcp/Ptap2NYF7mvyyAb7YxM57 X-Gm-Gg: ASbGncsInBm7GCDZfaQurAAjEO+iIHHh2qoINNA+Udbt0qT0fNf8/Hh0GxObk1E5aef uwSnqFZqRo2z6ZottM3dtSICXf1JpD+BSpSWfYFawrlkY9dl1takt0ltJ3IYpz9pUH77Cekni3O IeaTY4GiFtHXIr8+XiT1F7la7MlKZrhHCDlYtcGAfsV9l9Pphv5lYmqwxSKA+H4xR4VwGQvT6rM EWzB5Qxisif+5QbAS6xo+JCSyJytTMe2XrSsHfB8YD9YqNKa8QZJtEcQE63LBMdGnyTWEKGibC1 0gSv+G9ofF2FzZ3q1R99W5mdKcK8YCv12khAb3WxWo+5ByuXgM9GgpYW4Q/rAPoblOU= X-Google-Smtp-Source: AGHT+IG9Ls9W/IESMB4LWOCkW16SrzC1PCwHBk5e49L5BOBHPxGm/dUi0MSqmzwyfJ85RUjcSvyIog== X-Received: by 2002:a05:690c:ed4:b0:708:2604:4a10 with SMTP id 00721157ae682-70caafccab3mr386323447b3.18.1747989082200; Fri, 23 May 2025 01:31:22 -0700 (PDT) Received: from dcv-workpc.localnet ([2a02:2149:8af7:2100:cfe9:b5c1:ec40:f82]) by smtp.gmail.com with ESMTPSA id 00721157ae682-70e19dfedebsm2211557b3.52.2025.05.23.01.31.21 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 23 May 2025 01:31:21 -0700 (PDT) From: Dionysios-Charalampos Vythoulkas To: pgsql-admin , Pgsql-admin Cc: Wasim Devale Subject: Re: Replication lag Date: Fri, 23 May 2025 11:31:19 +0300 Message-ID: <6162737.lOV4Wx5bFT@dcv-workpc> In-Reply-To: References: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="nextPart5016410.31r3eYUQgx" Content-Transfer-Encoding: 7Bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --nextPart5016410.31r3eYUQgx Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="UTF-8" On streaming replication yes, you can perform read-only queries. On logical replication, as far as I know you can also run write queries, bu= t you have to be=20 careful to keep the data consistent. On =CE=A0=CE=B1=CF=81=CE=B1=CF=83=CE=BA=CE=B5=CF=85=CE=AE, 23 =CE=9C=CE=B1= =CE=90=CE=BF=CF=85 2025 10:13:10 =CE=A0.=CE=9C. EEST Wasim Devale wrote: > Hello, >=20 > Reply wal and query execution on replica can coexists? >=20 > Golden gate in oracle has this feature that they can coexists but in > postgresql do we have any provision like this. >=20 > Please assist. >=20 > Thanks, > Wasim --nextPart5016410.31r3eYUQgx Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="UTF-8"

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 y= ou have to be careful to keep the data consistent.


On =CE=A0=CE=B1=CF=81=CE=B1=CF=83=CE=BA=CE=B5=CF=85=CE=AE, 23 =CE=9C=CE= =B1=CE=90=CE=BF=CF=85 2025 10:13:10 =CE=A0.=CE=9C. 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



--nextPart5016410.31r3eYUQgx-- From laurenz.albe@cybertec.at Fri May 23 09:46:48 2025 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uIOzO-003s8L-0m for pgsql-admin@arkaria.postgresql.org; Fri, 23 May 2025 09:46:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uIOzM-000ccq-M9 for pgsql-admin@arkaria.postgresql.org; Fri, 23 May 2025 09:46:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uIOzM-000cca-AB for pgsql-admin@lists.postgresql.org; Fri, 23 May 2025 09:46:52 +0000 Received: from mail-ed1-x531.google.com ([2a00:1450:4864:20::531]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uIOzJ-000Vh7-2T for pgsql-admin@lists.postgresql.org; Fri, 23 May 2025 09:46:51 +0000 Received: by mail-ed1-x531.google.com with SMTP id 4fb4d7f45d1cf-5fff52493e0so10677861a12.3 for ; Fri, 23 May 2025 02:46:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec.at; s=google; t=1747993609; x=1748598409; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=0GZn7SrW6Wm94xlsNg9EZ8lYICWRH9cNoEfUhWcu7t8=; b=sGLyyGyPQkzN/WuM9jXtvnAzXo5dGmk1+D2+5gW64vPoIAPHy75naKG/oOh51c2wJa 7FEu9plb1tUu8IthZAwzySk4d5m82iGeU8jR+rCYxlA3e/meFrsiplmxGO15umsmmkkw P43tkdYnBiXX90UZKXI3k/qAaeeVvDsacqVtQuGhQvKBKDcFi1T3VhZr5ysQMXj77ezt wfttvAVZs8c03ELm6W6dojTmStNdOnaZdh0V11Il7lS8p3GUdOdI8vMkj3CciSl6Yo5G OhKtl3v6Z8zyX+09glG3i25qFtiPzGNgN/aVMB6wjV6K5amntzkCQpm5vTNs83rurRdE qy/w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1747993609; x=1748598409; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=0GZn7SrW6Wm94xlsNg9EZ8lYICWRH9cNoEfUhWcu7t8=; b=ROgMs9ruexaAex+xdJW9ttBPYfHweGPdVJP0f3ZMj+L6EfIUu6T2KLWulMFpY/LUOU qYXmNUTfR5A6NOpp3eVjLa8ZvStzDm8udnczsQokAvKmmuB8JgZobRqDVjLNM3a1WsM8 OIMtmGNCNF0YQhHfMWA3Wnxr9YjGG6BsB9vRSLUJ9WsF8IreLdbtxc5YvDpeH5krzevJ gXz5VW/HhcEkeyRcG/gl0tZ7HW/Kbog9Yj2lxD28mmD4HPDAZ2BNm7UynSpQo2ZrQtYv 9llmePSzsHLlsViGtwSdJimXJ5rU/AGvApaLAuL2lZ9TRUTEooY/ak4Oe2Z/POKeBY24 M5Bg== X-Forwarded-Encrypted: i=1; AJvYcCWOxaLRvG2Q4VNiFjqkR3astXXbEk9XWNIvdVR5QFNlUuMqabfBmxrrFxzUC2UkqP3+QDU2sKm0pmTWHA==@lists.postgresql.org X-Gm-Message-State: AOJu0YwGuxQlNrWg0tolyI4KCaI7L3txanzpKbWbLwIjVUoiy3WEpv2i l3ybLsXI0TbeX8wcIGC1kuJV0aEJ/J3dXP9uYURKmhr8VLFCHi/RK38U8rxV1AraSt7LExB7amW aApcp X-Gm-Gg: ASbGnctlQoUguL2+L4gLzBEwLFz11L9ti3+IfLV3mRjNV6aIuWdhaHciUhXUQcTIkKB VEI1Je+FY2xpgUD7xjU85sP5Ca4ZUUOI3zHvFXy8LtJtbfQkmjVjQyFvxxnuq/aGnQrq8ubeybf Dow6Y3iptkU+VA15Xn/E03TyrTe2xX14oO+nmU5iuKF7tu2FZm5cmTS8iinr8LY3bF3Zx07eE4a dZPTub5SVxzfpFjKSaPjmx7skGPpZ5CyrqUDBXCc65Emj07ch44Pcb2TahdD7lE+4ZzU7CHCPCp XA/lQ7cXct84jnecf62KhHTsUzZL5DNHdhuXBLwU29cncHjjbYY/hnmN5na/prvMVCULCip8bJW gqIBS X-Google-Smtp-Source: AGHT+IFvw9Ehpyg1liBvXbLHAVpwShoo0j5IWNMp8xaozTjL+vcvivolZkahD9PjoRb/seIsVIxt2Q== X-Received: by 2002:a50:d00a:0:b0:601:7a58:4b0f with SMTP id 4fb4d7f45d1cf-6017a584ef5mr17724886a12.18.1747993609060; Fri, 23 May 2025 02:46:49 -0700 (PDT) Received: from laurenz.albe-K4N0CV00F97414D ([2001:871:5e:6c9b:73f5:6897:6da1:73ea]) by smtp.gmail.com with ESMTPSA id 4fb4d7f45d1cf-6005a6e745fsm11942529a12.48.2025.05.23.02.46.48 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 23 May 2025 02:46:48 -0700 (PDT) Message-ID: <0903948da238870688d6a52bab616319947231c4.camel@cybertec.at> Subject: Re: Replication lag From: Laurenz Albe To: Wasim Devale , pgsql-admin , Pgsql-admin Date: Fri, 23 May 2025 11:46:48 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.56.1 (3.56.1-1.fc42) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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