POLICY-3622 - Getting issue details... STATUS
Prerequisites
Check number of files in each release
cd /git/docker/policy-db-migrator/src/main/docker/config/policyadmin/sql$
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:
/opt/app/policy/bin/prepare_upgrade.sh policyadmin
/opt/app/policy/bin/db-migrator -s policyadmin -o upgrade
Downgrade scripts:
/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:
To run scripts update /oom/kubernetes/policy/resources/config/db_migrator_policy_init.sh with the appropriate calls.
Please ensure the policy version you are deploying is in line with the db migrator schema version.
Every time you modify db_migrator_policy_init.sh you will have to undeploy, make and redeploy before updates are run.
1. Fresh Install
Number of files run = 109
Tables in policyadmin = 75
Records in policyadmin_schema_changelog = +109
Version in schema_versions: 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
Number of files run = 13
Tables in policyadmin = 73
Records in policyadmin_schema_changelog = +13
Version in schema_versions: 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
Number of files run = 13
Tables in policyadmin = 75
Records in policyadmin_schema_changelog = +13
Version in schema_versions: 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: 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
Number of files run = 13
Tables in policyadmin = 75
Records in policyadmin_schema_changelog = 13 (previous records removed when table was dropped)
Version in schema_versions: 0900
5. Upgrade to Istanbul (0900) after failed downgrade
Ensure you are on release 0900.
Rename 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
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_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
Number of files run = 11
Tables in policyadmin = 75
Records in policyadmin_schema_changelog = +11
Version in schema_versions: 0900
6. Downgrade to Honolulu (0800) after failed downgrade
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 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
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)
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 upgrade
Number of files run = 13
Tables in policyadmin = 75
Records in policyadmin_schema_changelog = +13
Version in schema_versions: 0900
Test Results
kubectl logs: db_migrator_kubectl_logs.xlsx
Resources
Insert statements for pdpstatistics and jpapdpstatistics_enginestats: apex-pdpstatistics.sql