Script to prepare DB profiles schema for remigration

Owned by Aliaksandr Fedasiuk
Last updated: Feb 28, 2024 by Olamide Kolawole
4 min read

  1. Create backup of <tenantId>_mod_di_converter_storage

  2. OPTIONAL | To be able to check the migration results in the future, execute:

    set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; create table snapshots_old as select job_profile_id, s.get_profile_snapshot ->> 'association_id' as association_id, s.get_profile_snapshot as snapshot from (select jp.id job_profile_id, get_profile_snapshot(jp.id, 'JOB_PROFILE', 'job_profiles', jp.id::TEXT) from job_profiles jp) s;

  3. Execute the following script. This script will remove data from the previous migration since the mod-di-converter-storage will start migration only when the “profile_wrappers” table is empty.

    set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; DO $$ DECLARE r record; BEGIN RAISE NOTICE '==='; RAISE NOTICE 'Preparing mod_di_converter_storage for migration.'; DROP TABLE IF EXISTS foreign_keys; RAISE NOTICE 'Find FKeys for deletion.'; CREATE TEMP TABLE foreign_keys AS SELECT conrelid :: regclass AS table_name, conname AS foreign_key FROM pg_constraint WHERE connamespace = (SELECT current_setting('SEARCH_PATH')) :: regnamespace AND contype = 'f' AND conrelid :: regclass :: text like '%_to_%' ORDER BY conrelid :: regclass :: text, contype desc; RAISE NOTICE 'Removing FKeys:'; FOR r in SELECT table_name, foreign_key FROM foreign_keys LOOP RAISE NOTICE ' Delete FKey:: % : %', r.table_name, r.foreign_key; EXECUTE 'ALTER TABLE ' || r.table_name || ' DROP CONSTRAINT ' || r.foreign_key || ';'; END LOOP; RAISE NOTICE 'FKeys were removed.'; RAISE NOTICE 'Disable triggers.'; SET session_replication_role = replica; RAISE NOTICE 'Removing old associations.'; FOR r in SELECT distinct table_name FROM foreign_keys LOOP EXECUTE 'UPDATE ' || r.table_name || ' SET masterwrapperid=null, detailwrapperid=null;'; END LOOP; RAISE NOTICE 'Removing old wrappers.'; TRUNCATE profile_wrappers; RAISE NOTICE 'Enabling triggers back.'; SET session_replication_role = DEFAULT; RAISE NOTICE 'Creating FKeys back:'; FOR r in SELECT table_name, foreign_key FROM foreign_keys LOOP RAISE NOTICE ' Create FKey:: % : %', r.table_name, r.foreign_key; EXECUTE 'ALTER TABLE ' || r.table_name || ' ADD CONSTRAINT ' || r.foreign_key || ' FOREIGN KEY (' || left(r.foreign_key, strpos(r.foreign_key, '_') -1)|| ') REFERENCES profile_wrappers(id) ON DELETE CASCADE;'; END LOOP; DROP TABLE IF EXISTS foreign_keys; RAISE NOTICE 'DB ready for migration.'; END $$;

  4. Execute the module update procedure.

  5. OPTIONAL | To check the results of the migration:

    set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; create table snapshots_new as select job_profile_id, s.get_profile_snapshot ->> 'association_id' as association_id, s.get_profile_snapshot as snapshot from (select jp.id job_profile_id, get_profile_snapshot(jp.id, 'JOB_PROFILE', 'job_profiles', jp.id::TEXT) from job_profiles jp) s;

    The query above should not return any results if the migration was successful.

  6. Delete the service tables

The following scripts may help if something goes wrong

 

  1. Script for creating FKeys

  2. Script for removing FKeys

  3. Script for cleaning links to old wrapperIds and old wrappers: