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

[Jon SCHEWE]

>> 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.

That sounds like it will do what I want.
Thank you.