Keeping postgres connection behind load balancer alive during long-running query (vacuum) Background:
I have a postgres instance behind a load balancer (AWS Classic Load Balancer). I want to access the database from another cluster through the load balancer and run queries. I cannot change the database setup (instance behind load balancer).
When executing long-running queries (VACUUM in my case), the connection is dropped by the load balancer after 1 hour. This happens because the connection is inactive and the load balancer terminates it.
tcp_keepalives_idle does not work here and the load balancer checks for actual transmitted data, not just packets (see the update on this question)
Problem:
How can I keep the connection alive while the VACUUM is running?
Is there a way to send data over the connection to indicate that it is still alive?
There is no way to have activity on a database connection that is waiting for a server response.
I'd say that that is a bug in the load balancing software, and you should report it. Your use case is a valid one.