SSH tunnel PSQL

Run a ssh tunnel to a database via jump / bastion host

  ssh -v -L 9999:[database host]:5432 jumphost

  psql -h localhost -p 9999 -U [user] --password -d [database]

If you get:

  psql: received invalid response to SSL negotiation: H

Maybe there's something already listening on localhost port 9999

Make a database dump:

  pg_dump -h localhost -p 9999 -U [user] --password  -d [database] --format=c --file=[snapshot filename]

And restore locally

  pg_restore --no-owner -C -d postgres [snapshot filename]

Disconnect other sessions with a database:

  select pg_terminate_backend(pg_stat_activity.pid) from pg_stat_activity where pg_stat_activity.datname = 'database name' and pid <> pg_backend_pid();

A bash function in case this happens a lot

function tunnelling_psql() {
  TUNNEL=${1:-9999:postgres_hostname:5432}
  REMOTE=${2:-jump_user@jump_hostname}

  ssh -nNT -L "$TUNNEL" "$REMOTE" &
  SSH_PID=$!
  sleep 1
                                                                                     
  export PGUSER=user
  export PGPASSWORD=password
  export PGDATABASE=database

  LOCAL_PORT=$(cut -d':' -f1 <<< $TUNNEL)

  psql -p "$LOCAL_PORT"
  kill $SSH_PID
}

carld/psql-ssh-tunnel.md