Dumping and restoring Postgres databases

Some quick notes for reference and how to avoid a common pitfall.

Dump existing database:

$ pg_dump -U USERNAME -h HOSTNAME DATABASE_NAME > dump.sql

Drop new database that you want to replace:

$ psql -h localhost -U postgres -c 'drop database DATABASE_NAME;'

You may get the following error:

ERROR:  database "DATABASE_NAME" is being accessed by other users
DETAIL:  There is 1 other session using the database.

If so, terminate the sessions and try deleting the database again with the following:

$ psql -h localhost -U postgres
postgres=# SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'DATABASE_NAME';
# Quit psql console
$ psql -h localhost -U postgres -c 'drop database DATABASE_NAME;'

After that, create the new database:

$ psql -h localhost -U postgres -c 'create database DATABASE_NAME;'

And load the dump into the new database:

$ psql -h localhost -U postgres DATABASE_NAME < dump.sql