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!