Hey guys,

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.

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.