Workflow for tenants database schema migration

Preparation

  1. Create target environment from bugfest rds snapshot
  2. Create source environment from intcon-snapshot-after-data-load-and-po-verification-10-26-2023 rds snapshot
  3. Update both environments to the latest versions of the modules you need. This is needed to have the same module versions and avoid any issues during future upgrades.
  4. On the target environment create the same tenats that schemas will be moved from the source environment. This is needed to avoid shared schemas (like public or data_import_global) migration and generate Postgresql roles
  5. On the target and source environments scale down all modules, to avoid data modifications during dump/restore activity

Example: Before

Tenant idSource envTarget env
fs09000000


+
fs09000002
+
fs09000003
+
cs00000int+
cs00000int_0001+
cs00000int_0002+
cs00000int_0003+
cs00000int_0004+
cs00000int_0005+

Example: After

Tenant idSource envTarget env
fs09000000


+
fs09000002
+
fs09000003
+
cs00000int++
cs00000int_0001++
cs00000int_0002++
cs00000int_0003++
cs00000int_0004++
cs00000int_0005++

Legend

ColorType

Regular tenant

ECS tenant

Schemas dump

There are 2 options to dump schemas from the source database:

Dump all tenants in one dump

Dump All
pg_dump --host="127.0.0.1" --port="5432" --username="folio" --dbname="folio" --encoding="UTF8" --password --verbose \
  --format="directory" \
  --jobs="8" \
  --compress="6" \
  --no-owner \
  --no-tablespaces \
  --schema 'cs00000int*' \
  --file=ecs_schemas_all

Dump each tenant to separate dump

Note

Ensure that all schemas related to the tenant are presented in the command

Dump cs00000int
pg_dump --host="127.0.0.1" --port="5432" --username="folio" --dbname="folio" --encoding="UTF8" --password --verbose \
  --format="directory" \
  --jobs="8" \
  --compress="6" \
  --no-owner \
  --no-tablespaces \
  --schema 'cs00000int_mod_agreements' \
  --schema 'cs00000int_mod_audit' \
  --schema 'cs00000int_mod_authtoken' \
  --schema 'cs00000int_mod_bulk_operations' \
  --schema 'cs00000int_mod_calendar' \
  --schema 'cs00000int_mod_circulation_storage' \
  --schema 'cs00000int_mod_configuration' \
  --schema 'cs00000int_mod_consortia' \
  --schema 'cs00000int_mod_copycat' \
  --schema 'cs00000int_mod_courses' \
  --schema 'cs00000int_mod_data_export' \
  --schema 'cs00000int_mod_data_export_spring' \
  --schema 'cs00000int_mod_data_export_worker' \
  --schema 'cs00000int_mod_data_import' \
  --schema 'cs00000int_mod_di_converter_storage' \
  --schema 'cs00000int_mod_email' \
  --schema 'cs00000int_mod_entities_links' \
  --schema 'cs00000int_mod_event_config' \
  --schema 'cs00000int_mod_feesfines' \
  --schema 'cs00000int_mod_finance_storage' \
  --schema 'cs00000int_mod_inventory' \
  --schema 'cs00000int_mod_inventory_storage' \
  --schema 'cs00000int_mod_invoice_storage' \
  --schema 'cs00000int_mod_kb_ebsco_java' \
  --schema 'cs00000int_mod_licenses' \
  --schema 'cs00000int_mod_login' \
  --schema 'cs00000int_mod_notes' \
  --schema 'cs00000int_mod_notify' \
  --schema 'cs00000int_mod_oai_pmh' \
  --schema 'cs00000int_mod_orders' \
  --schema 'cs00000int_mod_orders_storage' \
  --schema 'cs00000int_mod_organizations_storage' \
  --schema 'cs00000int_mod_password_validator' \
  --schema 'cs00000int_mod_patron_blocks' \
  --schema 'cs00000int_mod_permissions' \
  --schema 'cs00000int_mod_pubsub' \
  --schema 'cs00000int_mod_quick_marc' \
  --schema 'cs00000int_mod_remote_storage' \
  --schema 'cs00000int_mod_search' \
  --schema 'cs00000int_mod_service_interaction' \
  --schema 'cs00000int_mod_settings' \
  --schema 'cs00000int_mod_source_record_manager' \
  --schema 'cs00000int_mod_source_record_storage' \
  --schema 'cs00000int_mod_tags' \
  --schema 'cs00000int_mod_template_engine' \
  --schema 'cs00000int_mod_users' \
  --file=ecs_schemas_cs00000int
Dump cs00000int_0001...cs00000int_0005
pg_dump --host="127.0.0.1" --port="5432" --username="folio" --dbname="folio" --encoding="UTF8" --password --verbose \
  --format="directory" \
  --jobs="8" \
  --compress="6" \
  --no-owner \
  --no-tablespaces \
  --schema 'cs00000int_0001*' \
  --file=ecs_schemas_cs00000int_0001

Schemas restore

Before

Before starting activities of restoration you need to remove schemas that were generated during tenants creation from the target environment (for example: cs00000int_0001*) to avoid errors or skips during restoration.

