How to synchronize database of two Redmine instances

Added by Andreas Schuh over 7 years ago

Hi Redmine Folks,

due to security considerations and restrictions on our public web server, we are thinking about running two instances of Redmine, one publicly accessible for our released software and one running on our internal server. All projects would have an entry in the internal Redmine database. Everything that is posted to the public Redmine instance should automatically also appear in the internal Redmine instance. Due to firewall restrictions, we would need to have a periodic process running which pulls all changes and integrates them into the internal database. Moreover, all updates to entries in the internal database which have been initially created through the public instance would need to be synchronized back.

Scenario 1: One of our lab members creates a new issue using the internal Redmine instance. This issue only appears in the internal database and shall not be integrated in the public database.
Scenario 2: A user of our software creates a new issue using the public Redmine instance. This issue should be integrated in the database of our internal Redmine instance such that it is accessible via the internal web site. Otherwise, we would need to check two project pages for updates.
Scenario 3: An issue created by a user which has been integrated into the internal database is updated by us, for example, setting status to Closed. This update must be reintegrated into the public database such that the user can see it and be notified.

Any ideas on how this could be achieved? A solution on the database level which is actually rather independent of Redmine would be desired.

Thank you,
Andreas

Replies (10)

RE: How to synchronize database of two Redmine instances - Added by Gert van Dijk over 7 years ago

Interesting topic, because I think scalability and HA do not come as "batteries included" with most products, but it would be nice to provide a much more enterprise-level product if possible.

A solution on the database level which is actually rather independent of Redmine would be desired.

I don't think that's feasible. But correct me if I'm wrong. Your problem statement is on application level and you would like to have a solution for it in your database level, a level lower. That's usually not the best idea, since these levels have their own set of responsibilities; things in a level should not be dependent on things a level higher. Compare it to the DoD/OSI model in computer networking. Would be a failing Internet if TCP (L4) would rely on HTTP (L5) instead of the other way around.

Synchronizing two instances means dealing with quite some more things than just "synchronize" if both have read/write access on the same sets of data (database).
  • You're talking about a periodic pull/fetch. Even if you get this small, it's not indefinitely small. So what about generated unique tokens like IssueIDs? Do we just create a random IssueID and hope it will not conflict with other issues created?
  • Let's assume you'll get it streaming/realtime; What to do if the other instance is not reachable (e.g. network slow/down)?
  • You want to have a filter or define a selection out of the data to synchronize. The filter you mention defines Redmine issue creation with a certain direction you want to block (from internal to public). Let's assume you would find a way to filter out the queries perfectly in a MySQL master-master set. Then how are you going to deal with plugins, Redmine upgrades which probably have a changing database model, etc?

Do you see this is all application-level issues to deal with? Not something you can tell MySQL or other RDBMSs to solve. (again: responsibility thingy)

My proposed solution (won't help you know): extend the REST API to implement every single entity in Redmine. Then, new features should enable you to:
  • configure a remote Redmine instance as the 'database' back-end in the configuration, connecting to its REST API
  • the ability to set some filters about what to read from and what to write to remote
  • have a local read-only database for better response times and fallback in case of connection failure

Still, you'll have a single point of failure for writing, as there's only one instance accepting writes/updates.

Zooming out on your question, do you really need this solution for problem? Wouldn't you be helped with the 'Private' flag on issues and hide them from your public users? And then have both instances connected to the same MySQL database (+ rsyncing/mounting the files/ directory). I know this isn't perfect as your public webserver should have access on your private MySQL server. Additionally, I don't know if Redmine is tested to run two instances on one database, if supported at all.

Hope this helps.

RE: How to synchronize database of two Redmine instances - Added by Andreas Schuh over 7 years ago

All valid points, Gert, and thank you for your advise. You are right that this must be done on an application level, rather than a database level. I don't see an issue with the generation of the primary keys, though, as my goal is not to actually have a mirror copy of the database rows. Certainly, though, this appears to be more complicated than I can think of yet. See also my reply to the same topic in the ChiliProject forum (sorry for the two posts, I'm not sure if that was a good idea... but what shall you do with such a general question which applies to both, fork and original...).

RE: How to synchronize database of two Redmine instances - Added by Holger Kluge over 7 years ago

Hi,

i also thought about this and stumbled about http://www.rubyrep.org. They have a screencast for bidirectionally synchronizing two redmine instances at http://www.rubyrep.org/screencast.html.

I did not test this myself, but it may be worth a try...

RE: How to synchronize database of two Redmine instances - Added by Andreas Schuh over 7 years ago

Great! Thanks for sharing. This looks indeed promising. It might be possible to modify this tool to only synchronize rows which are in both tables and add new entries from database A to database B, but not vice versa.

RE: How to synchronize database of two Redmine instances - Added by Andreas Schuh over 7 years ago

Andreas Schuh wrote:

[...] It might be possible to modify this tool to only synchronize rows which are in both tables and add new entries from database A to database B, but not vice versa.

The event filters indeed provide such means of specifying which rows to synchronize.

"Event filters can be used to filter which unsynced / unreplicated differences should be processed by rubyrep"

RE: How to synchronize database of two Redmine instances - Added by Terence Mill over 7 years ago

Holger Kluge wrote:

Hi,

i also thought about this and stumbled about http://www.rubyrep.org. They have a screencast for bidirectionally synchronizing two redmine instances at http://www.rubyrep.org/screencast.html.

I did not test this myself, but it may be worth a try...

Why don't they sync the two tables 'schema_migrations' and plugin_shema_info'?

Does that mean i can have different plugin setups in both redmine instances? if redmine standard tables are never touched by plugin migration, can this work?

RE: How to synchronize database of two Redmine instances - Added by Mei Zhen Koh almost 6 years ago

Hi All,

Anyone had try out the above topic to synchronize database of two Redmine instances.
Can share on the experience if this is possible?

Thanks.

RE: How to synchronize database of two Redmine instances - Added by Jan Niggemann (redmine.org team member) almost 6 years ago

Please don't resurrect year-old threads (they're just gonna become zombies and eat our brains...) ;-)

No, really: There is no need to do that in your scenario. Please look at the replies to your question here: How two Redmine instances access the same Database
Of yourse you're free to shoot yourself in the foot if you like to.

RE: How to synchronize database of two Redmine instances - Added by Mei Zhen Koh almost 6 years ago

Jan Niggemann (redmine.org team member) wrote:

Please don't resurrect year-old threads (they're just gonna become zombies and eat our brains...) ;-)

No, really: There is no need to do that in your scenario. Please look at the replies to your question here: How two Redmine instances access the same Database
Of yourse you're free to shoot yourself in the foot if you like to.

Thanks for the input =)

RE: How to synchronize database of two Redmine instances - Added by Smorgas Borg almost 6 years ago

Jan Niggemann (redmine.org team member) wrote:

Please don't resurrect year-old threads (they're just gonna become zombies and eat our brains...) ;-)

No, really: There is no need to do that in your scenario. Please look at the replies to your question here: How two Redmine instances access the same Database
Of yourse you're free to shoot yourself in the foot if you like to.

I'm glad this was resurrected. I have two servers that are on two networks that can't be connected so the thread on setting two Redmine servers to access the same database doesn't help me. The rubyrep solution looks like it will save me a ton of time. I'll still have to jump through some hoops but it's better than writing all of it myself.

(1-10/10)