reset-postgres-db

#!/usr/bin/env bash

# This kills all connections to a database a resets it based
# on a backup file. The first argument passed should be the
# name of the database. The second should be the complete
# filepath for the backup. The backup should have already been
# compressed using the gzip utility. If you are working on a
# remote database set the environment variables recognized by
# psql
#
# http://www.postgresql.org/docs/9.5/static/libpq-envars.html
#
# e.g.
# ./resetdb.sh test-db /home/backup.gz

read -d '' KILLQUERY << EOD
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity WHERE datname = current_database()
AND pid <> pg_backend_pid();
EOD

(psql $1 -c "$KILLQUERY" && dropdb $1) || :

createdb $1

gunzip -c $2 | psql $1

I spent a lot of time trying to figure out the best way to reset my test database to a consistent state in between each test. I was eventually able to boil it down to these 10 lines of code.