Pglogical Replication Status Down

Question

I am using pglogical for logical replication in PostgreSQL cloudsql 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

If you were using EDB Enterprise Postgres you could use all the useful views like pglogical.worker_error here https://www.enterprisedb.com/docs/pgd/3.7/pglogical/troubleshooting/. As it is to get anything remotely helpful out the logs you have to observe the Postgres server log after setting the log_min_error_statement parameter to debug1 one perhaps even lower see https://postgresqlco.nf/doc/en/param/log_min_error_statement/ . What you might find more useful however is the below list of usual causes of this.

1. Check for overlapping tables present in multiple replica sets used by active subscriptions.
2. Check that the target table to be replicated to is empty / truncated
3. Make sure that the DDL for all table is precreated, did some get unwittingly removed during a DROP TABLE CASCADE operation maybe?
4. Make sure replica identity is not set on the table it should be default and will cause errors if set to full (on target & source).
5. Make sure the network access between the source and target hasn't been disabled since the replication started.

I appreciate 2 months after maybe too late for the original poster but I think many people using pglogical will encounter such issues.