cancel
Showing results for 
Search instead for 
Did you mean: 

3 Node Multi-Master Replication with EDB Postgres Replication Server 7.BETA

EDB Team Member

Please Note: The steps described in this article today(**04/28/2019**) refers to EDB Postgres Replication Server 7.x BETA. Expect some of the steps may change in GA or future releases. Recommended to refer to latest documentation here.

 

Watch steps-covering video: https://www.youtube.com/watch?v=NPSgtzrU7VU

 

Most of us know EnterpriseDB has a software package for cross database Multi-Master Replication(MMR) or Single-Master Replication(SMR) servers that supports an asynchronous replication system to replicate data to EDB Postgres Advanced Server(EPAS) and PostgreSQL. The initial software package name was xDB Replication Server (5.x) and subsequently converted into EDB xDB Replication Server(6.x) and today its called as EDB Postgres Replication Server(7.beta).

EDB xDB Replication Server(< 7.x version) was developed in JAVA language with Java object serialization model. It uses an architecture called publish and subscribe. It support two replication methods for cross database replication MMR or SMR.

 

  1. Trigger Based Method  - In trigger-based method, insert/update/delete triggers are created on publication tables to capture row changes and they are stored in shadow tables and later used by xDB Replication Server to reply changes on target by making database connection(JDBC/Libpq).
  2. Log Based Method - In Log-Based method, PostgreSQL Logical decoding framework used to extract changeset from publication tables in readable format and replayed on the target database by making database connection(JDBC/Libpq). Log Based applies ONLY between PG/EPAS to EPAS/PG.

New  EDB Postgres Replication Server 7.Beta(EPRS), has been re-architected in the incorporation of message streaming using Apache Kafka for replication changed data between database. Apache Kafka is a distributed data streaming platform that can publish, subscribe to, store, and process streams of records in real time. It is designed to handle data streams from multiple sources and deliver them to multiple consumers. Apache Kafka as the data streaming backbone in new EPRS to improve performance and provide enhanced failover capabilities.eprs7-v1.pngLet's implement second model as shown in the diagram, a 3 nodes MMR with EPRS hosting on the database servers.

 

Pre-Requisites

  • Servers (CentOS 7.x)
    • rag-node1 - 10.142.0.2
    • rag-node2 - 10.142.0.3
    • rag-node3 - 10.142.0.10
  • Dependency softwares
    • Java 1.8 or later
    • Setup Bi-directional passwordless ssh for 'enterprisedb' user between 3 Nodes
    • Open firewall ports for 2181,2881,3881,8081,8082(tcp)
  • EDB Software Packages
    • EDB Postgres Advanced Server 10.x
    • EDB Postgres Replication Server 7.BETA

 

Installation(EPRS/EPAS) and Database Configuration(Steps apply on all 3 Nodes)

 

  • Install EDB Postgres Advanced Server(EPAS) and EDB Postgres Replication Server(EPRS)
    yum install edb-rs* 
    yum install edb-as10-server
    Note: Enabling EDB YUM repository not covered here. Please refer to EDB Documentation
  • Create database cluster with the installed binaries. Use pre-bundled script to create database cluster.
    /usr/edb/as10/bin/edb-as-10-setup initdb
  • Adjust database cluster parameters to enable Logical Decoding Framework.
    cat <<EOT >>/var/lib/edb/as10/data/postgresql.conf
    #EDB Replication Server Changes
    #------------------------------
    wal_level = logical 
    max_wal_senders = 10 
    max_replication_slots = 10
    track_commit_timestamp = on
    EOT
  • Adjust the connections, so all 3 database nodes communicate un-interruptedly.
    sed -i 's/peer/trust/g ; s/ident/trust/g' /var/lib/edb/as10/data/pg_hba.conf
    sed -i '/\# IPv6/i host all    all     10.142.0.2/0     trust' /var/lib/edb/as10/data/pg_hba.conf
    sed -i '/\# IPv6/i host all    all     10.142.0.3/0     trust' /var/lib/edb/as10/data/pg_hba.conf
    sed -i '/\# IPv6/i host all    all     10.142.0.10/0     trust' /var/lib/edb/as10/data/pg_hba.conf
  • Start the database using 'systemctl' and check the connectivity.
    #systemctl start edb-as-10
    /usr/edb/as10/bin/psql -U enterprisedb -p 5444 -d edb -c 'select 1;'

 

Create Data Model with sample data

  • Create sample table and data on Node 1
    [root@rag-node1 ~]# psql -U enterprisedb -p 5444 -d edb -h 10.142.0.2 <<EOF
    create table rep_test(id int primary key, name char(10));
    insert into rep_test select a,'author'||a from generate_series(1,10) a;
    EOF
  • Create ONLY table structure on Node 2 & Node 3
    [root@rag-node1 ~]# psql -U enterprisedb -p 5444 -d edb -h 10.142.0.3 <<EOF
    create table rep_test(id int primary key, name char(10));
    EOF 
    [root@rag-node1 ~]# psql -U enterprisedb -p 5444 -d edb -h 10.142.0.10 <<EOF
    create table rep_test(id int primary key, name char(10));
    EOF 

