Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Info

Summary: this page provides information about how to move existing schemas to a new CAST Storage Service/PostgreSQL instance, whether on the same host server or on a different host server.

Introduction

...

CAST schemas - i.e. changing the instance and/or the database.

Introduction

This document provides information about how to move existing CAST schemas in the following scenarios:

Move existing CAST schemas to a new instanceYou have CAST schemas installed on CSS/PostgreSQL "

...

Instance A" and need to move them to CSS/PostgreSQL "

...

Instance B" whether the two instances are on the same host server or are on a different host servers

...

.
Move existing CAST schemas to a new database

You have CAST schemas installed in the "database A" (for example the default postgres database) and need to move them to "database B", whether on the same instance or to another instance (and whether the two instances are on the same host server or are on a different host servers).

Info
Up-to and including AIP Core 8.3.39, CAST schemas must be installed in the postgres database. From AIP Core 8.3.40, it is possible to choose the database name for your CAST schemas, therefore this scenarios is supported with AIP Core ≥ 8.3.40.


Available methods

The required actions can be completed using the following methods:

  1. Using the CombinedTransfer.bat, a predefined batch file provided in the AIP Core installation folder: <AIP Core installation directory>\CSSAdmin\CSSUpgrade
  2. Using the backup and restore tools provided with CAST AIP specifically for the CAST Storage Service. This AIP Core. This method can only be run from Windows, but it can be used for CAST Storage Service installed on Windows or PostgreSQL installed on Linux
  3. Using the CSS Upgrade Wizard, a tool provided in the CAST AIP 8.3.x installation, located in: <CAST AIP installation Directory>\CSSAdmin\CSSUpgrade.exe.Using the PostgreSQL pg_upgrade tool from the new release the PostgreSQL pg_upgrade tool from the new release of the DBMS - this tool allows data stored in the PostgreSQL data files to be upgraded to a later PostgreSQL release. This method can be run either from Windows or from Linux but is not officially supported by CAST.
  4. Using the CSS Upgrade Wizard, a tool provided in the AIP Core installation, located in: <AIP Core installation directory>\CSSAdmin\CSSUpgrade.exe. This method is deprecated starting AIP Core 8.3.25. Please use an alternative method.

Method capabilities


Run on OSSource
database
host OSTarget
database
host OSSchemas supportedPost transfer actions required?Notes

CombinedTransfer.bat

Windows onlyWindows/LinuxWindows/LinuxCombined only (Analysis, Dashboard, Management)NoRecommended method.
CSS Backup and Restore ToolsWindows onlyWindows/LinuxWindows/LinuxAnalysis, Dashboard, Management, MeasureYes - update connection profiles/namingRecommended method
.CSS Upgrade WizardWindows onlyWindows/LinuxWindows/LinuxAnalysis, Dashboard, Management, MeasureYes - update connection profiles/namingDeprecated starting CAST AIP 8.3.25. Please use an alternative method
, however, requires post transfer actions.

PostgreSQL pg_upgrade tool

Windows or LinuxWindows/LinuxWindows/LinuxAnalysis, Dashboard, Management, MeasureYes - update connection profiles/naming
  • Not officially supported by CAST - use at your own risk.
  • Only functions for instances located on the same host server.

Method 1: using the CombinedTransfer.bat file

...

CSS Upgrade WizardWindows onlyWindows/LinuxWindows/LinuxAnalysis, Dashboard, Management, MeasureYes - update connection profiles/naming

Deprecated starting CAST AIP 8.3.25. Please use an alternative method.

Method 1: using the CombinedTransfer.bat file

Info
  • CombinedTransfer.bat file is only available starting AIP Core 8.3.25.
  • CombinedTransfer.bat does not support moving Measurement schemas - please use an alternative tool if you need to move this schema.
  • The PSQL version number of the "source" CSS/PostgreSQL instance must be equal to or higher than the PSQL version number of the "target" CSS/PostgreSQL instance. In other words you cannot use this batch script to move from schemas from, for example, CSS4 (PostgreSQL 13) to CSS3 (PostgreSQL 9.6).
  • Your chosen combined installation of "source" schemas (Analysis, Dashboard, Management) must contain at least ONE application.
  • The default behaviour is to perform a copy. If you do not want to retain the schemas on the source server, use the -deletefromsource parameter.
  • The tool will automatically perform all required post upgrade actions, such as updating references in the _MNGT schema, creating a new legacy CAST Management Studio connection profile etc.

This method functions for installations of CSS on Windows and PostgreSQL on Linux but can only be run from Windows. It is a wrapper batch file for the CSS Backup and Restore Tools, provided as part of the CAST AIP Core ≥ 8.3.x, and involves a fully automated process of dumping the required schemas to file and then restoring the dumps on the new server. The CAST Storage Services/PostgreSQL instances can be located on the same host server or on two different host servers, and the batch file can be run on a server which is remote to the CAST Storage Service/PostgreSQL instances, or on the same server.

...

Code Block
languagetext
<CAST AIP installation>\CSSAdmin\CSSUpgrade\

Parameters

ParameterMandatory?Description

-mngt_list

(tick)

Specify the name of the Management schema you would like to move. The associated Analysis and Dashboard schemas will be moved automatically. You can specify multiple Management schemas using a comma separated list - this list must be enclosed in quote marks.

For example:

Code Block
Single Management schema: -mngt_list alpha_mngt
Multiple Management schemas -mngt_list "alpha_mngt,beta_mngt,delta_mngt"


-logpath

(error)

Log Locations [%ProgramData%\CAST\CAST\Logs\CombinedTransfer]

-sourcehost

(tick)

CSS/PostgreSQL Source Host [localhost]. Ensure you change this to a host name or IP address.

-sourceport

(tick)

CSS/PostgreSQL Source Port [2280]

-sourcedatabase

(error)

CSS/PostgreSQL Source DB [postgres]

-

. Use this option if your source database is not the default "postgres", for example -sourcedatabase mydatabase.

-sourceusername

(tick)

CSS/PostgreSQL Source Username [operator]

-targethost

(tick)

CSS/PostgreSQL Target Host [localhost]. Ensure you change this to a host name or IP address.

-targetport

(tick)

CSS/PostgreSQL Target Port [2280]

-targetdatabase

(error)

CSS/PostgreSQL Target DB [postgres]. Use this option if your target database is not the default "postgres" , for example -targetdatabase mydatabase.

-targetusername

(tick)

CSS/PostgreSQL Target Username [operator]

-workingDir

(error)

Backup Processing Location [%TEMP%\CAST\CAST\$CAST_MAJOR_VERSION$.$CAST_MINOR_VERSION$\YYYYMMDDhhmmssCombinedTransfer\]

-promptpassword

(error)

Prompt for CSS/PostgreSQL operator user password [Y]. Possible values are Y or N.

-overwrite

(error)

Delete and recreate schema if exists in Target DB [N]. Possible values are Y or N.

-deletefromsource

(error)

Delete Schemas from Source DB [N]. Possible values are Y or N. The default behaviour is to perform a copy. If you do not want to retain the schemas on the source server, use this parameter.

Usage

The following will show a password prompt for the CSS/PostgreSQL operator user passwords during the process:

...

Code Block
SET SOURCEPASSWORD=[TYPE_PASSWORD OF SOURCE CSS DB]
SET TARGETPASSWORD=[TYPE_PASSWORD OF TARGET CSS DB]
CombinedTransfer.bat -mngt_list "[management_schema_name/s]" -logpath [loglocation] -sourcehost [hostname] -sourceport [port_number] -sourcedatabase [database_name] -sourceusername [user_name] -targethost [hostname] -targetport [port_number] -targetdatabase [database_name] -targetusername [user_name]  -workingDir [processingDir] -overwrite Y -promptpassword N

Error codes

Error Code 296Provided localhost for SOURCEHOST. Ensure you use a host name or IP address.
Error Code 297Provided localhost for TARGETHOST. Ensure you use a host name or IP address.
Error Code 298Source and Target host/port are same.
Error Code 299PSQL Version of Source Server is greater than your Target Server. You cannot use this batch script to move from schemas from, for example, PostgreSQL 10 to CSS3 (PostgreSQL 9.6).
Error Code 300SOURCEPASSWORD not provided.
Error Code 301TARGETPASSWORD not provided
Error Code 302Source DB Connection failed / Authentication failed.
Error Code 303Target DB Connection failed / Authentication failed.
Error Code 304Provided management schema(s) name not found in Source DB.
Error Code 305No application has been found in this combined installation. Your chosen combined installation of schemas (Analysis, Dashboard, Management) must contain at least ONE application.
Error Code 306Backup process failed.
Error Code 307Restore process failed.
Error Code 308Updating table cms_inf_store_css in Target DB failed.
Error Code 309Connection Profile creation failed.
Error Code 310Dropping combined installation from Source DB failed.

Method 2: using CAST Storage Service backup and restore tools

This method functions for installations of CAST Storage Service on Windows and PostgreSQL on Linux but can only be run from Windows. The process involves dumping the required schemas to file and then restoring the dumps on the new host. The CAST Storage Services/PostgreSQL do not need to be installed on the same host, and both can be remote to the machine on which you are running the CAST Storage Service backup/restore tools.

Prerequisites

RequiredDescription
(tick)Ensure that no CAST AIP Core applications are using the source and target CAST Storage Service/PostgreSQL instances.
(tick)Ensure that you are using the backup and restore tools provided with CAST AIP Core 8.3.x.

Upgrade action

Please refer to the documentation in Maintenance activities for CAST Storage Service and PostgreSQL for more information:

  • You can use the CSSbackup or CSSBackupAll tools to perform the backup
  • You can use the CSSRestore or CSSRestoreAll tools to perform the restore on the new host
  • Ensure you backup the following CAST AIP schemas for each Application you are moving to the new host:
    • CAST Analysis Service (local)
    • CAST Dashboard Service (central)
    • CAST Management Service (mngt)
    • Any associated CAST Measurement Service (measure)

Post upgrade actions

Expand

When you have completed the upgrade, you will need to complete the following actions:

Update all CAST Management Studio connection profiles to allow connection to the same schemas on the new database/instance/host

from 8.3.40

Use tools\ConnectionProfile\CAST-Connection-Profile-Configuration.py (see Using the CAST-Connection-Profile-Configuration.py file).

up to 8.3.39

Open the following file with a text editor:

Code Block
languagetext
%appdata%\CAST\CAST\<version>\cast-ms.connectionProfiles.pmx

Modify in the relevant <connectionprofiles.ConnectionProfilePostgres> tag, the following attributes:

  • the host="xxx" to change the source <xxx> host name to the target <xxx> host name. Note that if the servers instances are located on the SAME host, there is no need to do this.
  • the port="xxxx" to change the source port="xxxx" to the target port="xxxx". Note that if you are using a custom port number for your CSS/PostgreSQL instance, you should enter it here.
  • the name= attribute in the relevant <connectionprofiles.ConnectionProfilePostgres> tag to ensure there is no confusion as to which CAST Storage Service/PostgreSQL users are connecting to (this is optional).

When the changes have been made, save the file.

Code Block
languagexml
titleBefore update
<?xml version="1.0" encoding="UTF-8"?>
<document version="1.1">
	<lot symbol="connectionProfiles" label="Connection profiles" requires="connectionprofiles:2.1;pmcgeneral:3.1;system:1.0">
		<connectionprofiles.ConnectionProfiles>
			<connectionProfiles>
				<connectionprofiles.ConnectionProfilePostgres entry="uuid:0f76b31a-e440-4210-af8b-6c16a4f05bec" name="v8325_mngt on CastStorageService _ MySourceServer:2282" user="operator" password="CRYPTED2:90B1A6EC1618661401B724DB5AC34595" schema="v8325_mngt" host="MySourceServer" port="2282"/>
				</connectionProfiles>
			</connectionprofiles.ConnectionProfiles>
	</lot>
</document>


Code Block
languagexml
titleAfter update
<?xml version="1.0" encoding="UTF-8"?>
<document version="1.1">
	<lot symbol="connectionProfiles" label="Connection profiles" requires="connectionprofiles:2.1;pmcgeneral:3.1;system:1.0">
		<connectionprofiles.ConnectionProfiles>
			<connectionProfiles>
				<connectionprofiles.ConnectionProfilePostgres entry="uuid:0f76b31a-e440-4210-af8b-6c16a4f05bec" name="v8325_mngt on CastStorageService _ MyTargetServer:2282" user="operator" password="CRYPTED2:90B1A6EC1618661401B724DB5AC34595" schema="v8325_mngt" host="MyTargetServer" port="2282"/>
				</connectionProfiles>
			</connectionprofiles.ConnectionProfiles>
	</lot>
</document>

Update connection profiles for CAST Server Manager, CAST Enlighten and CAST Report Center

CAST Server Manager, CAST Enlighten and CAST Report Center use a legacy type of connection profile that is difficult to edit manually. The simplest method therefore is to run each exe and either:

  • manually modify the connection settings in the GUI
  • create new connection profiles from scratch

Update cms_infWhen the database name is different from postgres, the host field must follow format : 'LIBPQ:<HOST>:<PORT>,<DATABASE>', and the port field is not used.

Update cms_inf_store_css table in _MNGT schema

If the source and target servers are located on the SAME host, run the following script to perform the required update, Run the following query:

  • where <prefix> is the schema prefix for the schemas that you have moved to the target server
  • where target <database> name is 'postgres'
  • where <port> is the port used by your target instance
  • and where
