Page tree

Versions Compared


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



Installation via YUM 

PostgreSQL can be installed using RPMs (binary) or SRPMs (source) managed by YUM. This is available for the following Linux distributions (CAST recommends using only 64-bit platforms):

  • Fedora
  • CentOS
  • Red Hat Enterprise LinuxRecommended versions of Linux:
    • all Linux distributions based on (or derived from) Red Hat

Configure your YUM repository

Locate and edit your distribution .repo file, located:

On Fedora/etc/yum.repos.d/fedora.repo and /etc/yum.repos.d/fedora-updates.repo[fedora] sections

  • On CentOS/etc/yum.repos.d/CentOS-Base.repo[base] and [updates] sections
  • On Red Hat/etc/yum/pluginconf.d/rhnplugin.conf [main] section

To the section(s) identified above, you need to append a line (otherwise dependencies might resolve to the postgresql supplied by the base repository):

Code Block

Update the RPM package

A PGDG file is available for each distribution. Browse and find your correct RPM. There is only one single repo RPM for all PostgreSQL versions for each platform. Ensure you modify the link to the rpm file in the command below: this command will download the RPM package for the latest release of Red Hat:

Code Block
rpm -ivh
yum update

Install PostgreSQL

First run the following command to view the available releases:

Code Block
yum list postgresql*

Then run the install, for example to install PostgreSQL 11:

Code Block
yum install postgresql11-server

Configure data storage location

The default location for PostgreSQL data storage is set to (where <name> is the release number):

Code Block

To customize this location, you need to edit the following file (where <name> is the release number):

Code Block

Initialize the PostgreSQL database server to configure data storage location

Once installed, please run the following command to initialize the server (where <name> is the release number):

Code Block
export PGSETUP_INITDB_OPTIONS="-E 'UTF-8' --no-locale"
/usr/pgsql-<name>/bin/postgresql-<name>-setup initdb

Configuring the server/database

Edit the pg_hba.conf file to allow clients/users

Before clients/users connect, you may wish to secure your PostgreSQL instance using the pg_hba.conf file. There are many ways to do this which will entirely depend on your own environment and organization - for example you may wish to allow access only from certain IP addresses/hosts, or specific PostgreSQL users. The pg_hba.conf file is explained in more detail here:

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

Code Block
vi pg_hba.conf

A default set of rules is provided, which will look similar to the following:

Code Block
# TYPE  DATABASE        USER           ADDRESS                  METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all               md5
# IPv6 local connections:
host    all             all            ::1/128                  md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all               md5
host    replication     all            ::1/128                  md5

This default configuration only allows connections from localhost using IPv4 and IPv6, therefore to allow any client to connect, you could add the following line:

Code Block
host all all md5

Or to allow a specific subnet:

Code Block
host all all md5

You can also restrict access to specific databases and logins. For example, to allow only the operator login to access the postgres database from any client, add the following like:

Code Block
host postgres operator md5

Check server configuration

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 PostgreSQL instance - and as such their configuration is specific to the host machine. Please use on the host machine to identify the correct settings and then modify postgresql.conf with the settings provided by pgtune. You should update the following settings with the values provided by pgtune:

  • effective_cache_size
  • min_wal_size
  • max_wal_size
  • wal_buffers

When running pgtune, choose the following:

  • Set the DB version to the installed version
  • Set the DB Type to Mixed type of applications
  • Set the Number of connections to 300

Some of the values suggested by pgtune are somewhat low. Therefore, please calculate the following postgresql.conf settings as follows:

  • shared_buffers - value should be 25% of available RAM with a max of 8 GB
  • maintenance_work_mem= 512 MB

Other settings

The following settings should also be modified to the values listed below:

Required value
listen_addresses'*'Instead of localhost by default. Enables connectivity from other machines.

log_line_prefix'%t [%p]: [%l-1] 'Don't forget the space before final quote mark.
datestyle'iso, mdy'Ensure that this option is active (it may already be active).
max_locks_per_transaction4096Higher value than 64 by default.
Start the

PostgreSQL database server actions (start/stop/restart etc.)

Where <name> is the release number:

Code Block
systemctl stop postgresql-<name>.service --> FOR SHUTDOWN
systemctl start postgresql-<name>.service --> FOR STARTING
systemctl enablerestart postgresql-<name>.service --> FOR RESTARTING
systemctl status postgresql-<name>.service --> FOR CHECKING STATUS
systemctl reload postgresql-<name>.service --> FOR CONFIGURATION RELOAD

Create users

By default PostgreSQL will create a system account user named postgres with the role postgres. The equivalent CAST Storage Service provided by CAST includes two default users as follows:

guestWelcomeToAIP-Note that in the CAST AIP ≥ 8.3.11, the "guest" user is no longer used.

If you would like to create these users, use the following commands:

Code Block
*create user operator with SUPERUSER password 'CastAIP';
*create user guest with password 'WelcomeToAIP';
*grant postgres to operator;

CAST AIP ≥ 8.3.12 - custom users

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:

Code Block
[postgres@cssx data]$ psql
*create user my_user with SUPERUSER password 'my_password';
*grant postgres to my_user;

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:

Code Block
[postgres@cssx data]$ psql
*grant create, connect, temporary on database postgres to my_role;

postgres database

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 The "postgres" 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 in PostgreSQL. CAST products will, by default, expect to create/connect to schemas in this database. However, it is also possible to configure CAST products to connect to a custom database using any CAST AIP productsother than "postgres". See Using a custom CAST Storage Service or PostgreSQL database other than the default postgres.

How to log all statement's plan

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

Code Block
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:

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