Thread: Syncronizing Databases at different Remote Location
Hello
--
Best Regards
Shakeel Abbas
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
Shakeel, this can be done using symmetricDS.
cheers
//Sandeep
--- On Wed, 4/11/09, ABBAS SHAKEEL <shakeel.abbas.qau@gmail.com> wrote:
> From: ABBAS SHAKEEL <shakeel.abbas.qau@gmail.com>
> Subject: [ADMIN] Syncronizing Databases at different Remote Location
> To: pgsql-admin@postgresql.org
> Date: Wednesday, 4 November, 2009, 3:48 PM
> HelloI 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
>
>
>
>
From cricket scores to your friends. Try the Yahoo! India Homepage! http://in.yahoo.com/trynew
Thanks Sandeep I will have a look to it and will inform you about success or failure.
--
Best Regards
Shakeel Abbas
On Wed, Nov 4, 2009 at 4:03 PM, sandeep kumar <s_kumar66@yahoo.com> wrote:
Shakeel, this can be done using symmetricDS.
cheers
//Sandeep
--- On Wed, 4/11/09, ABBAS SHAKEEL <shakeel.abbas.qau@gmail.com> wrote:
> From: ABBAS SHAKEEL <shakeel.abbas.qau@gmail.com>
> Subject: [ADMIN] Syncronizing Databases at different Remote Location
> To: pgsql-admin@postgresql.org
> Date: Wednesday, 4 November, 2009, 3:48 PM
> HelloI am new in the field of DatabaseFrom cricket scores to your friends. Try the Yahoo! India Homepage! http://in.yahoo.com/trynew> 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
>
>
>
>
--
Best Regards
Shakeel Abbas
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
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:
HelloI 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
Thanks Shurti That looks cool I will inform you also shortly about success or success :)
--
Best Regards
Shakeel Abbas
On Wed, Nov 4, 2009 at 8:03 PM, Shruthi A <shruthi.iisc@gmail.com> wrote:
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,
ShruthiOn Wed, Nov 4, 2009 at 3:48 PM, ABBAS SHAKEEL <shakeel.abbas.qau@gmail.com> wrote:HelloI 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
--
Best Regards
Shakeel Abbas
Sandeep I have worked on it (symmetricDS) and facing the following problem
what i have done is
1. extracted the zip file in a folder
2. created a sample database on two systems.
but when i try to execute under mentionded command i get exception. As Exception is self explanatory I did as it says. I added the custom varaible classes in postgresql in postgresql.conf file but still get the same exception.
I have restarted the postgresql service (as well as system). Still no use. Please help how to use it
./sym -p root.properties --auto-create
2009-11-05 15:50:12,979 INFO [org.jumpmind.symmetric.service.impl.BootstrapService] [main] Initializing SymmetricDS database.
2009-11-05 15:50:12,991 ERROR [org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect] [main] Please add "custom_variable_classes = 'symmetric'" to your postgresql.conf file
-----------------------------------------------------------------------------------------------
An exception occurred. Please see the following for details:
-----------------------------------------------------------------------------------------------
org.postgresql.util.PSQLException: ERROR: unrecognized configuration parameter "symmetric.triggers_disabled"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:236)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:205)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:443)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
[wrapped] org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select set_config('symmetric.triggers_disabled', '', false)]; nested exception is org.postgresql.util.PSQLException: ERROR: unrecognized configuration parameter "symmetric.triggers_disabled"
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:458)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:466)
at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:497)
at org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect$1.doInTransactionWithoutResult(PostgreSqlDbDialect.java:118)
at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
at org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect.enableSyncTriggers(PostgreSqlDbDialect.java:115)
at org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect.initForSpecificDialect(PostgreSqlDbDialect.java:60)
[wrapped] java.lang.RuntimeException: Missing custom variable class 'symmetric'
at org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect.initForSpecificDialect(PostgreSqlDbDialect.java:63)
at org.jumpmind.symmetric.db.AbstractDbDialect.initSupportDb(AbstractDbDialect.java:196)
at org.jumpmind.symmetric.service.impl.BootstrapService.autoConfigDatabase(BootstrapService.java:107)
at org.jumpmind.symmetric.service.impl.BootstrapService.setupDatabase(BootstrapService.java:131)
at org.jumpmind.symmetric.SymmetricLauncher.autoCreateDatabase(SymmetricLauncher.java:489)
at org.jumpmind.symmetric.SymmetricLauncher.main(SymmetricLauncher.java:213)
-
On Wed, Nov 4, 2009 at 4:03 PM, sandeep kumar <s_kumar66@yahoo.com> wrote:
Shakeel, this can be done using symmetricDS.
cheers
//Sandeep
--- On Wed, 4/11/09, ABBAS SHAKEEL <shakeel.abbas.qau@gmail.com> wrote:
> From: ABBAS SHAKEEL <shakeel.abbas.qau@gmail.com>
> Subject: [ADMIN] Syncronizing Databases at different Remote Location
> To: pgsql-admin@postgresql.org
> Date: Wednesday, 4 November, 2009, 3:48 PM
> HelloI am new in the field of DatabaseFrom cricket scores to your friends. Try the Yahoo! India Homepage! http://in.yahoo.com/trynew> 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
>
>
>
>
--
Best Regards
Shakeel Abbas