PostgreSQL Database recovery

Preparation

Stop related services in the environment

Scroll conditional content

YSoft SafeQ services need to be stopped before a database restore is performed and the cache on Spooler Controllers needs to be cleared before they are started again to avoid possible inconsistencies.

1. Stop almost all YSoft SafeQ services on the Management Server and Site Servers:Leave only the YSoft Bundled PostgreSQL 11 service running.You can use the following PowerShell script to perform the task:Run PowerShell as Administrator. Get-Service *YSoft* | Where-Object {$_.Name -ne 'YSoftPGSQL' -and $_.Name -ne 'YYSoftPGSQL' } | Stop-Service

ysoft_safeq

Scroll conditional content

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

ipm

Delete the current databases

1. On the database server, run the pgAdmin administration tool.

2. In the Object Browser window, double-click the YSoft SafeQ PostgreSQL server connection item and connect to the server.

3. Delete the current YSoft SafeQ databases:

   1. SQDB6

      

   2. SQDB6_IMS

   3. SQDB6_YPS (if YSoft SafeQ 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 topostgres using the same names as the original ones:

   1. SQDB6

      

   2. SQDB6_IMS

   3. SQDB6_YPS (if YSoft SafeQ 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 YSoft SafeQ Payment System is installed).

4. Also restore the SQDB6_DWHdatabase 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

Scroll conditional content

1. If you have YSoft SafeQ 6 MU9 or later and if you have database user passwords inplain text (default configuration), run the following procedure on the SQDB6 database:

   SELECT cluster_mngmt.spu_recover_tenant_db_passwords();

2. If you have YSoft SafeQ 6 MU8 or older or if you have encrypted database user passwords, follow these manual steps:
   Reconfigure tenantuser_1 user: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' 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@; Replace variables with real values: @tenantUser@ = tenantuser_1 @tenantPassword@ = tenantuser_1 password from step a (which must be decrypted). @tenantSchema@ = tenant_1 Run the modified query on the SQDB6 database.Reconfigure the dwhtenantuser_1 user: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' 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@; 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 Run the modified query on the SQDB6 database.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

ysoft_safeq

Scroll conditional content

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

   SELECT cluster_mngmt.spu_recover_tenant_db_passwords();

2. If you haveencrypteddatabase user passwords, follow these manual steps:

1. 1. Reconfigure tenantuser_1 user:
      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' 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@; Replace variables with real values: @tenantUser@ = tenantuser_1 @tenantPassword@ = tenantuser_1 password from step a (which must be decrypted). @tenantSchema@ = tenant_1 Run the modified query on the SQDB6 database.

   2. Reconfigure the dwhtenantuser_1 user: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' 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@; 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 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

ipm

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

Scroll conditional content

1. 1. If you have YSoft SafeQ 6 MU9 or later, run the following procedure on the SQDB6 database:

      SELECT cluster_mngmt.spu_clean_validator_tables();

   2. If you have SafeQ 6 MU8 or older, follow these manual steps:Delete records from all validator-related tables. delete from public.smartq_validator; delete from cluster_mngmt.smartq_validator; Foreach tenant_id do: delete from tenant_%i.smartq_validator; delete from dwhtenant_%i.smartq_validator;

   3. Run (6.0.105) DB Validator Tool.

   4. Continue with the Finalization section.

ysoft_safeq

Scroll conditional content

1. Run the following procedure on the SQDB6 database:

   SELECT cluster_mngmt.spu_clean_validator_tables();

2. Run (6.0.105) DB Validator Tool.

3. Continue with the Finalization section.

ipm

Finalization

Clear the Cache on Spooler Controllers and Restart All Services

Cache on Spooler Controllers needs to be deleted after Database restore to avoid possible inconsistencies.

Scroll conditional content

Delete Spooler Controller cache directory on all servers

Delete YSoft SafeQ Spooler Controller cache by deleting whole folder <SAFEQSPOC_DIR>\SpoolCache(e.g. c:\SafeQ6\SPOC\SpoolCache)

Start Management service again

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

1. Start YSoft SafeQ Management Service

Verify the Spooler Controller cache recovery settings

Go to tab System >Configuration on YSoft SafeQ management interface and set orsCacheRecovery property to enabled.

In case of YSoft SafeQ Spooler Controller cache data corruption, cache can be manually deleted and all job-related metadata will be recovered from YSoft SafeQ Management Server.

If you omit this step, all jobs stored on the YSoft SafeQ Spooler Controller might be lost after the end of procedure.

Start the remaining services on all servers

Do not start YSoft SafeQ Spooler Controller Group Service manually. If YSoft SafeQ Spooler Controller is part of Spooler Controller group then this service will start automatically when its configuration is ready.

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

You can use the following PowerShell script to perform the task:

Verify the correct Spooler Controller functionality

Once the YSoft SafeQ Spooler Controller cache is deleted and YSoft SafeQ Spooler Controller services are running, verify the correct YSoft SafeQ Spooler Controller functionality according to the article (6.0.105) YSoft SafeQ Spooler Controller Health Check.

ysoft_safeq

Scroll conditional content

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.

In case of Dispatcher Paragon Spooler Controller cache data corruption, cache can be manually deleted and all job-related metadata will be recovered from Dispatcher Paragon Management Server.

If you omit this step, all jobs stored on the Dispatcher Paragon Spooler Controller might be lost after the end of procedure.

Start the remaining services on all servers

Do not start Dispatcher Paragon Spooler Controller Group Service manually. If Dispatcher Paragon Spooler Controller is part of Spooler Controller group then this service will start automatically when its configuration is ready.

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.

ipm