Thread: Requesting help on PostgreSQL Replication
| Hello, Can I ask you guys for suggestion on my replication requirements? I'm new to postgresql and the replication itself, so your inputs will be highly appreciated. My Situation: I have deployed a POS unit in our remote store using Openbravo POS as the software with a local installation of PostgreSQL for database. Now I will need to connect to this remote database from the central office to fetch latest data from the store and update products, prices and customer records. I would need to get the latest data from the store at least within 4-8hours. Here are the details about my requirements and info's about the nodes. Remote Location POS 256mb RAM, 640mhz Celeron CPU, 40gb HDD Openbravo POS with PostgreSQL (database size aprrox 15mb) on Ubuntu 8.10 Sometimes Erratic, semi congested 384kbps connection shared w/ free wifi access. (If you'll ask why, we don't have a choice for a provider so far.) Central Server Database Virtual Machine 512mb RAM, 2.2ghz Shared AMD64 X2 CPU, 40gb HDD Openbravo POS with PostgreSQL on Ubuntu 8.10 Dedicated 1mbps T1 connection Major Replication Requirements (from the Central Office side): 1. Apply updates on certain tables like products, customers, users and settings and commit on the remote location POS. 2. Query Sales, Transactions, Inventory and other tables for reporting purposes from POS. 3. Fetch update at least within 4-8 hours interval. 4. Generate Backups I know you only provide support in the community for free and I maybe asking too much, so I'm not expecting a lot. But I would appreciate any inputs regarding my situation. Thanks a lot! cyberjorge |
On Mon, Aug 17, 2009 at 2:29 AM, Jorge Daine Quiambao<cyb3rjorg3@yahoo.com> wrote: > Hello, > > Can I ask you guys for suggestion on my replication requirements? I'm new to > postgresql and the replication itself, so your inputs will be highly > appreciated. > > My Situation: > > I have deployed a POS unit in our remote store using Openbravo POS as the > software with a local installation of PostgreSQL for database. Now I will > need to connect to this remote database from the central office to fetch > latest data from the store and update products, prices and customer records. > I would need to get the latest data from the store at least within 4-8hours. > > Here are the details about my requirements and info's about the nodes. > > Remote Location POS > 256mb RAM, 640mhz Celeron CPU, 40gb HDD > Openbravo POS with PostgreSQL (database size aprrox 15mb) on Ubuntu 8.10 > Sometimes Erratic, semi congested 384kbps connection shared w/ free wifi > access. (If you'll ask why, we don't have a choice for a provider so far.) > > Central Server Database Virtual Machine > 512mb RAM, 2.2ghz Shared AMD64 X2 CPU, 40gb HDD > Openbravo POS with PostgreSQL on Ubuntu 8.10 > Dedicated 1mbps T1 connection > > > Major Replication Requirements (from the Central Office side): > > 1. Apply updates on certain tables like products, customers, users and > settings and commit on the remote location POS. > 2. Query Sales, Transactions, Inventory and other tables for reporting > purposes from POS. > 3. Fetch update at least within 4-8 hours interval. > 4. Generate Backups > > I know you only provide support in the community for free and I maybe asking > too much, so I'm not expecting a lot. But I would appreciate any inputs > regarding my situation. 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).
| Thanks Scott for your timely response. Slon-I introduction says it probably won't work out well in
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 |
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).
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > Major Replication Requirements (from the Central Office side): > > 1. Apply updates on certain tables like products, customers, > users and settings and commit on the remote location POS. > 2. Query Sales, Transactions, Inventory and other tables > for reporting purposes from POS. > 3. Fetch update at least within 4-8 hours interval. > 4. Generate Backups Not sure what "generate backups" means in the replication sense, but if you have sites that are flaky, you might want to look at Bucardo: http://bucardo.org/ It does master->slave (like Slony), but also does master<->master if you need it. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200908191342 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkqMOZEACgkQvJuQZxSWSsgo4gCfYhM1itn1KyVt4l/nEG4nkek5 w5MAoMrnYEbtrqSoKpcHR1m2qtTem2z8 =T0gZ -----END PGP SIGNATURE-----