Postgres download prod, restore dev
I restore my development database from a production copy daily using these bash scripts. They depend on standard Unix tools, Postgres, and Crunchy Bridge CLIs. They can be customized per-project for pre- and post-processing.
The db-download-prod
script
downloads to the tmp/latest.backup
file on my filesystem:
mkdir -p tmp
pg_dump -Fc "$(cb uri app-production)" > tmp/latest.backup
I restore tmp/latest.backup
and post-process in
the db-restore-dev
script:
db="app_development"
dropdb --if-exists "$db"
createdb "$db"
psql "$db" -c "
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS plpgsql;
"
pg_restore tmp/latest.backup --verbose --no-acl --no-owner --dbname "$db"
psql "$db" -c "
UPDATE ar_internal_metadata
SET value = 'development'
WHERE key = 'environment';
-- Avoid re-running incomplete jobs
DELETE FROM jobs
WHERE status IN ('pending', 'started');
-- Avoid emailing production users
UPDATE users
SET active = false;
-- Turn on flags for developers
UPDATE
users
SET
active = true,
admin = true
WHERE
email IN (
'[email protected]',
'[email protected]'
);
"