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
tokeep sync.  
- To reach better performance from apps side,  we want to use pgbounder like app to split read/write on multiple
serversinstead of single server. 
- Sometimes, there is repl lag between master and slave pg servers (e.g. backup or other issues unexpected). Is there
flexiblesolution 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.