On Fri, Jan 18, 2008 at 09:27:08PM +0000, Gordan Bobic wrote:
> Andrew Sullivan wrote:
>> On Fri, Jan 18, 2008 at 04:09:45PM +0000, gordan@bobich.net wrote:
>>> That's just it - I don't think any user-land libraries would
>>> actually be required. One of supposed big advantages of MySQL is
>>> it's straightforward replication support. It's quite painful to
>>> see PostgreSQL suffer purely for the sake of lack of marketting in
>>> this department. :-(
>>
>> The "straigtforward" replication support in MySQL is seriously
>> broken.
>
> I am not arguing that it isn't! :-) I am merely trying to implement
> something at least as good (or rather, no more broken) for
> PostgreSQL with a minimum of effort.

In that case, use one of the existing solutions. They're all way
easier than re-inventing the wheel.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

David Fetter wrote:

>>>> That's just it - I don't think any user-land libraries would
>>>> actually be required. One of supposed big advantages of MySQL is
>>>> it's straightforward replication support. It's quite painful to
>>>> see PostgreSQL suffer purely for the sake of lack of marketting in
>>>> this department. :-(
>>> The "straigtforward" replication support in MySQL is seriously
>>> broken.
>> I am not arguing that it isn't! :-) I am merely trying to implement
>> something at least as good (or rather, no more broken) for
>> PostgreSQL with a minimum of effort.
>
> In that case, use one of the existing solutions. They're all way
> easier than re-inventing the wheel.

Existing solutions can't handle multiple masters. MySQL can do it at
least in a ring arrangement.

Gordan

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Gregory Youngblood wrote:
> On Sat, 2008-01-19 at 23:46 +0000, Gordan Bobic wrote:
>> David Fetter wrote:
>> > In that case, use one of the existing solutions. They're all way
>> > easier than re-inventing the wheel.
>>
>> Existing solutions can't handle multiple masters. MySQL can do it at
>> least in a ring arrangement.
>>
> What about pgcluster? It's supposed to be able to provide synchronous
> multi-master replication for postgresql.

I looked at that, too, but it wasn't really a "cluster of equal peers"
sort of solution, which is what I am after. Still, thanks for pointing
it out.

Gordan

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Jan 19, 2008 5:46 PM, Gordan Bobic <gordan@bobich.net> wrote:
> David Fetter wrote:
>
> >>>> That's just it - I don't think any user-land libraries would
> >>>> actually be required. One of supposed big advantages of MySQL is
> >>>> it's straightforward replication support. It's quite painful to
> >>>> see PostgreSQL suffer purely for the sake of lack of marketting in
> >>>> this department. :-(
> >>> The "straigtforward" replication support in MySQL is seriously
> >>> broken.
> >> I am not arguing that it isn't! :-) I am merely trying to implement
> >> something at least as good (or rather, no more broken) for
> >> PostgreSQL with a minimum of effort.
> >
> > In that case, use one of the existing solutions. They're all way
> > easier than re-inventing the wheel.
>
> Existing solutions can't handle multiple masters. MySQL can do it at
> least in a ring arrangement.

Then go use MySQL.

PostgreSQL multi-master replication systems I know of:

pgcluster
http://www.postgresql.org/about/news.752

bucardo
http://bucardo.org/

One of the features of PostgreSQL is that it's easily enough to extend
that you don't have to rely on just what's built in and supported by
core.

The multi-master replication in MySQL is NOT recommended for things
like bank style transactional systems. It was built for telecom
systems, where everything can be restored form a backup if the power
goes out.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Jan 19, 2008 6:14 PM, Gordan Bobic <gordan@bobich.net> wrote:
> Gregory Youngblood wrote:
> > On Sat, 2008-01-19 at 23:46 +0000, Gordan Bobic wrote:
> >> David Fetter wrote:
> >> > In that case, use one of the existing solutions. They're all way
> >> > easier than re-inventing the wheel.
> >>
> >> Existing solutions can't handle multiple masters. MySQL can do it at
> >> least in a ring arrangement.
> >>
> > What about pgcluster? It's supposed to be able to provide synchronous
> > multi-master replication for postgresql.
>
> I looked at that, too, but it wasn't really a "cluster of equal peers"
> sort of solution, which is what I am after. Still, thanks for pointing
> it out.

Oh, and there's this too:

Cybertec sync-multi-master
http://www.postgresql.org/about/news.752
http://www.postgresql.org/about/news.752

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Scott Marlowe wrote:
> On Jan 19, 2008 6:14 PM, Gordan Bobic <gordan@bobich.net> wrote:
>> Gregory Youngblood wrote:
>>> On Sat, 2008-01-19 at 23:46 +0000, Gordan Bobic wrote:
>>>> David Fetter wrote:
>>>>> In that case, use one of the existing solutions. They're all way
>>>>> easier than re-inventing the wheel.
>>>> Existing solutions can't handle multiple masters. MySQL can do it at
>>>> least in a ring arrangement.
>>>>
>>> What about pgcluster? It's supposed to be able to provide synchronous
>>> multi-master replication for postgresql.
>> I looked at that, too, but it wasn't really a "cluster of equal peers"
>> sort of solution, which is what I am after. Still, thanks for pointing
>> it out.
>
> Oh, and there's this too:
>
> Cybertec sync-multi-master
> http://www.postgresql.org/about/news.752
> http://www.postgresql.org/about/news.752

The design of that seems suspiciously similar to pgcluster with separate
load balancer and replicator servers.

Gordan

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Sun, 20 Jan 2008 00:34:11 +0000 Gordan Bobic wrote:

> Scott Marlowe wrote:
> > On Jan 19, 2008 6:14 PM, Gordan Bobic <gordan@bobich.net> wrote:
> >
> > Oh, and there's this too:
> >
> > Cybertec sync-multi-master
> > http://www.postgresql.org/about/news.752
> > http://www.postgresql.org/about/news.752
>
> The design of that seems suspiciously similar to pgcluster with separate
> load balancer and replicator servers.

It does not only look like pgcluster.


Bye

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Sat, 2008-01-19 at 23:46 +0000, Gordan Bobic wrote:

> David Fetter wrote:
>
> > In that case, use one of the existing solutions. They're all way
> > easier than re-inventing the wheel.
>
> Existing solutions can't handle multiple masters. MySQL can do it at
> least in a ring arrangement.
>

What about pgcluster? It's supposed to be able to provide synchronous
multi-master replication for postgresql.

Greg

On Jan 19, 2008 6:46 PM, Gordan Bobic <gordan@bobich.net> wrote:
> Existing solutions can't handle multiple masters. MySQL can do it at
> least in a ring arrangement.

mysql multi-master replication looks a lot better on paper than it
really is...one of the reasons mysql is able to seemingly provide it
so easily is that mysql doesn't take transactions and locks very
seriously whereas postgres does. Because spreading the locking system
around on multiple servers complicates everything and adds all kinds
of timing issues, you are unlikely to see any real performance benefit
over a single well built server unless your ratio of reads to writes
is extremely high (especially on a relatively high latency link like
ethernet). Since single master replication will fly in many of those
cases, the use case for multi-master replication is a lot smaller than
it appears on the surface. multi-master is more appealing if you do
not need locks strictly enforced and you can play fast and loose with
your data...many applications do not have this luxury however. Many
people who ask for this feature grossly misunderstand the complexity
of the problem and expect there to be a magical solution with few
trade-offs. mysql's solution to this problem, while quite useful at
solving particular problems, is not a 'solution' in the general sense.
By the way, many big and/or busy database are bound by the i/o
system, not the cpu.

PostgreSQL chose another path...we like our locks and want our
transactions to give exact and specific answers. This means a few
compromises on some things you take for granted in mysql (select
count(*) from foo; comes to mind), but maybe a better solution for
solving a wide range of applications where every transaction counts.
PostgreSQL also scales very well too multiple core systems (much
better than mysql, its been reported), so you can always scale the
server up fairly cheaply...x86 can easily scale up to 16 cores these
days fairly cheaply with decent results. Expect these trends to
continue going forwards...one thing that the PostgreSQL community
might be interested in the future is distributing a single query
across multiple cores...

Anyways, for replication on the single master side you have Slony
(trigger based) which is an effective solution along with some other
supported solutions (mammoth replicator, for example). At some point
in the future (maybe 8.4?) we will have PITR hot standby which will
likely be the 'in core' replication solution although various 3rd
party tools may extend on it and make it do various things.
Personally, unless I have some easily distributed task or have
insanely high transaction loading requirements (popular web site), I
would build twin servers, each capable of servicing the database
completely and use PITR to maintain a warm standby. Obviously, this
approach has limits but it's often a good place to start. If things
are too hot for a single server to handle, maybe it's time to start
dividing up the tasks into logical portions with shared data being
moved around (with db-link, or slony).

merlin

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings