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

On Tue, 2008-10-28 at 22:16 -0700, Joshua D. Drake wrote: 
> Alvaro Herrera wrote:
> > Hannu Krosing wrote:
> >> On Tue, 2008-10-28 at 15:18 -0700, Joshua Drake wrote:
> 
> >> Will there be an helper application for setting up and configuring
> >> changes in replication. or will it all be done using added SQL
> >> commands ?
> > 
> > Well, the interface I work on is all SQL commands :-)
> >
> 
> 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 ?

That is, do you use something similar to SQL/MED , discussed currently
on other thread on pgsql-hackers, or just tables, or external conf
files, or ... ?

> >>> Hmm I am not sure. We are pretty deep into the core and only use
> >>> triggers for GRANT/REVOKE/CREATE ROLE .
> >> By the way, why did you choose "pretty deep into the core" approach
> >> instead of triggers ?
> > 
> > Speed maybe?  I don't know.
> 
> 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 ?

> 2. Lots of people think trigger replication is a hack. (not interested 
> in arguing just stating an observation).

And making the illogical conclusion that non-trigger replication is not a hack ?

Probably the same people who think that "expensive" and "closed source" 
automatically means high quality :)

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.

> 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.

And you do it by initially replicating to a single "distributor" slave ?

> That is where the original idea of the MCP came from. They theory was, 
> we could have 50 slaves and the master wouldn't care.

A good goal. But why would anybody _need_ 50 slaves ? 

Perhaps some application with small data amount (meaning it fits on one server) 
and massively (100:1) skewed towards reading, like dynamically generated 
mostly-static-content web pages ?

> >> I mean, you probably end up duplicating (or missing) lots of
> >> postgreSQL-s internal goodness instead of just using what is already
> >> available ?
> > 
> 
> Well yes and no. We have made more and more use of the internal 
> postgresql code through 1.7, 1.8. I expect that trend will continue. A 
> hairy wart would be the use of own log, but hey its BSD if someone can 
> figure out how to make it work with WAL and not lose the feature set we 
> have 

Why not just make a new kind of wal-logged heap, which stores your version of trx log ?

> I would not vote against it and would happily except the patch 
> (assuming technical sign off by Alvaro and Alexey).
>
> >> I hope something useful will come out of this too, though I hoped that
> >> it already had some advantages over trigger-based replication, like
> 
> Well it does. We can replicate large objects for example 

I guess we get more benefit to general public in long run by making it possible 
to have triggers on large objects (and  system objects or DLL statements).

They could be some kind of restricted or "system" triggers, at least for DDL, but 
exposing the mechanisms to power users without them having to start patching backend 
code would be a good thing.

> and our user 
> experience is far more friendly than anything else.

Do you mean that you have a friendly UI for beginners ?

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 ?

> >> ability to replicate DDL .
> > 
> 
> That is what 1.9 is all about. Remember that the "hope" (no laughing 
> now) is that 1.9 will hit for 8.3 and 8.4 around the time 8.4 releases. 
> So this isn't a year away.
> 
> > I fear that our approach to replication is so ad-hoc that there's not
> > much to be gained from elsewhere.  Replicator is pretty much a fork
> > that's not likely to yield anything useful to upstream.
> 
> That is probably true.

If/when you get DDL replication done (and tested, and robust, ... :) ), it 
at least solves the following two problems for others as well

1) where to get at DDL statements at right time

2) how to put them in replication stream in right place/ right order

Having these solved is a big thing in itself, even if no actual code reuse is possible.

It may be easier to get nr 2) right in a more flexible trigger-based environment, 
so I try to talk Marko into looking at it for pgQ as well ;)


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