[pgpool-general: 8381] Re: backups with pgpool

[Tatsuo Ishii]

> I have applications that are backed in postgresql that want to do backups with pg_dump. When pointing them to our pgpool cluster with load balancing turned on the pg_dump uses the load balanced node. This is expected. However the pg_dump takes longer than the postgresql WAL application timeout (max_standby_archive_delay and max_standby_streaming_delay) and the connection is closed. I see in https://www.pgpool.net/docs/pgpool-II-4.0.2/en/html/runtime-config-load-balancing.html that I can keep pgpool from sending individual queries to the load balanced node, however I don't see how to do this with pg_dump. 
> 
> My current thought is to determine the primary backend and allow the application to connect directly to it for backups. However I'd rather not let my applications connection to the postgresql backends directly.
> 
> Does anyone have suggestions on how to handle this?

I am not sure I fully understand what you want to do but I guess you
want to execute pg_dump queries only on primary node? If so, you can
use app_name_redirect_preference_list, i.e.:

app_name_redirect_preference_list = 'pg_dump:primary'

This will redirect any queries from applications that have application
name "pg_dump" to the primary PostgreSQL.

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