How to change subscription status from down to replicating

Question

I am trying to replicate postgresql database from provider node to subscriber node. I have followed tutorial from http://bonesmoses.org/2016/10/14/pg-phriday-perfectly-logical/ and created provider node and added a table to replication set and similarly created subscriber to subscribe to this table and subscription is working fine for this. I added few more tables after that which are part of different database but on same postgres instance and have added these tables to new replication set but for this set replication is not working and subscriber is coming as down for this set. How can I make this subscription as replicating for the replication sets I have added later.

Below are the commands I have run on provider node:

Step 1 : Ran the pg_dump file to import the DDL commands

psql -U postgres -d databasename1 < exportDDL.pgsql

Step 2: Created the provider node

SELECT pglogical.create_node(
node_name := 'db_provider',
dsn := 'host=docker-container-ip port=5432 dbname=databasename1'

);

Step 3: Created replication state

SELECT pglogical.create_replication_set(
set_name := 'qrtz',
replicate_insert := TRUE, replicate_update := TRUE,
replicate_delete := TRUE, replicate_truncate := TRUE);

Step 4: Added three tables to this set

SELECT pglogical.replication_set_add_table(
set_name := 'qrtz', relation := ‘qrtz_logs', 
synchronize_data := TRUE);

SELECT pglogical.replication_set_add_table(
set_name := 'qrtz', relation := ‘qrtz_errors', 
synchronize_data := TRUE);

SELECT pglogical.replication_set_add_table(
set_name := 'qrtz', relation := ‘qrtz_calendars', 
synchronize_data := TRUE);

Subscriber node:

Step 1: Ran DDL commands using pg_dump file

psql -U postgres -d postgres < exportDDL.pgsql

Step 2: Created subscriber node

SELECT pglogical.create_node(
node_name := 'db_subscriber',
dsn := 'host=docker-container-ip port=5432 dbname=postgres');

Step 3: Created subscription

SELECT pglogical.create_subscription(subscription_name := 'qrtz_data’,  
replication_sets := array['qrtz'],
 provider_dsn := 'host=provider-docker-container-ip port=5432 
 dbname=databasename1');

Step 4: checked status by using

Select * from pglogical.show_subscription_status

It showed status as down for subscription qrtz_data

Answer 1

Down means the subscription did not succeed: there is usually some problem. Check Postgres logs for the errors. Once you resolve the errors, re-create the subscription by dropping and creating it again. Check the status. If down again, check error log again. Keep iterating until the status is initializing and then replicating.

Answer 2

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

Root cause

It is a very weird state and not sure how to overcome it and understand the root cause but I've managed to resolve this with following steps.

For us, reason was shutting down source and target databases and that was somehow resulting into this out of sync status.

Note : If your subscription status is replicating and source/target are out of sync then run the command mentioned in #additional note only and no need to apply resolution.

Resolution

If your subscription status is down

• Drop pglogical extension
• Create pglogical configuration for your publisher and subscriber databases again.

Additional Note

Last step will put subscription back in replicating state 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.