How to use the CSSAdmin tools for backup, restore and optimization

Overview

CAST provides a set of tools with CAST Imaging Core external link that provide the following actions:

  • backup a single or multiple schemas
  • restore a single or multiple schemas
  • optimize a single schema to improve performance

The tools can be found in the CSSAdmin folder at the root of your CAST Imaging Core installation on your analysis node(s). They should be run from the command line, or preferably via a batch file to control the script. All commands should be placed on one single line and any paths (i.e. to the .exe, dump file or log file) must be surrounded with quote marks if the path contains spaces.

Backing up schemas

Two tools are available to back up schemas:

  • CSSBackup.exe - will back up one schema at a time
  • CSSBackupAll.exe - will backup multiple schemas in one go

Both tools will back up any schema to file (one file per schema) - this file can then be used in a restore action via the CSSRestore.exe / CSSRestoreAll.exe tools. The backup is a “real” backup, i.e. the entire schema is backed up, not just the data.

Tool options

Option Description CSSBackup.exe CSSBackupAll.exe Required?
-schema Name of CAST schema to backup. ✅ ❌ ✅
-port CAST Storage Service/PostgreSQL instance port number (default: 2280) ✅ ✅ ✅
-password CAST Storage Service/PostgreSQL instance operator user password. ✅ ✅ ✅
-dumpdir Path to the folder that will store the result of the backup action. This folder must already exist. All schemas will be backed up using the .backup file extension. ❌ ✅ ✅
-file Path and name of backup dump file, using the .cssdmp extension. Backup will fail if this file already exists. ✅ ❌ ✅
-log Path and name of output log file - does not need to exist already (logging functions in append mode). The resulting file is text based. Additional text based log files are created in the same folder as specified by the -log option (one per schema). These log files will be named as follows and contain the PostgreSQL tools log messages:

<name-of-file-defined-by-"log"-option>.log_<schema-name>_<schema-type>_YYYMMDD8-HHMMSS.txt		 ✅ ✅ ✅
-host Name/IP address of machine hosting the CAST Storage Service/PostgreSQL instance (default: localhost). ✅ ✅ ❌
-username CAST Storage Service/PostgreSQL instance username (default: Operator). ✅ ✅ ❌
-database Use this option if you need to connect to a CAST Storage Service/PostgreSQL instance that uses a custom database, i.e. not postgres (default: postgres). ✅ ✅ ❌
-logtime Use this option to toggle the use of timestamps in all log files. By default this option is active. To disable it, use -logtime-. ✅ ✅ ❌
-verbose Use this option to toggle verbose log messages, specifically log messages generated by the PostgreSQL (pg_dump/pg_restore). By default this option is disabled. To activate it, use -verbose. When enabled, verbose log messages are written to the <schema-name>_<schema-type>.log_<schema-name>_<schema-type>_YYYMMDD8-HHMMSS.txt file located in the same folder as specified by the -log option. ✅ ✅ ❌
-exedir Used when you want to run the tool from outside the CAST Imaging Core installation folder: allows you to specify the path to the CAST Imaging Core installation folder. ✅ ✅ ❌
-compress (boolean: Y or N) Determines whether the resulting backup dump file(s) is compressed or not (default: Y). Setting -compress N is not recommended as the resulting file will be significantly bigger. ✅ ✅ ❌
-parallel Use this option to define multiple CPU threads to increase job performance. By default this option is not active therefore one single thread is used. Using the option -parallel on its own will use three threads. To use additional threads, define the number you require, for example -parallel 5 to use five threads. Note that the tool does not impose any limits on thread numbers, however a high thread number could use up all resources while the job is being actioned. ❌ ✅ ❌
-schemas Use this option to define the schemas you wish to backup, rather than backing up ALL schemas on the instance. Schemas to backup must be provided in a comma separated list, for example:

-schemas general_measure,app1_local,app1_central,app2_local,app22_mngt,app3_central		 ❌ ✅ ❌
-psqlexedir Allows the definition of an alternative location (for example, outside the CAST Imaging Core installation folder) for the PostgreSQL binaries used for the tool:
  • pg_dump.exe
  • psql.exe
  • pg_restore.exe
For example (this path must not end with a back slash ( \ )):

-psqlexedir C:\some_folder\pg_binaries		 ✅ ✅ ❌
-h Displays a list of available commands. ✅ ✅ ❌

Examples with minimum required options

Backup one schema called “my_schema” on a local instance to a file called “my_schema.cssdmp”:

CSSBackup.exe -port 2284 -schema my_schema -password CastAIP -file C:\temp\my_schema.cssdmp -log C:\temp\log\my_schema.log

Backup all schemas on a local instance to a folder called “output”:

CSSBackupAll.exe -port 2284 -password CastAIP -dumpdir C:\temp\output -log C:\temp\log\backup.log

Backup specific schemas on a local instance to a folder called “output”:

CSSBackupAll.exe -port 2284 -password CastAIP -schemas general_measure,app1_local,app1_central,app1_mngt,app2_local,app2_central,app2_mngt -dumpdir C:\temp\output -log C:\temp\log\backup.log

Supported data types

Ordinarily, the CSSBackup/CSSBackupAll tools will backup a standard analysis result schema and all its data. However, if you have customized the schema (by adding new tables or adding new columns to existing tables), you may find that the customizations are not included in the backup result. This can occur when your customizations use data types that are not supported by the backup process. CAST supports only the following datatypes:

  • integer
  • double precision
  • numeric
  • varchar
  • char
  • timestamp
  • text
  • bytea

If an unsupported data type is encountered during the backup process, the table containing the unsupported data type will be excluded from the backup. A list of excluded tables is displayed in the log file, which you should check on completion of the backup process.

Restoring schemas

Two tools are available to back up schemas:

  • CSSRestore.exe - will restore one schema at a time from a backup generated using CSSBackup.exe
  • CSSRestoreAll.exe - will restore multiple schemas in one go from a backup generated using CSSBackupAll.exe

Tool options

Option Description CSSRestore.exe CSSRestoreAll.exe Required?
-schema Name of CAST schema to restore. ✅ ❌ ✅
-port CAST Storage Service/PostgreSQL instance port number (default: 2280) ✅ ✅ ✅
-password CAST Storage Service/PostgreSQL instance operator user password. ✅ ✅ ✅
-dumpdir Path to the folder containing the result of the CSSBackupAll action, i.e. the backed up files. ❌ ✅ ✅
-file Path and name of backup dump file, using the .cssdmp extension. ✅ ❌ ✅
-log Path and name of output log file - does not need to exist already (logging functions in append mode). The resulting file is text based. Additional text based log files are created in the same folder as specified by the -log option (one per schema). These log files will be named as follows and contain the PostgreSQL tools log messages:

<name-of-file-defined-by-"log"-option>.log_<schema-name>_<schema-type>_YYYMMDD8-HHMMSS.txt		 ✅ ✅ ✅
-host Name/IP address of machine hosting the CAST Storage Service/PostgreSQL instance (default: localhost). ✅ ✅ ❌
-username CAST Storage Service/PostgreSQL instance username (default: Operator). ✅ ✅ ❌
-database Use this option if you need to connect to a CAST Storage Service/PostgreSQL instance that uses a custom database, i.e. not postgres (default: postgres). ✅ ✅ ❌
-logtime Use this option to toggle the use of timestamps in all log files. By default this option is active. To disable it, use -logtime-. ✅ ✅ ❌
-verbose Use this option to toggle verbose log messages, specifically log messages generated by the PostgreSQL (pg_dump/pg_restore). By default this option is disabled. To activate it, use -verbose. When enabled, verbose log messages are written to the <schema-name>_<schema-type>.log_<schema-name>_<schema-type>_YYYMMDD8-HHMMSS.txt file located in the same folder as specified by the -log option. ✅ ✅ ❌
-exedir Used when you want to run the tool from outside the CAST Imaging Core installation folder: allows you to specify the path to the CAST Imaging Core installation folder. ✅ ✅ ❌
-parallel Use this option to define multiple CPU threads to increase job performance. By default this option is not active therefore one single thread is used. Using the option -parallel on its own will use three threads. To use additional threads, define the number you require, for example -parallel 5 to use five threads. Note that the tool does not impose any limits on thread numbers, however a high thread number could use up all resources while the job is being actioned. ❌ ✅ ❌
-overwrite Use this option to force existing identically named schemas on the target instance to be dropped when the restore action is run. By default this option is not active and therefore if an identically named schema already exists on the target instance then the entire restore action will fail. ❌ ✅ ❌
-extension Use this option if the folder defined by -dumpdir containing the backed up schemas contains files with multiple different file extensions and you only want to restore files with specific file extensions. By default this option is active with the extension .backup meaning only files with the extension .backup will be restored. For example you can define -extension schemadump and only files with the .schemadump extension will be restored (anything else is ignored). ❌ ✅ ❌
-psqlexedir Allows the definition of an alternative location (for example, outside the CAST Imaging Core installation folder) for the PostgreSQL binaries used for the tool:
  • pg_dump.exe
  • psql.exe
  • pg_restore.exe
