How To Configure pglogical | streaming replication for PostgreSQL

The pglogical extension provides logical streaming replication for PostgreSQL

We use the following terms to describe data streams between nodes, deliberately reused from the earlier Slony technology:

• Nodes - PostgreSQL database instances

• Providers and Subscribers - roles taken by Nodes

• Replication Set - a collection of tables 

Architectural details:

• pglogical works on a per-database level, not whole server level like physical streaming replication

• One Provider may feed multiple Subscribers without incurring additional disk write overhead

• One Subscriber can merge changes from several origins and detect conflict between changes with automatic and configurable conflict resolution (some, but not all aspects required for multi-master).

• Cascading replication is implemented in the form of changeset forwarding.

Requirements :- 

• To use pglogical the provider and subscriber must be running PostgreSQL 9.4 or newer.
• The pglogical extension must be installed on both provider and subscriber. You must CREATE EXTENSION pglogical on both.
• Tables on the provider and subscriber must have the same names and be in the same schema. Future revisions may add mapping features.
• Tables on the provider and subscriber must have the same columns, with the same data types in each column. CHECKconstraints, NOT NULL constraints, etc must be the same or weaker (more permissive) on the subscriber than the provider.
• Tables must have the same PRIMARY KEYs. It is not recommended to add additional UNIQUE constraints other than the PRIMARY KEY .
• More limitation and restriction is covered here 

Installation of pglogical extension .

Download source code of pglogical extension from below link .

Link

Step - 1 

untar downloaded folder .

Step - 2 

I am configuring replication from 9.5 to 9.6 PostgreSQL community version .

Make sure you have configured correct environment variable .

go inside folder .

cd  pglogical-master

make USE_PGXS=1 install

Make below changes into postgresql.conf in both side .

wal_level = 'logical'

max_worker_processes = 10  

max_replication_slots = 10 

max_wal_senders = 10       

shared_preload_libraries = 'pglogical'

track_commit_timestamp = on 

pg_hba.conf has to allow replication connections

Create extension of both server .

create extension pglogical;

create extension pglogical_origin ;

  

Make sure you have created table structure on replica server as well .

Step 3 .Create Node and subscription .

On Primary/Master server .

A) 

SELECT pglogical.create_node(

node_name := 'provider1',

dsn := 'host=Replica/Subscriber_host port=5432 dbname=postgres'

);

B)Add all tables in `public` schema to the `default` replication set.

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

 

 

On Replica/Secondary server .

A)

create table employee(id int primary key ,name varchar(20));

B)

Once the provider node is setup, subscribers can be subscribed to it. First the

subscriber node must be created:

SELECT pglogical.create_node(

    node_name := 'subscriber1',

    dsn := 'host=Replica_host port=5432 dbname=postgres'

);

C) 

And finally on the subscriber node you can create the subscription which will

start synchronization and replication process in the background:

SELECT pglogical.create_subscription(

subscription_name := 'subscription1',

provider_dsn := 'host=master_host port=5432 dbname=postgres'

);

 

Step 4 

Lets insert some rows into table and check will that start syncing with secondary node .

I have already inserted 100 rows now i am inserting more 100 rows on primary server . 

Verify That on subscription/secondary server 

Step 5.Check which are backend process are running .

On Master server - 

 

On secondary Server -