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

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):

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';

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:

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

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