Re: Replication

On Mon, Jun 22, 2009 at 9:43 PM, Craig
Ringer<craig@postnewspapers.com.au> wrote:
> 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.

pgpool can work two ways really.  One way has a single write master,
then you use something like slony to replicate to slaves, and pgpool
can then read from all slaves, or it can do sync write and read to all
machines.

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

Exactly.

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

Later versions fixed that bug I believe, but we're still on 1.2, not 2.0.

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

What the developers were doing was this:

drop table user_widgets;
create table user_widgets ...

i.e. they were using drop / create table where a simple alter table
add column would suffice.  That is definitely a bad habit in my book.
And keep in mind, the developers never got to run ddl against the
production db, they run it against the dev db, put it into the db dev
notes and then I collect them all and run them in the proper sequence
in slony.  Pain in the butt, but it gets the job done and works well
under heavy daily load.