[pgpool-general: 8517] Re: Can pgpool load balance across read-write nodes?

[Tatsuo Ishii]

> Hi;
> 
> I have an architecture where I am using Foreign Data Wrappers to scale
> 
> So I have a read/write db (server a) and it has 2 schemas in it, schema 1 has a number of foreign tables to server b and schema 2 has a
> number of foreign tables pointing to server c
> 
> for 'writes' the app can simply connect to server a and the FDW's take care of where the data lives so server a is sort of a 'federated'
> server
> 
> I also want to load balance reads, so I have 2 Hot Standby's for server b and 2 Hot standby's for server c
> 
> Then I created 2 new 'federated' servers and these servers have Foreign servers defined that connect to the Hot standby nodes,
> something like this
> 
> *
> 
> Question:
> 
> Is it possible to have pgpool send all writes to server a and then load balance reads across servers e and h (note that e and h are
> actually read/write clusters)?

Yes, possible.

- Set backend_clustering_mode = 'streaming_replication'.

- Set node 0 to server a with backend_flag0 =
  'DISALLOW_TO_FAILOVER|ALWAYS_PRIMARY'.  This will prevent failover
  when server a goes down. Also you might want to set backend_weight0
  = 0 to prevent ready queries from being routed to server a.

- Set node 1 to server e. You might want to set backend_flag1 = 'DISALLOW_TO_FAILOVER'

- Set node 2 to server h. You might want to set backend_flag2 = 'DISALLOW_TO_FAILOVER'

- Optionally you might want to set sr_check_period = 0 so that the
  streaming replication check (checking server role and replication
  delay).

In this case data in a, e and h are not synced. I am not sure if that
is what you want.

If you want to sync a, e and h, you can set backend_clustering_mode =
'snapshot_isolation'. However there are some restrictions applied. See
the manual for more details.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp