On this page:
Target audience:
- CAST AI Administrators
Summary: CAST does not provide a CAST Storage Server (CSS) installer for Linux environments, however, CAST recommends installing the equivalent PostgreSQL version on a Linux server to take advantage of the superior performance over CSS installed on Windows - please follow the instructions below to do so.
Note that these instructions are for deploying PostgreSQL 9.6.x - equivalent to CSS3.
PostgreSQL can be installed using RPMs (binary) or SRPMs (source) managed by YUM. This is available for the following Linux distributions (CAST recommends to use only 64-bit platforms): Locate and edit your distribution .repo file, located: To the section(s) identified above, you need to append a line (otherwise dependencies might resolve to the postgresql supplied by the base repository): You cannot customize the location of PGDATA by using ./postgresql96-setup, therefore you need to edit the following file and change the default location of PGDATA: Once installed, please run the following command to initialize the server: Edit the pg_hba.conf file at /usr/local/pgsql/data using the vi command: Please replace the sample 192.168.x.x IP address and /24 subnet below with the appropriate allowed client IP addresses/subnets: Note that adding the following line will ensure ANY client can connect: To check if the server itself is configured correctly for CAST AIP, we need to verify and modify certain parameters in the postgresql.conf (the file is in the postgresql directory \db_data). The postgresql.conf settings listed below are directly related to available RAM for the CSS server - and as such their configuration is specific to the host machine. Please use http://pgtune.leopard.in.ua/ on the host machine to identify the correct settings for your host machine and then modify postgresql.conf with the settings provided by pgtune: The following settings should also be modified to the values listed below: Please refer to the parameters in the attached postgresql.conf file - this is taken directly from the CAST AIP CSS 3.0.x setup. The server needs to be restarted to take into account any modification. The equivalent CAST Storage Service provided by CAST includes two default users as follows: If you would like to create these users, use the following commands: If you are using CAST AIP ≥ 8.3.12, you are also free to create your own users and then use them with CAST AIP and related software - you need to have a minimum of one user with the SUPERUSER permission and then grant the "postgres" role to this user. For example: When using CAST AIP ≥ 8.3.30, it is possible to create custom users that do NOT require the SUPERUSER permission if you prefer not to grant this. For example, this script creates a role "my_role" with the password "my_password" that can login. The role has not been granted the SUPERUSER permission and instead only the minimum permissions required to operate CAST AIP are granted: 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. Using CAST Server Manager, connect to the 'postgres' database and follow the installation instructions in Install CAST AIP 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. Change the following parameters in the configuration file (..\db_data\postgresql.conf): Set this parameter to off when only actual row counts, and not exact times, are needed:Installation via YUM
Configure your YUM repository
exclude=postgresql*
Update the RPM package
[postgres@cssx data]$ rpm -ivh https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
[postgres@cssx data]$ yum update
Install latest service pack from PostgreSQL
[postgres@cssx data]$ yum install postgresql96 postgresql96-server postgresql96-libs postgresql96-contrib postgresql-devel
Configure data storage location
/usr/lib/systemd/system/postgresql-9.6.service
Initialize the PostgreSQL database server to configure data storage location
[postgres@cssx data]$ export PGSETUP_INITDB_OPTIONS="-E 'UTF-8' --no-locale"
[postgres@cssx data]$ cd /usr/pgsql-9.6/bin
[postgres@cssx data]$ ./postgresql96-setup initdb
Configuring the server/database
Edit the pg_hba.conf file
[postgres@cssx data]$ vi pg_hba.conf
# 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
host all all 0.0.0.0/0 md5
Check server configuration
pgtune
Other settings
listen_addresses '*' Instead of localhost by default. Enables connectivity from other machines. Port 2282 max_connections 300 temp_buffers 32MB work_mem 64MB Higher value than the setting provided by pgtune. maintenance_work_mem 1024MB Memory for backup/restore/vacuum operations. fsync off synchronous_commit off full_page_writes off commit_delay 10 checkpoint_completion_target 0.9 cursor_tuple_fraction 1.0 log_checkpoints on log_destination 'stderr' logging_collector on log_line_prefix '%t [%p]: [%l-1] ' Don't forget the space before final quote mark log_temp_files 1024kB log_autovacuum_min_duration 1000ms autovacuum_vacuum_cost_limit 200 bytea_output 'escape' datestyle 'iso, mdy' Ensure that this option is active (it may already be active). lc_messages 'C' lc_monetary 'C' lc_numeric 'C' lc_time 'C' max_locks_per_transaction 4096 Higher value than 64 by default. standard_conforming_strings on Start the PostgreSQL database server
[postgres@cssx data]$ systemctl enable postgresql-9.6.service
[postgres@cssx data]$ systemctl start postgresql-9.6.service
Create users
Username Password Permissions Notes operator CastAIP SUPERUSER - guest WelcomeToAIP - Note that in the CAST AIP ≥ 8.3.11, the "guest" user is no longer used. [postgres@cssx data]$ psql
*create user operator with SUPERUSER password 'CastAIP';
*create user guest with password 'WelcomeToAIP';
*grant postgres to operator;
CAST AIP ≥ 8.3.12 - custom users
[postgres@cssx data]$ psql
*create user my_user with SUPERUSER password 'my_password';
*grant postgres to my_user;
[postgres@cssx data]$ psql
*create role my_role LOGIN PASSWORD 'my_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
*grant create, connect, temporary on database postgres to my_role;
postgres database
CAST AIP schemas
How to log all statement's plan
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
auto_explain.log_timing = off # this parameter has no effect unlessauto_explain.log_analyze is enabled