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