Thanks Scott for your timely response.
Slon-I introduction says it probably won't work out well in
Sites where connectivity is really "flakey" Replication to nodes that are unpredictably connected. Replicating a pricing database from a central server to sales staff who connect periodically to grab updates. Sites where configuration changes are made in a haphazard way. A "web hosting" situation where customers can independently make arbitrary changes to database schemas is not a good candidate for Slony-I usage.
If not all of the above, I think my setup fall on at least 3. Will this not be a factor if I choose Slony? As much as possible I would like to use Slony because of good feedback that it actually works and good number of users.
Additional questions though, if I use Slony on my case, what will be the Master then? Is it the remote POS since it's the one that do most of the changes or my central database?
I'm glad to hear this statement "Slony is nice because it allows you to replicate whichever tables in whichever direction you want" but in some cases where both ends may need to work on a single table (not necessarily simultaneous |
), like the customer table on my case how do I best handle this?
For the backup, pg_dump always come in handy, I guess I can create a job schedule to do this. Will do more study on PITR, is it for data reliability purpose or just for logging?
Regards!
cyberjorge
I would look into slony or londiste for what you're doing. I'm not
familiar with londiste in the long range iffy connection realm, but
there's a lot you can do to monitor slony and send out alerts should
it fall behind etc. Slony is nice because it allows you to replicate
whichever tables in whichever direction you want, so you can have
master tables for some stuff central, and other tables that originate
on the remote sites and replicate to your big server back at the
office.
For backup look at either pg_dump (immediate take a backup program)
and PITR (allows continuous logging to prevent data loss).
