Database migration testing

Investigate any issues and document process of migrating existing database to new deployment

added to epic &1 (closed)

Looks like the SQL dump will at least need s/assets_asset/iar_asset/g

Once 'staging' instance up test moving from iar-test to it.

changed milestone to %Sprint 92

added 1 deleted label

changed time estimate to 1d

added spike workflowSprint Ready labels and removed 1 deleted label

added Operations label

assigned to @mb2174

added workflowIn Progress label and removed workflowSprint Ready label

added workflowSprint Ready label and removed workflowIn Progress label

added workflowIn Progress label and removed workflowSprint Ready label

changed milestone to %Sprint 93

marked this issue as related to frontend#174 (closed)

Prioritising https://gitlab.developers.cam.ac.uk/uis/devops/iam/card-database/card-api/-/issues/5 over this. I'm still assigned as I intend to return to it when I can. However feel me to grab it if you are looking for something to do.

added workflowSprint Ready label and removed workflowIn Progress label

added workflowIn Progress label and removed workflowSprint Ready label

assigned to @rjw57

unassigned @mb2174

I've had a prod and managed to restore to the devel instance using the following procedure.

Firstly, in a separate terminal, run a local Postgres database which one can restore the current IAR backup into:

docker run --rm --name iar-restore postgres

Go to the IAR backup bucket and download the latest SQL snapshot of iar-backend. Copy the backup into the container:

docker cp iar-backend_*.sql.gz iar-restore:/tmp/dump.sql.gz

Restore the dump into the database:

docker exec iar-restore sh -c 'gunzip -c /tmp/dump.sql.gz | psql -U postgres -f -'

(Ignore any errors about non-existent roles.)

Dump the asset, user and audit tables into a new SQL file renaming the asset table on the way:

docker exec iar-restore sh -c \
  'pg_dump -U postgres --data-only --table assets_asset --table auth_user --table automationcommon_audit | sed -e "s/assets_asset/iar_asset/" >/tmp/assets.sql'

Copy the SQL dump out of the container:

docker cp iar-restore:/tmp/assets.sql .

You can now kill the database container:

docker kill iar-restore

Prepare a SQL file to restore the database:

-- Write this file to "restore.sql" in the same directory as the "assets.sql" file.
DELETE FROM social_auth_association;
DELETE FROM social_auth_code;
DELETE FROM social_auth_usersocialauth;
DELETE FROM iar_asset;
DELETE FROM auth_user;
DELETE FROM automationcommon_audit;
\include assets.sql

Now follow the instructions in the IAR Readme to connect to the production database. As a reminder, firstly start a Cloud SQL proxy:

cloud_sql_proxy -instances $(logan --nopull --quiet --notty terraform output sql_instance_connection_name)=tcp:5432

Open the webapp settings secret and copy the webapp database user password.

Run the restore script against the database using a transaction to ensure atomic behaviour and pasting the webapp database user password when asked.

psql -h localhost -U webapp -1 -f restore.sql

I'll restore to production and staging once https://gitlab.developers.cam.ac.uk/uis/devops/iar/webapp/-/merge_requests/23 is merged and deployed.

added workflowReview Required label and removed workflowIn Progress label

mentioned in merge request !23

changed milestone to %Sprint 94

changed milestone to %Sprint 95

This is done now

closed

added workflowDone label and removed workflowReview Required label