[clug] [tech] Postgres Replication

[Daniel Pittman]

steve jenkin <sjenkin at canb.auug.org.au> writes:

> I'll be looking after some small MySQL and Postgres DB's and I'd like to run
> replication, if I can. There are some useful things to be done with a
> 'read-only' replica running on another system. And it gives us a reasonable,
> not instantaneous, 'fail-over' option.
>
> Have read the MySQL doco, and it seems very sophisticated & is built-in.

Yeah, it sounds like that.  Be aware that earlier versions of MySQL only have
statement-level replication, not row-level, so this statement is going to
break, by giving you divergent data across the servers:

    INSERT INTO example (time, ...) VALUES (NOW(), ...)

(Actually, I think they special-cased NOW(), but the general principal
 stands as a risk of statement-level replication.)

My personal experience is that it is not very robust otherwise, since we had
regular problems with statements getting lost in replication, so divergence
between the databases, even on local links.

> Reading the Postgres docs has been less than enlightening.  It seems
> Replication is provided by 3rd-party modules - and not nearly so well done.

Not so integrated, anyhow.  Part of this is that replication is extremely
hard, and the easy statement-level replication actually has more risks of
divergence.

[...]

> Anyone know of any better resource?  Anyone actually had to *do* either
> clustering or replication with Postgres (or can dob in a friend).

Yes: use slony1, which just works(tm).

> Do you know any alternative means?
> [eg Simplistic rsync copy of DB files or GFS or ...]

For local replication, use DRBD, or build the equivalent yourself from MD and
NBD.  It provides a "share nothing" disk that one machine can have read/write
access to, and in which writes are replicated across to the other machine.

DRBD provides the correct semantics for write completion, so you can safely
use it under a database and be confident that fsync means what the database
thinks it means.

We use this extensively for this sort of thing, and it works just fine.
Fail-over is, essentially, a "recover from a hard machine hang" situation,
which both DBMS implementations do just fine.

Regards,
        Daniel
-- 
✣ Daniel Pittman            ✉ daniel at rimspace.net            ☎ +61 401 155 707
               ♽ made with 100 percent post-consumer electrons
   Looking for work?  Love Perl?  In Melbourne, Australia?  We are hiring.