During

To restore previously dumped schemas use the next commands:

Restore all
pg_restore --host="127.0.0.1" --port="5432" --username="folio" --dbname="folio" \
  --verbose \
  --format="directory" \
  --jobs="8" \
  --exit-on-error \
  ecs_schemas_all

or

Restore cs00000int, cs00000int_0001...cs00000int_0005
pg_restore --host="127.0.0.1" --port="5432" --username="folio" --dbname="folio" \
  --verbose \
  --format="directory" \
  --jobs="8" \
  --exit-on-error \
  ecs_schemas_cs00000int

After

After restoration, we need to actualize privileges on schemas related to RMB modules. 

SQL script that restores privileges on schemas (please note that this script should be applied to each tenant schemas roles):

Restore privileges
DO $$
DECLARE
    tenantIds text[] := ARRAY['cs00000int','cs00000int_0001','cs00000int_0002','cs00000int_0003','cs00000int_0004','cs00000int_0005'];
    tenantId text;
	schema_name text;
BEGIN
    -- Iterate through the list of tenant IDs
    FOREACH tenantId IN ARRAY tenantIds
    LOOP
        -- Iterate through the list of schema/role names
        FOREACH schema_name IN ARRAY ARRAY[
                                              tenantId || '_mod_audit',
                                              tenantId || '_mod_authtoken',
                                              tenantId || '_mod_circulation_storage',
                                              tenantId || '_mod_configuration',
                                              tenantId || '_mod_copycat',
                                              tenantId || '_mod_courses',
                                              tenantId || '_mod_data_export',
                                              tenantId || '_mod_data_import',
                                              tenantId || '_mod_di_converter_storage',
                                              tenantId || '_mod_email',
                                              tenantId || '_mod_event_config',
                                              tenantId || '_mod_feesfines',
                                              tenantId || '_mod_finance_storage',
                                              tenantId || '_mod_inventory_storage',
                                              tenantId || '_mod_invoice_storage',
                                              tenantId || '_mod_kb_ebsco_java',
                                              tenantId || '_mod_login',
                                              tenantId || '_mod_notify',
                                              tenantId || '_mod_oai_pmh',
                                              tenantId || '_mod_orders',
                                              tenantId || '_mod_orders_storage',
                                              tenantId || '_mod_organizations_storage',
                                              tenantId || '_mod_patron_blocks',
                                              tenantId || '_mod_permissions',
                                              tenantId || '_mod_pubsub',
                                              tenantId || '_mod_settings',
                                              tenantId || '_mod_source_record_manager',
                                              tenantId || '_mod_source_record_storage',
                                              tenantId || '_mod_template_engine',
                                              tenantId || '_mod_users'
                                          ]
        LOOP
            EXECUTE 'GRANT ALL ON ALL TABLES IN SCHEMA ' || schema_name || ' TO ' || schema_name || ';';
        END LOOP;
    END LOOP;
END $$;

Additionally, it required casts creation, as it is not presented in dump

Create casts
DO $$
DECLARE
   tenantIds text[] := ARRAY['cs00000int','cs00000int_0001','cs00000int_0002','cs00000int_0003','cs00000int_0004','cs00000int_0005'];
   tenantId text;
BEGIN
   FOREACH tenantId IN ARRAY tenantIds
   LOOP
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_quick_marc.status) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_data_export_spring.jobstatus) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_data_export_spring.identifiertype) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_data_export_spring.exporttype) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_data_export_spring.entitytype) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_data_export_spring.batchstatus) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_consortia.setup_status) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_bulk_operations.updateoptiontype) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_bulk_operations.updateactiontype) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_bulk_operations.statustype) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_bulk_operations.statetype) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_bulk_operations.operationtype) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_bulk_operations.operationstatustype) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_bulk_operations.identifiertype) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_bulk_operations.entitytype) WITH INOUT AS IMPLICIT;';
      EXECUTE 'CREATE CAST (character varying AS ' || tenantId || '_mod_bulk_operations.approachtype) WITH INOUT AS IMPLICIT;';
   END LOOP;
END $$;


System users' configuration

Once schemas are restored:

  1. Start all modules
  2. Reset passwords for system users for each tenant (password should match SYSTEM_USER_PASSWORD env variable presented in modules that use system users)

System user password could be reset via Postman request:

https://s3.amazonaws.com/foliodocs/api/mod-login/r/login.html#authn_password_reset_action_post

https://s3.amazonaws.com/foliodocs/api/mod-login/r/login.html#authn_reset_password_post

Additional steps

  1. Reinstall modules for each newly copied tenant (?tenantParameters=reinstall=true)
    1. Ensure that the central tenant has (mod-consortia and folio_consortia-settings) and institutional tenants have only (mod-consortia)
  2. Create additional admins for ECS tenants (if needed)
  3. Create Edge users

Indexation

After completion of all previous steps, we should perform indexation for restored tenants

  1. Run indexation for central ECS tenant (cs00000int)
  2. Run indexation for each institutional teant (cs00000int_0001...cs00000int_0005)