LATEST NEWS

PostgresSQL Database Replication using SymmetricDS

img
Dec
23

PostgresSQL + SymmectricDS Relication

 

Introduction

I have 2 servers that require each database to sync with each other. Each are connected by LAN and running PostgresSQL 9.3 on Windows Server 2012 x64. I have tried to configure slony but it seems that they don’t release the 64bit version. All others are either trial or paid versions.

I need to get it up and running ASAP.After researching for hours on postgres database replication, I managed to get it working using SymmetricDS. I would like to share how I managed to do it. From the website, http://www.symmetricds.org/about/overview

SymmetricDS is open source software for multi-master database replication, filtered synchronization, or transformation across the network in a heterogeneous environment. It supports multiple subscribers with one direction or bi-directional asynchronous data replication. It uses web and database technologies to replicate tables between relational databases, in near real time if desired. The software was designed to scale for a large number of databases, work across low-bandwidth connections, and withstand periods of network outage.

By using database triggers, SymmetricDS guarantees that data changes are captured and atomicity is preserved. Support for database vendors is provided through a Database Dialect layer, with implementations for MySQL, Oracle, SQL Server, SQL Server Azure, PostgreSQL, DB2, Informix, Interbase, Firebird, HSQLDB, H2, Apache Derby, Greenplum, and SQLite included.

Synchronization can be configured to push data or pull data on a periodic basis. SymmetricDS allows for synchronization between two or more tiers of nodes, as might be needed in the following scenarios:

  • A handful of regional servers synchronizing from the general office to remote geographical areas
  • Dozens of Point of Sale (POS) register nodes using an embedded database to sync with a store server
  • Thousands of store server nodes using a departmental class database to sync with a regional node

To simplify things, I will use these as references.

a)  1st node – Root Node / master  will be referred as root-000
b)   2nd node – other nodes / 2nd master or slave will be referred branch-001

Installation

i) Install Java

– SymmetricDS requires java, so you will need to install the latest jdk/jre from java on both servers from Oracle Java SE

iI) Install SymmetricDS

– Download SymmetricDS from http://www.symmetricds.org/download and unzip it to your preferred of location. I installed it under C:\sym

iii) Create tables

– Make sure the tables that you want to replicate exists on both servers, but without data for node branch-001. We will push the data during the initial load when registering the node to attach it to the root node. As an example. below is the table scheme for ACCESS_LEVEL containing 2 columns, ACCESS_CODE and DESCRIPTION. Make sure you add some data on the root node to simulate initial loading.

-- START init.sql
-- Table: "ACCESS_LEVEL"
CREATE TABLE "ACCESS_LEVEL"
(
  "ACCESS_CODE" character(1) NOT NULL,
  "DESCRIPTION" character varying(512),
  CONSTRAINT "ACCESS_LEVEL_PK" PRIMARY KEY ("ACCESS_CODE")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "ACCESS_LEVEL"
  OWNER TO postgres;

-- Index: "ACCESS_LEVEL_PK_INDEX"
CREATE UNIQUE INDEX "ACCESS_LEVEL_PK_INDEX"
  ON "ACCESS_LEVEL"
  USING btree
  ("ACCESS_CODE" COLLATE pg_catalog."default");
-- END init.sql

 

Configuration

1) Configure database / user access

– Configure both PostgresSQL servers with the appropriate user / database settings in pg_hba.conf. This is to allow the specific user accessing the specific database from a specific IP. This configuration is crucial if you are going to run the branch node from the root node host server. It is possible to do so, as per SymmetricDS documentation.

– By default SymmetricDS will launch nodes found in /engines. In this guide, I will only set 1 engine per node to simplify it. Make sure the configurations are identical and the only difference is just the IP Addresses and registration.url parameters.

pg_hba.conf

2) Configure the root node

– On the root node server, create a root-000.properties file in the engine folder of the root node. Copy code below into the file. Examine this file to understand each configuration.

#The node name identifier
engine.name=root-000

# The class name for the JDBC Driver
db.driver=org.postgresql.Driver