<TargetServer>
  • <targetserver> is the host name of the target server.
This will  change the port number from 2282 to 5432 and ensure that the display name for the CAST Storage Service/PostgreSQL in the CAST Management Studio Services view is correct:
Code Block
language
Code Block
languagesql
set search_path = <prefix>_mngt;
update cms_inf_store_css set port=5432<port>, host='<targetserver>:<port>', object_name='<TargetServer><targetserver>:5432<port>';

If the source and target servers are located on DIFFERENT hosts, run the following query instead, where <prefix> is the schema prefix for the schemas that you have moved to the target server and where <TargetServer> is the host name of the target server. This will update the port number, host name and ensure that the display name for the CAST Storage Service/PostgreSQL in the CAST Management Studio Services view is correct:

Code Blocklanguage
  • when target database name is different from 'postgres':
Code Block
languagesql
set search_path = <prefix>_mngt;
update cms_inf_store_css set port=5432<port>, host='<TargetServer>LIBPQ:<targetserver>:<port>,<database>', object_name='<TargetServer><targetserver>:5432<port>,<database>';


Info

Note if you have moved more than one set of schemas from source to target, you will need to repeat the above queries for each <prefix>_mngt you have moved.

...


Method 3: using

...

Note
Note that CSS Upgrade Wizard is deprecated starting CAST AIP 8.3.25. Please use an alternative method.

...

pg_upgrade on host server

This method functions for installations of CSS on Windows and PostgreSQL on Linuxbut can only be run from Windows. The upgrade process uses the CSS Upgrade Wizard, provided as part of the CAST AIP ≥ 8.3.x, and involves a fully automated process of dumping the required schemas to file and then restoring the dumps on the new host. The CAST Storage Services/PostgreSQL do not need to be installed on the same host, and both can be remote to the machine on which you are running the CSS Upgrade Wizard, and assumes that the two instances are already installed on the same host. The upgrade process uses the pg_upgrade tool from the new release of the DBMS and will copy the data from the old CAST Storage Service/Postgres instance to the new, leaving the old CAST Storage Service/PostgreSQL instance in full working order.

Prerequisites

RequiredDescription
(tick)Ensure
that no CAST AIP applications are using
the
source and target
CAST Storage Service/PostgreSQL instances are installed on the same host server - this is highly recommended for performance reasons.
(tick)
Ensure that you are using the CSS Upgrade Wizard provided with CAST AIP 8.3.x.(tick)

You must not use "localhost" in the Host field in the CAST Upgrade Wizard for the Source and Target Servers even if the servers are both located on the current machine. The value entered in the Host field in the CAST Upgrade Wizard must match the value defined for the Host field in the Management Service editor in the CAST Management Studio:

Image Removed

If you have used "localhost" in the CAST Management Studio to define a connection to the Management Service, you MUST update this to use either the server's netbios name or its IP address before running the CSS Upgrade Wizard.

Upgrade action

...

Launch the CSS Upgrade Wizard from the Windows Start menu as follows:

Image Removed

Info
Note that if the CSS Upgrade Wizard is not visible in the start menu, it is located here: <CAST AIP installation Directory>\CSSAdmin\CSSUpgrade.exe.

When the CSS Upgrade Wizard is launched an initial Welcome page will be displayed. Click Next to continue. The Source Server page will then be displayed:

Image Removed

This page enables you to fill in the connection information for your "source server" - i.e. the machine hosting the CAST Storage Service/PostgreSQL whose schemas will be moved.

...

Fill in the required fields and then click Next. The Target Server page will then be displayed:

Image Removed

This page enables you to fill in the connection information for your "target server" - i.e. the machine hosting the CAST Storage Service/PostgreSQL that will "receive" the schemas from the source CAST Storage Service/PostgreSQL.

...

Fill in the required fields and then click Next. The Schema Upgrade Selection page will then be displayed. This page will list all the CAST AIP schemas on the Source Server that are eligible for upgrade to the Target Server. Select the schemas that you want to - place a check mark next to each schema as shown below. You can use the Refresh button (lower right corner) to refresh the list of schemas if required.

Image Removed

Click Next to continue. This next page requires you to input a temporary "Working Folder". This folder will be used to store the selected schemas in file format during the transfer process - please choose a folder with plenty of free space. The folder must also already exist and must be empty.

Image Removed

Click Finish to continue the process. A summary of the upgrade action is the displayed:

Image Removed

Check that the summary of the upgrade action is as you require. Click Upgrade to start the process. On completion a message will be displayed showing you where the log of the entire process is stored:

Image Removed

Please check the log file to ensure there are no failures reported during upgrade. A final page will be displayed - click Finish to terminate the process. Finally, check that the schemas have been correctly moved to the target server using CAST Server Manager.

Post upgrade actions

...

When you have completed the upgrade, you will need to complete the following actions:

Update all CAST Management Studio connection profiles to allow connection to the same schemas on the new host

Open the following file with a text editor:

Code Block
languagetext
%appdata%\CAST\CAST\<version>\cast-ms.connectionProfiles.pmx

Modify in the relevant <connectionprofiles.ConnectionProfilePostgres> tag, the following attributes:

  • the host="xxx" to change the source <xxx> host name to the target <xxx> host name. Note that if the two servers are located on the SAME host, there is no need to do this.
  • the port="xxxx" to change the source port="xxxx" to the target port="xxxx". Note that if you are using a custom port number for your CSS/PostgreSQL instance, you should enter it here.
  • the name= attribute in the relevant <connectionprofiles.ConnectionProfilePostgres> tag to ensure there is no confusion as to which CAST Storage Service/PostgreSQL users are connecting to (this is optional).

When the changes have been made, save the file.

Code Block
languagexml
titleBefore update
<?xml version="1.0" encoding="UTF-8"?>
<document version="1.1">
	<lot symbol="connectionProfiles" label="Connection profiles" requires="connectionprofiles:2.1;pmcgeneral:3.1;system:1.0">
		<connectionprofiles.ConnectionProfiles>
			<connectionProfiles>
				<connectionprofiles.ConnectionProfilePostgres entry="uuid:0f76b31a-e440-4210-af8b-6c16a4f05bec" name="v8325_mngt on CastStorageService _ MySourceServer:2282" user="operator" password="CRYPTED2:90B1A6EC1618661401B724DB5AC34595" schema="v8325_mngt" host="MySourceServer" port="2282"/>
				</connectionProfiles>
			</connectionprofiles.ConnectionProfiles>
	</lot>
