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
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_NAME=${PGDATABASE}-`date +%d-%m-%y`.dump
pg_dump --file ${DUMP_NAME} --format=custom
pg_restore --verbose --clean --no-acl --no-owner --dbname ${PGDATABASE} ${DUMP_NAME}
ssh -S ${SOCKET_PATH} -O exit ${USER}@${HOST}