# The JDBC URL used to connect to the database
db.url=jdbc:postgresql://192.168.1.1:5432/FNDB?protocolVersion=2&stringtype=unspecified

# The user to login as who can create and update tables
db.user=replication

# The password for the user to login as
db.password=12345

registration.url=
sync.url=http://192.168.1.1:8080/sync/root-000

group.id=root
external.id=000

# purge logging
job.purge.period.time.ms=7200000
# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=5000
# This is how often the push job will be run.
job.push.period.time.ms=10000
# This is how often the pull job will be run.
job.pull.period.time.ms=10000

3) Create SymmetricDS configuration tables

– On the root node, launch a privileged command prompt, change directory to C:\sym and issue this command

bin\symadmin --engine root-000 create-sym-tables

This command will create the SymmetricDS configuration tables and exit. Check the DB whether there are any tables with sym_* prefix.

sym_*

 

 

4) Import initial database configuration

– Now we will import a sql file containing the initial table configurations for our DB. Below is the init.sql file. Edit it to match your environment, eg. table names. The table sym_router is used by SymmetricDS for routing the data changes. The names I’ve created explains it. Under sym_trigger_router, notice that my table name is ACCESS_LEVEL and I wanted the table to mirror with each other. This is basically a trigger saying that, if a change detected in ACCESS_LEVEL table, it will replicate.

-- Clear tables
delete from sym_node_group_link;
delete from sym_node_group;
delete from sym_node_identity;
delete from sym_node_security;
delete from sym_node;

-- Root Nodes
-- Create node group
insert into sym_node_group (node_group_id, description) values ('root', 'Root Servers Group');
insert into sym_node_group (node_group_id, description) values ('branch', 'Node Servers Group');

-- Create group links
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('branch', 'root', 'P');
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('root', 'branch', 'W');

-- Create node info
insert into sym_node (node_id, node_group_id, external_id, sync_enabled) values ('000', 'root', '000', 1);
insert into sym_node_security (node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time,initial_load_id,initial_load_create_by,rev_initial_load_enabled,rev_initial_load_time,rev_initial_load_id,rev_initial_load_create_by,created_at_node_id) 
values ('000','12345',0,current_timestamp,0,current_timestamp,null,null,0,null,null,null,'000');
insert into sym_node_identity values ('000');

-- Create routers 
insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('root_2_branch', 'root', 'branch', 'default',current_timestamp, current_timestamp);

insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('branch_2_root', 'branch', 'root', 'default',current_timestamp, current_timestamp);


-- Create channels
insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values ('main_channel', 1, 100000, 1, 'channel for replication');

--- Create routing for ACCESS_LEVEL
insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values ('ACCESS_LEVEL','ACCESS_LEVEL','main_channel',current_timestamp,current_timestamp);
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('ACCESS_LEVEL','root_2_branch', 100, current_timestamp, current_timestamp);
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('ACCESS_LEVEL','branch_2_root', 100, current_timestamp, current_timestamp);
--- End routing for ACCESS_LEVEL

 

– Once completed, run this command to import the initial config for the replication.

bin\dbimport --engine root-000 init.sql

This command will import the sql file.

5) Launch the root node

– Run this to launch the sym root node. Observe the start up, make sure no errors.

bin\sym --engine root-000 --port 8080

6) Config the branch node

– Similar to the root node configuration, create a branch-001.properties file in the engine folder on the branch node.

#The node name identifier
engine.name=branch-001

# The class name for the JDBC Driver
db.driver=org.postgresql.Driver

# The JDBC URL used to connect to the database
db.url=jdbc:postgresql://192.168.1.2:5432/FNDB?protocolVersion=2&stringtype=unspecified

# The user to login as who can create and update tables
db.user=replication

# The password for the user to login as
db.password=12345

# The HTTP URL of the root node to contact for registration
registration.url=http://192.168.1.1:8080/sync/root-000

# Do not change these for running the demo
group.id=branch
external.id=001

# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=5000
# This is how often the push job will be run.
job.push.period.time.ms=10000
# This is how often the pull job will be run.
job.pull.period.time.ms=10000

7) Launch branch node

