Re: Postgres Logical Replication - how to see what subscriber is doing with received data?

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Shaheed Haque <[email protected]>
To: Michael Jaskiewicz <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Postgres Logical Replication - how to see what subscriber is doing with received data?
Date: Sun, 1 Sep 2024 17:22:01 +0100
Message-ID: <CAHAc2je=cCww1xNmZ1TpZG6vwOxut8htJ7NdWotGjrHGuj8ELg@mail.gmail.com> (raw)
In-Reply-To: <BN9PR03MB59965B5087688309C0D79DCEB7962@BN9PR03MB5996.namprd03.prod.outlook.com>
References: <BN9PR03MB59965B5087688309C0D79DCEB7962@BN9PR03MB5996.namprd03.prod.outlook.com>

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%3DT3h5WXCN9KjZOvuTNJaAAC_hg%40mail.g...
).

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, <[email protected]> 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 logical
>    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 albeit 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_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
> going to pull through, but other than these lsn values and database logs,
> I'm not sure what to check.
>
>
>
> Sincerely,
>
> mj
>

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Postgres Logical Replication - how to see what subscriber is doing with received data?
  In-Reply-To: <CAHAc2je=cCww1xNmZ1TpZG6vwOxut8htJ7NdWotGjrHGuj8ELg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox