Re: About postgres scale out

On Wed, Jul 17, 2013 at 03:10:37PM +0800, Xiang Jun Wu wrote:
- Hello,
-
- I'd like to ask a common question about scale out for postgres.
-
- Our current data volume is about 500GB ~ 1TB in one pg cluster(postgres 9.2). We've set up master/slave replication to keep sync.
- To reach better performance from apps side, we want to use pgbounder like app to split read/write on multiple servers instead of single server.
- Sometimes, there is repl lag between master and slave pg servers (e.g. backup or other issues unexpected). Is there flexible solution from pgbounder (or other ways) to identify that and switch read/write to not staled server smartly?
-
-
- Thank you in advance!

PgPool does this for you.

http://www.pgpool.net/docs/latest/pgpool-en.html#replication_mode
---
delay_threshold V3.0 -
Specifies the maximum tolerated replication delay of the standby against the primary server in WAL bytes. If
the delay exceeds delay_threshold, pgpool-II does not send SELECT queries to the standby server anymore.
Everything is sent to the primary server even if load balance mode is enabled, until the standby has
caught-up. If delay_threshold is 0 or sr checking is disabled, the delay checking is not performed. This
check is performed every 'sr_check_period'. The default value for delay_threshold is 0.
You need to reload pgpool.conf if you change this directive.