On Thu, May 13, 2010 at 8:15 PM, Jamie Tufnell <diesql@googlemail.com> wrote:
> Hi,
>
> We have a webapp backed by a single PostgreSQL server and I'd like to
> add a second primarily for availability. I figure if we can load
> balance reads too then why not.
>
> Having read over the HA docs/matrix, I'm considering using 9.0's hot
> standby with pgpool-II to automatically balance reads without having
> to adjust our webapp to split the reads/writes.
>
> Firstly, is this a sound setup and does anyone using it have any
> comments fore/against, gotchas?
Well, it's new and shiny, and not a lot of people are running it yet
since it's still in beta. I'd suggest being a beta tester on it and
reporting problems, cause now's the time to do it.
> Secondly, is there a companion tool to achieve this with pgbouncer?
> Note I have nothing against pgpool, just curious as we're already
> using pgbouncer in production.
slony?
Both are asynchronous, so some lag is to be expected. When slony runs
out of disk bandwidth it starts to fall behind. You can put slony
sl_log_x on a separate tablespace and that helps, but that's it's
limit to keeping up. I'm not sure what latency to expect from PITR.
We get 5 to 20 seconds under heavy load with slony generally. With
occasional spikes late at night when we run backups / maintenance
processes into the 30 to 60 second range.