1

Need some advice on how to proceed with this situation: My current scenario is that I have several postgresql (50+) boxes deployed throughout various locations and data centers and a beefy postgresql box setup at a homebase location. All of the deployed boxes have identical database layouts. I'm looking for a solution that would allow for a few things. I realize some of these options overlap and some might only contain mutually exclusive solutions. However, I'm interested to hear your thoughts :)

  1. Remotely query the deployed boxes and pull the results back to the homebase box for processing.

  2. Nightly (remote) "sync" or dump the deployed boxes' databases to a master database on the homebase box. Although each remote has box has the same table layout, the actual data differs across the board. Being able to dump all of this data remotely into master DB would be ideal.

  3. Remotely push a table entry to all of the deployed boxes from the homebase box.

  4. Ensure security of data in transit, and remotely deployed boxes.

Up to this point I've been floating on a homebrew multithreaded python/perl system that SSH's into these boxes remotely, which are ACL'ed off to the homebase server and pulls (or pushes) the raw query results over the ssh connection. I have even touched #2 (remote syncing) as I know that would get nasty really quick. I'm interested in any ideas for a more elegant solution that can scale up and stick to my FreeBSD/Linux environment.

Improve this question

2 Answers 2

Reset to default
1

1 Remotely query the deployed boxes and pull the results back to the homebase box for processing

A standard connection with SSL enabled and 'forced' would provide what you're looking for (see the answer for #4).

2 Nightly (remote) "sync" or dump the deployed boxes' databases to a master database on the homebase box. Although each remote has box has the same table layout, the actual data differs across the board. Being able to dump all of this data remotely into master DB would be ideal.

3 Remotely push a table entry to all of the deployed boxes from the homebase box.

Have you considered one of the many replication solutions available, including Slony?

4 Ensure security of data in transit, and remotely deployed boxes

PostgreSQL supports SSL out-of-box. You can also force each server instance to require SSL for connections. This will provide encryption at the transport level. I believe newer versions also support client certificates, if you want to have an additional layer of security.

Improve this answer
1
  • I haven't looked that deep into replication solutions yet, because Thanks! I wasn't sure if that was the right direction for this. My limited knowledge of replication didn't think it would entail joining multiple small databases into one single master DB. I assume this problem has been tackled before over the Cloud/Internet? Commented Apr 14, 2010 at 16:40
0

It appears to me that you have several databases storing the same information with no replication scheme in place.

My recommendation is:

(1) Consider replication over a secure transport. I'd be more likely to go with a VPN than with SSH for this.

(2) Drop into #postgresql on irc.freenode.net for some better advice.

(3) Ensure your remote boxes are all locked down. If you want to be sure of that, use OpenBSD instead of Freebsd/Linux. Peace of mind is worth lots!

Improve this answer
1
  • Thanks for the tips. Made a quick edit to #2 to reflect this, but the actual data in the remote db's differs and rather than 'replicating' (as I understand it), I need to aggregate all of these remote db's into the one master db. I'll definitely drop into #postgresql in the near future, and I haven't any problems with FreeBSD , PF, and remote access only open to a small IP range (though please correct me if I'm wrong). Migrating to OpenBSD isn't really an option at this point! Thanks again :) Commented Apr 14, 2010 at 16:11

You must log in to answer this question.