Refreshing data on a staging server

Sometimes we want to update the database on a staging server. The actual process may differ each time, but here's a shortcut script which should cover most cases. Read on an example for reference. Read to the end for ideas to do it quicker.

Direct transfer

# 0. Grant ssh access from staging to prod
# ofn-admin@staging: ssh-keygen -t ed25519
# ofn-admin@prod: vim .ssh/authorized_keys

# 1. Stop services
# Best to ensure all jobs are cleared first
ssh [email protected]
RAILS_ENV=staging bundle exec rake ofn:reset_sidekiq

ssh [email protected]
sudo systemctl stop puma sidekiq

# 2. Transfer data from prod to staging (5min)
ansible-playbook playbooks/db_transfer.yml --limit=fr_prod -e=rsync_to=fr_staging

# 3. Clean up data on staging
ssh [email protected]
cd apps/openfoodnetwork/current
RAILS_ENV=staging bundle exec rake ofn:data:truncate # this might crash, so run it separately
RAILS_ENV=staging bundle exec rake ofn:data:anonymize

# 4. Start 'er up
sudo systemctl start puma sidekiq

# 5. Remove SSH access
# ofn-admin@prod: vim .ssh/authorized_keys

Manual method

# 1. Copy and clean up data
hostname # make sure you're in your dev env!
script/mirror_db.sh [email protected]; beep # This may take some time.

rake ofn:data:truncate ofn:reset_sidekiq
rake ofn:data:anonymize

# 2. Dump and copy to staging (approx 6min for 1.5gb)
pg_dump -h localhost -U ofn open_food_network_dev > tmp/openfoodnetwork.org.uk-anonymised.sql

# Oh, bummer I had a different username, manually fixing it..
sed  -i -e "s/ofn;/ofn_user;/g"  tmp/openfoodnetwork.org.uk-anonymised.sql

rsync -Paruvz tmp/openfoodnetwork.org.uk-anonymised.sql  [email protected]:/tmp/

# 3. Update staging
# Ensure it has latest master checked out
https://github.com/openfoodfoundation/openfoodnetwork/actions/workflows/stage.yml

# There's probably some config out of date. Review the config and update if needed:
# https://github.com/openfoodfoundation/ofn-install/blob/master/inventory/host_vars/staging.openfoodnetwork.org.uk/config.yml
# And ensure the server is up to date:
~/projects/ofn-install $ ansible-playbook playbooks/fetch_secrets.yml && ansible-playbook playbooks/provision.yml --limit=uk_staging

# 4. Import db on staging
# This playbook has some handy helpers which will save time (eg kick out remote metabase connection). 
# I skipped the copy part because I already did it:
comment out first part of `roles/db_restore/tasks/main.yml`

~/projects/ofn-install $ ansible-playbook playbooks/db_restore.yml --limit=uk_staging --tag=restore -e"database_dump_path=/tmp/openfoodnetwork.org.uk-anonymised.sql