Preparation

Stop related services in the environment

1. Stop all Dispatcher Paragon services in the whole environment (Management Servers, Site Servers) except the Dispatcher Paragon Bundled PostgreSQL 11 (leave the service running).

Delete the current databases

1. On the database server, run the pgAdmin administration tool.
2. In the Object Browser window, double-click the Dispatcher Paragon PostgreSQL server connection item and connect to the server.
3. Delete the current Dispatcher Paragon databases:
   1. SQDB6

      

   2. SQDB6_IMS
   3. SQDB6_YPS (if Dispatcher Paragon Payment System is installed)
   4. SQDB6_DWH (if Data Warehouse is in a separate database, the database might be deployed on a different server)

Create new databases

1. Right-click Databases > Create > Database
   

2. Create new databases with Owner set to postgres using the same names as the original ones:
   1. SQDB6

      

   2. SQDB6_IMS
   3. SQDB6_YPS (if Dispatcher Paragon Payment System is installed)
   4. SQDB6_DWH (if Data Warehouse is in a separate database, the database might be deployed on a different server)

Restore the databases from the backup files

1. Restore the SQDB6 database from a backup file:

   1. Right-click the database name and select the Restore... option.
      
   2. Select your backup file.

      

   3. Click Select.

   4. A popup appears with information that the procedure ended successfully.

      
   5. Click on More details to see the log.

      
      

2. Restore the SQDB6_IMS database from a backup file.
3. Also, restore the SQDB6_YPS database from a backup file (if Dispatcher Paragon Payment System is installed).
4. Also restore the SQDB6_DWH database from a backup file (if Data Warehouse is in a separate database, the database might be deployed on a different server)

Reconfiguring the SQDB6 Database

1. If you have database user passwords in plain text (default configuration), run the following procedure on the SQDB6 database:

   SELECT cluster_mngmt.spu_recover_tenant_db_passwords();
2. If you have encrypted database user passwords, follow these manual steps:

1. 1. Reconfigure tenantuser_1 user:
      

      1. Find the tenantuser_1 password by running the following query on the SQDB6 database:

         SELECT db_pass FROM cluster_mngmt.tenants WHERE db_login='tenantuser_1'

      2. Prepare the following query:

         DO $body$ BEGIN IF NOT EXISTS(SELECT * FROM pg_catalog.pg_user WHERE usename = '@tenantUser@') THEN CREATE ROLE @tenantUser@ LOGIN PASSWORD '@tenantPassword@'; ELSE ALTER ROLE @tenantUser@ WITH PASSWORD '@tenantPassword@'; END IF; END $body$;

         GRANT ALL ON SCHEMA @tenantSchema@ TO @tenantUser@;

         ALTER USER @tenantUser@ SET search_path TO @tenantSchema@,cluster_mngmt,PUBLIC;

         GRANT ALL ON SCHEMA cluster_mngmt TO @tenantUser@;

      3. Replace variables with real values:

         @tenantUser@ = tenantuser_1

         @tenantPassword@ = tenantuser_1 password from step a (which must be decrypted).

         @tenantSchema@ = tenant_1
      4. Run the modified query on the SQDB6 database.
   2. Reconfigure the dwhtenantuser_1 user:

      1. Find the dwhtenantuser_1 password by running the following query on the SQDB6 database:

         SELECT db_pass FROM cluster_mngmt.tenant_warehouses WHERE db_login='dwhtenantuser_1'

      2. Prepare the following query:

         DO $body$ BEGIN IF NOT EXISTS(SELECT * FROM pg_catalog.pg_user WHERE usename = '@dwhTenantUser@') THEN CREATE ROLE @dwhTenantUser@ LOGIN PASSWORD '@tenantPassword@'; ELSE ALTER ROLE @dwhTenantUser@ WITH PASSWORD '@tenantPassword@'; END IF; END $body$;

         GRANT ALL ON SCHEMA @dwhTenantSchema@ TO @dwhTenantUser@;

         ALTER USER @dwhTenantUser@ SET search_path TO @dwhTenantSchema@,cluster_mngmt,PUBLIC;

         GRANT ALL ON SCHEMA @tenantSchema@ TO @dwhTenantUser@;

         GRANT ALL ON SCHEMA cluster_mngmt TO @dwhTenantUser@;

      3. Replace variables with real values:

         @tenantSchema@ = tenant_1

         @dwhTenantUser@ = dwhtenantuser_1

         @tenantPassword@ = dwhtenantuser_1 password from step a (which must be decrypted).

         @dwhTenantSchema@ = dwhtenant_1
      4. Run the modified query on the SQDB6 database.
   3. If you have more tenants, repeat the steps from section a. for all additional tenantuser_X and the steps from section b. for all corresponding dwhtenantuser_X

