When Server_A runs PostgreSQL but sits behind a private network, while Server_B has public access, the standard port forwarding technique works like this:

ssh -L 54320:Server_A:5432 user@Server_B

Then connect locally using:

psql -p 54320 -d db_name -U user -h localhost

Attempting to forward the default PostgreSQL port directly often triggers this error because the local PostgreSQL client defaults to Unix socket when no host is specified:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

When you need to exactly replicate the network environment where applications connect using Server_A's hostname, try these methods:

1. Local Hosts File Modification

# /etc/hosts entry (Linux/Mac)
127.0.0.1   Server_A

# Then run:
ssh -L 5432:Server_A:5432 user@Server_B

2. Double SSH Tunneling

# First hop to Server_B
ssh -L 2222:Server_B:22 user@Server_B

# Second hop from local port 5432 through Server_B to Server_A
ssh -p 2222 -L 5432:Server_A:5432 user@localhost

For complex network environments where multiple services need access:

ssh -D 1080 user@Server_B

# Configure psql to use SOCKS proxy (via ~/.pgpass or ENV variables)
export PGHOST=Server_A
export PGPORT=5432
psql -d db_name -U user

• Verify PostgreSQL's pg_hba.conf allows connections from Server_B
• Check postgresql.conf for listen_addresses = '*'
• Test basic connectivity: ssh user@Server_B "nc -zv Server_A 5432"
• When using hostname resolution, verify DNS or hosts file entries

Remember that SSH tunnels can introduce latency - for production environments, consider VPN solutions or SSHuttle for better performance.

When working with database servers in restricted network environments, we often encounter scenarios where direct access isn't possible. The typical setup involves:

• Server_A: Hosts PostgreSQL (port 5432) but sits behind a private network
• Server_B: Accessible from outside networks and can reach Server_A

The standard method for accessing PostgreSQL through Server_B works perfectly:

ssh -L 54320:Server_A:5432 user@Server_B

Then in another terminal:

psql -p 54320 -d db_name -U user

When attempting to forward directly to port 5432 locally, PostgreSQL clients can get confused between the local instance and forwarded connection. Here's how to resolve it:

# Use a different local port to avoid conflict
ssh -L 5432:Server_A:5432 -N user@Server_B

# Then connect using either of these:
psql -h localhost -p 5432 -d db_name -U user
# OR
psql -h 127.0.0.1 -p 5432 -d db_name -U user

When you need to make the connection appear as if it's coming directly from Server_A's hostname:

# First method: Add to /etc/hosts
127.0.0.1 Server_A

# Then forward using:
ssh -L 5432:Server_A:5432 user@Server_B

# Alternative method using remote forwarding (if Server_B has SSH access to your machine)
ssh -R 5432:localhost:5432 user@Server_B

For more robust solutions:

# Persistent SSH tunnel with autossh
autossh -M 0 -f -N -L 5432:Server_A:5432 user@Server_B

# Using SSH config for simplified management
Host pg_tunnel
  HostName Server_B
  User remote_user
  LocalForward 5432 Server_A:5432
  ServerAliveInterval 60
  ServerAliveCountMax 3

• Check PostgreSQL's pg_hba.conf to ensure it allows connections from Server_B
• Verify Server_B can resolve Server_A's hostname
• Ensure no local PostgreSQL service is running that might conflict with port 5432
• Consider using a different local port if conflicts persist