3

My company is looking at creating a consolidated server that will serve as a backup for many remote servers who have separate data from one another, and provide an application where the aggregate data can be used for research purposes. I was going to follow the general advice that a front facing application server should be placed in the DMZ, with the database server being behind the DMZ in the local network.

Are there any database synchronization tools for Postgres that can do regularly scheduled syncs of the remote databases and the consolidated database server by going through the front facing application server?

I have looked at Bucardo and it seems like both databases have to be able to "see" each other to work.

My current solution in mind is to have the remotes do a database dump, compress the file, send it (securely) to the front facing server, and have it verify the data and source before doing a restore of the dump on the database server. This sounds inefficient, so I am looking for some sort of program that can possibly assist.

Any suggestions are a big help!

edit: I no longer require an answer as a different setup has been selected, so any answers will be theoretical

Caleb
  • 31
  • 4
  • Check out the docs, specifically: Continuous Archiving and Point-in-Time Recovery (PITR), Log-Shipping Standby Servers – poige Jan 24 '19 at 03:42
  • As to the q-n marked with bold I can only assume very few people would dare to answer it because it's stated way too vague. "going through front facing app server" — no, really, whaaat? – poige Jan 24 '19 at 03:44
  • What does "verify the data and source" include? Checking that the (compressed) dump is not corrupt and can be restored (accepted db version) or inspection of internal structures/data? – Freddy Jan 25 '19 at 16:03
  • 1
    @poige by "going through a front facing app server" I mean can it communicate primarily with the server in the dmz, so I don't need to have a direct connection between the remote and local database servers. – Caleb Jan 25 '19 at 22:51
  • @Freddy by that I mean certification checking but instead of trusting anything signed with a root certificate, only trusting certs signed by a specific entity to indicate they should be trusted as data submitters. And yes, also ensuring it is not corrupt.is a valid dump file for version etc. – Caleb Jan 25 '19 at 22:59

0 Answers0