[OPEN-ILS-GENERAL] Simple database replication for PostgreSQL 9.0

[Dan Scott]

If you are running PostgreSQL 9.0 (and if you're on Evergreen 2.0 or
above, you probably should be - even though Evergreen 2.0 complains
about not finding a 9.0-specific schema, it just works)

... and you want a simple recipe for a replicated hot standby database
server (so that you can point your reporter at it and run big scary
queries that would otherwise bog down your production database server)

... then the following instructions for setting up PostgreSQL 9.0's
built-in binary replication might be for you:
http://bzr.coffeecode.net/replication9/postgresql-9-replication.html
(the article is CC-BY-SA 3.0 Unported, hi DIG!)

I found most of the tutorials for setting up replication with
PostgreSQL 9.0 include WAL archiving / shipping as well. While you
definitely want to set up WAL archiving / shipping to support
point-in-time-recovery (a hot standby server isn't going to help you
very much if someone issues a DROP TABLE biblio.record_entry CASCADE
on your production server and you don't have logs that you can use to
recover to just before that statement was issued), I thought it would
be helpful to distil the replication instructions down to the bare
minimum. As far as I can tell, PostgreSQL 9.0's streaming replication
avoids any of the weird obstacles that Slony throws in your path - so
for many Evergreen sites, it should be a big win.

Of course, if other sites have already experimented with PostgreSQL
9.0 replication and have any further advice (or warnings!) to offer,
it would be great to hear from you.