19

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
  • 23,261
  • 7
  • 71
  • 99
Devonte
  • 3,319
  • 5
  • 20
  • 15

2 Answers2

19

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.

Nathaniel Waisbrot
  • 23,261
  • 7
  • 71
  • 99
  • I suppose that `my_postgres:5432` stands for the docker container with the name of `my_postgres`, correct? I'm also trying this out for my docker stuff. – Pablo Alexis Domínguez Grau Feb 07 '21 at 17:44
  • Here, `my_postgres` is a name that resolves to a server listening on port 5432. If you had a docker-compose configuration, you might name your Postgres container `my_postgres` and then Docker would set up name resolution for you. But you could be outside of Docker and just using DNS. – Nathaniel Waisbrot Feb 09 '21 at 00:17
  • For me the connection times out when trying to connect to postgres from a client using this configuration. – Biel Simon May 10 '21 at 12:43
  • @BielSimon if you time out while connecting then your Nginx is unable to reach your Postgres. Probably one or both are misconfigured (maybe Postgres is not listening on the right interface, maybe Nginx is using the wrong port, etc). – Nathaniel Waisbrot May 11 '21 at 15:28
  • To those new with nginx, this code block is for ```nginx.conf``` that is usually located in ```/etc/nginx/```. Normally there is a ```http{ ...``` block but @NathanielWaisbrot adjusted it because it's for docker. – sutan Feb 22 '23 at 14:27
5

The issue was the "http://database_server" it is a tcp stream so you need to just proxy_pass database_server

also keep alive is not a directive that goes in a tcp upstream server

Devonte
  • 3,319
  • 5
  • 20
  • 15
  • technical implementation: in ```nginx.conf``` create a ```stream``` directive instead of the default ```http```. We can implement the ```proxy_pass``` in the ```stream``` block. Refer to the ```stream``` TCP load balancer [documentation](https://docs.nginx.com/nginx/admin-guide/load-balancer/tcp-udp-load-balancer/) – sutan Feb 22 '23 at 14:24