Policy DB Migrator Smoke Tests
Prerequisites
Check number of files in each release
1 ls 0800/upgrade/*.sql | wc -l = 96
2 ls 0900/upgrade/*.sql | wc -l = 13
3 ls 0800/downgrade/*.sql | wc -l = 96
4 ls 0900/downgrade/*.sql | wc -l = 13
Upgrade scripts
1 /opt/app/policy/bin/prepare_upgrade.sh policyadmin
2 /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
1 /opt/app/policy/bin/prepare_downgrade.sh policyadmin
2 /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
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.
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.
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:
1 DROP TABLE schema_versions;
2 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 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:
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:
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 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:
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:
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 |
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:
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 |
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:
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:
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 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.
1 SELECT count(*) FROM pdpstatistics;
2 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.
1 SELECT count(*) FROM pdpstatistics;
2 SELECT count(*) FROM jpapdpstatistics_enginestats;
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.
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.
SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'pdp';
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.
1 SELECT count(*) FROM pdpstatistics;
2 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.
1 SELECT count(*) FROM pdpstatistics;
2 SELECT count(*) FROM jpapdpstatistics_enginestats;
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.
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.
SELECT table_name, column_name, data_type, column_default FROM information_schema.columns WHERE table_name = 'pdp';
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