Re: Logical replication stalling for large tables with heavy write activity - Pg11

Thanks for checking and responding back, after detailed investigation of
the logs found that logical replication was timing out on initial sync of
large tables and initial copy of the tables would just restart.

So I changed parameters - max-standby_streaming_delay and
max_standby_archive_delay and then the issue no longer happens.

Thanks & Regards,
Mukesh Chhatani

On Mon, Aug 24, 2020 at 9:10 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Sun, Aug 16, 2020 at 3:48 AM Mukesh Chhatani
> <chhatani(dot)mukesh(at)gmail(dot)com> wrote:
> >
> > Hello Team,
> >
> > I am working on upgrading Postgres at my current organization using
> logical replication.
> >
> > In most of the cases Logical replication works fine, but in below
> scenario it just stalls for more than 24 hours
> >
> > Primary DB - Pg10.11
> > Table Size - 90 GB including 20 GB of indexes
> >
> > Secondary - Pg11.6
> >
> > After few hours table is still being copied in the pg_stat_replication
> and plg_replication_slot I can replication is live currently and table size
> on the secondary is increasing,
> >
>
> When you say replication is live, what do you mean by that? Isn't it
> expected for a heavy-write table because you are updating the table on
> the primary so secondary should also be getting the WAL corresponding
> to it?
>
> > When I check the table size on the primary using
> pg_size_pretty(pg_total_relation_size('test')), it shows 90 GB but on the
> secondary where replication is happening it shows more than 150GB, and
> still replication is in progress with no errors in the logs.
> >
> > This problem does not happen with tables which have less write activity
> (updates/inserts).
> >
>
> One way this could happen is if on the new server autovacuum is not
> able to clean the table with heavy write activity. Are settings of
> autovacuum on both servers the same?
>
> --
> With Regards,
> Amit Kapila.
>