summaryrefslogtreecommitdiffstats
path: root/docs/development/devtools/db-migrator-smoke.rst
diff options
context:
space:
mode:
Diffstat (limited to 'docs/development/devtools/db-migrator-smoke.rst')
-rw-r--r--docs/development/devtools/db-migrator-smoke.rst413
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