Timeout issues using NGINX as a Postgres Proxy

Question

I'm setting up a proxy server to intercept and forward all traffic to my Postgres database. I am using NGINX 1.19.2 to accomplish this.

The problem I have is that any queries running more than 10 minute time out. All shorter queries seem to run just fine.

Here is my entire nginx.conf:

worker_processes auto;
pid /run/nginx.pid;

include /etc/nginx/modules-enabled/*.conf;

events {
  worker_connections 768;
}

stream {
  server {
    # Allow up to 30s to establish a connection with the proxied database
    proxy_connect_timeout 30s;

    proxy_socket_keepalive on;

    listen 5432;
    proxy_pass my-server.rds.amazonaws.com:5432;
  }
}

I have tried several of the NGINX proxy-related configuration options (eg. proxy_read_timeout), however, those don't work inside of a stream { } context and need to be in an http { } context. For the purpose of connecting to Postgres, I cannot use http.

• Does anyone know what I can do to remove this 10 minute connection timeout with NGINX stream/proxy?
• Does anyone know a better alternative to accomplish what I'm trying to do that uses something other than NGINX?
  • I tried simpleproxy on Ubuntu, but that times out after 2 hours (yes, we have to support some long running queries, eg. VACUUM operations on billion+ row tables)

Answer 1

Do you see any errors in /var/log/nginx/error.log that relate to these requests?

If you see Connection reset by peer that would mean your upstream (Postgres) is closing the connection, not Nginx.

Also, are you sure it's not your downstream? Do you connect directly to this server or is there a Load Balancer -> Nginx -> Postgres? The Load Balancer could be terminating the connection.

I would try setting all the timeout settings absurdly high, they would go inside the server {} context

proxy_cache_lock_timeout
proxy_connect_timeout
proxy_next_upstream_timeout
proxy_read_timeout
proxy_send_timeout

client_body_timeout
client_header_timeout
keepalive_timeout
lingering_timeout
resolver_timeout
send_timeout

# Not sure if you're using fastcgi
fastcgi_cache_lock_timeout
fastcgi_connect_timeout
fastcgi_next_upstream_timeout
fastcgi_read_timeout
fastcgi_send_timeout

See docs for explanation of each:

• http://nginx.org/en/docs/http/ngx_http_proxy_module.html
• http://nginx.org/en/docs/http/ngx_http_core_module.html
• http://nginx.org/en/docs/http/ngx_http_fastcgi_module.html

For things like VACUUM the connection might terminate but the process may still be running. You could check running processes in Postgres to confirm when it's completed.