Re: Core team statement on replication in PostgreSQL - Mailing list pgsql-hackers

On Thu, 2008-05-29 at 12:05 -0700, Robert Hodges wrote:
> Hi everyone, 
> 
> First of all, I’m absolutely delighted that the PG community is
> thinking seriously about replication.  
> 
> Second, having a solid, easy-to-use database availability solution
> that works more or less out of the box would be an enormous benefit to
> customers.  Availability is the single biggest problem for customers
> in my experience and as other people have commented the alternatives
> are not nice.  It’s an excellent idea to build off an existing feature
> —PITR is already pretty useful and the proposed features are solid
> next steps.  The fact that it does not solve all problems is not a
> drawback but means it’s likely to get done in a reasonable timeframe. 
> 
> Third, you can’t stop with just this feature.  (This is the BUT part
> of the post.)  The use cases not covered by this feature area actually
> pretty large.  Here are a few that concern me: 
> 
> 1.) Partial replication. 
> 2.) WAN replication. 

1.) & 2.) are better done asunc, the domain of Slony-I/Londiste

> 3.) Bi-directional replication.  (Yes, this is evil but there are
> problems where it is indispensable.) 

Sure, it is also a lot harder and always has several dimensions
(performanse/availability7locking) which play against each other

> 4.) Upgrade support.  Aside from database upgrade (how would this ever
> really work between versions?), it would not support zero-downtime app
> upgrades, which depend on bi-directional replication tricks. 

Or you could use zero-downtime  app upgrades, which don't depend on
this :P

> 5.) Heterogeneous replication. 
> 6.) Finally, performance scaling using scale-out over large numbers of
> replicas.  I think it’s possible to get tunnel vision on this—it’s not
> a big requirement in the PG community because people don’t use PG in
> the first place when they want to do this.  They use MySQL, which has
> very good replication for performance scaling, though it’s rather weak
> for availability.  

Again, doing scale-out over large number of replicas should either be
async or for sync use some broadcast channel to all slaves (and still be
a performance problem on master, as it has to wait for slowest slave).

> As a consequence, I don’t see how you can get around doing some sort
> of row-based replication like all the other databases. 

Is'nt WAL-base replication "some sort of row-based replication" ?

>  Now that people are starting to get religion on this issue I would
> strongly advocate a parallel effort to put in a change-set extraction
> API that would allow construction of comprehensive master/slave
> replication. 

Triggers. see pgQ's logtrigga()/logutrigga(). See slides for Marko
Kreen's presentation at pgCon08.

>  (Another approach would be to make it possible for third party apps
> to read the logs and regenerate SQL.) 

which logs ? WAL or SQL command logs ?

> There are existing models for how to do change set extraction; we have
> done it several times at my company already.  There are also research
> projects like GORDA that have looked fairly comprehensively at this
> problem.

pgQ with its triggers does a pretty good job of change-set extraction.

------------------
Hannu