Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sync DataBases Postgre SQL and My sql

 
Anchit Kalra
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am writting a program in which I i need to synch two DB's one in Mysql and PostgreSql. So Does any one have any ideas on how to do it.

thanks anchit
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anchit,
What do you mean by "synch"? Replicate? Somehow merge the contents?
 
Anchit Kalra
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am really sorry, well i already have a database Cars in MySql on which my java gui works. What i need to do is incorporate a second DBMS such that if the first one (mysql) fails I have a backup on PostgresSql. So if a change is made in the My sql DB it should reflect on my POSTGRES SQL too.

So i wante dideas on how to do this....
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So you want a hot backup of a different database type? Doing this accross two databases will be difficult; the only way I can thing is by performing every update twice from your application (and ensuring your application is the only one that accesses the database). This will require XA transactions, which is a further overhead.

If your primary and slave databases were of the same type you could probably use out the box tools to do this. Any reason why they are not?
 
Anchit Kalra
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
can i make a Static String array


where the array will hold all the queries which have been fired till now and when the user exits a function can be called which will execute these queries
one by one on my second DataBase in PostgresSQL.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This approach has a number of problems:
  • what happens if your application crashes? All changes will fail in the second database
  • If you work round this problem by making the whole thing run in a big transaction you will impact the perfomance of your databases and application
  • If you use surrogate keys these values are arbitrary and not guarenteed to follow the same creation algorithm accross databases. So there is no guarentee the same SQL statement will have the same effect on both databases.
  • What happens if something else updates your slave database before your application? Or what happens if two or more users use your applciation at the same time?
  • What happens when the user doesn't exit a function, and instead just wanders away from their PC? How does you application time them out and what happens when it does?


  • Manually writing a hot backup application is non-trivial.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic