SSH Tunnel for PostgreSQL - Connection Refused -

i am, first time, attempting set application on remote/cloud vps (i using digital ocean if matters). attempting create ssh tunnel client remote database. not have attempted before, referenced this, this, , this.

after looking on articles, ran following on client/local machine:

 ssh -l 5433:localhost:5432 user@remote_ip 

then tried connect:

 psql -h localhost -p 5433 postgres; 

however, receive following error:

psql: not connect server: connection refused server running on host "localhost" (127.0.0.1) , accepting tcp/ip connections on port 5433? 

to knowledge, pg_hba.conf (on remote server) default:

# database administrative login unix domain socket local               postgres                                peer  # type  database        user            address                 method  # "local" unix domain socket connections local                                                   peer # ipv4 local connections: host                            127.0.0.1/32            md5 # ipv6 local connections: host                            ::1/128                 md5 

i changed "listen_addresses" in postgresql.conf *

    # - connection settings -  listen_addresses = '*'          # ip address(es) listen on;                                         # comma-separated list of addresses;                                         # defaults 'localhost'; use '*'                                         # (change requires restart) port = 5432                             # (change requires restart) max_connections = 100                   # (change requires restart) 

i tried substituting 127.0.0.1 forlocalhost no success.

any advice appreciated; ssh tunnels , not familiar with.

thanks. edit:

per @drdaeman excellent advice, ran following:

sudo ssh -n -vvv -l 5433:localhost:5432 user@host

the last few debug lines follows:

    debug1: local forwarding listening on 127.0.0.1 port 5433. debug2: fd 5 setting o_nonblock debug3: fd 5 o_nonblock debug1: channel 1: new [port listener] debug2: fd 3 setting tcp_nodelay debug3: ssh_packet_set_tos: set ip_tos 0x10 debug1: requesting no-more-sessions@openssh.com debug3: send packet: type 80 debug1: entering interactive session. debug1: pledge: network debug3: receive packet: type 80 debug1: client_input_global_request: rtype hostkeys-00@openssh.com want_reply 0 

output sudo netstat -ltpn | grep 5432

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               listen      5835/postgres  

it stops there, unresponsive commands.

thanks direction.

based on description, looks ok me - don't see problem is, commands you're running , configuration looks correct. here general steps can take diagnose issue:

first, check if postgresql server listening. on server, run this:

$ sudo netstat -ltpn | grep 5432 

(or can use ss -ltpn iproute2 instead of older netstat)

if don't see anything, means no process listening on tcp/5432. can try see if postgresql listening anywhere @ all:

$ sudo netstat -lpn | grep postgre 

if doesn't - check whenever server running (depends on os , distribution, check ps aux output first) , check server logs (probably in /var/log) if see problems there.

then, make sure don't accidentally run psql on server (when ssh, opens shell session unless specify -n flag). need run on local machine ;)

then, may consider adding -v (or -vvv) ssh command - it'll spew lot of useful debug information, e.g. normal operation looks this:

debug1: connection port 5433 forwarding localhost port 5432 requested. debug1: channel 3: new [direct-tcpip] debug1: channel 3: free: direct-tcpip: listening port 5433 localhost port 5432, connect ::1 port 60039 ::1 port 5433, nchannels 4 

if see channel 3: open failed: connect failed: connection refused instead, means postgresql had refused connection - , need check logs reasoning - possibly, after enabling log_connections , log_disconnections in config (don't forget reload configuration).