Connect to PostgreSQL on a VPS through an SSH tunnel

Port 5432 on a VPS is usually blocked by the firewall (as it should be), so your dev machine cannot connect to the database directly. An SSH tunnel solves that: it maps a port on your local machine to port 5432 on the VPS over an encrypted SSH connection. This guide shows how to open the tunnel and how to set up .env for each environment (local / production).

Throughout, replace <username> with your SSH user on the VPS, <server-ip> with the VPS IP, and <db_user>, <password>, <database_name> with your database details. ~/.ssh/id_ed25519 is the path to your SSH private key - change it if yours has a different name.

Quick reference

Local - SSH tunnel:

# Run in a separate terminal and keep it open for the whole work session
ssh -L 5432:localhost:5432 <username>@<server-ip> -i ~/.ssh/id_ed25519 -N

# .env.local
DATABASE_URL=postgresql://<db_user>:<password>@localhost:5432/<database_name>

Production (deployed on the same VPS as the database):

# .env.production
DATABASE_URL=postgresql://<db_user>:<password>@localhost:5432/<database_name>

1. Understand the connection model

+------------------+          SSH Tunnel           +------------------+
|   Local machine  | ---- port 5432 -------------> |   VPS            |
|   (dev machine)  |  ssh -L 5432:localhost:5432   |   PostgreSQL     |
|                  |                               |   :5432          |
+------------------+                               +------------------+

• Local: the dev machine cannot reach the database on the VPS directly (port 5432 is normally firewalled) -> use an SSH tunnel to map local port 5432 to port 5432 on the VPS.
• Production: the backend is deployed on the same VPS as the database -> use localhost directly.

2. Open the SSH tunnel for local dev

Open a separate terminal and run:

ssh -L 5432:localhost:5432 <username>@<server-ip> -i ~/.ssh/id_ed25519 -N

3. Configure .env.local for local dev

DATABASE_URL=postgresql://<db_user>:<password>@localhost:5432/<database_name>

Because the tunnel maps localhost:5432 to the VPS, the backend connects as if the database were running on your local machine.

4. Configure .env.production for a same-VPS deploy

DATABASE_URL=postgresql://<db_user>:<password>@localhost:5432/<database_name>

When the backend is deployed on the same VPS as PostgreSQL, localhost points straight at the database on that machine - no tunnel and no public IP required.

5. Advanced: run the tunnel in the background

If you would rather not keep a terminal open, run the tunnel in the background:

ssh -f -N -L 5432:localhost:5432 <username>@<server-ip> -i ~/.ssh/id_ed25519

To stop a background tunnel (macOS / Linux):

# Find the PID
ps aux | grep "ssh -f -N -L"

# Kill the process
kill <PID>

On Windows (PowerShell):

# Find the PID
Get-Process ssh | Where-Object { $_.CommandLine -like "*5432*" }

# Kill the process
Stop-Process -Id <PID>

6. Advanced: handle a port conflict

If a local PostgreSQL is already running on port 5432, give the tunnel a different local port:

ssh -L 5433:localhost:5432 <username>@<server-ip> -i ~/.ssh/id_ed25519 -N

Then update .env.local:

DATABASE_URL=postgresql://<db_user>:<password>@localhost:5433/<database_name>

The opposite approach - keep the tunnel on 5432 and move the local PostgreSQL to another port - is covered in Clone a PostgreSQL database from a VPS to local Windows.

Troubleshooting

The full command for a stable tunnel:

ssh -L 5432:localhost:5432 <username>@<server-ip> \
  -i ~/.ssh/id_ed25519 \
  -N \
  -o ServerAliveInterval=60 \
  -o ServerAliveCountMax=3