PostgreSQL Logical Replication

What is Logical Replication ?

Before introducing Logical replication, PostgreSQL replication works by shipping WAL files to standby server which applies the changes. In traditional replication mechanism standby is copy of all data or database (cluster).

PostgreSQL 10 published with support of Logical Replication. One of the uses of logical replication is to allow low- or no-downtime upgrading between PostgreSQL major versions. (Logical replication can also be used for moving data between instances on different operating systems or CPU architectures or with different low-level configuration settings such as block size or locale side grading if you will.)

The advantage of using logical replication to upgrade is that the application can continue to run against the old instance while the data synchronisation happens. There only needs to be a small outage while the client connections are switched over. 

Logical replication uses PUBLICATION on Master & SUBSCRIPTION on Standby server.

When Logical Replication is useful ?

• If you want to replicate data between different major versions of PostgreSQL.
• If you want to consolidate multiple databases into a single database for analytical purposes.
• If you want to send incremental changes in a single database.
• If you want different security access to replicated data to different groups of users.

Features Of Logical Replication

• Logical replication provides you flexibility to copy particular table / group of table / database to standby server.
• Logical Replication has cross-version support.
• Destination server can be used for writes.You can have different indexes and security definition.
• Logical Replication replicates data objects based upon their replication identity (generally a primary key or unique index).
• Logical Replication can be used for database migrations and upgrades.
• Publications can have several subscriptions.Allows parallel streaming across publishers

Let's setup Logical Replication

1. Configure setting on Primary instance for logical replication as below in postgresql.conf.

wal_level=logical               # Changes Require Restart
max_replication_slots = 10      # Must be set to at least the number of subscriptions expected to connect, plus some reserve for table synchronisation. 
max_wal_senders = 10            # Should be set to at least the same as max_replication_slots, plus the number of physical replicas that are connected at the same time.

2. Create test data and PUBLICATION on Primary Server

CREATE ROLE repuser REPLICATION LOGIN PASSWORD 'Password';

CREATE DATABASE customer;
\c customer
CREATE TABLE users(id serial primary key,name varchar(10), age int);

INSERT INTO users (id, name, age) VALUES (1, 'Chetan', 29);
INSERT INTO users (id, name, age) VALUES (2, 'Dev', 50);
INSERT INTO users (id, name, age) VALUES (3, 'Aryan', 59);
INSERT INTO users (id, name, age) VALUES (4, 'Rose', 50);
INSERT INTO users (id, name, age) VALUES (5, 'Jimmy', 29);
INSERT INTO users (id, name, age) VALUES (6, 'Adam', 40);
GRANT SELECT ON users TO repuser;

CREATE DATABASE login;
\c login
CREATE TABLE user_login(id serial primary key,cust_id int,password varchar(20));

INSERT INTO user_login (cust_id, password) VALUES (1, 'Chetan@123');
INSERT INTO user_login (cust_id, password) VALUES (2, 'Dev@123');
INSERT INTO user_login (cust_id, password) VALUES (3, 'Aryan@123');
INSERT INTO user_login (cust_id, password) VALUES (4, 'Rose@123');
INSERT INTO user_login (cust_id, password) VALUES (5, 'Jimmy@123');
INSERT INTO user_login (cust_id, password) VALUES (6, 'Adam@123');

GRANT SELECT ON user_login TO repuser;
CREATE PUBLICATION source FOR ALL TABLES;

NOTE : We have created 2 different databases customer & login , and separate tables in each database. I will show you how we can consolidate tables from different databases into single database.

3. Add user into  pg_hba.conf file on Primary server.

host    all          repuser             127.0.0.1/32            md5

4.  Make following changes on Standby/Secondary server in postgresql.conf.

max_replication_slots                   # set to at least the number of subscriptions that will be added to the subscriber. 
max_logical_replication_workers         # set to at least the number of subscriptions, again plus some reserve for the table synchronisation
max_worker_processes                    # set it to at least (max_logical_replication_workers + 1)

5. Create Schema like Primary on Standby server and SUBSCRIPTION.

# Creating Database which will consolidate tables from different databases
CREATE DATABASE customer_details;

# Create Schema
CREATE TABLE users(id serial primary key,name varchar(10), age int);
CREATE TABLE user_login(id serial primary key,cust_id int,password varchar(20));

# Creating SUBSCRIPTION for each database
CREATE SUBSCRIPTION customer_s CONNECTION 'dbname=customer host=127.0.0.1 user=repuser password=Password port=5432' PUBLICATION source;
CREATE SUBSCRIPTION login_s CONNECTION 'dbname=login host=127.0.0.1 user=repuser password=Password port=5432' PUBLICATION source;

That's It ....!

Monitoring Replication 

customer_details=# \dRs+
                                                          List of subscriptions
    Name    |  Owner   | Enabled | Publication | Synchronous commit |                              Conninfo                               
------------+----------+---------+-------------+--------------------+---------------------------------------------------------------------
 customer_s | postgres | t       | {source}    | off                | dbname=customer host=127.0.0.1 user=repuser password=Password port=5432
 login_s    | postgres | t       | {source}    | off                | dbname=login host=127.0.0.1 user=repuser password=Password port=5432
(2 rows)
customer_details=# select * from pg_subscription;
 subdbid |  subname   | subowner | subenabled |                             subconninfo                             | subslotname | subsynccommit | subpublications 
---------+------------+----------+------------+---------------------------------------------------------------------+-------------+---------------+-----------------
   16438 | customer_s |       10 | t          | dbname=customer host=127.0.0.1 user=repuser password=Password port=5432 | customer_s  | off           | {source}
   16438 | login_s    |       10 | t          | dbname=login host=127.0.0.1 user=repuser password=Password port=5432    | login_s     | off           | {source}
(2 rows)
customer_details=# select * from pg_subscription_rel ;
 srsubid | srrelid | srsubstate | srsublsn  
---------+---------+------------+-----------
   16471 |   16457 | r          | 0/1DD1BD0
   16472 |   16465 | r          | 0/1DD3B20
(2 rows)
NOTE : You can check the initial sync status of each table in a subscription in the system catalog pg_subscription_rel (look for r = ready in column srsubstate).

customer_details=# select * from pg_stat_subscription ;
 subid |  subname   |  pid  | relid | received_lsn |        last_msg_send_time        |      last_msg_receipt_time       | latest_end_lsn |         latest_end_time          
-------+------------+-------+-------+--------------+----------------------------------+----------------------------------+----------------+----------------------------------
 16471 | customer_s | 14966 |       | 0/1DD5518    | 2019-04-25 11:56:39.004207+05:30 | 2019-04-25 11:56:39.004647+05:30 | 0/1DD5518      | 2019-04-25 11:56:39.004207+05:30
 16472 | login_s    | 14998 |       | 0/1DD5518    | 2019-04-25 11:56:39.004206+05:30 | 2019-04-25 11:56:39.004615+05:30 | 0/1DD5518      | 2019-04-25 11:56:39.004206+05:30
(2 rows)

Limitation of Logical Replication 

• Logical Replication currently does not replicate schema changes –any DDL command executed at the source database does not cause a similar change in the destination database, unlike in streaming replication.
• Tables must have primary key or unique key.
• Does not replicate sequences.
• Does not replicate TRUNCATE
• Does not replicate Large Objects
• Subscriptions can have more columns or different order of columns, but the types and column names must match between Publication and Subscription.
• Superuser privileges to add all tables

******************************************************** THANK YOU***********************************************************

        

Do You Like This Blog ?