How to replicate a remote Postgres DB locally

There have been times when I've wanted an exact copy of a database locally. If I'm writing a particularly complex database migration, it's nice to have a clone of a QA environment with sample data.

This is super easy using the postgres client tools. You can stream the remote database directly into your local one!

NOTE: I always start with a fresh database locally to avoid any synchronization errors.

#!/bin/bash
set -eo pipefail
IFS=$'\n\t'

USER=postgres
HOST=localhost
PORT=5432
DB=postgres

REMOTE_DATABASE_URL=postgres://user:pass@host:port

dropdb -U $USER -h $HOST -p $PORT $DB
createdb -U $USER -h $HOST -p $PORT $DB
pg_dump --verbose --clean --no-acl --no-owner --if-exists $REMOTE_DATABASE_URL | psql 
--set ON_ERROR_STOP=on $DB -h $HOST -p $PORT -U $USER

echo "CATS: ALL YOUR *DATABASE* ARE BELONG TO US"

Now you can test your migrations over and over, resetting as you need, from the remote database!