How to Set up logical replication of postgresql in linux

What is replication ?

The process of copying data from one server to another one .The source of database server is usually called master server whereas the database server receiving the copied data is called as slave server/ Replica server .

What are different way of replication

  1. Streaming replication (physical replication)
  2. Logical Replication

Logical Replication in linux

logical replication is a method of replicating the data objects and their changes, based upon their replication identity (usually a primary key )

Logical replication allows fined grained control over both the data and replication and security

Subscriber pull the data from the publication they subscribe

Logical replication of a table typically start with a snapshot of the data on the publisher database of copying that to the server

How logical replication works :

Changes on the publisher are sent to the subscriber as they occurs in the real time .

subscriber applies the data in the same order as the publisher so that transactional consistency

Use Cases of postgresql /EDB logical replication

Publication and Subscriber Difference

Enough of talking let’s do some hands on

Lab Set up :

IP-Address Hostname postgres version Role

192.168.43.4 Publisher 12

192.168.43.157 Subscriber 12

Put the entry in both the node

Step 1. Install of postgresql on both the node

sudo apt-get install postgresql-12 -y

This command will install these packages / binaries

publisher Site Configuration :

Step 2. Configuration of postgresql on Publisher node (logical replication)

Start the service on publisher node

Now Configure the postgresql cofiguration file

vi /etc/postgresql/12/main/postgresql.conf

Navigate to this entry enter the publisher IP address
Change the wal_level from replica to logical

Configure the pg_hba configuration file

Put the subscriber IP with the authentication type md5

Restart the postgresql service to reload the changes that you just done

systemctl restart postgresql

systemctl status postgresql

Step 3. create the database , User and table

Create a database

Select the database to create table

\c datbase_name

\l

Create the table name reptable

CREATE TABLE reptable ( id SERIAL, name TEXT, price DECIMAL, CONSTRAINT reptable_pkey PRIMARY KEY (id) );

Check the table is made / or not

/dt

\du : list of role and attributed

If you want to grant privelleges to the table and database to the user :

GRANT ALL PRIVILEGES ON DATABASE repdb TO repuser;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO repuser;

Subscriber Site Configuration

start the postgresql service

systemctl start postgresql

Create database same as you create on the publisher node

Create the table :

CREATE TABLE reptable ( id SERIAL, name TEXT, price DECIMAL, CONSTRAINT reptable_pkey PRIMARY KEY (id) );

Publisher Site Configuration :

Create publication and add the table to that publication

CREATE PUBLICATION my_publication;

ALTER PUBLICATION my_publication ADD TABLE reptable;

Subscriber Site Configuration

Creating a Subscription . Subscriptions are used by PostgreSQL to connect to existing publications

CREATE SUBSCRIPTION my_subscription CONNECTION ‘host=192.168.43.4 port=5432 password=123456789 user=repuser dbname=repdb’ PUBLICATION my_publication;

Test the Replication :

Publisher node :

I will create some data in the publisher node and then we are going to check if it’s showing or not into the subscriber node

Execute this command on Publisher node :

INSERT INTO reptable (name, price) VALUES (‘postgresql’, 10.10);
INSERT INTO reptable (name, price) VALUES (‘EDB’, 20.10);
INSERT INTO reptable (name, price) VALUES (‘MYSQL’, 10.10);

Check in the Subscriber node :

Select everything from reptable

SELECT * FROM reptable;

--

--

--

Trying To learn as much as possible about different Techonology. About me I am just a normal Boy who lives in terminal

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Spyderpunk — Decentraland Game Jam entry (Part 1)

Another lesson learned

Terminating all active connection and dropping database — PostgreSQL

🏆🏆Congratulation to the first user who opened the first Legendary Box.

7 steps to convert XIB file to Swift code

My experience with CloudFormation, Terraform… hi Pulumi?

The art of polite code reviews

EE2033 Integrated System Lab Module Review

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sourabh Dey

Sourabh Dey

Trying To learn as much as possible about different Techonology. About me I am just a normal Boy who lives in terminal

More from Medium

Solving Jenkins “stderr=Host key verification failed” problem when all else fails

Deployment flow (Github -> Jenkins -> Remote -> AWS ECR)

Auto enable tap-to-click on Surface Pro 4 after suspend with libinput

re-enable from mouse setting menu

Extension LVM in linux RHEL 8 server

How to install Linux Mint on Dell XPS 9710 with NVIDIA GPU

Screenshot of the Linux Kernels GUI, 5.13 is selected