Create a new Django migration baseline on PROD based on current models and Postgres upgrade.

[lbeaufort]

Business Reason

Technical need: We should get rid of unused tables like posts_post and step_by_step_execution_log in the database. Probably tran_query_string?

Sample query to find empty tables

select n.nspname as table_schema,

c.relname as table_name

from pg_class c

join pg_namespace n on n.oid = c.relnamespace

where c.relkind = 'r'

and n.nspname not in ('information_schema','pg_catalog')

and c.reltuples = 0

order by table_schema,

table_name;

The plan is to create a new database architecture fresh on an upgraded version of Postgres. This ticket is the first step to eliminate the old migration files and replace them with a fresh baseline by creating migrations based on the current Django models.

Tasks for this ticket:

1. Remove the migration files for the current Django apps and create a new baseline "0001" migration file for each app

• [x] authentication
• [x] committee_accounts
• [x] contacts
• [ ] dcfimporter N/A no model_
• [ ] dotfecs N/A no model_
• [x] memo_text
• [ ] openfec N/A no model
• [x] reports
• [ ] settings N/A no model
• [ ] softdelete N/A no migration needed_
• [x] transactions
• [ ] validation N/A no model
• [x] web_services

2. Restore the following tables after the database is dropped and restored.

• [ ] authentication_account
• [ ] committee_accounts

3. Update or create e2e test fixtures to include the existing user account and committee account in the file django-backend/fixutres/e2e-test-data.json

4. Deploy the api with the new baseline 001 migration files

As part of this ticket, the new Postgres instance will be upgraded:

1. Upgrade postgres version to 14

2. extend backup retention period to 35 days - default is 14 days https://cloud.gov/docs/services/relational-database/

3. Remove db/e2e-test-db.sql and db/fec_clean_dev_db_backup-202111227.sql files.

Dev notes:

Steps to connect to the database:

1. Install cf connect plugin https://cloud.gov/docs/services/relational-database/#using-cf-service-connect-plugin

2. cf connect-to-service -no-client fecfile-web-api fecfile-api-rds

Steps to complete the migration for each environment:

1. Take a backup database export in case we need to restore everything using command pg_dump (from ssh window) --host=(from ssh window) --port=(from ssh window) --username=(from ssh window) -Fc > ~/pg_dump_full__.dmp (this may take 5+ minutes)

2. Export only the tables we want to actually import using command pg_dump --host= --port= --username= --data-only -t authentication_account -t authentication_account_id_seq -t committee_accounts > ~/pg_dump_data_only_minimal_tables__.sql

3. Edit the ~/pg_dump_data_only_minimal_tables__.sql file and remove column code_time from COPY public.authentication_account statement. Next, remove the column value from all of the data below this statement (it should be the last column in the data). Save the file when you are done with the _code_time_removed suffix.

4. Rename the existing cloud.gov rds instance by logging into cloud.gov and clicking Services and select space. Find fecfile-api-rds for and click edit. Click next until you see the name field and then add __mved to the end of it and click Finish_

5. Unbind all applications by click Unbind on the old renamed rds instance

6. Add a new postgres 14 rds instance with updated retention using the command

cf create-service aws-rds medium-psql-redundant fecfile-api-rds -c '

{"version": "14", "backup_retention_period": 35}

'

. Watch in the cloud.gov UI while this runs and wait till the service shows a green checkmark next to Create (this usually takes 15+ mins).

7. Merge the 592 code into the environment branch to target. Note: for Stage this will be cutting the release- branch Wait for CircleCI to deploy the changes to the environment and verify in the logs that it is up and running. Login to the database and verify 38 tables. Verify that authentication_account and committee_accounts are empty and that django_migrations has the new baseline migrations (and dependencies) only.

8. Import the authentication_account and committee_accounts tables using the command psql --host= --port= --username=__code_time_removed.sql

9. Login to Fecfile Online in the environment targeted and smoke test that things work as expected.

10. verify by running SELECT version(); that we are running PostgreSQL 14.9 now

QA Notes

null

DEV Notes

null

Design

null

FECFILE-258

See full ticket and images here: FECFILE-258

[lbeaufort]

@mjtravers and @dheitzer - once this work is done, can we get rid of https://github.com/fecgov/fecfile-web-api/blob/develop/db/fec_clean_dev_db_backup-20211227.sql?

[mjtravers]

@lbeaufort @dheitzer Yes, that file will be replaced by a bare bones Django database SQL file and be removed from the repo.

[mjtravers]

Moved back to In Progress for updating the baseline migrations with changes made in the develop branch.

[dheitzer]

The latest development branch has been merged in and all migrations have been re-baselined to squash the new f1m migration.

[mjtravers]

Passes CR. Sending to QA.

[mjtravers]

Database changes have been deployed to DEV. Will be deployed to STAGE when release deployed for sprint 37

[WiseQA]

QA review verified that PROD and DEV database changes were deployed, as all date (e.g., records, contacts, etc.) were removed from QA Committee ID account used for testing.

QA Review Completed. Moved to Stage Ready.