Note that using PostgreSQL 9.2.x for CAST AIP is now considered "deprecated support".

Summary: CAST does not provide a CAST Storage Server (CSS) installer for Linux environments, however, it is possible to install the equivalent PostgreSQL version on a Linux server if you need to - please follow the instructions below.

Note that these instructions are for deploying PostgreSQL 9.2.x - equivalent to CSS2.

Download and install PostgreSQL

Download from EnterpriseDB

Download and install a release of PostgreSQL from http://www.enterprisedb.com/products-services-training/pgdownload

Initialize the PostgreSQL database server to configure data storage location

Once installed, please run the following command to configure the data storage location. The -D option indicates the location where the data will be stored:

[postgres@cssx postgresql-9.2.13]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

Configuring the server/database

Edit the pg_hba.conf file

Edit the pg_hba.conf file at /usr/local/pgsql/data using the vi command:

[postgres@cssx data]$ vi pg_hba.conf

Please replace the sample 192.168.x.x IP address below with the appropriate allows client IP addresses:

# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 192.168.21.57/24 md5
 # IPv6 local connections:
host all all ::1/128 md5

Note that adding the following line will ensure ANY client can connect:

host all all 0.0.0.0/0 md5

Start the PostgreSQL Database Server

[postgres@cssx postgresql-9.2.13]$ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data -i

Configure the 'postgres' database with the pgAdmin tool

All CAST AIP products will expect the CAST AIP schemas to be members of the postgres database - i.e. all connections to a PostgreSQL instance will connect direct to the postgres database. This database is created by default during the installation of the PostgreSQL instance and should be retained. While you can create an alternative custom database, it is not possible to connect to a custom database using any CAST AIP products.

When using the PostgreSQL installer from EnterpriseDB, the default 'postgres' database will be created with parameters that may cause issues when using the server to host CAST AIP schemas. As such, CAST highly recommends that you create another default database and remove the one created by the installer. You should use the pgAdmin tool to do this:

  • Create a new database called "postgres1" by right clicking on the Databases node and selecting New Database:

  • Set the owner to the postgres user in the Properties tab:

  • Ensure the following parameters are set in the Definition tab:
    • Encoding = UTF8
    • Tablespace = pg_default
    • Collation (LC_COLLATE) = C
    • Character Type (LC_CTYPE) = C

  • Leave all other parameters at their defaults
  • The SQL script to create the new database should look like this:

  • Once created, the new database will appear alongside the default database:

  • To make the postgres1 database the default database, first disconnect from the server by right clicking it and selecting Disconnect Server:

  • When disconnected, right click the server and select Properties

  • Now change the Maintenance Database option from postgres to template1

  • Now reconnect to the server and delete the existing postgres database (ensure you do not have any production schemas attached to this database!) by right clicking the postgres database and selecting Delete/Drop:

  • Rename the postgres1 database to postgres by right clicking the postgres1 database and selecting Properties, then using the Name field to rename the database:

  • Now disconnect from the server again by right clicking it and selecting Disconnect Server
  • When disconnected, right click the server and select Properties
  • Now change the Maintenance Database option again, from template1 to postgres

  • The new database is now up and running with the correct parameters.

Server configuration

To check if the server itself is configured correctly CAST AIP, we need to verify the parameters below found in postgresql.conf (the file is in the postgresql directory\db_data):

Parameters
Required value
Comments
Port2280
max_connections300
shared_buffers2GB
temp_buffers32MB
work_mem64MB
maintenance_work_mem1024MB
fsyncoff
synchronous_commitoff
full_page_writesoff
wal_buffers16MB
commit_delay10
checkpoint_segments128
checkpoint_completion_target0.9
set_page_cost1.0
random_page_cost2.0
effective_cache_size4GB
default_statistics_target100
cursor_tuple_fraction1.0
log_destination'stderr'
logging_collectoron
log_min_messageswarning
log_min_duration_statement-1
log_line_prefix'%t [%p]: [%l-1] 'Don't forget the space before final quote mark
autovacuumon (in windows installation, this is the default value)
autovacuum_vacuum_threshold50
autovacuum_analyze_threshold50
autovacuum_vacuum_scale_factor0.2
autovacuum_vacuum_cost_delay20ms
autovacuum_vacuum_cost_limit200
bytea_output'escape'
client_encodingsql_ascii (in windows installation, this is the default value)
lc_messafes'C'
lc_monetary'C'
lc_numeric'C'
lc_time'C'
max_locks_per_transaction4096
standard_conforming_stringson

Please refer to the parameters in the attached postgresql.conf file - this is taken directly from the CAST AIP CSS2 setup.

The server needs to be restarted to take account the modification.

Create CAST AIP users 'operator' and 'guest' with the standard passwords

[postgres@cssx data]$ psql
*create user operator with password 'CastAIP';
*create user guest with password 'WelcomeToAIP';
Note that in the CAST AIP ≥ 8.3.11, the "guest" user is no longer used, therefore it is no longer necessary to create it.

Grant the required privileges to the 'operator' user

Grant the same privileges as the 'postgres' user to the 'operator' user. We can use the pgadmin tool to do so as shown below by right clicking on the 'operator' user. Please use the latest version of pgadmin > v1.16.X:

Use Server Manager to install the CAST schemas

Using CAST Server Manager, connect to the 'postgres' database and follow the installation instructions in Install CAST schemas. Please make sure you are using the correct port and connection string when connecting to the PostgreSQL Server:

Note: When you execute CAST Server Manager to install the schemas, the Connection profile, including the user name and password, are stored in a CWSIProfileConnection.INI located in the CAST_ALL_USERS_PATH (refer to the CastGlobalSettings.ini file to set this variable). If any user executes CAST Server Manager on a machine where this file has been defined, they will see the list of Server Connection Profiles stored in CWSIProfileConnection.INI. The password of the SQL user (operator) is encrypted in this file. If you don’t want to share the password with all users, you can predefine the list of Server Connection Profiles and set this file as read-only for the users.

Make 'operator' the schema owner for all CAST schemas

This will allow the CAST Management Studio to connect to the postgres database. We can use the pgAdmin tool to change the schema owner to 'operator' for each schema as shown below (right click on each schema):

How to log all statement's plan

Change the following parameters in the configuration file  (..CAST\CASTStorageService2\db_data\postgresql.conf):


auto_explain.log_min_duration = '0s'            # setting this to zero logs all plan
auto_explain.log_nested_statements = on         # log statements executed inside a function
auto_explain.log_verbose = on                   # it's equivalent to the VERBOSE option of EXPLAIN
auto_explain.log_buffers = on                   # it's equivalent to the BUFFERS option of EXPLAIN
auto_explain.log_analyze = on                   # causes EXPLAIN ANALYZE output; when this parameter is on, per-plan-node timing occurs for all statements executed, whether or not they run long enough to actually get logged. This can have an extremely negative impact on performance. Turning off auto_explain.log_timing ameliorates the performance cost, at the price of obtaining less information.
shared_preload_libraries = 'auto_explain'       # change requires restart

Set this parameter to off when only actual row counts, and not exact times, are needed:


auto_explain.log_timing = off                   #  this parameter has no effect unlessauto_explain.log_analyze is enabled