PostgreSQL is a powerful, open source object-relational database system ( ORDBMS ). Unlike other relational database systems, PostgreSQL allows users to create unique operators, complex data types, aggregate functions, data type conversion character, and other various database objects through the SQL function.
One of the most exciting feature that comes with the new PostgreSQL 10 release, is Logical Replication. Logical replication is a method of replicating data objects and their changes. Logical replication is based on publications and subscriptions. This is a common approach that is also used by other popular RDBMS such as Oracle and Microsoft SQL.
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. Check our previous tutorials to learn how to install PostgreSQL 10:
- How To Install PostgreSQL 10 on CentOS 7 / RHEL 7
- How To Install and Use PostgreSQL 10 on Ubuntu 16.04 LTS
- How To Enable Network Remote Access To PostgreSQL Database Server
– 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