PostgreSQL Tutorial - 17 - How to setup Logical replication in PostgreSQL step by step on Ubuntu

Step 1 — Configuring PostgreSQL for Logical Replication
On db-master,

...
#listen_addresses = 'localhost' # what IP address(es) to listen on;
---Modify like below:
listen_addresses = 'localhost, postgresqldb_master_private_ip_address'
...

...
#wal_level = replica # minimal, replica, or logical
---Modify like below:
wal_level = logical
...
Save the file and close it.

...
# TYPE DATABASE USER ADDRESS METHOD
...
host all all postgresqldb_replica_private_ip_address/32 md5

Save the file and close it.

Next, let’s set our firewall rules to allow traffic from db-replica to port 5432 (your running port) on db-master:

$ sudo ufw allow from postgresqldb_replica_private_ip_address to any port 5432

Finally, restart the PostgreSQL server for the changes to take effect:

$ sudo systemctl restart postgresql
-------------------------------------------------------------
Step 2 — Setting Up a Database, User Role, and Table

First, open the psql prompt as the postgres user with the following command on both db-master and db-replica:

$ sudo -u postgres psql

Create a new database called example on both hosts:
postgres=# CREATE DATABASE chiragLogicalRep;

Using the \connect meta-command, connect to the databases you just created on each host:

postgres=# \c chiraglogicalrep

Create a new table called widgets with arbitrary fields on both hosts:

chiragLogicalRep=# CREATE TABLE products
chiragLogicalRep=# (
chiragLogicalRep=# id SERIAL,
chiragLogicalRep=# name TEXT,
chiragLogicalRep=# price DECIMAL,
chiragLogicalRep=# CONSTRAINT products_pkey PRIMARY KEY (id)
chiragLogicalRep=# );

On db-master,

Still on db-master, grant full privileges on the example database to the user role you just created:

chiragLogicalRep=# GRANT ALL PRIVILEGES ON DATABASE chiraglogicalrep TO chirag;

Next, grant privileges on all of the tables contained in the database to your user:

chiragLogicalRep=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO chirag;

---------------------------------------------------
Step 3 — Setting Up a Publication

On db-master, create a publication called my_publication:

CREATE PUBLICATION my_publication;

Add the widgets table you created previously to it:

ALTER PUBLICATION my_publication ADD TABLE products;

-------------------------------------
Step 4 — Creating a Subscription

On db-replica,

-------------------------------------------
Step 5 — Testing and Troubleshooting

On db-master,
insert the following data on the widgets table:

chiragLogicalRep=# INSERT INTO products (name, price) VALUES ('Pen', 5.90), ('Notebook', 9.10), ('Pencil', 8.50);

On db-replica,
run the following query to fetch all the entries on this table:

chiragLogicalRep=# SELECT * FROM products;

For any doubts and query, please write on youtube video comments section.

Note : Flow the Process shown in video.

😉Subscribe and like for more videos:
💛Don't forget to, 💘Follow, 💝Like, 💖Share 💙&, Comment

Tutorial Link :

Thanks & Regards,
Chitt Ranjan Mahto "Chirag"
_________________________________________________________________________________________
Note: All scripts used in this demo will be available in our website.
Link will be available in description.

#ChiragsTutorial
#DBATutorial
#DBATutorialLearning
#DBAEducation
#DBATutorialFree
#DatabaseTutorial
#PostgreSQLDatabaseTutorial
#PostgreSQLDBATutorial
#PostgreSQLDatabaseTutorial
#ChiragsDBATutorial
#ChiragsDatabaseTutorial
#ChiragsPostgreSQLDatabaseTutorial
#ChiragsPostgreSQLDBATutorial
#ChiragsPostgreSQLDatabaseTutorial
#CreateDatabaseinPostgreSQL

[viratsati]

So If I add something to replica server in the same db same table some more entries in table in replica will it reflect in server?

[prabhakarkumarsingh]

How did you connect both linux vm, so that they can communicate through private IP address ?