Setup terminal with environment variable

 

To setup, we will use the EPRS RepCLI client(runRepCLI.sh). Setting up the terminal with shortcut commands and default location will save your command line time. The script below is useful for terminal work.

 

[root@rag-node1 ~]# more eprs_env.sh
#!/bin/bash
EPRS_CLIENT_CONFIG=/usr/edb-rs-7.0/client/etc
EPRS_SERVER_CONFIG=/usr/edb-rs-7.0/server/etc
alias eprs_server='bash /usr/edb-rs-7.0/server/bin/runServer.sh'
alias eprs_client='bash /usr/edb-rs-7.0/client/bin/runRepCLI.sh'
alias epsql='/bin/psql -h localhost -U enterprisedb -p 5444 -d edb'

Enable environment variables by running 'source' command

 

 

[root@rag-node1 ~]# source eprs_env.sh


Start EPRS service & setup dabase user password(Steps apply on all 3 Nodes)

 

  • Start EPRS Serivce
    [root@rag-node1 ~]# systemctl start edb-rs-server.service
    [root@rag-node2 ~]# systemctl start edb-rs-server.service
    [root@rag-node3 ~]# systemctl start edb-rs-server.service
  • Setup database user password. We will be using 'enterprisedb' superuser and same password for all 3 nodes.
    psql -U enterprisedb -p 5444 -d edb -h 10.142.0.2  -c "alter user enterprisedb identified by adminedb;"
    psql -U enterprisedb -p 5444 -d edb -h 10.142.0.3  -c "alter user enterprisedb identified by adminedb;"	 	
    psql -U enterprisedb -p 5444 -d edb -h 10.142.0.10 -c "alter user enterprisedb identified by adminedb;"

 

Configuration(Node 1)

 

Once EPRS service started on all 3 nodes, we can execute configuration steps from Node 1. Below steps are executed on Node 1

  • Encrypt the database user password. EPRS do not allow plain text password in command line, so we need to encrypt the password using RepCLI and use the encrypted password later when we execute "-adddb" commands.
    [root@rag-node1 ~]# echo 'adminedb' >dbpassword.txt
    [root@rag-node1 ~]# /usr/edb-rs-7.0/client/bin/runRepCLI.sh -encrypt -input ~/dbpassword.txt -output ~/dbpassword.out
    The encrypt command completed successfully.
  • Setup the replication network with port number(default: 8082) and UNIQUE servername. First replication server joins the network will be LEADER service(Node 1). An 'admin' user is created with administrative role which has all permission to setup MMR. After joining the Leader service we need to setup the password for the 'admin' user.
    [root@rag-node1 ~]# eprs_client -joinnetwork -servername leaderNode -host 10.142.0.2 -port 8082 -user admin
  • Setup 'admin' user password. Option '-savepassword' will create '.ngxpass' password file with all details in the same directory.
    [root@rag-node1 ~]# eprs_client -setadminpassword -savepassword
    Enter admin password: 
    Admin password is set successfully.
  • Join Node 2 & Node 3 as members to replication network with replication server port number(default: 8082) and UNIQUE servername.
    [root@rag-node1 ~]# eprs_client -joinnetwork -servername remoteNode2 -host 10.142.0.3  -port 8082 -user admin
    [root@rag-node1 ~]# eprs_client -joinnetwork -servername remoteNode3 -host 10.142.0.10 -port 8082 -user admin
  • Now add the databases of Node 1, Node 2, & Node 3 with UNIQUE database identifier and -dbtype. If -dbtype set as 'enterprisedb' then it refers to EPAS, if -dbtype set as 'postgresql' then it refers to community PostgreSQL. We need to pass the database information accordingly.
    [root@rag-node1 ~]# eprs_client -adddb \
    			        -servername leaderNode \
    			        -dbid node1db \
    			        -dbtype enterprisedb \
    			        -dbhost 10.142.0.2 \
    			        -dbport 5444 \
    			        -dbuser enterprisedb \
    			        -dbpassword N7Ryv4TGFInSPnvctbilyg== \
    			        -database edb \
    			        -user admin
    [root@rag-node1 ~]# eprs_client -adddb \
    			        -servername remoteNode2 \
    			        -dbid node2db \
    			        -dbtype enterprisedb \
    			        -dbhost 10.142.0.3 \
    			        -dbport 5444 \
    			        -dbuser enterprisedb \
    			        -dbpassword N7Ryv4TGFInSPnvctbilyg== \
    			        -database edb \
    			        -user admin		
    [root@rag-node1 ~]# eprs_client -adddb \
    			        -servername remoteNode3 \
    			        -dbid node3db \
    			        -dbtype enterprisedb \
    			        -dbhost 10.142.0.10 \
    				-dbport 5444 \
    				-dbuser enterprisedb \
    				-dbpassword N7Ryv4TGFInSPnvctbilyg== \
    			        -database edb \
    			        -user admin
  • Create publication with list of tables whose changes data is to be capatured and streamed to other consumer(nodes) databases. Below command, 'samplepub' is the publication name with set of tables('public.rep_test') and -nodetype RW(read/write).
    [root@rag-node1 ~]# eprs_client -createpub \
    				-pubname samplepub \
    				-servername leaderNode \
    				-dbid node1db \
    				-nodetype RW \
    				-tables public.rep_test \
    				-user admin
  • Join publication created to register databases to be a consumer, producer or both for a specific publication using -nodetype RW(Read/Write).
    [root@rag-node1 ~]# eprs_client -joinpub -servername remoteNode2 -dbid node2db -nodetype RW -pubname samplepub -user admin
    [root@rag-node1 ~]# eprs_client -joinpub -servername remoteNode3 -dbid node3db -nodetype RW -pubname samplepub -user admin
  • Perform a snapshot to load these subscription database tables with the rows that are present in the publication database tables.
    [root@rag-node1 ~]# eprs_client -startsnapshot -pubname samplepub -dbid node2db -user admin
    [root@rag-node1 ~]# eprs_client -startsnapshot -pubname samplepub -dbid node3db -user admin
    Note: Run -checksnapshot to get the status of the snapshot.
  • After the row content of all publication tables are consistent across the publication and subscription databases, startthe streaming.
    [root@rag-node1 ~]# eprs_client -startstreaming -pubname samplepub -dbid node2db -user admin
    [root@rag-node1 ~]# eprs_client -startstreaming -pubname samplepub -dbid node3db -user admin

