Re: PostgreSQL + Replicator developer meeting 10/28 - Mailing list pgsql-hackers

On Wed, 2008-10-29 at 09:01 -0700, Joshua D. Drake wrote:
> On Wed, 2008-10-29 at 12:02 +0200, Hannu Krosing wrote:
> > On Tue, 2008-10-28 at 22:16 -0700, Joshua D. Drake wrote: 
> > > Alvaro Herrera wrote:
> > > Case in point. To replicate a table currently you do this:
> > > 
> > > ALTER TABLE foo ENABLE REPLICATION;
> > > ALTER TABLE foo ENABLE REPLICATION ON SLAVE 0;
> > 
> > How do you define SLAVE 0 ?
> 
> SLAVE 0 is defined by a GUC on the slave, replication_slave_no I think.
> ( I would have to check the docs)

And the only other GUC slave needs for replication is MCP/Slave connect
info ?

> > > Well actually that was my decision years ago and it was made for two 
> > > reasons:
> > > 
> > > 1. Speed
> > 
> > Is this some MySQL kind of speed, achieved by compromising on ACID ?
> > 
> 
> No this is the kind that realizes that throwing triggers on 300
> relations is a slow way to go about replication.

It may be slow-ish to set up initially, but in your (old) tips wikipage
you say that replicating more than 1000 relations would be prohibitively
slow.

I don't think this would be true for trigger-based replication.

And FK  checks implemented using with triggers, and I have not heard
much complaining about it being a hack, or unsuitable upwards of some
N-hundred tables.

> > Actually I see trigger based replication as a power tool. You can make it (almost) 
> > as fast as any source level hack, even if the latter compromises on on ACID, plus 
> > you can be much more flexible on what/when/how you replicate.
> 
> Like I said, really not interested in the trigger versus not discussion.

Why not ? Because you are actually doing it with triggers, just not
standard postgreSQL table-level triggers, but your own ones patched into
backend ?

When doing it without triggers (or to be more exact, with triggers
hardwired into backend) you still have to solve most of the same
problems that trigger-based replication does, just with less visibility
and less help from existing infrastructure.


One more question about triggers - what happens to triggers, foreign
keys and other constraints on slave ? 


> > One of the core requirements of the original replicator which can still 
> > > be seen today AND is a requirement of 1.9 as well is:
> > > 
> > > Though shall not cause grief to thy master even if thy replicates many 
> > > children.
> > 
> > IOW, one slave and many slaves cause similar load on master.
> 
> One slave doesn't effect the load on the master any more than fifty.

But have you measured, what is impact of replication when going from 0
to 1 slaves ? 

...

> > > and our user 
> > > experience is far more friendly than anything else.
> > 
> > Do you mean that you have a friendly UI for beginners ?
> > 
> 
> Not just beginners. People don't like complicated software. Generally
> speaking, if you go to a DBA of other systems, someone with 10 years
> experience and you show them Slony and they look at you like your nuts.

I wonder why. To me Slony's UI (slonik) seems quite simplistic, the only
thing they have above what Replicator seems to have is grouping tables
so that tables inside one group are always guaranteed to be in a
consistent state on the slave.

Otherways I can't see how 

------------------
create set (id=1, origin=1, comment="Master");
set add table (set id=1, origin=1, id=1, fully qualified name = 'public.accounts');
---
store node (id=2, comment = 'Slave node');
store path (server = 1, client = 2, conninfo='dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER');
--
subscribe set ( id = 1, provider = 1, receiver = 2,
------------------

can be made much simpler

You can't replicate a table on Replicator by just one command:

ALTER TABLE public.accounts ENABLE REPLICATION;
ALTER TABLE public.accounts ENABLE REPLICATION ON SLAVE 2;

You first have to set things up by telling master and slave about 
themselves ("Thou Art the Master") and about each other.

> It doesn't matter that Slony works great. It doesn't matter that it is
> proven in the field. 

Actually slony works great for simple cases, and not-so-great for more
complex topologies or higher loads. It is relatively easy to push Slony
over by accident.

And it was Slony's apparent _simplicity_ that fooled me into believing
that it is a robust system, similar to postgreSQL proper. A belief, the
consequences of which caused me a lot of grief and frustration for about
two years

> > Or is it proved to be robust in continued use, with changes going smoothly 
> > in and automatic recovery from network outages and random reboots on live 
> > systems in 24/7 environment ?
> > 
> 
> Well we have had plenty of people use it although I admit over the last
> year we have been weening them off in preparation for 1.8. 1.8 is a big
> step up from previous releases and yes it has some issues all software
> does but we are working on them.

Is Replicator always replicating from one single master to one or more
slaves ?

Or is it possible to set it up so, that some tables are replicated from
one and some others from some other master.

I don't mean real multi-master, just that the single master is not the
same for all tables - A good example would be a sales system where each
office has its own sales table for inserting/updating data, but has a
read-only fresh copy of all other offices data via replication .

-- 
------------------------------------------
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability   Services, Consulting and Training