TCP proxy to postgres database as an upstream server in nginx

Question

Question: Is it possible to set Nginx as a reverse proxy for a database? These are the flags I have at the moment and I believed that having the --with-stream module was sufficient to use TCP streams to the database. Is this a PLUS feature?

Nginx configuration options:

--prefix=/etc/nginx --sbin-path=/usr/sbin/nginx --modules-path=%{_libdir}/nginx/modules --conf-path=/etc/nginx/nginx.conf --error-log-path=/var/log/nginx/error.log --http-log-path=/var/log/nginx/access.log --pid-path=/var/run/nginx.pid --lock-path=/var/run/nginx.lock --http-client-body-temp-path=/var/cache/nginx/client_temp --http-proxy-temp-path=/var/cache/nginx/proxy_temp --http-fastcgi-temp-path=/var/cache/nginx/fastcgi_temp --http-uwsgi-temp-path=/var/cache/nginx/uwsgi_temp --http-scgi-temp-path=/var/cache/nginx/scgi_temp --user=nginx --group=nginx --with-http_ssl_module --with-http_realip_module --with-http_addition_module --with-http_sub_module --with-http_dav_module --with-http_flv_module --with-http_mp4_module --with-http_gunzip_module --with-http_gzip_static_module --with-http_random_index_module --with-http_secure_link_module --with-http_stub_status_module --with-http_auth_request_module --with-threads --with-stream --with-stream_ssl_module --with-http_slice_module --with-mail --with-mail_ssl_module --with-file-aio --with-http_v2_module --with-cc-opt='-g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2' --with-ld-opt='-Wl,-z,relro -Wl,--as-needed' --with-ipv6

Nginx config

stream {

    include conf.d/streams/*.conf;
}

contents of conf.d/streams/upstream.conf

upstream database_server {
    least_conn;
    keepalive 512;
    server 192.168.99.103:32778 max_fails=5 fail_timeout=30s weight=1;

}

Error message from Nginx

2016/02/22 03:54:13 [emerg] 242#242: invalid host in upstream "http://database_server" in /etc/nginx/conf.d/streams/database_server.conf:9

Nathaniel Waisbrot Nathaniel Waisbrot · Accepted Answer · 2020-07-08T03:04:30

Here's an nginx configuration that worked for me (I'm running inside Docker, so some of these options are to help with that):

worker_processes auto;

daemon off;

error_log stderr info;

events {
    worker_connections 1024;
}

stream {
    upstream postgres {
        server my_postgres:5432;
    }

    server {
        listen 5432 so_keepalive=on;
        proxy_pass postgres;
    }
}

The key for me was the line listen 5432 so_keepalive=on;, which turns on TCP keepalive. Without that, I could connect but my connection would get reset after a few seconds.

TCP proxy to postgres database as an upstream server in nginx

2 Answers