Reconfiguring the SQDB6 with data warehouse (DWH)

Basic information:

This section focuses on SQDB6 with data warehouse reconfiguration. Therefore, it is possible to skip this section if it is not relevant for the given situation.

Reconfiguring the database:

1. Follow the steps 1 (Preparation) and 2 (Delete the current databases) if not done already;
2. Run the following modified queries;
   
   

   Inside SQDB6:

   DO $body$ BEGIN IF NOT EXISTS(SELECT * FROM pg_catalog.pg_user WHERE usename = 'tenantuser_1') THEN CREATE ROLE tenantuser_1 LOGIN PASSWORD '@tenantPassword@'; ELSE ALTER ROLE tenantuser_1 WITH PASSWORD '@tenantPassword@'; END IF; END $body$;

   GRANT ALL ON SCHEMA tenant_1 TO tenantuser_1;

   ALTER USER tenantuser_1 SET search_path TO tenant_1,cluster_mngmt,PUBLIC;

   GRANT ALL ON SCHEMA cluster_mngmt TO tenantuser_1;

   
   Inside SQDB6_DWH:

   DO $body$ BEGIN IF NOT EXISTS(SELECT * FROM pg_catalog.pg_user WHERE usename = 'dwhtenantuser_1') THEN CREATE ROLE dwhtenantuser_1 LOGIN PASSWORD '@tenantPassword@'; ELSE ALTER ROLE dwhtenantuser_1 WITH PASSWORD '@tenantPassword@'; END IF; END $body$;

   GRANT ALL ON SCHEMA dwhtenant_1 TO dwhtenantuser_1;

   ALTER USER dwhtenantuser_1 SET search_path TO dwhtenant_1,cluster_mngmt,PUBLIC;

   GRANT ALL ON SCHEMA cluster_mngmt TO dwhtenantuser_1;

3. Run validation tables
   
   Inside SQDB6:

   SELECT cluster_mngmt.spu_clean_validator_tables();

   
   Inside SQDB_DWH:

   DELETE from public.smartq_validator;

   DELETE from cluster_mngmt.smartq_validator;

   DELETE from dwhtenant_1.smartq_validator;

   
   

4. Repeat the steps for any additional tenants (if present)

Re-validating the Database

1. Run the following procedure on the SQDB6 database:

   SELECT cluster_mngmt.spu_clean_validator_tables();
2. Run DB Validator Tool.
3. Continue with the Finalization section.

Finalization

Clear the Cache on Spooler Controllers and Restart All Services

Delete Spooler Controller cache directory on all servers

Delete Dispatcher Paragon Spooler Controller cache by deleting whole folder <SPOC_DIR>\SpoolCache (e.g. C:\DispatcherParagon\SPOC\SpoolCache)

Start Management service again

Open the Services window (e.g. Start > Run > services.msc) and start the following services:

1. Start Dispatcher Paragon Management Service

Verify the Spooler Controller cache recovery settings

Go to tab System > Configuration on Dispatcher Paragon Management web interface and set orsCacheRecovery property to enabled.

Start the remaining services on all servers

1. Start remaining Dispatcher Paragon services with Automatic startup type in no particular order

Verify the correct Spooler Controller functionality

Once the Dispatcher Paragon Spooler Controller cache is deleted and Dispatcher Paragon Spooler Controller services are running, verify the correct Dispatcher Paragon Spooler Controller functionality according to the article Spooler Controller Health Check.