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

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.