Re: Syncronizing Databases at different Remote Location

Hello,

I guess you a bit lucky here. I worked on exactly the same problem for a
requirement in my project. And i've winded it up just today. I'm using
Java for my application, but i'm doing this particular task outside Java,
using shell directly. (It is a 3-line shell script) And then call this
script from the Java appl.

This is what i've done: psql (the postgres client) can connect to a remote
host (with -h option) provided the server is configured to receive remote
connections with appropriate authentication. (Please refer to my
yesterday's mails in the same mailing list with the subject line "Need help
in enabling remote connection") I maintain a table called 'Modifications'
in each of the sites A,B,C. This table contains all changes (inserts,
updates, deletes) i've done to all tables and are simply recorded as dml
statements in this table. During synchronization (which is hopefully
initialized at one of A,B,C), i copy the contents of this table into a text
file and empty this table. Then with psql i connect to the remote database
(Z) and simply run this dml file on it (using the cmd \i filename ), so
that all the changes are reflected at Z. This method prevents redundant
updates, conflicts and is even optimized.

Since i worked on this problem for quite some time and didnt find any direct
solution, i guess there isn't much else to do here.

Cheers,
Shruthi

On Wed, Nov 4, 2009 at 3:48 PM, ABBAS SHAKEEL
<shakeel(dot)abbas(dot)qau(at)gmail(dot)com>wrote:

> Hello
> I am new in the field of Database Management. Most of times i worked with
> Software development and Database Design.
> I need some advice regarding database management.
>
> I have three Database (DB) servers at different locations. suppose A, B and
> C as three servers. where as Z is a central server which have VPN to all
> other servers.
>
> I have to insert/update records to central server Z if there is any
> insert/update in any of the A,B and C server.
>
> Now I can write a JAVA code that when ever there is insertion/updation of
> record of A/B/C do same in central Server Z using JAVA sockets I can send
> records to central server where Socket server will listen and then insert or
> update records in DB.
>
> But I come to know that there is a concept called Replication.
> I studied it and found that it is used for load balancing. I dont need
> load balancing I only need to Syncronize only Two tables of all A,B,C
> servers to central server Z.
>
>
> Please suggest how to move forward.(Open source solutions )
>
>
> --
> Best Regards
> Shakeel Abbas
>
>