RE: Logical replication fails when adding multiple replicas

On Monday, March 20, 2023 8:46 PM Will Roper <will(dot)roper(at)democracyclub(dot)org(dot)uk> wrote:

Hi,

> We’re having some issues with Postgresql’s logical replication. Specifically
> trying to add several replicas at once. Essentially we can add replicas one at
> a time, but when we try and add two or more together some of the table
> subscriptions (as described in pg_subscription_rel) fail to get to
> ‘STATE_READY’ (‘r’). Here’s a bit more detail.
>
> How the problem manifests/what we observe When we try to add several replicas
> at the same time most of them have at least some tables/subscriptions (in the
> pg_subscription_rel) that get stuck in STATE_FINISHEDCOPY and never reach
> STATE_READY. The tables have the right number of rows, but the logs show
> postgres is in a constant cycle of errors like:
>
> 2023-03-18 16:00:19.974 UTC [4207] LOG: logical replication table synchronization worker for subscription "polling_stations_01c7ee00f17021f94", table "uk_geo_utils_onspd" has started
> 2023-03-18 16:00:19.999 UTC [4207] ERROR: could not start WAL streaming: ERROR: replication slot "pg_37982_sync_37918_7210774007126708177" does not exist

Are there some other kind of ERROR messages on subscriber before the first "slot ... does
not exist" ? I think it's possible that some other ERROR happened when first time
setting the state to READY, and then the table sync keeps retrying and failing.

Best Regards,
Hou zj