For example (this path must not end with a back slash ( \ )):

-psqlexedir C:\some_folder\pg_binaries		 ✅ ✅ ❌
-h Displays a list of available commands. ✅ ✅ ❌

Examples with minimum required options

Restore one schema called “my_schema” to a local instance from a file called “my_schema.cssdmp”:

CSSRestore.exe -port 2284 -schema my_schema -password CastAIP -file C:\temp\my_schema.cssdmp -log C:\temp\log\my_schema.log

Restore all backed up schemas to a local instance from a folder called “output”:

CSSRestoreAll.exe -port 2284 -password CastAIP -dumpdir C:\temp\output -log C:\temp\log\restore.log

Optimizing schemas

Over time and through continued use, the efficiency of your application’s schemas may well start to degrade (“gaps” in table data, inefficient indexes etc.) - this degradation can significantly impact the performance of CAST Imaging, with the most visible impact seen in the performance of the CAST Dashboards. To counter this, CAST provides a tool (called CssOptimize.exe) that can be run to optimize the schemas stored in your CAST Storage Service/PostgreSQL instance - i.e. to clean up defects that have appeared over time.

This tool is run automatically when running analyses, e.g.:

  • immediately on completion of a snapshot (for the Dashboard schema)
  • immediately on completion of a data upload to the Measure schema

It is also possible to change/disable the behavior of the automated use of the tool - see: Using the CSS Optimize option.

The optimize actions can also be run manually via the UI:

  • See here (for the Measurement schema)
  • See here (for the Application schemas)

To manually optimize a schema, use the CSSOptimize.exe tool.

Tool options

Option Description Required?
-schema Name of CAST schema to optimize. ✅
-port CAST Storage Service/PostgreSQL instance port number (default: 2280) ✅
-password CAST Storage Service/PostgreSQL instance operator user password. ✅
-log Path and name of output log file - does not need to exist already (logging functions in append mode). The resulting file is text based. Additional text based log files are created in the same folder as specified by the -log option (one per schema). These log files will be named as follows and contain the PostgreSQL tools log messages:

<name-of-file-defined-by-"log"-option>.log_<schema-name>_<schema-type>_YYYMMDD8-HHMMSS.txt		 ✅
-operation Choose the PostgreSQL optimize operation to perform: ✅
-host Name/IP address of machine hosting the CAST Storage Service/PostgreSQL instance (default: localhost). ❌
-username CAST Storage Service/PostgreSQL instance username (default: Operator). ❌
-database Use this option if you need to connect to a CAST Storage Service/PostgreSQL instance that uses a custom database, i.e. not postgres (default: postgres). ❌
-logtime Use this option to toggle the use of timestamps in all log files. By default this option is active. To disable it, use -logtime-. ❌
-h Displays a list of available commands. ❌

Examples with minimum required options

Optimize one schema with “analyze” called “my_schema” on a local instance:

CSSOptimize.exe -port 2284 -schema my_schema -password CastAIP -operation analyze -log C:\temp\log\my_schema.log