pglogical on PostgreSQL12( logical replication )

pglogical is a logical replication system implemented entirely as a PostgreSQL extension. Fully integrated, it requires no triggers or external programs. Logical Replication extension for PostgreSQL 12, 11, 10, 9.6, 9.5, 9.4 (Postgres), providing much faster replication than Slony, Bucardo or Londiste, as well as cross-version upgrades.
In my example to configure pglogical I used two systems 192.168.1.93 for provider(publish) and 192.168.1.120 for subscriber (subscribe).Both systems are on Centos7.

Step 1. Install PostgreSQL 

I assume that on both systems PostgreSQL12 has already installed. If not installed use below link
[root@pc1 ~]#

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latestpglogical.noarch.rpm

[root@pc1 ~]#yum install postgresql12-server postgresql12-contrib
[root@pc1 ~]#/usr/pgsql-12/bin/postgresql-12-setup initdb
[root@pc1 ~]#systemctl enable postgresql-12
[root@pc1 ~]#systemctl start postgresql-12 
Step 2.Then install the “2ndQuadrant’s General Public” repository for your PostgreSQL version, by running the
following instructions as root on the destination Linux server:
[root@pc1 ~]#curl https://access.2ndquadrant.com/api/repository/dl/default/release/12/rpm
| bash
[root@pc1 ~]# yum install postgresql12-pglogical

Step 3. Open postgresql.conf file and edit below parameters

[root@pc1 ~]#cd /var/lib/pgsql/12/data

root@pc1 data]# vi 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'

Save and exit
from postgresql.conf file

[root@pc1 data]# systemctl restart postgresql-12.service

[root@pc1 data]# systemctl status postgresql-12.service
pg_hba.conf has to allow replication
connections from localhost.

Step 4. Next the pglogical extension
has to be installed on all nodes(192.168.1.93 and 192.168.1.120):

[root@pc1 ~]#su Postgres [Hint :You can use
pgAdmin tool for it]

bash-4.2$psql

Postgres=#

CREATE EXTENSION pglogical;

To check run
this

Postgres=# \dx+ pglogicalll

Objects in extension "pglogical"
Object description
---------------------------------------------------------------

 function
pglogical.alter_node_add_interface(name,name,text)

 function
pglogical.alter_node_drop_interface(name,name)

 function
pglogical.alter_replication_set(name,boolean,boolean,boolean,boolean)

 function
pglogical.alter_subscription_add_replication_set(name,name)

 .

 .

table pglogical.sequence_state

 table
pglogical.subscription

 view
pglogical.tables
(51 rows)
Step 5. Now create a table testpglogical on both nodes for testing purpose .
CREATE TABLE testpglogical (empno serial primary key, empname text);
Now create the provider node(On 192.168.1.93):
SELECT pglogical.create_node(
node_name := 'provider1',
            dsn := 'host=192.168.1.93 port=5432 dbname=postgres'
);
select pglogical.create_replication_set('replication_set');
select pglogical.replication_set_add_table(
                                           set_name := 'replication_set',
                                           relation := 'testpglogical',
                                           synchronize_data := true
                              );

[ If we want to Add all tables in public schema to the default replication set.

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

Optionally you can also create additional replication sets and add tables to them (see Replication sets)It's usually better to create replication sets before subscribing so that all tables are synchronized during initial replication setup in a single initial transaction. However, users of bigger databases may instead wish to create them incrementally for better control.

https://github.com/2ndQuadrant/pglogical#replication-sets

Step 6. On subscriber Node (On192.168.1.120) execute below scripts:

[root@pc1 ~]# cd /var/lib/pgsql/

vi .pgpass

*:*:*:postgres:welcome   [Hint : welcome is password of user postgres]

Save and exit

[root@pc1 ~]# su Postgres [Hint : You can use pgAdmin tool for it]

bash-4.2$psql

Postgres=#

Create table testpglogical (empno serial primary key, empname text);

Now create the subscriber node(On 192.168.1.120):

select pglogical.create_node(

  node_name := 'subscriber',

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

);

select pglogical.create_subscription(

  subscription_name := 'subscription',

  replication_sets := array['replication_set'],

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

);

Now your pglogical has configured.

Whatever you will do with table testpglogical on provider node(192.168.1.93) it will reflecte in table testpglogical on subscriber node(192.168.1.120).

e.g.

On provider node(192.168.1.93)

INSERT INTO testpglogical VALUES (1,'SAGAR'),(2,'VISHWAJEET'),(3,'DINESH');

On subscriber node(192.168.1.120)

SELECT *  FROM testpglogical;

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. CHECK constraints, 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 (see below).

To explore more on it use below links:

Use cases supported are:

• Upgrades between major versions (given the above restrictions)
• Full database replication
• Selective replication of sets of tables using replication sets
• Selective replication of table rows at either publisher or subscriber side (row_filter)
• Selective replication of table columns at publisher side
• Data gather/merge from multiple upstream servers

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 change set forwarding.

To explore more on it go through below links:
https://github.com/2ndQuadrant/pglogical

https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/

https://github.com/2ndQuadrant/pglogical#replication-sets