Cascaded Replication using pglogical on different version of PostgreSQL

I am going to show you how to configure cascaded pglogical replication on a different version of PostgreSQL.

I have installed three version of PostgreSQL (i.e 9.5,9.6,10)
Les install pglogical for all the version of PostgreSQL.

I did set up on CENTOS 7.2.

Here is the link to Download and install pglogical:

yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-3.noarch.rpm

PostgreSQL 9.5: yum install postgresql95-pglogical

PostgreSQL 9.6: yum install postgresql96-pglogical

PostgreSQL 10 : yum install postgresql10-pglogical

Here  PostgreSQL 9.5 is on 5432 port,9.6 is on 5433,port,10 is on 5434 port.
Add below parameters in postgresql.conf for all versions of PostgreSQL and add replication entry for servers(I am doing it on Local).

postgresql.conf

wal_level = 'logical'
max_worker_processes = 10   # one per database needed on provider node
                            # one per node needed on subscriber node
max_replication_slots = 10  # one per node needed on provider node
max_wal_senders = 10        # one per node needed on provider node
shared_preload_libraries = 'pglogical'

pg_hba.conf

local   replication     postgres                                trust
host    replication     postgres        127.0.0.1/32            trust

Now Let's create a pglogical database and in it create a pglogical extension on all versions.

create database pglogical;
\c pglogical
create extension pglogical;

5432 port will be the acting as provider node to 5433.

pglogical:provider :5432

select pglogical.create_node 
( 
    node_name := 'provider', 
    dsn := 'host=127.0.0.1 port=5432 dbname=pglogical' 
); 


ex: 

pglogical=# select pglogical.create_node 
pglogical-# ( 
pglogical(#     node_name := 'provider', 
pglogical(#     dsn := 'host=127.0.0.1 port=5432 dbname=pglogical' 
pglogical(# ); 
 create_node 
-------------
  3171898924
(1 row)

Now add the all tables from public schema to replication set. If you have sequences then you can add those as well to the same replication set.
Note: there should be pk on tables otherwise it won't allow the user to create replication set.

select pglogical.replication_set_add_all_tables('default', array['public']); 

ex:

pglogical=# select pglogical.replication_set_add_all_tables('default', array['public']); 
 replication_set_add_all_tables 
--------------------------------
 t
(1 row)

pglogical:subscriber and provider:5433

select pglogical.create_node( 
    node_name := 'subscriber', 
    dsn := 'host=127.0.0.1 port=5433 dbname=pglogical' 
); 

ex:

pglogical=# select pglogical.create_node( 
pglogical(#     node_name := 'subscriber', 
pglogical(#     dsn := 'host=127.0.0.1 port=5433 dbname=pglogical' 
pglogical(# ); 
 create_node 
-------------
  2941155235
(1 row)

Here on 5433, you have to create replication set same as 5432 because 5433 is also acting as a provider to 5434.

select pglogical.replication_set_add_all_tables('default', array['public']);

ex: 

pglogical=# select pglogical.replication_set_add_all_tables('default', array['public']); 

 replication_set_add_all_tables 

--------------------------------

 t

(1 row)

As 5433 is subscriber there should be subscription between 5432 and 5433. 

SELECT pglogical.create_subscription(

    subscription_name := 'subscription',

    provider_dsn := 'host=127.0.0.1 port=5432 dbname=pglogical');

ex:

 pglogical=# SELECT pglogical.create_subscription(

    subscription_name := 'subscription',

    provider_dsn := 'host=127.0.0.1 port=5432 dbname=pglogical');

 create_subscription 

---------------------

          2875150205

(1 row)

Lets check the data in 5432,5433,5434 .

On 5432: 

pglogical=# show port;

 port 

------

 5432

(1 row)

pglogical=# insert into test1 values (generate_series(1,15),'cascade');

INSERT 0 15

pglogical=# 

pglogical=# 

pglogical=# select count(*) from test1 ;

 count 

-------

    15

(1 row)

On 5433 :

pglogical=# show port;

 port 

------

 5433

(1 row)

pglogical=# select count(*) from test1 ;

 count 

-------

    15

(1 row)

pglogical=# 

On 5434:

pglogical=# show port;

 port 

------

 5434

(1 row)

pglogical=# select count(*) from test1 ;

 count 

-------

     0

(1 row)

On 5434 no data because of replication not yet setup between 5433 and 5434.

5434 port will be the acting as a subscriber to 5433.

pglogical:only subscriber:5434

select pglogical.create_node( 

    node_name := 'subscriber1', 

    dsn := 'host=127.0.0.1 port=5434 dbname=pglogical' 

); 

ex: 

pglogical=# select pglogical.create_node( 

pglogical(#     node_name := 'subscriber1', 

pglogical(#     dsn := 'host=127.0.0.1 port=5434 dbname=pglogical' 

pglogical(# ); 

 create_node 

-------------

   330520249

(1 row)

SELECT pglogical.create_subscription(

    subscription_name := 'subscription2',

    provider_dsn := 'host=127.0.0.1 port=5433 dbname=pglogical');

ex: 

pglogical=# SELECT pglogical.create_subscription(

    subscription_name := 'subscription2',

    provider_dsn := 'host=127.0.0.1 port=5433 dbname=pglogical');

 create_subscription 

---------------------

          1871150101

(1 row)

Now let's check the count on 5434 whether 15 records got replicated or not?

On 5434 :

pglogical=# show port ;

 port 

------

 5434

(1 row)

pglogical=# select count(*) from test1 ;

 count 

-------

    15

(1 row)

There are various options in pglogical like you can do a table, column, row level replication.