Re: Syncronizing Databases at different Remote Location - Mailing list pgsql-admin

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.abbas.qau@gmail.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