</document>
Code Block
languagexml
titleAfter update
<?xml version="1.0" encoding="UTF-8"?>
<document version="1.1">
	<lot symbol="connectionProfiles" label="Connection profiles" requires="connectionprofiles:2.1;pmcgeneral:3.1;system:1.0">
		<connectionprofiles.ConnectionProfiles>
			<connectionProfiles>
				<connectionprofiles.ConnectionProfilePostgres entry="uuid:0f76b31a-e440-4210-af8b-6c16a4f05bec" name="v8325_mngt on CastStorageService _ MyTargetServer:2282" user="operator" password="CRYPTED2:90B1A6EC1618661401B724DB5AC34595" schema="v8325_mngt" host="MyTargetServer" port="2282"/>
				</connectionProfiles>
			</connectionprofiles.ConnectionProfiles>
	</lot>
</document>

Update connection profiles for CAST Server Manager, CAST Enlighten and CAST Report Center

CAST Server Manager, CAST Enlighten and CAST Report Center use a legacy type of connection profile that is difficult to edit manually. The simplest method therefore is to open each one and either:

  • manually modify the connection settings
  • create new connection profiles from scratch

Update server display name for CAST Management Studio

The following action will ensure that the name displayed for the CAST Storage Service in the CAST Management Studio Services view is updated to the new host name:

Image Removed

Run the following query in PGAdmin against the _MNGT table on the target CAST Storage Service/PostgreSQL (i.e. the CAST Storage Service/PostgreSQL to which you have moved the schemas): replacing <server B> with the target CAST Storage Service/PostgreSQL name:

Code Block
languagesql
select 'update ' || table_schema || '.' || table_name || ' set object_name = 
''<server B>'' where host = ''<server B>'' ;'
from information_schema.tables
where table_name = 'cms_inf_store_css'

The above query will produce update query scripts for every CAST AIP schema on the target CAST Storage Service/PostgreSQL to update the host names in the cms_inf_store_css table. For example:

Image Removed

Copy each update query script you need to run (these should cover all the CAST AIP schemas you have moved to the target CAST Storage Service/PostgreSQL with the CSS Upgrade Wizard) into a new PGAdmin query window, as follows:

Image Removed

Run the query.

Method 4: using pg_upgrade on host server

This method functions for installations of CSS on Windows and PostgreSQL on Linux, and assumes that the two CAST Storage Services are already installed on the same host. The upgrade process uses the pg_upgrade tool from the new release of the DBMS and will copy the data from the old CAST Storage Service/Postgres instance to the new, leaving the old CAST Storage Service/PostgreSQL instance in full working order.

Prerequisites

...

The upgrade process will leave the old CAST Storage Service/PostgreSQL instance in place and unchanged, however, CAST does recommend that you backup any CAST AIP schemas you may have on this server using the CAST tools. See Maintenance activities for CAST Storage Service and PostgreSQL.

...

For CAST Storage Service installed on Windows

...

  • Start a command prompt (cmd.exe) as an administrator (search for "CMD" and then right click and select "Run as administrator")
  • Create a Windows user called "postgres" and add a password (Welcome2017!):
Code Block
languagetext
net /user postgres Welcome2017! /add
  • Now stop the two CAST Storage Service servers, for example:
Code Block
languagetext
net stop CastStorageService2
net stop CastStorageService3
  • Now open a text editor as an administrator (right click and select "Run as administrator") and edit the following files by changing the authentification method to trust. Assuming you have installed the CAST Storage Service with default settings these files will be located here:
    • %PROGRAMFILES%\CAST\CASTStorageService2\db_data\pg_hba.conf
    • %PROGRAMFILES%\CAST\CASTStorageService3\db_data\pg_hba.conf
Code Block
languagetext
//BEFORE
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5
host    all         all         ::1/0                 md5
# This line allows any client to connect to the server. Doing so, we don't need to manage it anymore.
host    all         all         0.0.0.0/0      		  md5
 
//AFTER
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5	trust
# IPv6 local connections:
host    all         all         ::1/128               md5	trust
host    all         all         ::1/0                 md5	trust
# This line allows any client to connect to the server. Doing so, we don't need to manage it anymore.
host    all         all         0.0.0.0/0      		  md5	trust
  • Grant specific Windows permissions to the "postgres" Windows user on the CSS2 and CSS3 data folders. Assuming you have installed the CAST Storage Service with default settings these data folders will be located here:
    • %PROGRAMFILES%\CAST\CASTStorageService2\db_data
    • %PROGRAMFILES%\CAST\CASTStorageService3\db_data
Code Block
languagetext
icacls %PROGRAMFILES%\CAST\CASTStorageService2\db_data /grant postgres:(CI)(OI)F /T
icacls %PROGRAMFILES%\CAST\CASTStorageService3\db_data /grant postgres:(CI)(OI)F /T
Info

This will grant the following permissions to the Windows postgres user:

  • (OI)(CI) - This folder, subfolders, and files.
  • F - Full access (Edit_Permissions+Create+Delete+Read+Write)
  • /T - Traverse all subfolders to match files/directories. This will apply permission changes to all subfolders whether or not they are set to inherit permissions from the parent.

See https://ss64.com/nt/icacls.html for more information.

  • Close the existing command prompt and start a new command prompt (cmd.exe) running it as the Windows postgres user - enter the password when prompted:
Code Block
languagetext
runas /user:postgres "cmd.exe"
  • Set the PATH variable to include the "bin" folder of the CSS3 installation folder:
Code Block
languagetext
set path=%path%;%PROGRAMFILES%\CAST\CASTStorageService3\bin
  • Create a new folder "upgradeCSS" in %TEMP% to store files generated during the upgrade process, assign specific Windows permissions to the new "postgres" Windows user on this folder and then change focus into this new folder:
Code Block
languagetext
mkdir %temp%\upgradeCSS
icacls %temp%\upgradeCSS /grant postgres:(CI)(OI)F /T
cd %temp%\upgradeCSS
  • Run the upgrade process as follows:
Code Block
languagetext
pg_upgrade -d %PROGRAMFILES%\CAST\CASTStorageService2\db_data -D %PROGRAMFILES%\CAST\CASTStorageService3\db_data -b %PROGRAMFILES%\CAST\CASTStorageService2\bin -B %PROGRAMFILES%\CAST\CASTStorageService3\bin -p portCSS2 -P portCSS3
  • When the upgrade process has completed, ensure you remove the trust authentication method in the following two files:
    • %PROGRAMFILES%\CAST\CASTStorageService2\db_data\pg_hba.conf
    • %PROGRAMFILES%\CAST\CASTStorageService3\db_data\pg_hba.conf
  • Now start the two CAST Storage Service servers, for example:
Code Block
languagetext
net start CastStorageService2
net start CastStorageService3

For PostgreSQL installed on Linux

...

  • Edit the authentification method in the pg_hba.conf file for both PostgreSQL 9.2 and 9.6: /opt/PostgreSQL/9.*/data/pg_hba.conf, for example using vi:
Code Block
languagebash
sudo vi /opt/PostgreSQL/9.2/data/pg_hba.conf
 
and:
 
sudo vi /opt/PostgreSQL/9.6/data/pg_hba.conf
    • Switch all authentication methods to trust in both files:

Image Removed

    • Save the files in vi.
  • Create a .pgpass file (this file can contain passwords to be used if the upgrade process requires a password for connection (and no password has been specified otherwise) as follows:
Code Block
languagebash
echo hostname92:port92:database92:postgres:Postgrespassword> ~/.pgpass
echo hostname96:port96:database96:postgres:Postgrespassword>> ~/.pgpass

 

...

The syntax of a .pgpass file should be:

  • hostname:port:database:username:password

Therefore:

...

Enter the port numbers for the respective CSS:

PostgreSQL 9.2.x2280
PostgreSQL 9.6.x2282

...

  • Stop the PostgreSQL instances (you can check the name of the postgres services with the command "sudo systemctl -a | grep -i postgres"), for example: 
Code Block
languagebash
sudo systemctl stop postgresql-9.2
sudo systemctl stop postgresql-9.6
  • Switch to the postgres Linux user using the -u command, and add -i (so that the .pgpass file is read):
Code Block
languagebash
sudo -i -u postgres
  • Set the PATH variable to include the "bin" folder in the PostgreSQL 9.6 installation folder:
Code Block
languagebash
PATH=$PATH:/opt/PostgreSQL/9.6/bin
  • Reload the PostgreSQL configuration (this is required since the pg_hba.conf files have been edited) for both the 9.2 and 9.6 instances:
Code Block
languagebash
pg_ctl -D /opt/PostgreSQL/9.2/data reload
pg_ctl -D /opt/PostgreSQL/9.6/data reload
  • Create a new folder "upgradePostgres" in tmp to store files generated during the upgrade process:
Code Block
languagebash
mkdir /tmp/upgradePostgres
  • Change permissions on the "upgradePostgres" folder:
Code Block
languagebash
chmod 777 /tmp/upgradePostgres
  • Change focus into this "upgradePostgres" folder:
Code Block
languagebash
cd /tmp/upgradePostgres_dir
  • Run the upgrade process as follows:
Code Block
languagetext
pg_upgrade -d /opt/PostgreSQL/9.2/data -D /opt/PostgreSQL/9.6/data -b /opt/PostgreSQL/9.2/bin -B /opt/PostgreSQL/9.6/bin -p portCSS2 -P portCSS3
  • When the upgrade process has completed, ensure you remove the trust authentication method using vi:
Code Block
languagebash
sudo vi /opt/PostgreSQL/9.2/data/pg_hba.conf
 
and:
 
sudo vi /opt/PostgreSQL/9.6/data/pg_hba.conf
  • Start the PostgreSQL instances (you can check the name of the postgres services with the command "sudo systemctl -a | grep -i postgres"), for example: 
Code Block
languagebash
sudo systemctl start postgresql-9.2
sudo systemctl start postgresql-9.6

Post upgrade actions

...

When you have completed the upgrade, you will need to complete the following actions:

Update all CAST Management Studio connection profiles to allow connection to the same schemas on CSS3 

Open the following file with a text editor:

Code Block
languagetext
%appdata%\CAST\CAST\<version>\cast-ms.connectionProfiles.pmx

Modify in the relevant <connectionprofiles.ConnectionProfilePostgres> tag, the following attributes:

  • the host="CSS2" to change the source <CSS2> host name to the target <CSS3> host name. Note that if the CSS2 and CSS3 servers are located on the SAME host, there is no need to do this.
  • the port="2280" to change the source port="2280" (for CSS2) to the target port="2282" (for CSS3). Note that if you are using a custom port number for your CSS/PostgreSQL instance, you should enter it here.
  • the name= attribute in the relevant <connectionprofiles.ConnectionProfilePostgres> tag to ensure there is no confusion as to which CAST Storage Service users are connecting to (this is optional).

When the changes have been made, save the file.

Code Block
languagexml
titleBefore update
<?xml version="1.0" encoding="UTF-8"?>
<document version="1.1">
	<lot symbol="connectionProfiles" label="Connection profiles" requires="connectionprofiles:2.1;pmcgeneral:3.1;system:1.0">
		<connectionprofiles.ConnectionProfiles>
			<connectionProfiles>
				<connectionprofiles.ConnectionProfilePostgres entry="uuid:0f76b31a-e440-4210-af8b-6c16a4f05bec" name="v827_mngt on CastStorageService _ CSS2:2280" user="operator" password="CRYPTED2:90B1A6EC1618661401B724DB5AC34595" schema="v827_mngt" host="CSS2" port="2280"/>
				</connectionProfiles>
			</connectionprofiles.ConnectionProfiles>
	</lot>
</document>
Code Block
languagexml
titleAfter update
<?xml version="1.0" encoding="UTF-8"?>
<document version="1.1">
	<lot symbol="connectionProfiles" label="Connection profiles" requires="connectionprofiles:2.1;pmcgeneral:3.1;system:1.0">
		<connectionprofiles.ConnectionProfiles>
			<connectionProfiles>
				<connectionprofiles.ConnectionProfilePostgres entry="uuid:0f76b31a-e440-4210-af8b-6c16a4f05bec" name="v827_mngt on CastStorageService _ CSS3:2282" user="operator" password="CRYPTED2:90B1A6EC1618661401B724DB5AC34595" schema="v827_mngt" host="CSS3" port="2282"/>
				</connectionProfiles>
			</connectionprofiles.ConnectionProfiles>
	</lot>
</document>

Update connection profiles for CAST Server Manager, CAST Enlighten and CAST Report Center to allow connection to the same schemas on CSS3 

CAST Server Manager, CAST Enlighten and CAST Report Center use a legacy type of connection profile that is difficult to edit manually. The simplest method therefore is to run each exe and either:

  • manually modify the connection settings in the GUI
  • create new connection profiles from scratch

Update cms_inf_store_css table in _MNGT schema on CSS3

If the CSS2 and CSS3 are located on the SAME host, run the following script to perform the required update, where <prefix> is the schema prefix for the schemas that you have moved to CSS3 and where <CSS3> is the host name of the CSS3 server. This will change the port number from 2280 to 2282 and ensure that the display name for the CAST Storage Service in the CAST Management Studio Services view is correct:

Code Block
languagesql
set search_path = <prefix>_mngt;
update cms_inf_store_css set port=2282, object_name='<CSS3>:2282';

If the CSS2 and CSS3 servers are located on DIFFERENT hosts, run the following query instead, where <prefix> is the schema prefix for the schemas that you have moved to CSS3 and where <CSS3> is the host name of the CSS3 server. This will update the port number, host name and ensure that the display name for the CAST Storage Service in the CAST Management Studio Services view is correct:

Code Block
languagesql
set search_path = <prefix>_mngt;
update cms_inf_store_css set port=2282, host='<CSS3>', object_name='<CSS3>:2282';

...

Note:

...

The upgrade process will leave the old CAST Storage Service/PostgreSQL instance in place and unchanged, however, CAST does recommend that you backup any AIP schemas you may have on this server using the CAST tools. See Maintenance activities for CAST Storage Service and PostgreSQL.

(tick)Ensure that no applications are using the CAST Storage Service/PostgreSQL instance.

For CAST Storage Service installed on Windows

Expand
  • Start a command prompt (cmd.exe) as an administrator (search for "CMD" and then right click and select "Run as administrator")
  • Create a Windows user called "postgres" and add a password (Welcome2017!):
Code Block
languagetext
net /user postgres Welcome2017! /add
  • Now stop the two instances, for example:
Code Block
languagetext
net stop CastStorageService2
net stop CastStorageService3
  • Now open a text editor as an administrator (right click and select "Run as administrator") and edit the following files by changing the authentification method to trust. Assuming you have installed the CAST Storage Service with default settings these files will be located here:
    • %PROGRAMFILES%\CAST\CASTStorageService2\db_data\pg_hba.conf
    • %PROGRAMFILES%\CAST\CASTStorageService3\db_data\pg_hba.conf
Code Block
languagetext
//BEFORE
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5
host    all         all         ::1/0                 md5
# This line allows any client to connect to the server. Doing so, we don't need to manage it anymore.
host    all         all         0.0.0.0/0      		  md5
 
//AFTER
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5	trust
# IPv6 local connections:
host    all         all         ::1/128               md5	trust
host    all         all         ::1/0                 md5	trust
# This line allows any client to connect to the server. Doing so, we don't need to manage it anymore.
host    all         all         0.0.0.0/0      		  md5	trust
  • Grant specific Windows permissions to the "postgres" Windows user on the CSS2 and CSS3 data folders. Assuming you have installed the CAST Storage Service with default settings these data folders will be located here:
    • %PROGRAMFILES%\CAST\CASTStorageService2\db_data
    • %PROGRAMFILES%\CAST\CASTStorageService3\db_data
Code Block
languagetext
icacls %PROGRAMFILES%\CAST\CASTStorageService2\db_data /grant postgres:(CI)(OI)F /T
icacls %PROGRAMFILES%\CAST\CASTStorageService3\db_data /grant postgres:(CI)(OI)F /T


Info

This will grant the following permissions to the Windows postgres user:

  • (OI)(CI) - This folder, subfolders, and files.
  • F - Full access (Edit_Permissions+Create+Delete+Read+Write)
  • /T - Traverse all subfolders to match files/directories. This will apply permission changes to all subfolders whether or not they are set to inherit permissions from the parent.

See https://ss64.com/nt/icacls.html for more information.

  • Close the existing command prompt and start a new command prompt (cmd.exe) running it as the Windows postgres user - enter the password when prompted:
Code Block
languagetext
runas /user:postgres "cmd.exe"
  • Set the PATH variable to include the "bin" folder of the CSS3 installation folder:
Code Block
languagetext
set path=%path%;%PROGRAMFILES%\CAST\CASTStorageService3\bin
  • Create a new folder "upgradeCSS" in %TEMP% to store files generated during the upgrade process, assign specific Windows permissions to the new "postgres" Windows user on this folder and then change focus into this new folder:
Code Block
languagetext
mkdir %temp%\upgradeCSS
icacls %temp%\upgradeCSS /grant postgres:(CI)(OI)F /T
cd %temp%\upgradeCSS
  • Run the upgrade process as follows:
Code Block
languagetext
pg_upgrade -d %PROGRAMFILES%\CAST\CASTStorageService2\db_data -D %PROGRAMFILES%\CAST\CASTStorageService3\db_data -b %PROGRAMFILES%\CAST\CASTStorageService2\bin -B %PROGRAMFILES%\CAST\CASTStorageService3\bin -p portCSS2 -P portCSS3
  • When the upgrade process has completed, ensure you remove the trust authentication method in the following two files:
    • %PROGRAMFILES%\CAST\CASTStorageService2\db_data\pg_hba.conf
    • %PROGRAMFILES%\CAST\CASTStorageService3\db_data\pg_hba.conf
  • Now start the two CAST Storage Service servers, for example:
Code Block
languagetext
net start CastStorageService2
net start CastStorageService3


For PostgreSQL installed on Linux

Expand

Edit the authentication method in the pg_hba.conf file for your source and target instances; for example using vi:

Code Block
languagebash
sudo vi /opt/PostgreSQL/9.2/data/pg_hba.conf
 
and:
 
sudo vi /opt/PostgreSQL/9.6/data/pg_hba.conf

Switch all authentication methods to trust in both files and save the files in vi.

Image Added

Create a .pgpass file (this file can contain passwords to be used if the upgrade process requires a password for connection (and no password has been specified otherwise) as follows:

Code Block
languagebash
echo hostname92:port92:database92:postgres:Postgrespassword> ~/.pgpass
echo hostname96:port96:database96:postgres:Postgrespassword>> ~/.pgpass

 

Info

The syntax of a .pgpass file should be:

  • hostname:port:database:username:password

Therefore:

hostname92 / hostname96Enter the Linux hostname for the source and target PostgreSQL instances. They should be identical as they are installed on the same machine.
port92 / port96

Enter the port numbers for the respective instances

database92 / database96Enter the name database used the instances.
postgresThis is the "postgres" login within the PostgreSQL instance itself. It is created specifically during the installation of PostgreSQL.
postgrespasswordEnter the password for the "postgres" login.


Stop the PostgreSQL instances (you can check the name of the postgres services with the command "sudo systemctl -a | grep -i postgres"), for example: 

Code Block
languagebash
sudo systemctl stop postgresql-9.2
sudo systemctl stop postgresql-9.6

Switch to the postgres Linux user using the -u command, and add -i (so that the .pgpass file is read):

Code Block
languagebash
sudo -i -u postgres

Set the PATH variable to include the "bin" folder in the PostgreSQL 9.6 installation folder:

Code Block
languagebash
PATH=$PATH:/opt/PostgreSQL/9.6/bin

Reload the PostgreSQL configuration (this is required since the pg_hba.conf files have been edited) for both the 9.2 and 9.6 instances:

Code Block
languagebash
pg_ctl -D /opt/PostgreSQL/9.2/data reload
pg_ctl -D /opt/PostgreSQL/9.6/data reload

Create a new folder "upgradePostgres" in tmp to store files generated during the upgrade process:

Code Block
languagebash
mkdir /tmp/upgradePostgres

Change permissions on the "upgradePostgres" folder:

Code Block
languagebash
chmod 777 /tmp/upgradePostgres

Change focus into this "upgradePostgres" folder:

Code Block
languagebash
cd /tmp/upgradePostgres_dir

Run the upgrade process as follows:

Code Block
languagetext
pg_upgrade -d /opt/PostgreSQL/9.2/data -D /opt/PostgreSQL/9.6/data -b /opt/PostgreSQL/9.2/bin -B /opt/PostgreSQL/9.6/bin -p portCSS2 -P portCSS3

When the upgrade process has completed, ensure you remove the trust authentication method using vi:

Code Block
languagebash
sudo vi /opt/PostgreSQL/9.2/data/pg_hba.conf
 
and:
 
sudo vi /opt/PostgreSQL/9.6/data/pg_hba.conf

Start the PostgreSQL instances (you can check the name of the postgres services with the command "sudo systemctl -a | grep -i postgres"), for example: 

Code Block
languagebash
sudo systemctl start postgresql-9.2
sudo systemctl start postgresql-9.6


Post upgrade actions

Expand

When you have completed the upgrade, you will need to complete the following actions:

Update all CAST Management Studio connection profiles to allow connection to the same schemas on the target instance

from 8.3.40

Use tools\ConnectionProfile\CAST-Connection-Profile-Configuration.py (see Using the CAST-Connection-Profile-Configuration.py file).

up to 8.3.39

Open the following file with a text editor:

Code Block
languagetext
%appdata%\CAST\CAST\<version>\cast-ms.connectionProfiles.pmx

Modify in the relevant <connectionprofiles.ConnectionProfilePostgres> tag, the following attributes:

  • the host="xxx" to change the source <xxx> host name to the target <xxx> host name. Note that if the instances are located on the SAME host, there is no need to do this.
  • the port="xxxx" to change the source port="xxxx" to the target port="xxxx". Note that if you are using a custom port number for your CSS/PostgreSQL instance, you should enter it here.
  • the name= attribute in the relevant <connectionprofiles.ConnectionProfilePostgres> tag to ensure there is no confusion as to which CAST Storage Service/PostgreSQL users are connecting to (this is optional).

When the changes have been made, save the file.

Code Block
languagexml
titleBefore update
<?xml version="1.0" encoding="UTF-8"?>
<document version="1.1">
	<lot symbol="connectionProfiles" label="Connection profiles" requires="connectionprofiles:2.1;pmcgeneral:3.1;system:1.0">
		<connectionprofiles.ConnectionProfiles>
			<connectionProfiles>
				<connectionprofiles.ConnectionProfilePostgres entry="uuid:0f76b31a-e440-4210-af8b-6c16a4f05bec" name="v827_mngt on CastStorageService _ CSS2:2280" user="operator" password="CRYPTED2:90B1A6EC1618661401B724DB5AC34595" schema="v827_mngt" host="CSS2" port="2280"/>
				</connectionProfiles>
			</connectionprofiles.ConnectionProfiles>
	</lot>
</document>


Code Block
languagexml
titleAfter update
<?xml version="1.0" encoding="UTF-8"?>
<document version="1.1">
	<lot symbol="connectionProfiles" label="Connection profiles" requires="connectionprofiles:2.1;pmcgeneral:3.1;system:1.0">
		<connectionprofiles.ConnectionProfiles>
			<connectionProfiles>
				<connectionprofiles.ConnectionProfilePostgres entry="uuid:0f76b31a-e440-4210-af8b-6c16a4f05bec" name="v827_mngt on CastStorageService _ CSS3:2282" user="operator" password="CRYPTED2:90B1A6EC1618661401B724DB5AC34595" schema="v827_mngt" host="CSS3" port="2282"/>
				</connectionProfiles>
			</connectionprofiles.ConnectionProfiles>
	</lot>
</document>

Update connection profiles for CAST Server Manager, CAST Enlighten and CAST Report Center

CAST Server Manager, CAST Enlighten and CAST Report Center use a legacy type of connection profile that is difficult to edit manually. The simplest method therefore is to run each exe and either:

  • manually modify the connection settings in the GUI
  • create new connection profiles from scratch

When the database name is different from postgres, the host field must follow format : 'LIBPQ:<<HOST>>:<<PORT>>,<<DATABASE', and the port field is not used.

Update cms_inf_store_css table in _MNGT schema on target instance

When the two instances are located on the SAME host, run the following script to perform the required update:

  • where <prefix> is the schema prefix for the schemas that you have moved
  • where target <database> name is 'postgres'
  • where <targetserver> is the host name of the target server
  • where <port> is the port used by your target instance.
Code Block
languagesql
set search_path = <prefix>_mngt;
update cms_inf_store_css set port=<port>, object_name='<targetserver>:<port>';
  • when target database name is different from 'postgres':
Code Block
languagesql
set search_path = <prefix>_mngt;
update cms_inf_store_css set port=<port>, object_name='<targetserver>:<port>,<database>';


Info

If you have moved more than one set of schemas from source to target, you will need to repeat the above queries for each <prefix>_mngt you have moved.


Anchor
method2
method2
Method 4: using the CSS Upgrade Wizard

Note
Note that CSS Upgrade Wizard is deprecated starting CAST AIP 8.3.25. Please use an alternative method.

This method functions for installations of CSS on Windows and PostgreSQL on Linux but can only be run from Windows. The upgrade process uses the CSS Upgrade Wizard, provided as part of the CAST AIP ≥ 8.3.x, and involves a fully automated process of dumping the required schemas to file and then restoring the dumps on the new host. The CAST Storage Services/PostgreSQL do not need to be installed on the same host, and both can be remote to the machine on which you are running the CSS Upgrade Wizard.

Prerequisites

RequiredDescription
(tick)Ensure that no CAST AIP applications are using the source and target CAST Storage Service/PostgreSQL instances.
(tick)Ensure that you are using the CSS Upgrade Wizard provided with CAST AIP 8.3.x.
(tick)

You must not use "localhost" in the Host field in the CAST Upgrade Wizard for the Source and Target Servers even if the servers are both located on the current machine. The value entered in the Host field in the CAST Upgrade Wizard must match the value defined for the Host field in the Management Service editor in the CAST Management Studio:

Image Added

If you have used "localhost" in the CAST Management Studio to define a connection to the Management Service, you MUST update this to use either the server's netbios name or its IP address before running the CSS Upgrade Wizard.

Upgrade action

Expand

Launch the CSS Upgrade Wizard from the Windows Start menu as follows:

Image Added

Info
Note that if the CSS Upgrade Wizard is not visible in the start menu, it is located here: <CAST AIP installation Directory>\CSSAdmin\CSSUpgrade.exe.

When the CSS Upgrade Wizard is launched an initial Welcome page will be displayed. Click Next to continue. The Source Server page will then be displayed:

Image Added

This page enables you to fill in the connection information for your "source server" - i.e. the machine hosting the CAST Storage Service/PostgreSQL whose schemas will be moved.

HostThe host name of the source CAST Storage Service/PostgreSQL as defined in the Host field in the Management Service editor in the CAST Management Studio (see prerequisites above).
PortThe port number used for the source CAST Storage Service/PostgreSQL.
DatabaseThe PostgreSQL database - please leave this set to postgres.
UserPlease leave this set to operator.
PasswordEnter the password for your operator user.

Fill in the required fields and then click Next. The Target Server page will then be displayed:

Image Added

This page enables you to fill in the connection information for your "target server" - i.e. the machine hosting the CAST Storage Service/PostgreSQL that will "receive" the schemas from the source CAST Storage Service/PostgreSQL.

HostThe host name of the target CAST Storage Service/PostgreSQL. You can also use its IP address.
PortThe port number used for the targetCAST Storage Service/PostgreSQL.
DatabaseThe PostgreSQL database - please leave this set to postgres.
UserPlease leave this set to operator.
PasswordEnter the password for your operator user.

Fill in the required fields and then click Next. The Schema Upgrade Selection page will then be displayed. This page will list all the CAST AIP schemas on the Source Server that are eligible for upgrade to the Target Server. Select the schemas that you want to - place a check mark next to each schema as shown below. You can use the Refresh button (lower right corner) to refresh the list of schemas if required.

Image Added

Click Next to continue. This next page requires you to input a temporary "Working Folder". This folder will be used to store the selected schemas in file format during the transfer process - please choose a folder with plenty of free space. The folder must also already exist and must be empty.

Image Added

Click Finish to continue the process. A summary of the upgrade action is the displayed:

Image Added

Check that the summary of the upgrade action is as you require. Click Upgrade to start the process. On completion a message will be displayed showing you where the log of the entire process is stored:

Image Added

Please check the log file to ensure there are no failures reported during upgrade. A final page will be displayed - click Finish to terminate the process. Finally, check that the schemas have been correctly moved to the target server using CAST Server Manager.

Post upgrade actions

Expand

When you have completed the upgrade, you will need to complete the following actions:

Update all CAST Management Studio connection profiles to allow connection to the same schemas on the new host

Open the following file with a text editor:

Code Block
languagetext
%appdata%\CAST\CAST\<version>\cast-ms.connectionProfiles.pmx

Modify in the relevant <connectionprofiles.ConnectionProfilePostgres> tag, the following attributes:

  • the host="xxx" to change the source <xxx> host name to the target <xxx> host name. Note that if the two servers are located on the SAME host, there is no need to do this.
  • the port="xxxx" to change the source port="xxxx" to the target port="xxxx". Note that if you are using a custom port number for your CSS/PostgreSQL instance, you should enter it here.
  • the name= attribute in the relevant <connectionprofiles.ConnectionProfilePostgres> tag to ensure there is no confusion as to which CAST Storage Service/PostgreSQL users are connecting to (this is optional).

When the changes have been made, save the file.

Code Block
languagexml
titleBefore update
<?xml version="1.0" encoding="UTF-8"?>
<document version="1.1">
	<lot symbol="connectionProfiles" label="Connection profiles" requires="connectionprofiles:2.1;pmcgeneral:3.1;system:1.0">
		<connectionprofiles.ConnectionProfiles>
			<connectionProfiles>
				<connectionprofiles.ConnectionProfilePostgres entry="uuid:0f76b31a-e440-4210-af8b-6c16a4f05bec" name="v8325_mngt on CastStorageService _ MySourceServer:2282" user="operator" password="CRYPTED2:90B1A6EC1618661401B724DB5AC34595" schema="v8325_mngt" host="MySourceServer" port="2282"/>
				</connectionProfiles>
			</connectionprofiles.ConnectionProfiles>
	</lot>
</document>


Code Block
languagexml
titleAfter update
<?xml version="1.0" encoding="UTF-8"?>
<document version="1.1">
	<lot symbol="connectionProfiles" label="Connection profiles" requires="connectionprofiles:2.1;pmcgeneral:3.1;system:1.0">
		<connectionprofiles.ConnectionProfiles>
			<connectionProfiles>
				<connectionprofiles.ConnectionProfilePostgres entry="uuid:0f76b31a-e440-4210-af8b-6c16a4f05bec" name="v8325_mngt on CastStorageService _ MyTargetServer:2282" user="operator" password="CRYPTED2:90B1A6EC1618661401B724DB5AC34595" schema="v8325_mngt" host="MyTargetServer" port="2282"/>
				</connectionProfiles>
			</connectionprofiles.ConnectionProfiles>
	</lot>
</document>

Update connection profiles for CAST Server Manager, CAST Enlighten and CAST Report Center

CAST Server Manager, CAST Enlighten and CAST Report Center use a legacy type of connection profile that is difficult to edit manually. The simplest method therefore is to open each one and either:

  • manually modify the connection settings
  • create new connection profiles from scratch

Update server display name for CAST Management Studio

The following action will ensure that the name displayed for the CAST Storage Service in the CAST Management Studio Services view is updated to the new host name:

Image Added

Run the following query in PGAdmin against the _MNGT table on the target CAST Storage Service/PostgreSQL (i.e. the CAST Storage Service/PostgreSQL to which you have moved the schemas): replacing <server B> with the target CAST Storage Service/PostgreSQL name:

Code Block
languagesql
select 'update ' || table_schema || '.' || table_name || ' set object_name = 
''<server B>'' where host = ''<server B>'' ;'
from information_schema.tables
where table_name = 'cms_inf_store_css'

The above query will produce update query scripts for every CAST AIP schema on the target CAST Storage Service/PostgreSQL to update the host names in the cms_inf_store_css table. For example:

Image Added

Copy each update query script you need to run (these should cover all the CAST AIP schemas you have moved to the target CAST Storage Service/PostgreSQL with the CSS Upgrade Wizard) into a new PGAdmin query window, as follows:

Image Added

Run the query.

Note about custom tablespaces

If you have used custom tablespaces on an existing CAST Storage Service/PostgreSQL instance and are moving CAST schemas to a new CAST Storage Service/PostgreSQL instance where default tablespaces will be used, then you should ensure that you run the following queries to update the references in the CAST schemas to the tablespaces that will be used - the queries essentially reset the tablespaces references to use the default tablespaces.

These queries should be run on all schemas that will use the default tablespaces:

Code Block
update sys_site_options
set option_value = 'pg_default'
where option_name in ('SITE_TABLE_SPACE','SITE_TEMP_SPACE');

update sys_site_options
set option_value = ''
where option_name in ('SITE_DATA_SPACE','SITE_INDEX_SPACE');