Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

HOW TO SETUP A LOGICAL REPLICATION WITHIN 5 MINUTES ON POSTGRESQL

In this tutorial, we are going to demonstrate you how to setup a logical replication between two PostgreSQL 10 nodes listening on port 5432. On both servers we already installed PostgreSQL 10.
– On the primary node, we need to set the parameter wal_level to logical in the postgresql.conf file
[root@ylclpsql01 ~]# vi /var/lib/pgsql/10/data/postgresql.conf
# - Settings -

wal_level = logical                    # minimal, replica, or logical
                                        # (change requires restart)

– Restart the PostgreSQL service using the following command:
[root@ylclpsql01 ~]# systemctl restart postgresql-10
– To verify if the logical replication is properly configured use the following command:
postgres=# show wal_level;
 wal_level
-----------
 logical
(1 row)
– On the primary node, let’s create a database named testdb and a table article:
postgres=# CREATE DATABASE testdb;
CREATE DATABASE

testdb=# CREATE TABLE article(idart int primary key,name varchar(10), quantity int);
CREATE TABLE
– We’ll also need a user with replication privileges:
testdb=# CREATE ROLE rep REPLICATION LOGIN PASSWORD 'Password';
CREATE ROLE

testdb=# GRANT ALL ON article TO rep;
GRANT
testdb=#
– Let’s add a few rows on the article table:
testdb=# INSERT INTO article (idart, name, quantity) VALUES (1, 'articolo1', 20);
INSERT 0 1
testdb=# INSERT INTO article (idart, name, quantity) VALUES (2, 'articolo2', 50);
INSERT 0 1
– Let’s create a pubblication named mypub for table article using the following command :
testdb=# CREATE PUBLICATION mypub FOR TABLE article;
CREATE PUBLICATION
testdb=#
– You can verify that the publication was created with the following psql meta-command:
testdb=# \dRp+
 Publication mypub
 All tables | Inserts | Updates | Deletes
------------+---------+---------+---------
 f          | t       | t       | t
Tables:
 "public.article"
– Finally, add the following line in the pg_hba.conf file to allow access to the testdb database for the new user rep with an encrypted password
[root@ylclpsql01 ~]# vi /var/lib/pgsql/10/data/pg_hba.conf
#
# TYPE   DATABASE   USER   CIDR-ADDRESS   METHOD
  Host   testdb     rep    0.0.0.0/0      md5
– After making changes, we have to restart the PostgreSQL server
# sudo systemctl restart postgresql-10
– Now let’s connect to secondary node, we already created a database called destdb. Before we create the subscription, we need to create the table article first.
destdb=# CREATE TABLE article(idart int primary key,name varchar(10), quantity int);
CREATE TABLE
– Use the following command to set up the subscription
destdb=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=testdb host=192.168.1.30 user=rep password=Password port=5432' PUBLICATION mypub;
NOTICE:  created replication slot "mysub" on publisher
CREATE SUBSCRIPTION
destdb=#
– In secondary node, use the following query to check if the table has been replicated correctly:
destdb=# table article;
 idart | name      | quantity
-------+-----------+----------
     1 | articolo1 | 20
     2 | articolo2 | 50
(2 rows)
– As for the publication, there is a psql meta-command to display all the subscriptions created:
destdb=# \dRs+
 List of subscriptions

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

Postgresql maximum size

How to configure Replication Manager (repmgr) ?

PostgreSQL pgBadger