– On the branch node, double check whether the tables exists. Run the command below to launch the  branch node

bin\sym --engine branch-001 --port 9090

8) Verify root node / branch node

– Once everything started, observe the live log, and noticed that the branch node is trying to register with root.

9) Allowing node registration

– To allow node registration, run this on the root node. Notice how the node communicates and register to with the root node.

bin\symadmin --engine root-000 open-registration branch 001

10) Initial branch node loading

– Once registration is complete, init the node to push the data. Run this command from the root node.

bin\symadmin --engine root-000 reload-node 001

11) Installing as a windows service

– If everything is ok, you might want to install it as a windows service. The java wrapper will run as a windows service. Edit the config file sym_service.conf located in c:\sym\conf directory. Change the service name to something related.

#********************************************************************
# Wrapper Java Properties
#********************************************************************

# Java Runtime Executable
wrapper.java.command=java

# Java Classpath
wrapper.java.classpath.1=../patches
wrapper.java.classpath.2=../patches/*
wrapper.java.classpath.3=../lib/*.jar
wrapper.java.classpath.4=../web/WEB-INF/lib/*.jar

# Java Additional Parameters
wrapper.java.additional.1=-Dlog4j.configuration=file:../conf/log4j.xml
wrapper.java.additional.2=-Dsym.keystore.file=../security/keystore
wrapper.java.additional.3=-Djavax.net.ssl.trustStore=../security/cacerts
wrapper.java.additional.4=-Dorg.eclipse.jetty.server.Request.maxFormContentSize=800000
wrapper.java.additional.5=-Dorg.eclipse.jetty.server.Request.maxFormKeys=100000
wrapper.java.additional.6=-Djava.io.tmpdir=../tmp
wrapper.java.additional.7=-Dsun.net.client.defaultReadTimeout=1800000 
wrapper.java.additional.8=-Dsun.net.client.defaultConnectTimeout=1800000
wrapper.java.additional.9=-Dfile.encoding=utf-8
wrapper.java.additional.10=-XX:+HeapDumpOnOutOfMemoryError
wrapper.java.additional.11=-XX:HeapDumpPath=../tmp
# Recommend using the -server option for optimal garbage collection operation
#wrapper.java.additional.12=-server

# Initial Java Heap Size (in MB)
wrapper.java.initmemory=256

# Maximum Java Heap Size (in MB)
wrapper.java.maxmemory=256

#********************************************************************
# Wrapper Application Properties
#********************************************************************

# Application main class and arguments
wrapper.app.parameter.1=org.jumpmind.symmetric.SymmetricLauncher

#********************************************************************
# Wrapper Logging Properties
#********************************************************************

# Log file to use for wrapper output logging.
wrapper.logfile=../logs/wrapper.log

# Wrapper process ID
wrapper.pidfile=../tmp/wrapper.pid

# SymmetricDS process ID
wrapper.server.pidfile=../tmp/server.pid

# Log level (SEVERE, WARNING, INFO, CONFIG, FINE, FINER, FINEST)
wrapper.logfile.loglevel=INFO

# Size of log file when it will rotate to next file 
wrapper.logfile.maxsize=10m

# Number of log files to rotate and keep
wrapper.logfile.maxfiles=3

#********************************************************************
# Wrapper Service Properties 
#********************************************************************

# Name of the service
wrapper.name=SymNode001

# Display name of the service
wrapper.displayname=SymNode001

# Description of the service
wrapper.description=Database Synchronization

# Mode in which the service is installed.  Either auto or manual.
wrapper.starttype=auto

Run the command below to install as a service.

bin\sym_service install

Open services.msc to verify that the service is installed.

services.msc

If these task look complicated to you, you might want to try the SymmetricDS Pro version, which offers GUI and easy configuration. There’s a 30-days trial available. Check it out here.  SymmetricDS Professional Edition

You could always the documentation for more detail information.

http://www.symmetricds.org/docs/overview
http://www.symmetricds.org/doc/3.6/quick-start/html-single/quick-start.html
https://www.facebook.com/symmetricds
https://twitter.com/symmetricds
http://www.youtube.com/jumpmindinc