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