Thread: real multi-master replication?
hi, i read about some replication system for postgresql, but - as far as i know there is none real multi-master replication system for postgresql. all i have seen are based on "query replication" with various "hacks" for specific constructions (like now()). my question is - is there any (even fully commercial) multi-master replication system for postgresql that will work with all possible constructs, triggers, random data and so on? i mean - i dont want to bother with choosing to 'note' somehow that 'this particular query' has to be replicated somehow. i'm thinking about working solution that will allow multi-master connections. anything? anywhere? depesz
"hubert depesz lubaczewski" <depesz@gmail.com> wrote: > > hi, > i read about some replication system for postgresql, but - as far as i > know there is none real multi-master replication system for > postgresql. > all i have seen are based on "query replication" with various "hacks" > for specific constructions (like now()). > my question is - is there any (even fully commercial) multi-master > replication system for postgresql that will work with all possible > constructs, triggers, random data and so on? > i mean - i dont want to bother with choosing to 'note' somehow that > 'this particular query' has to be replicated somehow. > i'm thinking about working solution that will allow multi-master connections. > > anything? anywhere? Have you looked at pgpool? -- Bill Moran http://www.potentialtech.com
Bill Moran wrote: > "hubert depesz lubaczewski" <depesz@gmail.com> wrote: >> hi, >> i read about some replication system for postgresql, but - as far as i >> know there is none real multi-master replication system for >> postgresql. >> all i have seen are based on "query replication" with various "hacks" >> for specific constructions (like now()). >> my question is - is there any (even fully commercial) multi-master >> replication system for postgresql that will work with all possible >> constructs, triggers, random data and so on? >> i mean - i dont want to bother with choosing to 'note' somehow that >> 'this particular query' has to be replicated somehow. >> i'm thinking about working solution that will allow multi-master connections. >> >> anything? anywhere? > > Have you looked at pgpool? afaik pgpool is statement based and not really multimaster either ... Stefan
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote: > > Bill Moran wrote: > > "hubert depesz lubaczewski" <depesz@gmail.com> wrote: > >> hi, > >> i read about some replication system for postgresql, but - as far as i > >> know there is none real multi-master replication system for > >> postgresql. > >> all i have seen are based on "query replication" with various "hacks" > >> for specific constructions (like now()). > >> my question is - is there any (even fully commercial) multi-master > >> replication system for postgresql that will work with all possible > >> constructs, triggers, random data and so on? > >> i mean - i dont want to bother with choosing to 'note' somehow that > >> 'this particular query' has to be replicated somehow. > >> i'm thinking about working solution that will allow multi-master connections. > >> > >> anything? anywhere? > > > > Have you looked at pgpool? > > afaik pgpool is statement based and not really multimaster either ... Well, it's multi-master to the degree that all servers are read/write, and therefore any server can take over. How would you define multi-master? -- Bill Moran http://www.potentialtech.com
Bill Moran wrote: > Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote: >> Bill Moran wrote: >>> "hubert depesz lubaczewski" <depesz@gmail.com> wrote: >>>> hi, >>>> i read about some replication system for postgresql, but - as far as i >>>> know there is none real multi-master replication system for >>>> postgresql. >>>> all i have seen are based on "query replication" with various "hacks" >>>> for specific constructions (like now()). >>>> my question is - is there any (even fully commercial) multi-master >>>> replication system for postgresql that will work with all possible >>>> constructs, triggers, random data and so on? >>>> i mean - i dont want to bother with choosing to 'note' somehow that >>>> 'this particular query' has to be replicated somehow. >>>> i'm thinking about working solution that will allow multi-master connections. >>>> >>>> anything? anywhere? >>> Have you looked at pgpool? >> afaik pgpool is statement based and not really multimaster either ... > > Well, it's multi-master to the degree that all servers are read/write, > and therefore any server can take over. not sure I follow - pgpool will simply replay the queries to each backend-server that are going through it. You cannot directly write to the servers (well you can - but that will likely cause inconsistent data) - and you have all the problems with non-determinstic queries as well as problems of getting a node back in sync after a downtime or connection loss. > > How would you define multi-master? for true multimaster one would expect to be able to write to all the nodes and keep the data consistent/sync or have some sort of conflict resolution for an async solution. Stefan
Hi, On Sun, 2007-03-04 at 20:18 +0100, hubert depesz lubaczewski wrote: > anything? anywhere? PGCluster may be the thing that you are looking for. However, if you are looking at something that is similar to Oracle's RAC, it is the PGCluster-II that you are looking for, which is under heavy development right now. Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Attachment
hubert depesz lubaczewski wrote: > hi, > i read about some replication system for postgresql, but - as far as i > know there is none real multi-master replication system for > postgresql. > all i have seen are based on "query replication" with various "hacks" > for specific constructions (like now()). > my question is - is there any (even fully commercial) multi-master > replication system for postgresql that will work with all possible > constructs, triggers, random data and so on? Not that I know of no. Sincerely, Joshua D. Drake > i mean - i dont want to bother with choosing to 'note' somehow that > 'this particular query' has to be replicated somehow. > i'm thinking about working solution that will allow multi-master > connections. > > anything? anywhere? > > depesz > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
> Well, it's multi-master to the degree that all servers are read/write, > and therefore any server can take over. > > How would you define multi-master? > > Per the original posts consideration, pgpool would have to be able to deal with constants such as now() and current_date. Can it? Joshua D. Drake
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote: > > Bill Moran wrote: > > Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote: > >> Bill Moran wrote: > >>> "hubert depesz lubaczewski" <depesz@gmail.com> wrote: > >>>> hi, > >>>> i read about some replication system for postgresql, but - as far as i > >>>> know there is none real multi-master replication system for > >>>> postgresql. > >>>> all i have seen are based on "query replication" with various "hacks" > >>>> for specific constructions (like now()). > >>>> my question is - is there any (even fully commercial) multi-master > >>>> replication system for postgresql that will work with all possible > >>>> constructs, triggers, random data and so on? > >>>> i mean - i dont want to bother with choosing to 'note' somehow that > >>>> 'this particular query' has to be replicated somehow. > >>>> i'm thinking about working solution that will allow multi-master connections. > >>>> > >>>> anything? anywhere? > >>> Have you looked at pgpool? > >> afaik pgpool is statement based and not really multimaster either ... > > > > Well, it's multi-master to the degree that all servers are read/write, > > and therefore any server can take over. > > not sure I follow - pgpool will simply replay the queries to each > backend-server that are going through it. I guess I'm comparing it to Slony, which has a clear delineation between master and slave. With pgpool, you don't have the failover procedure in the same way, in that each server can be read/write at all times. > You cannot directly write to the servers (well you can - but that will > likely cause inconsistent data) - and you have all the problems with > non-determinstic queries as well as problems of getting a node back in > sync after a downtime or connection loss. Right. > > How would you define multi-master? > > for true multimaster one would expect to be able to write to all the > nodes and keep the data consistent/sync or have some sort of conflict > resolution for an async solution. Something more like pgcluster II. -- Bill Moran http://www.potentialtech.com
On Mar 4, 2007, at 11:18 AM, hubert depesz lubaczewski wrote: > hi, > i read about some replication system for postgresql, but - as far as i > know there is none real multi-master replication system for > postgresql. > all i have seen are based on "query replication" with various "hacks" > for specific constructions (like now()). > my question is - is there any (even fully commercial) multi-master > replication system for postgresql that will work with all possible > constructs, triggers, random data and so on? > i mean - i dont want to bother with choosing to 'note' somehow that > 'this particular query' has to be replicated somehow. > i'm thinking about working solution that will allow multi-master > connections. I don't believe there is, or can be, any asynchronous multi-master replication system for any database that will work with all possible general purpose constructs. Given that, you might need to be more specific about your needs (and rethink your application architecture based on the reality of the issue), unless the constraints of synchronous replication work for you particular problem space. Cheers, Steve
Steve Atkins <steve@blighty.com> writes:
> On Mar 4, 2007, at 11:18 AM, hubert depesz lubaczewski wrote:
>> i read about some replication system for postgresql, but - as far as i
>> know there is none real multi-master replication system for
>> postgresql.
> I don't believe there is, or can be, any asynchronous multi-master
> replication system for any database that will work with all possible
> general purpose constructs.
Even more to the point: there is no universally applicable replication
solution. If something such as the OP asks for existed, its overhead
would be so extreme that hardly anyone would use it for real-world
cases. So compromise is always the order of the day.
> Given that, you might need to be more specific about your
> needs (and rethink your application architecture based on the
> reality of the issue),
Exactly.
regards, tom lane
On 3/4/07, Bill Moran <wmoran@potentialtech.com> wrote: > How would you define multi-master? i am able to write to any machine in cluster, and read from any. hopefully - wiithout any kind of "single point of failure" (like pgpool connection point). depesz
On 3/4/07, Devrim GÜNDÜZ <devrim@commandprompt.com> wrote: > PGCluster may be the thing that you are looking for. However, if you are > looking at something that is similar to Oracle's RAC, it is the > PGCluster-II that you are looking for, which is under heavy development > right now. i dont know oracle. but would pgcluster work with triggers modifying data in other tables? i mean: when i do insert to table x, i have triggers that modify (or add) records in other tables as well. from what i know (which might be untrue) this will not work. depesz
On 3/5/07, Steve Atkins <steve@blighty.com> wrote: > I don't believe there is, or can be, any asynchronous multi-master > replication system for any database that will work with all possible > general purpose constructs. but i dont insist on async. if there is multi-master replication for postgresql it would be great if it would be sync. > Given that, you might need to be more specific about your > needs (and rethink your application architecture based on the > reality of the issue), unless the constraints of synchronous > replication work for you particular problem space. i dont have a project (at the moment) that would require multi-master. i'm just checking my options - as in original mail: i heard a lot of "multi-master replication systems", but all of them seem to replicate queries, and not data. which is unacceptable for me. depesz
> but i dont insist on async. if there is multi-master replication for > postgresql it would be great if it would be sync. I don't know it this is what you are looking for, but this new link on the postgresql home page suggests that a new versionof master-to-master replication is now available. http://www.postgresql.org/about/news.752 Regards, Richard Broersma Jr.
>
> I don't believe there is, or can be, any asynchronous multi-master
> replication system for any database that will work with all possible
> general purpose constructs.
>
I believe it's possible in theory if you have system wide transaction
locking, i.e. synchronous. However, if you have to have system wide
transaction locking, what's the point? You have server X, that has to
wait for a transaction to finish on server Y, why don't you make them
the same server?
It would be nice to have some sort of paradigm for synchronizing
databases that go on and offline with each other and even have diverse
data structures. I know there's a whole science of transaction
processing which is simple in concept, but very often difficult to
implement in practice. It's a matter of matching transactions/records
up at an atomic level and replicating them across different servers.
The sort of "holy grail" for me, and I believe a lot of other people, is to:
1. have a server that can easily and transparently replicate itself
in different environments for speed, security, and fault tolerant purposes.
2. allow for people to go offline with their datasets, for instance
on a laptop on an airplane, and then go back online with relative ease.
3. Have a well defined and simple system for identifying and dealing
with conflicts that arise from multiple copies of the same dataset.
Just ideas that I have on this topic. I wonder if anybody's doing any
work on the subject.
On 3/5/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote: > > but i dont insist on async. if there is multi-master replication for > > postgresql it would be great if it would be sync. > I don't know it this is what you are looking for, but this new link on the postgresql home page suggests that a new versionof master-to-master replication is now available. > http://www.postgresql.org/about/news.752 i contacted the company some time ago, and the information i got was that their product is based on query-replication. depesz -- http://www.depesz.com/ - nowy, lepszy depesz
On Sunday 04 March 2007 21:28, Bill Moran wrote:
> Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote:
> > Bill Moran wrote:
> > > Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote:
> > >> Bill Moran wrote:
> > >>> "hubert depesz lubaczewski" <depesz@gmail.com> wrote:
> > >>>> hi,
> > >>>> i read about some replication system for postgresql, but - as far as
> > >>>> i know there is none real multi-master replication system for
> > >>>> postgresql.
> > >>>> all i have seen are based on "query replication" with various
> > >>>> "hacks" for specific constructions (like now()).
> > >>>> my question is - is there any (even fully commercial) multi-master
> > >>>> replication system for postgresql that will work with all possible
> > >>>> constructs, triggers, random data and so on?
> > >>>> i mean - i dont want to bother with choosing to 'note' somehow that
> > >>>> 'this particular query' has to be replicated somehow.
> > >>>> i'm thinking about working solution that will allow multi-master
> > >>>> connections.
> > >>>>
> > >>>> anything? anywhere?
> > >>>
> > >>> Have you looked at pgpool?
> > >>
> > >> afaik pgpool is statement based and not really multimaster either ...
> > >
> > > Well, it's multi-master to the degree that all servers are read/write,
> > > and therefore any server can take over.
> >
> > not sure I follow - pgpool will simply replay the queries to each
> > backend-server that are going through it.
>
> I guess I'm comparing it to Slony, which has a clear delineation between
> master and slave. With pgpool, you don't have the failover procedure in
> the same way, in that each server can be read/write at all times.
>
This is typically reffered to as dual master (you have two unsynchronized
master servers), though the terminology is so mixed up these days none of it
is really clear.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Hi, hubert depesz lubaczewski wrote: > i contacted the company some time ago, and the information i got was > that their product is based on query-replication. Yes, AFAIK, their solution is two phase commit based, like Sequoia. Regards Markus
Hi, On Wed, 2007-03-07 at 12:14 +0100, Markus Schiltknecht wrote: > hubert depesz lubaczewski wrote: > > i contacted the company some time ago, and the information i got was > > that their product is based on query-replication. > > Yes, AFAIK, their solution is two phase commit based, like Sequoia. I thought it was PGCluster. At least this is what I understood from the drawings. -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Attachment
Hi, Devrim GÜNDÜZ wrote: >> Yes, AFAIK, their solution is two phase commit based, like Sequoia. > > I thought it was PGCluster. At least this is what I understood from the > drawings. Uhm, you're right, it looks very similar to PgCluster, not Sequoia. So it's not two phase commit based, right? Regards Markus