[clug] [tech] Postgres Replication

[Brendan Jurd]

2009/9/18 steve jenkin <sjenkin at canb.auug.org.au>:
> 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.
>

This feature (read-only replica) is currently called "Hot Standby" in
Postgres, and it's being actively developed right now.  It almost made
it into 8.4 (in fact the reason it took so long for 8.4 to come out
was that they kept delaying in the hopes that Hot Standby would make
it in) but it had to be punted to 8.5 because there were a few
outstanding design questions.

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

Yep.  The most commonly used replication tool for Postgres is Slony:
http://www.slony.info/.  This may or may not be what you need -- it's
async multi-slave replication powered by triggers on the master.

The Postgres project is keenly aware that this is one area where MySQL
has at least the appearance of being in the lead.  Although to be
honest given what I know about MySQL I wouldn't trust its replication
as far as I could throw a 4U server.

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

Yep.  I had a requirement to replicate some of the tables in a
database to a remote server, but Slony wasn't *quite* appropriate for
my situation.  So I wrote my own solution for async one-way
replication using triggers and a little Python daemon.  It really
wasn't all that difficult. The daemon is a whole 394 lines of code
(comments and whitespace included).  My solution is not especially
powerful, in particular it doesn't have any tricksy failover features,
but it has been doing its job very well for several years now.  I'm
afraid I'm not at liberty to disclose the source code, but if you want
to know more I can go into detail on how it works.

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

Doing a naive filesystem-level copy of a running Postgres cluster won't work.

You can get a "warm standby" by what's called "WAL shipping".  See
http://www.postgresql.org/docs/current/static/warm-standby.html

Basically you take a snapshot of the database cluster at a particular
point in time and load that onto your slave server.  Then you
regularly rsync across the WAL transaction log files to your slave.
If the master fails and you want to bring up the slave, you start up
Postgres on the slave, it replays all the transactions found in the
WAL logs and once it has done so you have an exact replica of the
master as at the most recent rsync of the WAL files.

Probably not what you were looking for, but it is a pretty cool way of
doing backups because it substantially reduces your recovery time in
the event the master does die.

Cheers,
BJ