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

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools