0

I have 2 differents applications (Web app and Desktop app) with differents database servers but same structure.

I want to have the same data in all databases, no matter where the user insert/update/delete a record. This is the easiest for me but I don't think is the optimal.

So, for example, if I insert a record in the desktop app, this record must be inserted into the web app server ("cloud") and vice versa.

I'm using Spring+Hibernate+PostgreSQL for the web app and JavaFX+Hibernate+PostgreSQL for the desktop app.

I'm considering 2 options at the moment:

  • Use sockets to send messages between servers every time a record has been inserted/deleted/updated.

  • Use Triggers and Foreign Data Wrapper in PostgreSQL. I'm not too familiarize with this, so I don't know if I can make what I want with this option. Can they work together?

There is another option? What do you think is the best?

Improve this question
4
  • 2
    This doesn't sound like a good plan. You should keep the data in one place (though in server environments, replication is of course recommended for reliability). You probably shouldn't have any local data, and your desktop app should work with the web app's database using a REST API or the like. Commented Jan 8, 2019 at 11:13
  • If both apps share exactly the same data, you should only use a single database server. Commented Jan 8, 2019 at 11:16
  • Yeah, but the problem is the client want a local db in case they lost connection. I wanted to use a REST API but I couldn't. They'll work with the desktop app almost every time, but they want to have access to the data if they aren't in the office. Commented Jan 8, 2019 at 11:24
  • I forgot to mention that there are few branch offices, and there is a server in every office. Commented Jan 8, 2019 at 11:32

1 Answer 1

Reset to default
1

The simplest and maybe best solution is to have one central read-write database and several physical replication standbys.

The standby servers will be physical copies of the main database, and you can read from them (but not modify data).

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for the answer. I'll read more about replication since you and @RealSkeptic comment me about this. My question is, if I can only read from standby server (local I guess), how can I modify data from the desktop app? Via RestAPI as RealSkeptic said?
The application can write to the remote central RW database directly using JDBC as usual. You must understand that in the solution when writes are done to central and read are always from local the application should be aware of that as there is a replication delay, so for example application can insert the record and then if it tries to read it from local DB it may not be there yet. It's probably better to have a toggle that switches between remote read-write DB and local read-only DB.
@jdbg91 It doesn't matter if you use REST or some other API, but modifications would always be to the central database. Reads normally too. The replica wouldn't be local, but nearby. If the central DB is not reachable, you can use the replica, but then you cannot modify data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.