use SSH Tunnel to connect remote database in PostgreSQL


Due to special circumstances, we cannot open the DB port of the remote server. so we can only use SSH to do port forwarding to access the database. However, you can use some graphical interface tools, the principle is the same. Here, I will introduce how to use the terminal link. 

1. Create the corresponding SSH configuration file ~/ssh.config, with the following content:

HostName xxx.xxx.xxx.xxx

User encore

Port 22

Compression yes

DynamicForward 9090

ServerAliveInterval 10

# Port Forwardings

LocalForward 5444 127.0.0.1:5432

As for the location of the file, you can do whatever you want, I personally place it in the root directory of my computer.

2. Next use the backend script to start the program

/usr/bin/ssh -v -N -F ~/ssh.conf -l encore xxx.xxx.xxx.xxx &>/dev/null &

3. Then you can use the database normally locally

psql -h localhost -p 5444 -d database -U user

4. If you want to know the script is running well, you can use the following command to check

ps aux | grep xxx.xxx.xxx.xxx


Hope it can help you!