Thats all, after completing the above steps we should see DML changes are replicated between all 3 nodes. 

 

Thank you!

--Raghav

 

5 Comments
Adventurer

Hi,

Missing in Pre-Requisites

You need to open 5444/tcp in firewall.

 

Paul-Emmanuel

Level 3 Adventurer

Hi,

Please upgrade this blog to the current EPRS version 7.0.0-LAv2.

There are errors when trying to execute an encrypted password (path of EPRS has changed).

 

[root@rag-node1 ~]# /usr/edb-rs-7.0/client/bin/runRepCLI.sh -encrypt -input ~/dbpassword.txt -output ~/dbpassword.out
-bash: /usr/edb-rs-7.0/client/bin/runRepCLI.sh: No such file or directory
[root@rag-node1 ~]# 

 

If I execute the previous command with the correct path:

 

[root@rag-node1 ~]# /usr/edb/rs-7.0/client/bin/runRepCLI.sh -encrypt -input ~/dbpassword.txt -output ~/dbpassword.out
Error: User is not specified. Please specify -user option while executing the command. 
 Note that the user passed should have required permissions to execute the command.
[root@rag-node1 ~]#

 

Even if I fix the path issue in eprs_env.sh, then when I try to register the network:

 

[root@rag-node1 ~]# eprs_client -joinnetwork -servername leaderNode -host 10.142.0.2 -port 8082 -user admin
Enter user password: 
Please first register the current service localhost:8082 with the network.
Service leaderNode has failed to register with the network.
[root@rag-node1 ~]#

 

 

Thanks in advance

Paulo La Science

 

Adventurer

hi sir,

the above steps are related to logical replication, in which I have to create new table everytime on each node, can you help me multi - master replication configuration where table is need to be created only at one node, instead of 2 nodes

 

 

means how DDL statements will be replicated in mutli-master replication.

 

Do I always need to execute DDL everytime on every node ?

Level 3 Adventurer

Hi,

As mention in the original post you have to create table(DDL) on all nodes.

[root@rag-node1 ~]# psql -U enterprisedb -p 5444 -d edb -h 10.142.0.3 <<EOF
create table rep_test(id int primary key, name char(10));
EOF 
[root@rag-node1 ~]# psql -U enterprisedb -p 5444 -d edb -h 10.142.0.10 <<EOF
create table rep_test(id int primary key, name char(10));
EOF 

I hope this is more clear.

Regards,

PauloLaScience

Adventurer

so everytime do I have create DB/table using DDLs in all Nodes ??

 

if yes then please let me know any alternate way for replicating the DDLs as well on another node, As I have a huge database and it is difficult to create all tables one by one on all nodes, it would be better for me if I create table only at one node and DDLs will be replicating in another node automatically.