Updating YSoft SafeQ Management Server database architecture
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.
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.
Uninstall YSoft SafeQ Management Server
Uninstall YSoft SafeQ Management Server completely including database.Clean install YSoft SafeQ Management Server
Install YSoft SafeQ Management Server in multi database configuration.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.
Stop YSoft SafeQ services
For details, see chapter Updating from Build to Build, section Stop YSoft SafeQ services.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
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.
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 exampleDROP
SCHEMA
dwhtenant_1
CASCADE
;
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.