Re: Replication - Mailing list pgsql-general

On Mon, 2009-06-22 at 21:12 -0600, Scott Marlowe wrote:
> On Mon, Jun 22, 2009 at 8:59 PM, Craig
> Ringer<craig@postnewspapers.com.au> wrote:
>
> > So ... it doesn't seem likely that statement-level replication would
> > ever get far in Pg because of nasty issues like this one.
>
> It's exactly what pg_pool does, and you can choose it if you know what
> you're doing.  But yes, it's usually a bad fit for replication by
> itself.

Ah - I didn't realise that pg_pool did statement-based inter-node
replication; I was under the impression that it was primarily for
distribution of read-only queries between multiple clone nodes. Am I
confusing it and pgbouncer? I haven't had any need for tools intended to
scale up client counts, and haven't looked into them much.

In any case, I have a _bit_ less of a problem with the notion of
statement-level replication when it's add-on software rather than a core
part of the DB. To me, it seems like you should be able to trust the DB
to just get it right, and not have to worry about whether something's
safe with replication, etc.

If you have some kind of add-on or intermediary messing with things, at
least it's clear where responsibility lies - with the admin and the
add-on. The DB just does what it's told, consistently and just like
normal. Even so, I'm still very definitely not a fan of something that
can essentially cause silent data corruption as a result of minor
oversights in statement design.

I'm a bit leery of Slony for related reasons - because it messes with
the way Pg works to the point where, as you noted, you can't even drop
tables. Clients have to be very wary of the replication system's quirks
and issues, which I intensely dislike.

I know all these things, like MySQL's statement-level replication, have
their uses and have real advantages in situations where it's worth
trading coding pain and admin time for performance (to the point where
they may in some situations be BETTER than clustering / mm replication),
but I do think they're still VERY ugly. I personally really wouldn't
want to see statement level replication in particular in the core.

As you can probably imagine, I'm really rather excited about the coming
work on synchronous block-level replication to a read-only slave. I'd
also be pretty happy to see DDL triggers and/or triggers on system
tables to allow things like Slony to be a bit less ugly to work with.

> Once I told the developers not to drop tables in order
> to change them, things got better.  Really it was bad habits learned
> from other dbs.

I can understand working around such limitations, but it seems a stretch
to call use of the DB's normal capabilities "bad habits". Well, unless
you mean people using DROP TABLE / CREATE TABLE instead of just using
ALTER TABLE to do the job - but even then, there are times when a drop
and re-create is the preferable option in the absence of external
limitations like Slony.

--
Craig Ringer