On Tue, Nov 17, 2009 at 04:15:29PM -0800, David Kerr wrote:
> I like the idea of load balancing, but how does that work, exactly, since Slony 
> is asynchronous? 

You handle read-only and read-write queries differently.  Read-only
queries get a consistent but not necessarily current view of the
data.  So if your pattern is "update database, read results" with web
pages, it just won't work -- you need the latest version of the data.
For environments where imperfect currency is ok (think most web pages,
for instance), it'll work.  But be careful: I never got pgpool to
work well for me.

> Second for an HA solution, my real concern, if the load balancing isn't viable
> (so to speak) can you configure PGPool to point to one node and then fail over
> to another node (automatically) ? I understand that's a PGPool specific question
> but since it seems like a common config with Slony I thought i'd chance it 
> and ask here.

The question here is how much data loss you're willing to take.
Alternatively, how much read-write downtime can you stand?

If you MUST have an up-to-date view of the data, but if the read-write
node fails you can just live with reads, then you're golden.  Set up
two pools.  Pool 1 is for read-write and read, and handles all the
data under normal circumstances.  Pool 2 is a standby, and if the
database on pool 1 is lost, it takes over.  Write transactions on this
pool always fail, but it will allow you to do read-only for as long as
you still have a good database system there.  The data is current as
of the last applied snapshot (which need not be the last write action
in the data origin, but it might be good enough).

If you can stand to lose some transactions, then you can do full
automatic failover.  But careful!  That data is lost more or less
forever, so you need to be prepared for that.

A

-- 
Andrew Sullivan
ajs at crankycanuck.ca