This is probably what you need. What scared me away is that it is a query based system, there are some potential drawbacks depending on the type of query, but you have advantages too, such as extreme parallel execution, so I suggest to take a look!
http://wiki.postgresql.org/wiki/Pgpool-II mic 2010/11/12 ron_m <ron.mco...@gmail.com>: > Thanks for the pointers from both of you, I appreciate that. It would > be best to have multiple master but that will be very difficult. > Bucardo has multiple master but only for 2 masters once you get into > the docs for it. The MySQL scheme I mentioned used primary key > skipping on autonumbers so if the pool of replicas could reach 10 the > autonumber increment was set to 10 then each machine got an offset to > start with. It looks like PostgreSQL can do this as well with the > sequences but appears to be a DDL setting and not a DB server config > setting. I am new to PostgreSQL so I may not have found that aspect > yet. By being careful with the app design I think I can get it down to > one master which is more manageable and avoid key conflicts. > > Ron > > On Nov 11, 1:00 pm, Michele Comitini <michele.comit...@gmail.com> > wrote: >> There are some good news for postgresql 9.0: >> >> http://www.postgresql.org/docs/9.0/interactive/warm-standby.html >> >> some of those features above are possible on 8.4 with some difficult >> configuration tricks, see wiki.postgresql.org. >> >> mic >> >> 2010/11/11 mdipierro <mdipie...@cs.depaul.edu>: >> >> > Hi Ron, >> > I do not much about this topic. Will single master be enough? >> > You may want to look into these tools as well. >> >> >http://www.slony.info/ >> >http://www.sistemasagiles.com.ar/trac/wiki/PyReplicaEn >> >https://public.commandprompt.com/projects/replicator >> >> > On Nov 11, 1:51 pm, ron_m <ron.mco...@gmail.com> wrote: >> >> Any of you have experience with Bucardo or pgpool-II as a replication >> >> add-on? >> >> >> Some background: >> >> I switched from MySQL to PostgreSQL very cleanly using web2py as the >> >> vehicle. Sort description to document the process: Made a copy of the >> >> app, removed the content of the databases directory, added the >> >> prerequisite components (database and driver) to the system, created >> >> an empty DB, changed the connection string in the model, started MySQL >> >> verison of app in shell mode and ran all the data out to one CSV file >> >> and finally started the PostgreSQL version up in shell mode and did an >> >> import of the same CSV file followed by a db.commit(). After all that >> >> the application worked except for one group by orderby query >> >> PostgreSQL didn't like which was easy to fix and the change worked in >> >> MySQL as well. This was a database with 28 tables linked with lots of >> >> relations. >> >> >> My compliments to this great application server and infrastructure >> >> surrounding it. Of the available migration tools I found out on the >> >> net, most failed to work and would require extensive manual editing. >> >> >> The application will be installed in 10 locations scattered all over >> >> Alaska. All the locations are connected by a WAN with IPSEC to form a >> >> VPN so it looks like it is all in the same room except for network >> >> performance. >> >> >> Each location must survive a network outage and continue to work, The >> >> weather can be a problem up there. >> >> >> Any data tables that change rapidly are to remain local to each >> >> location. >> >> >> About 2/3 of the database is configuration information which changes >> >> very slowly. One table if this were running would have changed once in >> >> 5 years. Some tables change more often as employees come and go or >> >> equipment is added to a location. Config changes can be delayed by >> >> downed connections so eventual consistency is okay. >> >> >> I need something automatic since the people using the system are not >> >> technical and cannot be depended on to to a task. >> >> >> By restricting the application I could get the updates to one database >> >> instance but then there is a time delay until the local copy is in >> >> sync. >> >> >> I looked at MySQL Replication as described in the Linux Journal >> >> article July 2010 where they do a ring which has each server to the >> >> left in the ring is master to the slave to the right but I could see >> >> with intermittent networks down due to bad weather this could be a >> >> headache waiting to happen. Also the MySQL licensing has a degree of >> >> uncertainty to it so I would rather stay away. >> >> >> I am aware of PostgreSQL-R which is in beta, The uuid and timestamps >> >> available in web2py model help but then needs to be driven by cron in >> >> a batch oriented update. >> >> >> Sometimes I look at the NoSQL databases like CouchDB or MongoDB but >> >> then the foreign keys from the rapidly changing data is a problem. I >> >> could bridge it using equipment hostname or something like that but >> >> still I would rather stay inside one database. >> >> >> Hard problem to solve completely I think. >> >> >> Comments? >> >> >> Thanks >> >> >> Ron >> >>
