Thread: Replication suggestions (weak multi-master)

Hi,

I've gone through the list of replication solution on the PostgreSQL
website but I think I'm looking for something rather specialised.

The situation is that there are little satellite machines around the
place, seperated by public internet, each writing to their own log
tables. What I want is for each satellite to be able to query an
"aggregate table" which is simply the UNION ALL of all the little
tables. Timeliness is not critical (although nice) and it needs to
handle satellites going down and rejoining. I'm ok with a master
machine somewhere.

The problems I see with the ones I can find are:
- They require direct remote access to the pg server, not possible in my case
- Use Java, also not an option
- Don't handle the aggregating (need more triggers)

I'm impressed by Slony and I could probably make it do what I want with
enough trickery. But again the need to be able to connect from any
server to any other. To put it in Slony terms, the origin never needs
to move. If the machine is down, nothing needs to be updated.

I'm considering starting with dbmirror and simply writing a script to
make it do what I want. But I'm asking in case someone has a simple
solution I've overlooked.

Thanks in advance,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Tue, Mar 01, 2005 at 11:41:11AM +0100, Martijn van Oosterhout wrote:
> I'm impressed by Slony and I could probably make it do what I want with
> enough trickery. But again the need to be able to connect from any
> server to any other. To put it in Slony terms, the origin never needs
> to move. If the machine is down, nothing needs to be updated.
>
> I'm considering starting with dbmirror and simply writing a script to
> make it do what I want. But I'm asking in case someone has a simple
> solution I've overlooked.

My suspicion that the log shipping support in Slony 1 (HEAD right
now) or the approach outlined at OSCON last year (anonymous file
shipping based on dbmirror -- mighty cool) is something closeish to
what you'll need.

--
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
        --Dennis Ritchie