public inbox for [email protected]
help / color / mirror / Atom feedFrom: Muhammad Ikram <[email protected]>
To: Shaheed Haque <[email protected]>
Cc: 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: Mon, 2 Sep 2024 11:08:20 +0500
Message-ID: <CAGeimVrMDJnxZaQj9ia2SJ3pOpsvreeb+s3j9qXukoGuncH9Zw@mail.gmail.com> (raw)
In-Reply-To: <CAHAc2je=cCww1xNmZ1TpZG6vwOxut8htJ7NdWotGjrHGuj8ELg@mail.gmail.com>
References: <BN9PR03MB59965B5087688309C0D79DCEB7962@BN9PR03MB5996.namprd03.prod.outlook.com>
<CAHAc2je=cCww1xNmZ1TpZG6vwOxut8htJ7NdWotGjrHGuj8ELg@mail.gmail.com>
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 deleted
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.
Regards,
Muhammad Ikram
On Sun, Sep 1, 2024 at 9:22 PM Shaheed Haque <[email protected]> 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%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
>>
>
--
Muhammad Ikram
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], [email protected]
Subject: Re: Postgres Logical Replication - how to see what subscriber is doing with received data?
In-Reply-To: <CAGeimVrMDJnxZaQj9ia2SJ3pOpsvreeb+s3j9qXukoGuncH9Zw@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