...
Records in policyadmin_schema_changelog = +109
Version in schema_versions: 0900
...
Modify db_migrator_policy_init.sh - Remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts"
Make/Redeploy to run downgrade
Number of files run = 13
Tables in policyadmin = 73
Records in policyadmin_schema_changelog = 109 +13=122
Version in schema_versions: 0800
...
Modify db_migrator_policy_init.sh - Remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts"
Make/Redeploy to run upgrade
Number of files run = 13
Tables in policyadmin = 75
Records in policyadmin_schema_changelog = 122 +13=135
Version in schema_versions: 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
Number of files run = 13
Tables in policyadmin = 75
...
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 policyadminschema: RENAME TABLE backup_pdpstatistic pdpstatistics to pdpstatistics;
Modify db_migrator_policy_init.sh - Remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts"
Make + Re-run/redeploy/Redeploy to run upgrade
Number of files run = 11
Tables in policyadmin = 75
Records in policyadmin_schema_changelog = 24 +11
Version in schema_versions: 0900
...
Ensure you are on release 0900.
Add timeStamp column to papdpstatistics_enginestats: 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 papdpstatistics_enginestats: 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
Number of files run = 9
Tables in policyadmin = 73
Records in policyadmin_schema_changelog = +9 (maybe more depending on the number retries)
Version in schema_versions: 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: 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: ALTER TABLE pdpstatistics DROP COLUMN POLICYUNDEPLOYCOUNTRename pdpstatistics table in policyadmin schema: 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
Number of files run = 3
Tables in policyadmin = 73
Records in policyadmin_schema_changelog = +3
Version in schema_versions: 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: ALTER TABLE pdp ADD COLUMN LASTUPDATE datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER HEALTHY;
Make/Redeploy to run upgrade
Once the db is running but before galera-config is run rename pdpstatistics table in policyadmin schema: RENAME TABLE pdpstatistics to backup_pdpstatistics;
This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0)
Rename backup_pdpstatistic table in policyadminschema: RENAME TABLE backup_pdpstatistic to pdpstatistics;
Update PDP table: 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
Number of files run = 14
Tables in policyadmin = 75
Records in policyadmin_schema_changelog = +14 (maybe more depending n the number of retries)
Version in schema_versions: 0900
9. Downgrade to Honolulu (0800) with data in pdpstatistics and jpapdpstatistics_enginestats
Ensure you are on release 0900.
Run the sql statements in apex-pdpstatistics.sql from the "Resources" section to populate pdpstatistics and jpapdpstatistics_enginestats with data.
Modify db_migrator_policy_init.sh - Remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts"
Make/Redeploy to run upgrade
Check the tables to ensure the number records is the same.
Check pdpstatistics to ensure the primary key has changed - 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.
Check the pdp table to ensure the LASTUPDATE column has been dropped.
Redeploy to run upgrade
Number of files run = 13
Tables in policyadmin = 73
Records in policyadmin_schema_changelog = +13
Version in schema_versions: 0800
10. Upgrade to Istanbul (0900) with data in pdpstatistics and jpapdpstatistics_enginestats
Ensure you are on release 0800.
pdpstatistics and jpapdpstatistics_enginestats should contain data from the last test.
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.
Check pdpstatistics to ensure the primary key has changed - 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.
Check the pdp table to ensure the LASTUPDATE column has been added and the value has defaulted to the CURRENT_TIMESTAMP.
Redeploy to run upgradeRe-run/redeploy
Number of files run = 1213
Tables in policyadmin = 7375
Records in policyadmin_schema_changelog = 36+13
Version in schema_versions: 08000900
Test Results
kubectl logs: db_migrator_kubectl_logs.xlsx
Resources
Insert statements for pdpstatistics and jpapdpstatistics_enginestats: apex-pdpstatistics.sql