Preparation

Create target environment from bugfest rds snapshot
Create source environment from intcon-snapshot-after-data-load-and-po-verification-10-26-2023 rds snapshot
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.
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
On the target and source environments scale down all modules, to avoid data modifications during dump/restore activity

Example: Before

Tenant id	Source env	Target env
fs09000000		+
fs09000002		+
fs09000003		+
cs00000int	+
cs00000int_0001	+
cs00000int_0002	+
cs00000int_0003	+
cs00000int_0004	+
cs00000int_0005	+

Example: After

Tenant id	Source env	Target env
fs09000000		+
fs09000002		+
fs09000003		+
cs00000int	+	+
cs00000int_0001	+	+
cs00000int_0002	+	+
cs00000int_0003	+	+
cs00000int_0004	+	+
cs00000int_0005	+	+

Legend

Color	Type
	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:

Start all modules
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

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)
Create additional admins for ECS tenants (if needed)
Create Edge users

Indexation

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

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

Folio Development Teams

Workflow for tenants database schema migration

Preparation

Schemas dump

Dump all tenants in one dump

Dump each tenant to separate dump

Schemas restore

Before

During

After

System users' configuration

Additional steps

Indexation

[data-colorid=ewyuffqbk1]{color:#212121} html[data-color-mode=dark] [data-colorid=ewyuffqbk1]{color:#dedede}Preparation

Schemas dump

Dump all tenants in one dump

Dump each tenant to separate dump

Schemas restore

Before

During

After

System users' configuration

Additional steps

Indexation

Preparation