Upgrade prod CMS DB to postgres V11

[patphongs]

Summary

What we're after:
Postgres databases in cloud.gov are now provisioned as Postgres version 11. We need to re-provision the production CMS Wagtail DB to version 11.

This should be done after hours as this will require a switch over of the database service to a new provisioned service.

Completion criteria

• The production CMS database is upgraded to version 11

Tech steps or considerations

• Create a new medium-psql-redundant DB service in production cf cs aws-rds medium-psql-redundant <service name>. Wait until the service is ready before doing additional work.
• Unbind the CMS app from the attached DB service cf us cms
• Bind the CMS app to the newly created service cf bs cms.
• In a separate terminal, Connect to the DB service to perform a dump: cf connect-to-service -no-client cms <service>
• Perform a dump of the latest Wagtail DB: /Library/PostgreSQL/11/bin/pg_restore --dbname postgres://<username>:<password>@<host>:<port>/<name> --no-acl --no-owner <file name>
• Perform a restore on the new PG instance
• Rebuild the prod CMS application in CircleCI
• Check to make sure things look good in the website and inside of Wagtail
• Have the content team check to make sure the content looks good in the morning. If everything looks ok, we can delete the old medium-psql-redundant DB service.
• Delete the old medium-psql-redundant DB service: cf ds <service name>

[pkfec]

In dev space i ran below steps to configure CMS app with the PG11 DB service:

1. create a new PG11 service:
   cf create-service aws-rds medium-psql-redundant fec-dev-cms-pg11

2. unbind CMS app form existing/current DB service
   cf unbind-service cms fec-dev-cms

3. bind CMS app to latest pg11 service, wait for minimum of 40mins, until the new service is available in cloud.gov. When tried to bind before 40 mins, got below error:
   macadmins-MacBook-Pro-5:~ pkasireddy$ cf bind-service cms fec-dev-cms-pg11
   Binding service fec-dev-cms-tst to app cms in org fec-beta-fec / space dev as pkasireddy@fec.gov...
   Service broker error: There was an error binding the database instance to the application. Error: Instance not available yet. Please wait and try again..
   FAILED

4. bind cms app with the latest pg11 service
   cf bind-service cms fec-dev-cms-pg11

5. take a fresh dump of the exising db from pg11 client location installed on your local
   /Library/PostgreSQL/11/bin/pg_dump -F c --no-acl --no-owner -f /Users/pkasireddy/Projects/db-dumps/<name of the dump file>.dump postgres://<username>:<password>@localhost:<port number>/<db name>

6. restore this dump onto the latest db fec-dev-cms-pg11
   /Library/PostgreSQL/11/bin/pg_restore --dbname postgres://<username>:<password>@localhost:<port number>/<db name> --no-acl --no-owner <name of the dump file>.dump

There were some extension error, which can be ignored:

pg_restore: [archiver (db)] Error from TOC entry 3979; 0 0 COMMENT EXTENSION hstore
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension hstore
    Command was: COMMENT ON EXTENSION hstore IS 'data type for storing sets of (key, value) pairs';



pg_restore: [archiver (db)] Error from TOC entry 3980; 0 0 COMMENT EXTENSION pg_trgm
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension pg_trgm
    Command was: COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';



WARNING: errors ignored on restore: 2

7. rename fec-dev-cms service
   cf rename-service fec-dev-cms fec-dev-cms-pg9

8. rename fec-dev-cms-pg11 to fec-dev-cms
cf rename-service fec-dev-cms-pg11 fec-dev-cms

9. rebuild the latest CMS develop branch build on circleci
   https://app.circleci.com/pipelines/github/fecgov/fec-cms/329/workflows/2a865dbf-cef7-4680-aad7-6539a3cd3e73/jobs/3549

10. Test the dev website that the data tables are loading with data.
    Receipts:
    https://dev.fec.gov/data/receipts/?data_type=processed&two_year_transaction_period=2020&min_date=01%2F01%2F2019&max_date=12%2F31%2F2020
    All Candiates:
    https://dev.fec.gov/data/candidates/?has_raised_funds=true&is_active_candidate=true

DR steps - This is not required, but good to know if something goes wrong with the PG11 service, we can fall back to PG9 service.

11. unbind cms app from pg11 service
    cf unbind-service cms fec-dev-cms

12. rename PG11 service fec-dev-cms to fec-dev-cms-pg11
cf rename-service fec-dev-cms fec-dev-cms-pg11

13. bind the cms app to the OLD pg9 service
    cf bind-service cms fec-dev-cms-pg9

14. rename pg9 service to fec-dev-cms
    cf rename-service fec-dev-cms-pg9 fec-dev-cms

15. rebuild the latest CMS develop branch build on circleci
    https://app.circleci.com/pipelines/github/fecgov/fec-cms/329/workflows/2a865dbf-cef7-4680-aad7-6539a3cd3e73/jobs/3549

16. test dev website

[pkfec]

I have successfully upgraded CMS prod db to pg11 (11.5) on 06/10/2020 btw 8-9pm. Verified that the fec.gov is UP and RUNNING. No issues to report.

[djgarr]

the content i've added/changed recently looks to be all there and correct

[kathycarothers]

The content I've added and changed recently is there and correct.

[pkfec]

content team spot checked that the pages, content in the pg11 cms db and confirmed that looks OK. I will go ahead and delete the old service from prod space. See instructions here: https://github.com/fecgov/fec-eregs/wiki/How-to-switch-shared-pqsl-service-to-medium-psql-redundant#------how-to-delete-the-old-instance

[pkfec]

All checks mentioned in the completion criteria are done. closing this issue.