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 1sl75q-005ILp-Lw for pgsql-general@arkaria.postgresql.org; Mon, 02 Sep 2024 13:27:43 +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 1sl75p-00EEyU-RV for pgsql-general@arkaria.postgresql.org; Mon, 02 Sep 2024 13:27:42 +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 1sl75p-00EEyL-8K for pgsql-general@lists.postgresql.org; Mon, 02 Sep 2024 13:27:41 +0000 Received: from mail-vk1-xa2f.google.com ([2607:f8b0:4864:20::a2f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sl75l-000IG7-Me for pgsql-general@postgresql.org; Mon, 02 Sep 2024 13:27:40 +0000 Received: by mail-vk1-xa2f.google.com with SMTP id 71dfb90a1353d-4fcfd6b870aso1508334e0c.0 for ; Mon, 02 Sep 2024 06:27:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725283657; x=1725888457; darn=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=rxO+6JX1jGBpBwUJAoEW7H2tpmT8qwyn3qdTg/8YA3s=; b=hJ5bVf37FEJlzVM5FDHXHS6W45LW8EZF9+Jn5kNL4cT6gN6+Vzh34RB48SiP6yStut OeF22fc43Ldn+yHJBi3Y2wy4NueCHpXq7KdYZO9ftAt1fd7mPjEPhPEmfMLoHD4CnBRU 3NqsqJkhccvXo4JNdy+vggKhkyeX23wgQjzihuZP5n+Idx8XzygFmv9dlrgryT4rAr/m TmABa3bKpX+VK3LIRkuGbxxh01+Nom2CK0kGlaRUXXnhF0owLVCSkc3h0ByjjAL7ccW0 vnmeFWrGbdos9ZFCcBITVvYba0rfWXmoOTty77JVpHMuwCRRV2YUhTSrEt87eSksGs2h cWVg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725283657; x=1725888457; 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=rxO+6JX1jGBpBwUJAoEW7H2tpmT8qwyn3qdTg/8YA3s=; b=GdZ1Sq5OzZIF+2c3JCp2MbEN3dr+4HtsAAs0Y98dPvDDv0RDr233enp8zEr56GuIg4 1Jrf/QBffvFEAZcjCSo7xh3g7yAAGG0u7b02FBgSGIFvpvd6w4m16hIZK+xFXiPzbh+n I+fV4R+1YXKGBrCFG8MW7r9xX/G+s+Uc3qaiNOmwfoKDImSfhdv96GKC+Qe8jXE97aBp KpVWwNdHKGhBI/6A9pW1WmZN3GQn/z0HnJLDABncJkGL0eWbtQ1OmIgOm8BzJqu+lCsT mho0sacvjqWvOzm4lKm9IpSg/Obq6KWojVUKGxusuvwIgstg613r3Yyr8lRj97qvRrPt dGVw== X-Forwarded-Encrypted: i=1; AJvYcCUe0h20l/u6cpuT1DJIw+s0Hr4wa40McNsnQGc9znVpjS2GMYpyy4gbfSdb9ZYbQ9fJ3eeU5IcJGq8AR8tu@postgresql.org X-Gm-Message-State: AOJu0YxuAVw3/ubiGq94cFi9Z/CiGoImjmUexpmM0wZpq5BDOm3YuSmh vRNCHnVJ6rbClzPnzGd7BhE/T2VCEfF9t+VEszj1ozR0jRrUBV41HWhl+qp/Y6uv5ocRGzPnPwj 0+m/KJZ1HcLHimZdXpu7DFlaGxWc= X-Google-Smtp-Source: AGHT+IH9+rfUYA1Nz005MsNHbXjuAqDds6bl1rhDWI6QczK0QJVDoMr7prGdw8lOTSG9eKi0lzciv+MC6lJiyMoedQQ= X-Received: by 2002:a05:6122:222a:b0:4f5:199b:2a61 with SMTP id 71dfb90a1353d-500aadf45abmr5766887e0c.9.1725283656697; Mon, 02 Sep 2024 06:27:36 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Shaheed Haque Date: Mon, 2 Sep 2024 14:27:24 +0100 Message-ID: Subject: Re: Postgres Logical Replication - how to see what subscriber is doing with received data? To: Muhammad Ikram Cc: Michael Jaskiewicz , pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="0000000000007e8f3b062122e776" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007e8f3b062122e776 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Muhammad, On Mon, 2 Sep 2024, 09:45 Muhammad Ikram, wrote: > Hi Shaheed, > I think you must have already analyzed the outcome of queries > on pg_replication_slots, pg_current_wal_lsn(), pg_stat_subscription etc.= I > could find a query SELECT > pg_size_pretty(pg_wal_lsn_diff('', > '')); > Yes. My point is that it is hard to go from byte numbers to table entries. Aps a side note if you want to see what has been applied to subscribers vs > what exists on publisher then here is something from my previous > experience. We used to have a Data Validation tool for checking tables/ro= ws > across publisher/subscriber. > Ack. That's pretty much what I had to build. We also used pg_dump for another tool that was meant for making copies of > schemas. > I'm somewhat fortunate to have a simple use case where all I am doing is a copy of the "old" deployment to a "new" deployment such that when the two ends are in close sync, I can freeze traffic to the old deployment, pause for any final catchup, and then run a Django migration on the new, before switching on the new (thereby minimising the down time for the app). What I found by just looking at LSN numbers was that the database LSN were close but NOT the same. Once I built the tool, I was able to see which tables were still in play, and saw that some previously overlooked background timers were expiring, causing the activity. Net result: the LSNs can tell you if you are not in sync, but not the reason why. (Again, I understand that row counts worked for me, but might not work for others). Thanks for your kind help and pointers! Regards, > Muhammad Ikram > > > > On Mon, Sep 2, 2024 at 12:42=E2=80=AFPM Shaheed Haque > wrote: > >> Hi Muhammad, >> >> On Mon, 2 Sep 2024, 07:08 Muhammad Ikram, wrote: >> >>> Hi Shaheed, >>> >>> Maybe these considerations could help you or give any hint to the >>> problem ? >>> >>> >>> Check if wal_receiver_timeout being set to 0 could potentially cause >>> issues, like not detecting network issues quickly enough. Consider >>> re-evaluating this setting if you see connection issues. >>> >>> If you notice that some data is missing on subscriber then could you >>> increase max_slot_wal_keep_size on publisher so that WALs are not delet= ed >>> until they are applied on subscriber. >>> >>> Do you have flexibility to increase max_worker_processes and >>> max_logical_replication_workers, work_mem and maintenance_work_mem on >>> subscriber (In case bottleneck exists on subscriber) >>> >>> If there's significant lag, consider whether it might be more efficient >>> to drop the subscription and re-initialize it from scratch using a new = base >>> backup, depending on the data volume and how long it might take for the >>> existing replication to catch up. >>> >> >> Thanks for the kind hints, I'll certainly look into those. >> >> My main interest however was with the "visibility" question, i.e. to get >> an understanding of the gap between the two ends of a replication slot, >> ideally in human terms (e.g. tables x records). >> >> I understand the difficulties of trying to produce a meaningful metric >> that spans two (or more) systems but let's be honest, trying to diagnose >> which knobs to tweak (whether in application, PG, the OS or the network)= is >> basically black magic when all we really have is a pair of opaque LSNs. >> >> >> >> >>> >>> Regards, >>> Muhammad Ikram >>> >>> >>> On Sun, Sep 1, 2024 at 9:22=E2=80=AFPM Shaheed Haque >>> wrote: >>> >>>> Since nobody more knowledgeable has replied... >>>> >>>> I'm very interested in this area and still surprised that there is no >>>> official/convenient/standard way to approach this (see >>>> https://www.postgresql.org/message-id/CAHAc2jdAHvp7tFZBP37awcth%3DT3h5= WXCN9KjZOvuTNJaAAC_hg%40mail.gmail.com >>>> ). >>>> >>>> Based partly on that thread, I ended up with a script that connects to >>>> both ends of the replication, and basically loops while comparing the >>>> counts in each table. >>>> >>>> On Fri, 30 Aug 2024, 12:38 Michael Jaskiewicz, >>>> wrote: >>>> >>>>> I've got two Postgres 13 databases on AWS RDS. >>>>> >>>>> - One is a master, the other a slave using logical replication. >>>>> - Replication has fallen behind by about 350Gb. >>>>> - The slave was maxed out in terms of CPU for the past four days >>>>> because of some jobs that were ongoing so I'm not sure what logica= l >>>>> replication was able to replicate during that time. >>>>> - I killed those jobs and now CPU on the master and slave are both >>>>> low. >>>>> - I look at the subscriber via `select * from >>>>> pg_stat_subscription;` and see that latest_end_lsn is advancing al= beit very >>>>> slowly. >>>>> - The publisher says write/flush/replay lags are all 13 minutes >>>>> behind but it's been like that for most of the day. >>>>> - I see no errors in the logs on either the publisher or >>>>> subscriber outside of some simple SQL errors that users have been = making. >>>>> - CloudWatch reports low CPU utilization, low I/O, and low >>>>> network. >>>>> >>>>> >>>>> >>>>> Is there anything I can do here? Previously I set wal_receiver_timeou= t >>>>> timeout to 0 because I had replication issues, and that helped things= . I >>>>> wish I had *some* visibility here to get any kind of confidence that >>>>> it's going to pull through, but other than these lsn values and datab= ase >>>>> logs, I'm not sure what to check. >>>>> >>>>> >>>>> >>>>> Sincerely, >>>>> >>>>> mj >>>>> >>>> >>> >>> -- >>> Muhammad Ikram >>> >>> > > -- > Muhammad Ikram > > --0000000000007e8f3b062122e776 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Muhammad,=C2=A0

On Mon, 2 Sep 2024, 09:45 Muhammad I= kram, <mmikram@gmail.com> wrote:
Hi=C2=A0Shaheed,
I think you must have already = analyzed the outcome of queries on=C2=A0pg_replication_slots,=C2=A0 pg_curr= ent_wal_lsn(),=C2=A0pg_stat_subscription etc. I could find a query SELECT p= g_size_pretty(pg_wal_lsn_diff('<publisher_restart_lsn>', '= ;<subscriber_replayed_lsn>'));

Yes. My point is that it is= hard to go from byte numbers to table entries.=C2=A0

Aps a side note if you want to see what= has been applied to subscribers vs what exists on publisher then here is s= omething from my previous experience. We used to have a Data Validation too= l=C2=A0for checking tables/rows across publisher/subscriber.

Ack. T= hat's pretty much what I had to build.=C2=A0
We also used pg_dump for another tool that wa= s meant for making copies of schemas.

I'm somewhat fortunate= to have a simple use case where all I am doing is a copy of the "old&= quot; deployment to a "new" deployment such that when the two end= s are in close sync, I can freeze traffic to the old deployment, pause for = any final catchup, and then run a Django migration on the new, before switc= hing on the new (thereby minimising the down time for the app).=C2=A0
=

What I found by just looking = at LSN numbers was that the database LSN were close but NOT the same. Once = I built the tool, I was able to see which tables were still in play, and sa= w that some previously overlooked background timers were expiring, causing = the activity.

Net result= : the LSNs can tell you if you are not in sync, but not the reason why. (Ag= ain, I understand that row counts worked for me, but might not work for oth= ers).=C2=A0

Thanks for y= our kind help and pointers!=C2=A0


Regards,
Muhammad Ikram



On Mon, Sep 2, 2024 at = 12:42=E2=80=AFPM Shaheed Haque <shaheedhaque@gmail.com> wrote:
Hi Muhammad,=C2=A0

On Mon, 2 Sep 2024, 07:08 Muhammad Ikra= m, <mmikram@gmail.com> wrote:
Hi Shaheed,

Maybe these considerations could help you or give any hint to the pr= oblem ?


Check if wal_receiver_timeout being set to 0 could poten= tially cause issues, like not detecting network issues quickly enough. Cons= ider re-evaluating this setting if you see connection issues.

If you= notice that some data is missing on subscriber then could you increase max= _slot_wal_keep_size on publisher so that WALs are not deleted until they ar= e applied on subscriber.

Do you have flexibility to increase max_wor= ker_processes and max_logical_replication_workers, work_mem and maintenance= _work_mem on subscriber (In case bottleneck exists on subscriber)

If= there's significant lag, consider whether it might be more efficient t= o drop the subscription and re-initialize it from scratch using a new base = backup, depending on the data volume and how long it might take for the exi= sting replication to catch up.

Thanks for the kind hints, I'= ll certainly look into those.=C2=A0

My main interest however was with the "visibility" qu= estion, i.e. to get an understanding of the gap between the two ends of a r= eplication slot, ideally in human terms (e.g. tables x records).

I understand the difficulties of t= rying to produce a meaningful metric that spans two (or more) systems but l= et's be honest, trying to diagnose which knobs to tweak (whether in app= lication, PG, the OS or the network) is basically black magic when all we r= eally have is a pair of opaque LSNs.=C2=A0



=


=C2=A0Regards,
Muhammad Ikram=


On Sun, Sep 1, 2024 at 9:22=E2=80=AFPM Shaheed Haque &= lt;shaheedhaque@gmail.com> wrote:

Since nob= ody more knowledgeable has replied...

I'm very interested in this area and still surprised tha= t there is no official/convenient/standard way to approach this (see https://www.postgresql.org/message-id/CAHAc2jdAHvp7= tFZBP37awcth%3DT3h5WXCN9KjZOvuTNJaAAC_hg%40mail.gmail.com).

Based partly on that thread, I ended up with a script that c= onnects to both ends of the replication, and basically loops while comparin= g the counts in each table.


On Fri= , 30 Aug 2024, 12:38 Michael Jaskiewicz, <mjaskie= wicz@ghx.com> wrote:

I've got two Postgres 13 databases on AWS RDS.

  • One is a master, the other a slave using logical re= plication.
  • Replication has fallen= behind by about 350Gb.
  • The slave= was maxed out in terms of CPU for the past four days because of some jobs = that were ongoing so I'm not sure what logical replication was able to = replicate during that time.
  • I kil= led those jobs and now CPU on the master and slave are both low.<= /u>
  • I look at the subscriber via `select * from= pg_stat_subscription;` and see that latest_end_lsn is advancing albeit ver= y slowly.
  • The publisher says writ= e/flush/replay lags are all 13 minutes behind but it's been like that f= or most of the day.
  • I see no erro= rs in the logs on either the publisher or subscriber outside of some simple= SQL errors that users have been making.
  • CloudWatch reports low CPU utilization, low I/O, and low network.

=C2=A0

Is there anything I can do here? Previously I set wa= l_receiver_timeout timeout to 0 because I had replication issues, and that = helped things. I wish I had some visibility here to get any kind of confidence that it's goi= ng to pull through, but other than these lsn values and database logs, I= 9;m not sure what to check.

=C2=A0

Sincerely,

mj



--
Muhammad Ikram



--
Muhammad Ikram

--0000000000007e8f3b062122e776--