Remote PostgreSQL database without public access connection and maintenance

db.md

Connect

DBHOST value must be localhost if db server deployed on HOST or RDS instance hostname if you need to use bastion(HOST) to assess db server deployed in the private subnet.

SOCKET_PATH=/tmp/%r@%h-%p
USER=<ssh user>
HOST=<host to connect>
DBHOST=<db host>
export PGPORT=<local port>
ssh ${USER}@${HOST} \
  -o StrictHostKeyChecking=no \
  -o UserKnownHostsFile=/dev/null \
  -o IdentitiesOnly=no \
  -S ${SOCKET_PATH} \
  -f -N -L ${PGPORT}:${DBHOST}:5432

Check connection

export PGUSER=<db user>
export PGDATABASE=<db name>
export PGPASSWORD=<db user password>
export PGHOST=localhost
psql

press ^D to exit from database shell

Dump database

DUMP_NAME=${PGDATABASE}-`date +%d-%m-%y`.dump
pg_dump --file ${DUMP_NAME} --format=custom

Restore database

pg_restore --verbose --clean --no-acl --no-owner --dbname ${PGDATABASE} ${DUMP_NAME}

Close SSH connection

ssh -S ${SOCKET_PATH} -O exit ${USER}@${HOST}