1 .. This work is licensed under a Creative Commons Attribution
2 .. 4.0 International License.
3 .. http://creativecommons.org/licenses/by/4.0
5 Policy DB Migrator Smoke Tests
6 ##############################
11 Check number of files in each release
16 ls 0800/upgrade/*.sql | wc -l = 96
17 ls 0900/upgrade/*.sql | wc -l = 13
18 ls 1000/upgrade/*.sql | wc -l = 9
19 ls 0800/downgrade/*.sql | wc -l = 96
20 ls 0900/downgrade/*.sql | wc -l = 13
21 ls 1000/downgrade/*.sql | wc -l = 9
29 /opt/app/policy/bin/prepare_upgrade.sh policyadmin
30 /opt/app/policy/bin/db-migrator -s policyadmin -o upgrade # upgrade to Jakarta version (latest)
31 /opt/app/policy/bin/db-migrator -s policyadmin -o upgrade -t 0900 # upgrade to Istanbul
32 /opt/app/policy/bin/db-migrator -s policyadmin -o upgrade -t 0800 # upgrade to Honolulu
35 You can also run db-migrator upgrade with the -t and -f options
43 /opt/app/policy/bin/prepare_downgrade.sh policyadmin
44 /opt/app/policy/bin/db-migrator -s policyadmin -o downgrade -t 0900 # downgrade to Istanbul
45 /opt/app/policy/bin/db-migrator -s policyadmin -o downgrade -t 0800 # downgrade to Honolulu
46 /opt/app/policy/bin/db-migrator -s policyadmin -o downgrade -t 0 # delete all tables
48 Db migrator initialization script
49 =================================
51 Update /oom/kubernetes/policy/resources/config/db_migrator_policy_init.sh with the appropriate upgrade/downgrade calls.
53 The policy version you are deploying should either be an upgrade or downgrade from the current db migrator schema version.
55 Every time you modify db_migrator_policy_init.sh you will have to undeploy, make and redeploy before updates are applied.
64 * - Number of files run
66 * - Tables in policyadmin
73 2. Downgrade to Honolulu (0800)
74 *******************************
76 Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts" tagged as Honolulu
78 Make/Redeploy to run downgrade.
84 * - Number of files run
86 * - Tables in policyadmin
93 3. Upgrade to Istanbul (0900)
94 *****************************
96 Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts".
98 Make/Redeploy to run upgrade.
104 * - Number of files run
106 * - Tables in policyadmin
113 4. Upgrade to Istanbul (0900) without any information in the migration schema
114 *****************************************************************************
116 Ensure you are on release 0800. (This may require running a downgrade before starting the test)
118 Drop db-migrator tables in migration schema:
123 DROP TABLE schema_versions;
124 DROP TABLE policyadmin_schema_changelog;
126 Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts".
128 Make/Redeploy to run upgrade.
134 * - Number of files run
136 * - Tables in policyadmin
143 5. Upgrade to Istanbul (0900) after failed downgrade
144 ****************************************************
146 Ensure you are on release 0900.
148 Rename pdpstatistics table in policyadmin schema:
152 RENAME TABLE pdpstatistics TO backup_pdpstatistics;
154 Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts"
156 Make/Redeploy to run downgrade
158 This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0)
160 Rename backup_pdpstatistic table in policyadmin schema:
164 RENAME TABLE backup_pdpstatistics TO pdpstatistics;
166 Modify db_migrator_policy_init.sh - Remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts"
168 Make/Redeploy to run upgrade
174 * - Number of files run
176 * - Tables in policyadmin
183 6. Downgrade to Honolulu (0800) after failed downgrade
184 ******************************************************
186 Ensure you are on release 0900.
188 Add timeStamp column to papdpstatistics_enginestats:
192 ALTER TABLE jpapdpstatistics_enginestats ADD COLUMN timeStamp datetime DEFAULT NULL NULL AFTER UPTIME;
194 Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts"
196 Make/Redeploy to run downgrade
198 This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0)
200 Remove timeStamp column from jpapdpstatistics_enginestats:
204 ALTER TABLE jpapdpstatistics_enginestats DROP COLUMN timeStamp;
206 The config job will retry 5 times. If you make your fix before this limit is reached you won't need to redeploy.
208 Redeploy to run downgrade
214 * - Number of files run
216 * - Tables in policyadmin
223 7. Downgrade to Honolulu (0800) after failed upgrade
224 ****************************************************
226 Ensure you are on release 0800.
228 Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts"
230 Update pdpstatistics:
234 ALTER TABLE pdpstatistics ADD COLUMN POLICYUNDEPLOYCOUNT BIGINT DEFAULT NULL NULL AFTER POLICYEXECUTEDSUCCESSCOUNT;
236 Make/Redeploy to run upgrade
238 This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0)
240 Once the retry count has been reached, update pdpstatistics:
244 ALTER TABLE pdpstatistics DROP COLUMN POLICYUNDEPLOYCOUNT;
246 Modify db_migrator_policy_init.sh - Remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts"
248 Make/Redeploy to run downgrade
254 * - Number of files run
256 * - Tables in policyadmin
263 8. Upgrade to Istanbul (0900) after failed upgrade
264 **************************************************
266 Ensure you are on release 0800.
268 Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts"
274 ALTER TABLE pdp ADD COLUMN LASTUPDATE datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER HEALTHY;
276 Make/Redeploy to run upgrade
278 This should result in an error (last row in policyadmin_schema_changelog will have a success value of 0)
284 ALTER TABLE pdp DROP COLUMN LASTUPDATE;
286 The config job will retry 5 times. If you make your fix before this limit is reached you won't need to redeploy.
288 Redeploy to run upgrade
294 * - Number of files run
296 * - Tables in policyadmin
303 9. Downgrade to Honolulu (0800) with data in pdpstatistics and jpapdpstatistics_enginestats
304 *******************************************************************************************
306 Ensure you are on release 0900.
308 Check pdpstatistics and jpapdpstatistics_enginestats are populated with data.
313 SELECT count(*) FROM pdpstatistics;
314 SELECT count(*) FROM jpapdpstatistics_enginestats;
316 Modify db_migrator_policy_init.sh - remove any lines referencing upgrade and add the 2 lines under "Downgrade scripts"
318 Make/Redeploy to run downgrade
320 Check the tables to ensure the number of records is the same.
325 SELECT count(*) FROM pdpstatistics;
326 SELECT count(*) FROM jpapdpstatistics_enginestats;
328 Check pdpstatistics to ensure the primary key has changed:
332 SELECT column_name, constraint_name FROM information_schema.key_column_usage WHERE table_name='pdpstatistics';
334 Check jpapdpstatistics_enginestats to ensure id column has been dropped and timestamp column added.
338 SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'jpapdpstatistics_enginestats';
340 Check the pdp table to ensure the LASTUPDATE column has been dropped.
344 SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'pdp';
351 * - Number of files run
353 * - Tables in policyadmin
360 10. Upgrade to Istanbul (0900) with data in pdpstatistics and jpapdpstatistics_enginestats
361 ******************************************************************************************
363 Ensure you are on release 0800.
365 Check pdpstatistics and jpapdpstatistics_enginestats are populated with data.
370 SELECT count(*) FROM pdpstatistics;
371 SELECT count(*) FROM jpapdpstatistics_enginestats;
373 Modify db_migrator_policy_init.sh - remove any lines referencing downgrade and add the 2 lines under "Upgrade scripts"
375 Make/Redeploy to run upgrade
377 Check the tables to ensure the number of records is the same.
382 SELECT count(*) FROM pdpstatistics;
383 SELECT count(*) FROM jpapdpstatistics_enginestats;
385 Check pdpstatistics to ensure the primary key has changed:
389 SELECT column_name, constraint_name FROM information_schema.key_column_usage WHERE table_name='pdpstatistics';
391 Check jpapdpstatistics_enginestats to ensure timestamp column has been dropped and id column added.
395 SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'jpapdpstatistics_enginestats';
397 Check the pdp table to ensure the LASTUPDATE column has been added and the value has defaulted to the CURRENT_TIMESTAMP.
401 SELECT table_name, column_name, data_type, column_default FROM information_schema.columns WHERE table_name = 'pdp';
407 * - Number of files run
409 * - Tables in policyadmin
417 The number of records added may vary depending on the number of retries.
419 With addition of Postgres support to db-migrator, these tests can be also performed on a Postgres version of database.
420 In addition, scripts running the aforementioned scenarios can be found under `smoke-tests` folder on db-migrator code base.