In this document, we are sharing the steps required to setup the MMR replication between 2 EPAS databases which are running on 2 different hosts and xDB installed on 3rd host. Below are the high level steps to be performed for setting up MMR replication
- Install,configure and start the xDB publication service on xDB host
- Password Encryption
- Add PDN ( previously called as MDN) node
- Create Publication (Set of tables to be published)
- Add Non-PDN ( previously called as non- MDN) node
- Take Snapshot
- Synchronize
- Schedule synchronization
Where,
PDN means Primary Definition Node, This is the first database node added to MMR Setup
Non-PDN are all the others Primary nodes added in future
PDN:
| Connectivity Params | Value |
| DB host | 172.22.14.39 |
| DB port | 5444 |
| DB user | enterprisedb |
| DB Name | xdb_demo |
Non-PDN:
| Connectivity Params | Value |
| DB host | 172.22.14.40 |
| DB port | 5444 |
| DB user | enterprisedb |
| DB Name | edb |
xDB Host : 172.22.14.44
Publication Name : xdb_pub
Publication table list : tab1,tab2
In this demo setup, the xDB server is installed on separate host (172.22.14.44) so, all the given below steps are performed on xDB host (172.22.14.44).
- Ports 9051 and 9052 should be open on xDB host.
- The xDB Publication service runs on 9051 and xDB subscription service runs on 9052 port.
- The pg_hba.conf entries should be configured on all databases node to be participated in the xDB replication setup. This is to allow the database connection from xDB host.
- Publication tables must have the primary key.
Step 1: Start the publication service.
Before starting the MMR setup, we have to start the publication service on xDB host (172.22.14.44) . For MMR replication only Publication service needs to be running.
Step 1.1: copy service file edb-xdbpubserver.service and edb-xdbsubserver.service from /usr/lib/systemd/system to /etc/systemd/system
[root@localhost bin]# cp /usr/lib/systemd/system/edb-xdbpubserver.service /etc/systemd/system/
[root@localhost bin]# cp /usr/lib/systemd/system/edb-xdbsubserver.service /etc/systemd/system/
Step 1.2: Start the publication service
[root@localhost bin]# systemctl start edb-xdbpubserver.service
Step 1.3: Check the status of the publication service
[root@localhost bin]# systemctl status edb-xdbpubserver.service
For all steps after this point, we have used the edb-repcli.jar (located in binary location of the EPRS i.e. /usr/edb/xdb) with command line arguments.
Step 2: Password encryption
Encrypt the PDN,Non-PDN,admin user as well as database user passwords using -encrypt option
Syntax
java -jar edb-repcli.jar
-encrypt ## Subcommand to encrypt the password
-input ../etc/infile.txt ## file path which stores the plaintext password to be encrypted
-output ../etc/outfile.txt ## file path which will hold the encrypted password
Example
[root@localhost bin]# java -jar edb-repcli.jar -encrypt -input ../etc/infile.txt -output ../etc/outfile.txt
[root@localhost bin]# cat ../etc/infile.txt
kishor1234
[root@localhost bin]# cat ../etc/outfile.txt
hV66jbp9L8bvLxZ0Bea/tw==
[root@localhost bin]#
Default publication server admin user credentials file:
[root@localhost bin]# cat /etc/edb-repl.conf
admin_user=admin
admin_password=SJ70z8Gk0zY= ## Plain text password is edb
Create the publication server configuration file by specifying publication server details (like publication server host,port,user and password)
cat /usr/edb/xdb/etc/publication_server.conf
host=localhost
port=9051
user=admin
password=SJ70z8Gk0zY=
Step 3: Add Primary Definition Node
Syntax
java -jar edb-repcli.jar
-addpubdb ## Sub command to add publication database
-repsvrfile /usr/edb/xdb/etc/publication_server.conf ## Path of Publication server configuration file
-dbtype enterprisedb ## Type of database enterprisedb or postgresql (in case of opensource)
-dbhost localhost ## Host ip of the database server which is going to be publication database
-dbport 5444 ## Port no. of the database server which is going to be publication database
-dbuser enterprisedb ## DB user (superuser) of the database server which is going to be publication database
-dbpassword hV66jbp9L8bvLxZ0Bea/tw== ## encrypted password of the db user of the database server which is going to be publication database
-database xdb_demo ## database name of the database server which is going to be publication database
-repgrouptype m ## replication type i.e. mmr
-changesetlogmode t ## Trigger based replication or log based replication. if trigger base then t else w
Example
[root@localhost bin]# java -jar edb-repcli.jar -addpubdb -repsvrfile /usr/edb/xdb/etc/publication_server.conf -dbtype enterprisedb -dbhost 172.22.14.39 -dbport 5444 -dbuser enterprisedb -dbpassword hV66jbp9L8bvLxZ0Bea/tw== -database xdb_demo -repgrouptype m -changesetlogmode t
Adding publication database...
Publication database added successfully.
Publication database id:1
Step 4: Create publication(Set of tables to be published)
Syntax
java -jar edb-repcli.jar
-createpub pub_db ## Sub command to add publication database
-repsvrfile /usr/edb/xdb/etc/publication_server.conf ## Path of Publication server configuration file
-pubdbid 1 ## MDN Publication database id
-reptype t ## Type of replication t for synchronization s for snapshot only
-tables public.tab1 public.tab2 ## List of schema qualified tables to be published
Example
[root@localhost bin]# java -jar edb-repcli.jar -createpub pub_db -repsvrfile /usr/edb/xdb/etc/publication_server.conf -pubdbid 1 -reptype t -tables public.tab1 public.tab2
Creating publication...
Tables:[[public.tab1, TABLE], [public.tab2, TABLE]]
Filter clause:[]
Conflict Resolution Option:[ Earliest Timestamp, Earliest Timestamp ]
Standby Conflict Resolution Option:[ Manual, Manual ]
Publication created.
Step 5: Add Non-Primary Definition Node
Note : Before adding Non-PDN node make sure that PDN node and publication on PDN node is created.
Syntax
java -jar edb-repcli.jar
-addpubdb ## Sub command to add publication database
-repsvrfile /usr/edb/xdb/etc/publication_server.conf ## Path of Publication server configuration file
-dbtype enterprisedb ## Type of database server
-dbhost 172.22.14.40 ## hostname/IP of database server
-dbport 5444 -dbuser enterprisedb ## Username of database server
-dbpassword hV66jbp9L8bvLxZ0Bea/tw== ## encrypted password of database user
-database xdb_demo ## Database name from database server to be added as publication database
-repgrouptype m ## mode of replication for publication database to be added m for multimaster s for single master
-changesetlogmode t ## type of replication i.e. t for trigger base and w for log based
Example
[root@localhost bin]# java -jar edb-repcli.jar -addpubdb -repsvrfile /usr/edb/xdb/etc/publication_server.conf -dbtype enterprisedb -dbhost 172.22.14.02 -dbport 5445 -dbuser enterprisedb -dbpassword hV66jbp9L8bvLxZ0Bea/tw== -database xdb_demo -repgrouptype m -changesetlogmode t
Adding publication database...
Replicating publication schema...
Publication database added successfully. Publication database id:53
Step 6: Snapshot operation
Using this command, take the initial snapshot from PDN database node to all Non-PDN database nodes for specific publication.
Syntax
java -jar edb-repcli.jar
-dommrsnapshot pub_db ## Subcommand for snapshot operation in MMR with MDN publication name
-pubhostdbid 31 ## Non-MDN publication database id
-repsvrfile /usr/edb/xdb/etc/publication_server.conf ## Path of Publication server configuration file
Example
[root@localhost bin]# java -jar edb-repcli.jar -dommrsnapshot pub_db -pubhostdbid 53 -repsvrfile /usr/edb/xdb/etc/publication_server.conf
Step 7: Synchronization operation
Note: Before run the synchronize operation on any Non-PDN node make sure that, initial snapshot has been taken prior to synchronize operation.
Using this command, manually synchronize the data across all publications database nodes for specific publication.
Syntax
java -jar edb-repcli.jar
-dosynchronize pub_db ## Subcommand for synchoronization operation with MDN publication name
-repsvrfile /usr/edb/xdb/etc/publication_server.conf ## Path of Publication server configuration file
-repgrouptype m ## mode of replication m for MMR and s for SMR
Example
[root@localhost bin]# java -jar edb-repcli.jar -dosynchronize pub_db -repsvrfile /usr/edb/xdb/etc/publication_server.conf -repgrouptype m
Performing synchronize...
Publication synchronized successfully.
[root@localhost bin]#
Step 8: Synchronization Schedule
Automate the data synchronization replication by defining the schedule for data synchronize operation. In below example, defined the schedule of 5 seconds interval between two synchronize replication operation.
Syntax
java -jar edb-repcli.jar
-confschedulemmr 1 ## subcommand to configure MMR schedule
-pubname pub_db ## name of Publication created on MDN node
-repsvrfile /usr/edb/xdb/etc/publication_server.conf ## Path of Publication server configuration file
-realtime 5 ## time interval for next synchronisation operation(here 5 seconds)
Example
[root@localhost bin]# java -jar edb-repcli.jar -confschedulemmr 1 -pubname pub_db -repsvrfile /usr/edb/xdb/etc/publication_server.conf -realtime 5
Configuring scheduler ...
Job is successfully scheduled.
[root@localhost bin]#
Before inserting value in PDN database on Non-PDN node
edb=# \c xdb_demo
You are now connected to database "xdb_demo" as user "enterprisedb".
xdb_demo=# select * from tab1;
id | val
1 | kishor1
2 | kishor2
3 | kishor3
4 | kishor4
5 | kishor5
6 | kishor6
7 | kishor7
(7 rows)
After inserting value in PDN database on Non-PDN node
xdb_demo=# select * from tab1;
id | val
1 | kishor1
2 | kishor2
3 | kishor3
4 | kishor4
5 | kishor5
6 | kishor6
7 | kishor7
8 | val8
(8 rows)
Before inserting value in Non-PDN database on PDN node
xdb_demo=# select * from tab1;
id | val
1 | kishor1
2 | kishor2
3 | kishor3
4 | kishor4
5 | kishor5
6 | kishor6
7 | kishor7
8 | val8
(8 rows)
After inserting value in Non-PDN database on PDN node
xdb_demo=# select * from tab1;
id | val
1 | kishor1
2 | kishor2
3 | kishor3
4 | kishor4
5 | kishor5
6 | kishor6
7 | kishor7
8 | val8
9 | val9
(9 rows)
Related to