Skip to content

Instantly share code, notes, and snippets.

@jrr
Last active April 18, 2022 13:12
Show Gist options
  • Save jrr/cc6feb5ed9f62aeffbcac3795edb0dd1 to your computer and use it in GitHub Desktop.
Save jrr/cc6feb5ed9f62aeffbcac3795edb0dd1 to your computer and use it in GitHub Desktop.
Postgres clone
#! /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"
#! /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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment