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]'
    );
"