[SOLVED] How to change subscription status from down to replicating

How to change subscription status from down to replicating

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

Solution

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.