Обсуждение: Advice on HA/LB setup for small installation

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?

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.

Thanks!
J

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.

Jamie Tufnell <diesql@googlemail.com> writes:
> 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.

If you mean distributing the read-only queries to more than one server
backend, you can use haproxy in TCP mode for that. Application talks to
pgbouncer, which talks to haproxy, which connects to PostgreSQL.

Please note that nothing there will triage the read-only queries apart
from your application, and the hot-standby PostgreSQL (which will error
out).

Regards,
--
dim