pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAdvice on HA/LB setup for small installation
3+ messages / 3 participants
[nested] [flat]
* Advice on HA/LB setup for small installation
@ 2010-05-14 02:15 Jamie Tufnell <diesql@googlemail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Jamie Tufnell @ 2010-05-14 02:15 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Advice on HA/LB setup for small installation
@ 2010-05-14 02:45 Scott Marlowe <scott.marlowe@gmail.com>
parent: Jamie Tufnell <diesql@googlemail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Scott Marlowe @ 2010-05-14 02:45 UTC (permalink / raw)
To: Jamie Tufnell <diesql@googlemail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Advice on HA/LB setup for small installation
@ 2010-05-31 13:43 Dimitri Fontaine <dfontaine@hi-media.com>
parent: Jamie Tufnell <diesql@googlemail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Dimitri Fontaine @ 2010-05-31 13:43 UTC (permalink / raw)
To: Jamie Tufnell <diesql@googlemail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-05-31 13:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-14 02:15 Advice on HA/LB setup for small installation Jamie Tufnell <diesql@googlemail.com>
2010-05-14 02:45 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-31 13:43 ` Dimitri Fontaine <dfontaine@hi-media.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox