diff options
Diffstat (limited to 'docs/development/devtools/db-migrator-smoke.rst')
| -rw-r--r-- | docs/development/devtools/db-migrator-smoke.rst | 413 |
1 files changed, 413 insertions, 0 deletions
diff --git a/docs/development/devtools/db-migrator-smoke.rst b/docs/development/devtools/db-migrator-smoke.rst new file mode 100644 index 00000000..4aa41e46 --- /dev/null +++ b/docs/development/devtools/db-migrator-smoke.rst @@ -0,0 +1,413 @@ +.. This work is licensed under a Creative Commons Attribution +.. 4.0 International License. +.. http://creativecommons.org/licenses/by/4.0 + +Policy DB Migrator Smoke Tests +############################## + +Prerequisites +************* + +Check number of files in each release + +.. code:: + :number-lines: + + ls 0800/upgrade/*.sql | wc -l = 96 + ls 0900/upgrade/*.sql | wc -l = 13 + ls 0800/downgrade/*.sql | wc -l = 96 + ls 0900/downgrade/*.sql | wc -l = 13 + +Upgrade scripts +=============== + +.. code:: + :number-lines: + + /opt/app/policy/bin/prepare_upgrade.sh policyadmin + /opt/app/policy/bin/db-migrator -s policyadmin -o upgrade + +.. note:: + You can also run db-migrator upgrade with the -t and -f options + +Downgrade scripts +================= + +.. code:: + :number-lines: + + /opt/app/policy/bin/prepare_downgrade.sh policyadmin + /opt/app/policy/bin/db-migrator -s policyadmin -o downgrade -f 0900 -t 0800 + +Db migrator initialization script +================================= + +Update /oom/kubernetes/policy/resources/config/db_migrator_policy_init.sh with the appropriate upgrade/downgrade calls. + +The policy version you are deploying should either be an upgrade or downgrade from the current db migrator schema version. + +Every time you modify db_migrator_policy_init.sh you will have to undeploy, make and redeploy before updates are applied. + +1. Fresh Install +**************** + +.. list-table:: + :widths: 60 20 + :header-rows: 0 + + * - Number of files run + - 109 + * - Tables in policyadmin + - 75 + * - Records Added + - 109 + * - schema_version + - 0900 + +2. Downgrade to Honolulu (0800) +******************************* + +Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts" + +Make/Redeploy to run downgrade. + +.. list-table:: + :widths: 60 20 + :header-rows: 0 + + * - Number of files run + - 13 + * - Tables in policyadmin + - 73 + * - Records Added + - 13 + * - schema_version + - 0800 + +3. Upgrade to Istanbul (0900) +***************************** + +Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts". + +Make/Redeploy to run upgrade. + +.. list-table:: + :widths: 60 20 + :header-rows: 0 + + * - Number of files run + - 13 + * - Tables in policyadmin + - 75 + * - Records Added + - 13 + * - schema_version + - 0900 + +4. Upgrade to Istanbul (0900) without any information in the migration schema +***************************************************************************** + +Ensure you are on release 0800. (This may require running a downgrade before starting the test) + +Drop db-migrator tables in migration schema: + +.. code:: + :number-lines: + + DROP TABLE schema_versions; + DROP TABLE policyadmin_schema_changelog; + +Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts". + +Make/Redeploy to run upgrade. + +.. list-table:: + :widths: 60 20 + :header-rows: 0 + + * - Number of files run + - 13 + * - Tables in policyadmin + - 75 + * - Records Added + - 13 + * - schema_version + - 0900 + +5. Upgrade to Istanbul (0900) after failed downgrade +**************************************************** + +Ensure you are on release 0900. + +Rename pdpstatistics table in policyadmin schema: + +.. code:: + + RENAME TABLE pdpstatistics TO backup_pdpstatistics; + +Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts" + +Make/Redeploy to run downgrade + +This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0) + +Rename backup_pdpstatistic table in policyadmin schema: + +.. code:: + + RENAME TABLE backup_pdpstatistics TO pdpstatistics; + +Modify db_migrator_policy_init.sh - Remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts" + +Make/Redeploy to run upgrade + +.. list-table:: + :widths: 60 20 + :header-rows: 0 + + * - Number of files run + - 11 + * - Tables in policyadmin + - 75 + * - Records Added + - 11 + * - schema_version + - 0900 + +6. Downgrade to Honolulu (0800) after failed downgrade +****************************************************** + +Ensure you are on release 0900. + +Add timeStamp column to papdpstatistics_enginestats: + +.. code:: + + ALTER TABLE jpapdpstatistics_enginestats ADD COLUMN timeStamp datetime DEFAULT NULL NULL AFTER UPTIME; + +Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts" + +Make/Redeploy to run downgrade + +This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0) + +Remove timeStamp column from jpapdpstatistics_enginestats: + +.. code:: + + ALTER TABLE jpapdpstatistics_enginestats DROP COLUMN timeStamp; + +The config job will retry 5 times. If you make your fix before this limit is reached you won't need to redeploy. + +Redeploy to run downgrade + +.. list-table:: + :widths: 60 20 + :header-rows: 0 + + * - Number of files run + - 14 + * - Tables in policyadmin + - 73 + * - Records Added + - 14 + * - schema_version + - 0800 + +7. Downgrade to Honolulu (0800) after failed upgrade +**************************************************** + +Ensure you are on release 0800. + +Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts" + +Update pdpstatistics: + +.. code:: + + ALTER TABLE pdpstatistics ADD COLUMN POLICYUNDEPLOYCOUNT BIGINT DEFAULT NULL NULL AFTER POLICYEXECUTEDSUCCESSCOUNT; + +Make/Redeploy to run upgrade + +This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0) + +Once the retry count has been reached, update pdpstatistics: + +.. code:: + + ALTER TABLE pdpstatistics DROP COLUMN POLICYUNDEPLOYCOUNT; + +Modify db_migrator_policy_init.sh - Remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts" + +Make/Redeploy to run downgrade + +.. list-table:: + :widths: 60 20 + :header-rows: 0 + + * - Number of files run + - 7 + * - Tables in policyadmin + - 73 + * - Records Added + - 7 + * - schema_version + - 0800 + +8. Upgrade to Istanbul (0900) after failed upgrade +************************************************** + +Ensure you are on release 0800. + +Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts" + +Update PDP table: + +.. code:: + + ALTER TABLE pdp ADD COLUMN LASTUPDATE datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER HEALTHY; + +Make/Redeploy to run upgrade + +This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0) + +Update PDP table: + +.. code:: + + ALTER TABLE pdp DROP COLUMN LASTUPDATE; + +The config job will retry 5 times. If you make your fix before this limit is reached you won't need to redeploy. + +Redeploy to run upgrade + +.. list-table:: + :widths: 60 20 + :header-rows: 0 + + * - Number of files run + - 14 + * - Tables in policyadmin + - 75 + * - Records Added + - 14 + * - schema_version + - 0900 + +9. Downgrade to Honolulu (0800) with data in pdpstatistics and jpapdpstatistics_enginestats +******************************************************************************************* + +Ensure you are on release 0900. + +Check pdpstatistics and jpapdpstatistics_enginestats are populated with data. + +.. code:: + :number-lines: + + SELECT count(*) FROM pdpstatistics; + SELECT count(*) FROM jpapdpstatistics_enginestats; + +Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts" + +Make/Redeploy to run downgrade + +Check the tables to ensure the number records is the same. + +.. code:: + :number-lines: + + SELECT count(*) FROM pdpstatistics; + SELECT count(*) FROM jpapdpstatistics_enginestats; + +Check pdpstatistics to ensure the primary key has changed: + +.. code:: + + SELECT column_name, constraint_name FROM information_schema.key_column_usage WHERE table_name='pdpstatistics'; + +Check jpapdpstatistics_enginestats to ensure id column has been dropped and timestamp column added. + +.. code:: + + SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'jpapdpstatistics_enginestats'; + +Check the pdp table to ensure the LASTUPDATE column has been dropped. + +.. code:: + + SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'pdp'; + + +.. list-table:: + :widths: 60 20 + :header-rows: 0 + + * - Number of files run + - 13 + * - Tables in policyadmin + - 73 + * - Records Added + - 13 + * - schema_version + - 0800 + +10. Upgrade to Istanbul (0900) with data in pdpstatistics and jpapdpstatistics_enginestats +****************************************************************************************** + +Ensure you are on release 0800. + +Check pdpstatistics and jpapdpstatistics_enginestats are populated with data. + +.. code:: + :number-lines: + + SELECT count(*) FROM pdpstatistics; + SELECT count(*) FROM jpapdpstatistics_enginestats; + +Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts" + +Make/Redeploy to run upgrade + +Check the tables to ensure the number records is the same. + +.. code:: + :number-lines: + + SELECT count(*) FROM pdpstatistics; + SELECT count(*) FROM jpapdpstatistics_enginestats; + +Check pdpstatistics to ensure the primary key has changed: + +.. code:: + + SELECT column_name, constraint_name FROM information_schema.key_column_usage WHERE table_name='pdpstatistics'; + +Check jpapdpstatistics_enginestats to ensure timestamp column has been dropped and id column added. + +.. code:: + + SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'jpapdpstatistics_enginestats'; + +Check the pdp table to ensure the LASTUPDATE column has been added and the value has defaulted to the CURRENT_TIMESTAMP. + +.. code:: + + SELECT table_name, column_name, data_type, column_default FROM information_schema.columns WHERE table_name = 'pdp'; + +.. list-table:: + :widths: 60 20 + :header-rows: 0 + + * - Number of files run + - 13 + * - Tables in policyadmin + - 75 + * - Records Added + - 13 + * - schema_version + - 0900 + +.. note:: + The number of records added may vary depnding on the number of retries. + +End of Document |