Preparation

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. 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