Manual update from single database to multi database architecture at PostgreSQL database system

Update procedure for one tenant with examples for the first tenant. You could update all tenants data analogically. Create system backup before the update.

1. Backup single database and dwhtenant schema only

   Backup YSoft SafeQ Management Server database (default name is SQDB6) in pgAdmin utility. Right click on databse and choose backup. You could run command line command also, at example:

   pg_dump.exe --host localhost --port 5433 --username postgres --no-password --format custom --verbose --file sqdb6.backup SQDB6

   Backup YSoft SafeQ Management Server database (SQDB6) dwhtenant schema in pgAdmin utility. Right click on dwhtenant schema and choose backup. You could run command line command also, at example:

   pg_dump.exe --host localhost --port 5433 --username postgres --no-password --format custom --verbose --file sqdb6_dwhtenant_1.backup --schema dwhtenant_1 SQDB6

   For details, see chapter Backup of databases for detailed instructions.

2. Uninstall YSoft SafeQ Management Server

   Uninstall YSoft SafeQ Management Server completely including database.
   
   

3. Clean install YSoft SafeQ Management Server

   Install YSoft SafeQ Management Server in multi database configuration.
   
   

4. Get configuration data

   Get necessary configuration data from production database (SQDB6):

   -- get tenant password and tenant schema name [@tenant_password@, @tenant_schema_name@]

   select db_pass, schema_name from cluster_mngmt.tenants;

   -- get dwhtenant password and dwhtenant schema name [@dwhtenant password@, @dwhtenant_schema_name@]

   select db_pass, schema_name from cluster_mngmt.tenant_warehouses;

   -- get server guid and id [@server_guid@, @server_id]

   select server_guid, id from cluster_mngmt.cluster_server;

   Write it down, you will use these values later for Update configuration data in step 6.

   
   

5. Stop YSoft SafeQ services

   For details, see chapter Updating from Build to Build, section Stop YSoft SafeQ services.
   
   

6. Drop new production database and restore the old one

   Drop production database (SQDB6) in pgAdmin. Right click on production database and choose drop database. You could run sql command also, at example:

   DROP DATABASE "SQDB6";

   In pgAdmin create new production database (SQDB6). You could run sql command also, at example:

   CREATE DATABASE "SQDB6" WITH ENCODING='UTF8';

   In pgAdmin restore whole database (SQDB6) from step 1. Right click on production database (SQDB6) and choose restore database. You could run command line command also, at example:

   pg_restore.exe --host localhost --port 5433 --username postgres --dbname SQDB6 --no-password --verbose sqdb6.backup

7. Update configuration data

   Update necessary configuration data in production database (SQDB6):

   -- update server guid

   update cluster_mngmt.cluster_server set server_guid = '@server_guid@' where id = @server_id@;

   -- update tenant password

   update cluster_mngmt.tenants set db_pass = '@tenant_password@' where schema_name = '@tenant_schema_name@';

   -- update dwhtenant password

   update cluster_mngmt.tenant_warehouses set db_pass = '@dwhtenant password@' where schema_name = '@dwhtenant_schema_name@';

   Replace placeholders by values from step number 4.

8. Drop dwhtenant schema from production database

   IMPORTANT Make sure you drop ONLY the schema.

   Drop dwhtenant schema in production database (SQDB6) via pgAdmin. You could run sql command also, at example
   DROP SCHEMA dwhtenant_1 CASCADE;

9. Restore dwhtenant schema to warehouse database

   Restore dwhtenant schema backup into warehouse databse (default name is SQDB6_SQDW) in pgAdmin utility, choose "Restore option #1": Only data. You could run sql command also, at example:

   pg_restore.exe --host localhost --port 5433 --username postgres --dbname SQDB6_SQDW --no-password --data-only --schema dwhtenant_1 --verbose sqdb6_dwhtenant_1.backup

   Ignore all errors and warnings.

   Multitenant environment

   If you have multitenant environment backup all dwhtenant schemas in step 1, second part and repeat steps 8-9 for remaining tenants.