Обсуждение: Unify distant Postgres databases
Hi,
We desire to implement a multi-site server that unifies data from distant Postgres databases in a nightly batch. The distant databases have all the same architecture (schema). The size of data exchanged between distant servers and the multi-site manager has to be reduced to the strictly usefully data.
We naturally have been interested by the WAL archiving (PITR). But after a deep analysis of this skill, we still encounter problems. The main problem is unifying the data from many databases in a common database.
Has anyone experienced a solution to this problem... maybe not with WAL?
Thanks
Hugues
On Fri, 2006-05-05 at 04:21, Houssais Hugues wrote: > Hi, > > We desire to implement a multi-site server that unifies data from > distant Postgres databases in a nightly batch. The distant databases > have all the same architecture (schema). The size of data exchanged > between distant servers and the multi-site manager has to be reduced > to the strictly usefully data. > > We naturally have been interested by the WAL archiving (PITR). But > after a deep analysis of this skill, we still encounter problems. The > main problem is unifying the data from many databases in a common > database. > > Has anyone experienced a solution to this problem... maybe not with > WAL? Are you talking a big multi-way setup? That's rather complex, and resolution of conflicting updates can keep a DBA busy full time in a poorly thought out setup. OTOH, if you're looking at having one or more one-way pushes in your setup, you might want to look at using slony. There are a lot of ways you can set it up, depending on your needs. Got a bit more detail on what you're wanting to do?
Each distant database works on its own domain of data. Then no conflict should happen during updates. One thing I have not specified is that the distant databases don't handle global data but only data collected at the locallevel. Slony-1 seems not to provide replication from multi-partial databases to one global database. But maybe I'm wrong... Canyou tell me more about this use of Slony? Hugues -----Message d'origine----- De : Scott Marlowe [mailto:smarlowe@g2switchworks.com] Envoyé : vendredi 5 mai 2006 19:10 À : Houssais Hugues Cc : pgsql general Objet : Re: [GENERAL] Unify distant Postgres databases On Fri, 2006-05-05 at 04:21, Houssais Hugues wrote: > Hi, > > We desire to implement a multi-site server that unifies data from > distant Postgres databases in a nightly batch. The distant databases > have all the same architecture (schema). The size of data exchanged > between distant servers and the multi-site manager has to be reduced > to the strictly usefully data. > > We naturally have been interested by the WAL archiving (PITR). But > after a deep analysis of this skill, we still encounter problems. The > main problem is unifying the data from many databases in a common > database. > > Has anyone experienced a solution to this problem... maybe not with > WAL? Are you talking a big multi-way setup? That's rather complex, and resolution of conflicting updates can keep a DBA busy full time in a poorly thought out setup. OTOH, if you're looking at having one or more one-way pushes in your setup, you might want to look at using slony. There are a lot of ways you can set it up, depending on your needs. Got a bit more detail on what you're wanting to do?
On Tue, May 09, 2006 at 10:10:50AM +0200, Houssais Hugues wrote: > Each distant database works on its own domain of data. Then no conflict should happen during updates. > One thing I have not specified is that the distant databases don't handle global data but only data collected at the locallevel. > Slony-1 seems not to provide replication from multi-partial databases to one global database. But maybe I'm wrong... Canyou tell me more about this use of Slony? Slony-I is in fact single-master. Slony-II will be multi-master, but it's also vaporware right now. What you can do is setup different tables for each set of local data and replicate all of those back to a central location. That location can then merge everything together and push it back out. You can also just have each location be a master for it's local data and have all the other locations subscribe to it. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461