Neat trick. Is this any faster than pg_dump and pg_load? Note: there's plenty of optimization you can apply to pg_dump and even use pg_restore, none applied below.
Like so:
function save_database() {
mkdir -p /tmp/database-snapshots
DATABASE_NAME=$PROJECT_NAME
pg_dump -Fc "$DATABASE_NAME" >"/tmp/database-snapshots/$DATABASE_NAME.dump"
}
function restore_database() {
DATABASE_NAME=$PROJECT_NAME
DATABASE_DUMP="/tmp/database-snapshots/$DATABASE_NAME.dump"
if [ ! -f "$DATABASE_DUMP" ]; then
echo "No dump to restore"
return
else
psql postgres -c 'SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid()'
psql postgres -c "DROP DATABASE IF EXISTS \"$DATABASE_NAME\""
psql postgres -c "CREATE DATABASE \"$DATABASE_NAME\""
pg_restore -Fc -j 8 -d "$DATABASE_NAME" "$DATABASE_DUMP"
fi
}
Oh another key downside I remembered of the template mechanism, at least with Postgres 9.6, is that you can't have any connections open to the source database when making a snapshot copy.
Like so: