[clug] [tech] Postgres Replication

Steve McInerney steve at stedee.id.au
Sun Sep 20 23:52:36 MDT 2009


On Fri, 2009-09-18 at 08:48 +1000, steve jenkin wrote:
> 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.
> 
> Reading the Postgres docs has been less than enlightening.
> It seems Replication is provided by 3rd-party modules - and not nearly
> so well done.

The others have mentioned slony, adding an additional "me too" there;
Just some gotchas to be aware of:

* the application should be able to handle the DB going away underneath
it. Not imho ideal, but it seems to work better if it can. ie fail to a
ro replica; or use a pool of ro replicas for select's, and only use the
master for updates; type of thing. Such that in a worst case, you can
still function - albeit update crippled.

* watch out for replication lag. We can and do get very large delays
when massive transaction updates go thru.
Again, this can be best handled in the application layer - ie if lag() >
5 secs; stick with master, type of thing. Was and is rare, but was also
a real problem for us when replication was introduced.


Otherwise slony "just works", quick & pain free - mostly - compared to
my prior experience with doing similar under Oracle 8i and 10g.


Cheers!
- Steve




More information about the linux mailing list