Create ImageMaster database with ImageMaster 9 model

The same database model is needed for the standard database model and the audit trail database model. So if you plan to set up a dedicated database instance for audit trails, you must use the same scripts to create this database model for audit trails.

Preparations

Transfer the installation scripts from the ImageMaster assembly release (subfolder “db-model/ima-db-model-9.x.y.z/PostgreSQL”) onto the database server. Make sure that these scripts are owned by the “postgres” system account and that the shell script is executable, e.g.

Start database cluster

Sudo into the postgres system account:

Execute the “pg_ctl” command to start the database cluster (with the matching installation path for your specific version). The command below also generates a logfile in the current directory:

For related details also see chapter Start, stop, and status.

Create database “imadb”

Execute the create-database shell script (from the ImageMaster assembly release) to create the ImageMaster database:

The script internally executes the “CREATE DATABASE statement” [PgSQL CreateDB] and asks for the relevant parameters:

• database name, e.g. “imadb"

  This refers to the dedicated ImageMaster database name, which must also be configured in the connection settings on the application server according to the installation manual [IM ImageMaster], seeDatabase installation.

• locale for CTYPE, e.g. “en_US.UTF-8”

  Set the same locale which is set on the application server (also see chapter Locale settings). With this setting you can override the default cluster locale that was set in chapter Create and configure database cluster by step “Create a database cluster:”.

As another alternative, create the database with the dedicated database owner using the commands below.

Alternative to create database with dedicated user and schema

First connect to the cluster as a user with admin privileges (for example, by running the “psql” command as “postgres” OS user). Then execute the following command where you specify the username “<imauser>” and the password “<imapassword>”:

CREATE ROLE <imauser>
  LOGIN ENCRYPTED
  PASSWORD '<imapassword>'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

Execute the following command to create the database with the above specified user <imauser> as an owner:

CREATE DATABASE <imadb>
  WITH OWNER = <imauser>
  TEMPLATE = 'template0'
  ENCODING = 'UTF8'
  LC_CTYPE = '<lc_ctype>';

• <imadb> refers to the database name, which must also be configured in the connection settings on the application server according to the installation manual [IM ImageMaster].

  In these connection settings on the application server you will also need to set the username and password from above.

• <lc_ctype>: Set the same locale which is set on the application server (also see chapter Locale settings). With this setting you can override the default cluster locale that was set in chapter Create and configure database cluster by step “Create a database cluster:”.

Connect to the newly created database (for example, by running “psql -d <imadb>” as “postgres” OS user) and create the ImageMaster schema “<imaschema>”. The schema is a namespace inside the database where ImageMaster objects will be stored:

CREATE SCHEMA <imaschema> AUTHORIZATION <imauser>;
ALTER ROLE <imauser> SET search_path=<imaschema>, "$user", public;

Create ImageMaster database model

Use the core SQL script “ima9_core_postgresql.sql” (from the ImageMaster assembly release) to create the ImageMaster database model. The script requires to specify tablespace names of existing tablespaces:

• In a production environment it is recommended creating two separate tablespaces, one for database tables and one for database indices. For related details on tablespace creation see [PgSQL Tablespaces].

• There is an out-of-the-box tablespace “pg_default” that can be used for testing purposes.

• For all required tablespaces there are placeholders in the prepared SQL script, that can be adjusted (check for instructions which are directly included as comments in the script).

• Instead of defining the tablespace names in the script, these can also be passed as parameters in a command line call.

The following example illustrates a script call with the instruction to use the default tablespace “pg_default” for both required tablespaces (parameters “TABSPACE” and “IDXSPACE”) for the already existing database “imadb” and database user “imauser”:

Check the output of the script execution for errors. About 1.437 rows of output should be generated which indicate the creation of tables, comments, and functions. If the script finishes successfully, the last output row indicates a final “COMMIT” statement, otherwise, if there were any errors, a “ROLLBACK” is indicated.