Postgres clone

readme.md

Scripts to locally clone a remote Postgres DB, through an SSH tunnel.

https://spin.atomicobject.com/2020/11/10/scripts-to-clone-prod-data-locally/

backup-remote-db.sh

#! /usr/bin/env bash

if [[ -z "${DB_PASSWORD}" ]]; then
    echo "Please set a DB_PASSWORD environment variable in order to connect to the RDS Database."
    echo "You may be able to retrieve it with:"
    echo "  aws secretsmanager get-secret-value --secret-id arn:aws:secretsmanager:xx:xx:xx:xx"
    exit 1
fi

if [[ -z "${DB_SSH_KEY}" ]]; then
    echo "Please set an DB_SSH_KEY environment variable to the path to the db-ssh-key.pem file. (you can find it in the team password manager)"
    exit 1
fi

export PGPASSWORD=$DB_PASSWORD
export PGUSER=pguser
export PGDATABASE=project-db

db_host=project-db-dev.xx.xx.rds.amazonaws.com
ssh_host=xx.xx.compute.amazonaws.com
ssh_user=ec2-user
db_port=5432
date=$(date '+%Y-%m-%d.%H-%M-%S')
filename=$date.dev.dump.sql

#### Establish SSH Tunnel
ssh -f -o ExitOnForwardFailure=yes -L localhost:5433:"$db_host":$db_port $ssh_user@"$ssh_host" -i "$DB_SSH_KEY" sleep 10

#### Dump DB

# The `--no-privileges` option is important to avoid an error upon restore like:
# psql:2020-10-27.22-50-38.dev.dump.sql:575: ERROR: role "rdsadmin" does not exist
#
# ( https://www.postgresql.org/docs/12/app-pgdump.html#PG-DUMP-OPTIONS )

pg_dump --no-privileges -h localhost -p 5433 --file "$filename"

locally-load-db-backup.sh

#! /usr/bin/env bash

if [ "$#" -ne 1 ]; then
    echo "Usage:"
    echo "./load-dump.sh backup.sql"
    exit 1
fi

DUMP_FILE=$1

export PGHOST=localhost
export PGPORT=5432
export PGUSER=root
export PGPASSWORD=password
DATABASE_NAME=my-project-dev

#### Terminate Connections

# This avoids errors like:
# ERROR:  database "my-project-dev" is being accessed by other users
# DETAIL:  There is 1 other session using the database.`)

psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '${DATABASE_NAME}' AND pid <> pg_backend_pid();"

#### Drop / Recreate
psql -c "DROP DATABASE IF EXISTS \"${DATABASE_NAME}\""

echo "SELECT 'CREATE DATABASE \"${DATABASE_NAME}\"' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '${DATABASE_NAME}')\gexec" | psql

#### Load from dump
psql -f "$DUMP_FILE" "$DATABASE_NAME"