Sync DataBases Postgre SQL and My sql

[Anchit Kalra]

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]

Anchit,
What do you mean by "synch"? Replicate? Somehow merge the contents?

[Anchit Kalra]

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]

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]

can i make a Static String array

   public Static String [] queries;
 

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]

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.