EDB replication server tool can be used to replicate tables between databases. Below topologies are supported by this tool.

  1. Between postgres to postgres
  2. Between oracle to postgres and vice versa
  3. Between sql sever and postgres and vice versa

In this article, we will explain steps for replicating tables between postgres to postgres database.

Publication server – Source database

Subscription server –  Target database

In this example , publication server is -> 10.20.30.40
subscription server is -> 10.20.30.41

Important points:

  1. Make sure the tables to be replicated are not present on subscription server(target db)
  2. The tables should be a primary key

1.Install edb replication server tool: ( both  source and target server)

[root@ ~]# yum install edb-xdb
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
edb1                                                                                                                      | 2.5 kB  00:00:00
rhel-7-server-extras-rpms                                                                                                 | 3.4 kB  00:00:00
rhel-7-server-optional-rpms                                                                                               | 3.2 kB  00:00:00
rhel-7-server-rpms                                                                                                        | 3.5 kB  00:00:00
(1/4): rhel-7-server-optional-rpms/7Server/x86_64/updateinfo                                                              | 3.0 MB  00:00:01
(2/4): rhel-7-server-rpms/7Server/x86_64/updateinfo                                                                       | 4.2 MB  00:00:01
(3/4): rhel-7-server-optional-rpms/7Server/x86_64/primary_db                                                              |  10 MB  00:00:03
(4/4): rhel-7-server-rpms/7Server/x86_64/primary_db                                                                       |  89 MB  00:00:21
Resolving Dependencies
--> Running transaction check
---> Package edb-xdb.x86_64 0:7.0.1-1.rhel7 will be installed
--> Processing Dependency: edb-xdb-subscriber = 7.0.1-1.rhel7 for package: edb-xdb-7.0.1-1.rhel7.x86_64
--> Processing Dependency: edb-xdb-publisher = 7.0.1-1.rhel7 for package: edb-xdb-7.0.1-1.rhel7.x86_64
--> Processing Dependency: edb-xdb-libs = 7.0.1-1.rhel7 for package: edb-xdb-7.0.1-1.rhel7.x86_64
--> Processing Dependency: edb-xdb-console = 7.0.1-1.rhel7 for package: edb-xdb-7.0.1-1.rhel7.x86_64
--> Running transaction check
---> Package edb-xdb-console.x86_64 0:7.0.1-1.rhel7 will be installed
---> Package edb-xdb-libs.x86_64 0:7.0.1-1.rhel7 will be installed
---> Package edb-xdb-publisher.x86_64 0:7.0.1-1.rhel7 will be installed
---> Package edb-xdb-subscriber.x86_64 0:7.0.1-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================
 Package                                   Arch                          Version                               Repository                   Size
=================================================================================================================================================
Installing:
 edb-xdb                                   x86_64                        7.0.1-1.rhel7                         edb1                        9.6 k
Installing for dependencies:
 edb-xdb-console                           x86_64                        7.0.1-1.rhel7                         edb1                        1.6 M
 edb-xdb-libs                              x86_64                        7.0.1-1.rhel7                         edb1                         13 M
 edb-xdb-publisher                         x86_64                        7.0.1-1.rhel7                         edb1                         41 k
 edb-xdb-subscriber                        x86_64                        7.0.1-1.rhel7                         edb1                         11 k

Transaction Summary
=================================================================================================================================================
Install  1 Package (+4 Dependent packages)

Total download size: 15 M
Installed size: 18 M
Is this ok [y/d/N]: y
Downloading packages:
-------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                            400 MB/s |  15 MB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : edb-xdb-libs-7.0.1-1.rhel7.x86_64                                                                                             1/5
  Installing : edb-xdb-subscriber-7.0.1-1.rhel7.x86_64                                                                                       2/5
  Installing : edb-xdb-publisher-7.0.1-1.rhel7.x86_64                                                                                        3/5
  Installing : edb-xdb-console-7.0.1-1.rhel7.x86_64                                                                                          4/5
  Installing : edb-xdb-7.0.1-1.rhel7.x86_64                                                                                                  5/5
  Verifying  : edb-xdb-libs-7.0.1-1.rhel7.x86_64                                                                                             1/5
  Verifying  : edb-xdb-subscriber-7.0.1-1.rhel7.x86_64                                                                                       2/5
  Verifying  : edb-xdb-publisher-7.0.1-1.rhel7.x86_64                                                                                        3/5
  Verifying  : edb-xdb-console-7.0.1-1.rhel7.x86_64                                                                                          4/5
  Verifying  : edb-xdb-7.0.1-1.rhel7.x86_64                                                                                                  5/5

Installed:
  edb-xdb.x86_64 0:7.0.1-1.rhel7

Dependency Installed:
  edb-xdb-console.x86_64 0:7.0.1-1.rhel7            edb-xdb-libs.x86_64 0:7.0.1-1.rhel7         edb-xdb-publisher.x86_64 0:7.0.1-1.rhel7
  edb-xdb-subscriber.x86_64 0:7.0.1-1.rhel7

Complete!



2.Create encrypted password for admin :


[root@bin]# cat rep_passfile
edb#123


[root@bin]#  java -jar edb-repcli.jar -encrypt -input /usr/edb/xdb/bin/rep_passfile -output /usr/edb/xdb/bin/encrypted_pwd

[root@bin]# cat /usr/edb/xdb/bin/encrypted_pwd
YGskFD33GfU=

3. Update the edb-repl.conf file with encrypted password


[root@xdb]# cat /etc/edb-repl.conf
admin_user=admin
admin_password=YGskFD33GfU=

4. Verify the replication server configuration :


[root@sysconfig]# pwd
/usr/edb/xdb/etc/sysconfig
[root@sysconfig]# cat xdbReplicationServer-70.config
#!/bin/sh

JAVA_EXECUTABLE_PATH=`which java`
JAVA_MINIMUM_VERSION=1.8
JAVA_BITNESS_REQUIRED=64
JAVA_HEAP_SIZE="-Xms256m -Xmx1536m"
PUBPORT=9051
SUBPORT=9052

Repeat step 1 to 4 on the target server also.

5. Create required user/role on both source and target:


--- on source db:

postgres=#  CREATE ROLE pubuser WITH LOGIN SUPERUSER PASSWORD 'pub#123';
CREATE ROLE



-- on target db


postgres=# CREATE ROLE subuser WITH LOGIN SUPERUSER PASSWORD 'subuser#123';
CREATE ROLE

6. Update pg_hba.conf file and reload the postgres cluster [ ON BOTH SOURCE AND TARGET ]



--- pg_hba.conf file on publication server .

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
host    all             all             0.0.0.0/0               md5

# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
host    replication     all             10.20.30.41/32          md5. ---- add this subscription server ip 



-- pg_hba.conf file on subscription server 

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0               trust
host    template1       bart1           127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     bart1           127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    replication     all             10.20.30.40/32          md5. - -- add this publication server ip 

7.Now reload the pg_cluster.


systemctl reload edb-as-12.service

 

8.Restart publication server



[root@data]# systemctl restart edb-xdbpubserver
[root@data]#
[root@data]#  systemctl status edb-xdbpubserver
● edb-xdbpubserver.service - Publication Server Service script for Linux
   Loaded: loaded (/usr/lib/systemd/system/edb-xdbpubserver.service; disabled; vendor preset: disabled)
   Active: active (running) since Thu 2022-07-14 09:52:36 +03; 4s ago
  Process: 31314 ExecStart=/bin/bash -c cd /usr/edb/xdb/bin; ./runPubServer.sh  >> /var/log/edb/xdb/edb-xdbpubserver.log 2>&1 & (code=exited, status=0/SUCCESS)
 Main PID: 31316 (bash)
    Tasks: 18
   CGroup: /system.slice/edb-xdbpubserver.service
           ├─31316 /bin/bash -c cd /usr/edb/xdb/bin; ./runPubServer.sh  >> /var/log/edb/xdb/edb-xdbpubserver.log 2>&1 &
           └─31345 /usr/bin/java -XX:-UsePerfData -Xms256m -Xmx1536m -XX:ErrorFile=/var/log/edb/xdb/pubserver_pid_%p.log -Djava.library.path=/...

Jul 14 09:52:36 SCDvLBTC28 systemd[1]: Starting Publication Server Service script for Linux...
Jul 14 09:52:36 SCDvLBTC28 systemd[1]: Started Publication Server Service script for Linux.

9. Now use xmanager or mobaxterm to invoke gui[ on publication server ]

enterprisedb$ /usr/edb/xdb/bin/runRepConsole.sh

File – > Publication Server – Register Server

password for admin – edb#123

 

SMR -> Add Database

Here i have selected WAL Stream

 

10.Create Publication and select the table list that need to be prepared.

Make sure that these tables has primary key.

11. Now prepare subscription server:

Update the edb-repl.conf file in subscription server with publication server details.


--- Publication server conf file ( after update) 

-bash-4.2$ cat /etc/edb-repl.conf
#xDB Replication Server Configuration Properties
#Thu Jul 14 10:01:34 AST 2022
admin_password=YGskFD33GfU\=
user=pubuser
port=5444
password=G8iCGMwd6Qg\=
type=enterprisedb
admin_user=admin
host=10.20.30.40
database=postgres

-- Subscription server conf file ( after update)

-bash-4.2$ cat edb-repl.conf
admin_user=admin
admin_password=YGskFD33GfU\=
user=pubuser
port=5444
password=G8iCGMwd6Qg\=
type=enterprisedb
host=10.20.30.40
database=postgres

12. Restart the subscription server service:


[root@~]# systemctl restart edb-xdbsubserver.service

[root@~]# systemctl status edb-xdbsubserver.service
● edb-xdbsubserver.service - Subscription Server Service script for Linux
   Loaded: loaded (/usr/lib/systemd/system/edb-xdbsubserver.service; disabled; vendor preset: disabled)
   Active: active (running) since Fri 2022-07-15 11:01:01 +03; 1 weeks 0 days ago
  Process: 23284 ExecStart=/bin/bash -c cd /usr/edb/xdb/bin; ./runSubServer.sh  >> /var/log/edb/xdb/edb-xdbsubserver.log 2>&1 & (code=exited, status=0/SUCCESS)
 Main PID: 23285 (bash)
    Tasks: 36
   CGroup: /system.slice/edb-xdbsubserver.service
           ├─23285 /bin/bash -c cd /usr/edb/xdb/bin; ./runSubServer.sh  >> /var/log/edb/xdb/edb-xdbsubserver.log 2>&1 &
           └─23314 /usr/bin/java -XX:-UsePerfData -XX:ErrorFile=/var/log/edb/xdb/subserver_pid_%p.log -Djava.awt.headless=true -jar /usr/edb/x...

Jul 15 11:01:01 SCDvLBTC41 systemd[1]: Starting Subscription Server Service script for Linux...
Jul 15 11:01:01 SCDvLBTC41 systemd[1]: Started Subscription Server Service script for Linux.

13. Register subscription server:

14.CREATE SUBSCRIPTION:

-You need to ass the publication server details.

 

Now we have both publication and subscriptions are created.

Currently the tables are not present on the target side ( i.e subscription server). So for the 1st time as part of initial load , we need to do snapshot. This snapshot will clone the table from source to target.

15.SNAPSHOT CREATION:

 

 

Now snapshot is done . We can configure continuous sync with a repeat interval.

16.Continuous Sync:

17.View Replication History:

 

— Check the row count of tables on both source and target:

 

Reference: https://www.enterprisedb.com/docs/eprs/latest/01_introduction/