Re: High Availability / Replication with Sequoia

Richard Huxton wrote:
> Mr(dot)Frog(dot)to(dot)you(at)googlemail(dot)com wrote:

>> I am looking now at a scenario that does not seem to be a native
>> ability of Postgres, but might possibly be overcome with Sequoia. I am
>> hoping that there exists the possibility of using Sequoia to replicate
>> a DB between / among a number of machines in the office, some of which
>> are not always connected to the lan.
>
> You might want to look at slony with log-shipping
> http://www.slony.info/documentation/logshipping.html
> Have a master server on the lan, others grab files as and when they
> need. Do read the "limitations" though.

I haven't used this or Sequoia before but slony log-shipping sounds
closer to what you want for the mobile users.

Sequoia appears to cater for always available servers to replicate to so
may not suite your mobile users.

>> The application does not allow writeback to the db, so for all intents
>> and purposes you can consider it read only.
>
> Fine with slony.
>
>> To keep the applications database up to date with new information I
>> would be using ETL applications like Spoon / PDI. This will be done to
>> an as yet undecided 'point of origin', but it is probably safe to say
>> that it will be a commercial db server somewhere on our network. The
>> latency from our network to the 'Data Warehouse' (read as badly
>> managed dogs breakfast) is huge. Suffice to say the desire for local
>> db's is high, as is the desire to make the application portable for
>> our sometimes connected laptop users.
>
> The syncing with your commercial DB is probably the most fiddly bit.
> That's not so bad, since it's one-way.
>

Not sure I get your whole plan here or not....

The main thing I am thinking here is whether you plan to have each user
sync from the commercial db or another postgresql db?

Sequoia appears to support different db sources but I would check
whether it supports replicating the same data between different 'brands'
of db or whether the master and slave must be the same brand if this is
where you plan to use it.

You could look at having postgresql draw data from your commercial db on
a set schedule something like odbclink at pgfoundry may help there.

How many of your users are mobile? Could most users be accommodated by
one central server with some mobile users getting the local copy to take
with them? Maybe syncing data when they request it? Or are you set on
automating the syncing?

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz