database surgery.

[fgregg]

Hi @jeancochrane I mentioned that you'll need to do a little database surgery on production when the time comes, so might as well bring those changes in now.

The migrations, used to drop the "value_id" column from a variety of tables using raw sql. This caused running of the migrations to fail when building from scratch. I fixed this up, but the staging and production DB the current migrations will not work on production without some tweaks. Here's what I did.

Before running the migrations: alter table X add value_id text;

I'm very sorry, I didn't keep the list of tables in a safe place (i had them in a sql file on the staging server and they got overwritten on the next deploy).

Also, I had to remove remnants of another app from the db,.

drop table if exists socialaccount_socialaccount cascade
drop table if exists socialaccount_socialapp cascade;
drop table if exists socialaccount_socialapp_sites cascade;
drop table if exists socialaccount_socialtoken cascade;
drop table if exists account_emailaddress cascade;
drop table if exists account_confirmation cascade;

[jeancochrane]

Thanks for these docs @fgregg! Looks like https://github.com/security-force-monitor/sfm-cms/commit/d8fd2e79772c1fca997b2a8e7bbe50561d22a38e is the commit where you added migrations removing value_id fields. I'll start with altering those tables and see if anything else shakes out.

Once we're greenlit for a production deploy, my plan of attack is to pull down a dump of the production DB locally and attempt to run the migration surgery. I'll add notes here with the full list of my steps. Once I get that working, I'll move on to updating the remote DB.

[fgregg]

Very wise.

[jeancochrane]

Import tasks

• [x] Make database backups:

pg_dump -U postgres -Fc sfm > sfm_20190319.dump
pg_dump -U postgres -Fc importer > importer_20190319.dump

• [x] Database surgery on sfm database:

alter table composition_compositionclassification add value_id text;
alter table emplacement_emplacementalias add value_id text;
alter table membershipperson_membershippersonendcontext add value_id text;
alter table membershipperson_membershippersonstartcontext add value_id text;
alter table organization_organizationalias add value_id text;
alter table organization_organizationclassification add value_id text;
alter table violation_violationperpetratorclassification add value_id text;
alter table violation_violationtype add value_id text;

drop table if exists socialaccount_socialaccount cascade;
drop table if exists socialaccount_socialapp cascade;
drop table if exists socialaccount_socialapp_sites cascade;
drop table if exists socialaccount_socialtoken cascade;
drop table if exists account_emailaddress cascade;
drop table if exists account_confirmation cascade;

drop materialized view organization_site_name_export;

• [x] Deploy code to production (this will run migrations)

• [x] In a tmux session, run python manage.py make_flattened_views --recreate to recreate the materialized view we had to delete

• [x] Change into importer directory:

cd sfm-importer

• [x] Edit Makefile:

diff --git a/Makefile b/Makefile
index 62e9a85c..3eda03e8 100644
--- a/Makefile
+++ b/Makefile
@@ -24,11 +24,11 @@ link_locations: import_directory flush_db
        python manage.py link_locations

 .PHONY : update_db
update_db : import_directory auth_models.json flush_db link_locations import_google_docs
        python manage.py loaddata auth_models.json
        python manage.py make_flattened_views --recreate
        python manage.py update_countries_plus
-       python manage.py make_search_index --recreate
+       # python manage.py make_search_index --recreate
        # psql importer < sfm_pc/management/commands/flush/rename.sql

• [x] In a tmux shell, run make update_db
• [x] Run python manage.py make_search_index --recreate
• [x] Run psql < sfm_pc/management/commands/flush/rename.sql
• [x] Restart Gunicorn process with sudo supervisorctl restart sfm:sfm-cms and confirm everything looks good

[jeancochrane]

Done! https://whowasincommand.com is up to date with the latest data and migrations.

[tlongers]

Also, I had to remove remnants of another app from the db,

drop table if exists socialaccount_socialaccount cascade
drop table if exists socialaccount_socialapp cascade;
drop table if exists socialaccount_socialapp_sites cascade;
drop table if exists socialaccount_socialtoken cascade;
drop table if exists account_emailaddress cascade;
drop table if exists account_confirmation cascade;

Quick question @jeancochrane - evz updated the authentication app a few PRs ago. Might these tables be required, even if we are not at the present time making use of the functionality that they underpin?

Many apologies for adding to this thread now; I missed it first time around.

[jeancochrane]

That's a great question @tlongers. Do you happen to have a link to the PR? I'm having trouble finding it. I'd never used these tables before, but it's possible they're important for functionality I'm not aware of.

[tlongers]

Hrm, this possibly .... https://github.com/security-force-monitor/sfm-cms/commit/4bd7a98eafd4ef4ad24312ffc2413523040ffd93 which also seems to relate to this https://github.com/security-force-monitor/sfm-cms/commit/1af049645c8d4f3aaf0ccd5deddf379cca6a227e? No issue if I've got it wrong - it just tweaked my spidey sense a bit :)

(edited to add another commit I found)