Pglogical Replication Status Down

Question

I am using pglogical for logical replication in PostgreSQL(CloudSQL GCP) and I found my subscription down thus data is not replicating at all from source table to target table..

I checked logs on Publisher Database side

background worker "logical replication launcher" (PID 388) exited with exit code 1"

on Subscriber Database side

2023-07-13 10:50:19.450 UTC [6]: [223-1] db=,user= LOG:  worker process: pglogical apply 79870:9787070 (PID 7987) exited with exit code 1

I tried removing subscription and recreating it again but status started with initialising but became down later.

However I had error while removing subscription, not sure if it is serious.

could not drop slot "pgl_development_provider_consent_sub" on provider, you will probably have to drop it manually

How can I remove the lag between subscriber and publisher and subscription status to replicating ?

Answer 1

Solution

• Drop the extension pglogical
• Create pglogical configuration for your source and target tables.

Additional Note

Last step will start replication fine for new entries but lag might still be there.

So to ensure both the source and target tables are in sync, run this below command

SELECT pglogical.alter_subscription_resynchronize_table('${SUBSCRIPTION_NAME}', '${SCHEMA_NAME}.${TABLE_NAME}');

In a matter of few minutes both the tables have data in sync.