[pgpool-general: 9214] Re: Load balancing of write queries among health-checked nodes

[Tatsuo Ishii]

> Hi,
> 
>> Hello. I have a PostgreSQL high availability cluster with Pgpool-II, but I don't use the virtual IP feature so my clients don't know which node to send queries to. DNS round-robin is not a solution because it can't distinguish between healthy and dead nodes.
>> 
>> I thought about having a Pgpool-II instance on each client (client Pgpool-II -> cluster Pgpool-II -> PostgreSQL), but AFAIK it can't distribute write queries.
> 
> I am not sure I fully understand your requirement but I think you can
> do something like:
> 
> client-A -> pgpool-A -+
>                       |
> client-B -> pgpool-B -+- PostgreSQL (primary and standby)
>                       |
> client-C -> pgpool-C -+
> 
> Here client-A, client-B and client-C are PostgreSQL compatible
> clients, for example psql.
> 
> pgpool-A, pgpool-B and pgpool-C are pgpool cluster with watchdog configured.
> client-A, client-B and client-C can send read or write queries to
> pgpool-A, pgpool-B or pgpool-C respectively.
> 
> One downside of the configuration is, if pgpool-A goes down, client-A
> cannot send queries to pgpool-A. client-A has to switch the connection
> to pgpool-B or pgpool-C if it wants to continue processing.

To mitigate the problem, you could specify multiple host in your client. i.e.:
psql "host=A,B port=9999,9999 dbname=test" for client-A. This will try
A first but if A is down, it will try B next.

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