Upgrade prod CMS DB to postgres V11

patphongs commented 4 years ago

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

[x] The production CMS database is upgraded to version 11

Tech steps or considerations

[x] 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.
[x] Unbind the CMS app from the attached DB service cf us cms
[x] Bind the CMS app to the newly created service cf bs cms.
[x] In a separate terminal, Connect to the DB service to perform a dump: cf connect-to-service -no-client cms <service>
[x] 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>
[x] Perform a restore on the new PG instance
[x] Rebuild the prod CMS application in CircleCI
[x] Check to make sure things look good in the website and inside of Wagtail
[x] 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.
[x] Delete the old medium-psql-redundant DB service: cf ds <service name>

pkfec commented 4 years ago

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

create a new PG11 service: cf create-service aws-rds medium-psql-redundant fec-dev-cms-pg11
unbind CMS app form existing/current DB service cf unbind-service cms fec-dev-cms
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
bind cms app with the latest pg11 service cf bind-service cms fec-dev-cms-pg11
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>
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

rename fec-dev-cms service cf rename-service fec-dev-cms fec-dev-cms-pg9
rename fec-dev-cms-pg11 to fec-dev-cms cf rename-service fec-dev-cms-pg11 fec-dev-cms
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
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.

unbind cms app from pg11 service cf unbind-service cms fec-dev-cms
rename PG11 service fec-dev-cms to fec-dev-cms-pg11 cf rename-service fec-dev-cms fec-dev-cms-pg11
bind the cms app to the OLD pg9 service cf bind-service cms fec-dev-cms-pg9
rename pg9 service to fec-dev-cms cf rename-service fec-dev-cms-pg9 fec-dev-cms
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
test dev website

pkfec commented 4 years ago

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 commented 4 years ago

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

kathycarothers commented 4 years ago

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

pkfec commented 4 years ago

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 commented 4 years ago

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

fecgov / fec-cms