Question

How to setup Logical replication in PostgreSQL 14 step by step on Ubuntu

Answer 1

inchirags@gmail.com   Chirag's PostgreSQL DBA Tutorial         https://www.chirags.in

*****************************************************************************************

* How to setup Logical replication in PostgreSQL 14 step by step on Ubuntu *

*****************************************************************************************

What Is PostgreSQL Replication? 

The process of copying data from a PostgreSQL database server to another server is called PostgreSQL Replication. The source database server is usually called the Master server, whereas the database server receiving the copied data is called the Replica server.

In this Video, we will see how to setup Logical replication in PostgreSQL step by step on Ubuntu 22.04.

----Steps:---

Step 1 — Configuring PostgreSQL for Logical Replication

On db-master, 

open /etc/postgresql/14/main/postgresql.conf, the main server configuration file:

$ sudo nano /etc/postgresql/14/main/postgresql.conf

...

#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.

Next, let’s edit /etc/postgresql/14/main/pg_hba.conf, the file that controls allowed hosts, authentication, and access to databases:

$ sudo nano /etc/postgresql/14/main/pg_hba.conf

...

# 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, 

let’s create a new user role with the REPLICATION option and a login password. The REPLICATION attribute must be assigned to any role used for replication. We will call our user chirag, but you can replace this with your own username. Make sure to also replace my_password with your own secure password:

    chiragLogicalRep=# CREATE ROLE chirag WITH REPLICATION LOGIN PASSWORD 'admin@123';

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

Publications are the mechanism that PostgreSQL uses to make tables available for replication. The database server will keep track internally of the connection and replication status of any replica servers associated with a given publication. On db-master, you will create a publication, my_publication, that will function as a master copy of the data that will be sent to your subscribers — in our case, db-replica.

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;

With your publication in place, you can now add a subscriber that will pull data from it.

-------------------------------------

Step 4 — Creating a Subscription

On db-replica, 

let’s create a subscription called my_subscription. The CREATE SUBSCRIPTION command will name the subscription, while the CONNECTION parameter will define the connection string to the publisher. This string will include the master server’s connection details and login credentials, including the username and password you defined earlier, along with the name of the example database. Once again, remember to use db-master’s private IP address, and replace my_password with your own password:

    CREATE SUBSCRIPTION my_subscription CONNECTION 'host=postgresqldb_master_private_ip_address port=5432 password=admin@123 user=chirag dbname=chiragLogicalRep' PUBLICATION my_publication;

You will see the following output confirming the subscription:

Output:

NOTICE:  created replication slot "my_subscription" on publisher

CREATE SUBSCRIPTION

-------------------------------------------

Step 5 — Testing and Troubleshooting

To test replication between our master and replica, let’s add some data to the products table and verify that it replicates correctly.

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;

You should now see:

Output

 id |    name    | price 
----+------------+-------
  1 | Pen        |  5.90
  2 | Notebook   |  9.10
  3 | Pencil     |  8.50
(3 rows)

Success! The entries have been successfully replicated from db-master to db-replica. From now on, all INSERT, UPDATE, and DELETE queries will be replicated across servers unidirectionally.

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:

https://www.youtube.com/@chiragstutorial

Don't forget to, Follow, Like,  Share &, Comment

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.