Configuring PostgreSQL

• In order to use PostgreSQL on Linux_x86_64, you must install the latest platform appropriate libpq.so.5 library.
• The following parameters must be used to create new PostgreSQL connections and databases in HCL Compass:

  Server

  The DB server name or IP address.

  DB

  The PostgreSQL database name to use.

  User Name

  The PostgreSQL username. This is usually the owner of the DB.

  Password

  The PostgreSQ account password.

  Connect Options

  This parameter is optional. Port=<port number>. The default value is 5432.

• Use the following example to create a test connection to server in pdsql:

  pdsql -v postgresql -s compassserver1 -db postgres -u postgres -p mypostgrespassword

• The following example illustrates creation of a blank database that can be used as a schema repository (master database) or user database. The following example assumes that the PostgreSQL account password in postgres and creates a user and db named myschemarepodb.

  pdsql -v postgresql -s compassserver1 -db postgres -u postgres -p postgres
  CREATE DATABASE "myschemarepodb";
  CREATE USER "myschemarepodb" WITH ENCRYPTED PASSWORD 'psqlpasswd1';
  GRANT ALL PRIVILEGES ON DATABASE "myschemarepodb" TO "myschemarepodb";
  quit;
  
  pdsql.exe  -echo -noprompt -autocommit  -v POSTGRESQL -s compassserver1 -db myschemarepodb -u postgres -p postgres
  GRANT ALL ON SCHEMA public TO "myschemarepodb";
  quit;
  
  pdsql.exe  -echo -noprompt -autocommit  -v POSTGRESQL -s compassserver1 -db myschemarepodb -u myschemarepodb -p psqlpasswd1
  create schema "myschemarepodb"
  quit;

  You can then use the maintenance tool or the designer tool to create the new database as you would with any other vendor.

pdsql -v postgresql -s compassserver1 -db postgres -u postgres -p mypostgrespassword

1. Firewall issues: you may encounter the following error:

   EXCEPTION: server closed the connection unexpectedly

   If you encounter this error, the server may have terminated abnormally before or while processing the request. To resolve the issue, contact IT services to open the firewall for the specified DB server and port. Port 5432 is the default.

2. pg_hba.conf config issue. This error returns the following:

   EXCEPTION: FATAL:  no pg_hba.conf entry for host "10.134.194.209", user "postgres", database "postgres", no encryption
   
   State: 08001 Native: 101
   Connect String used: DRIVER={HCL PostgreSQL ODBC DRIVER};SERVER=10.134.50.227;PORT=5432;Database=postgres;UID=postgres;PWD=******;LFConversion=0

   To resolve this issue, correct the pg_hba.conf file. The file contains instructions on how to fix it. The following example illustrates how to edit the file to allow all hosts to connect using password/md5:

   
   host    all             all             all                     password
   host    all             all             all                     md5

3. postgresql.conf issue. This error returns the following:

   EXCEPTION: could not connect to server: Connection refused (0x0000274D/10061)
           Is the server running on host "10.134.50.227" and accepting
           TCP/IP connections on port 5432?
   
   State: 08001 Native: 101
   Connect String used: DRIVER={HCL PostgreSQL ODBC DRIVER};SERVER=10.134.50.227;PORT=5432;Database=postgres;UID=postgres;PWD=******;LFConversion=0

   To resolve the issue, add the following to the postgresql.conf file:

   listen_addresses = '*'

   This will allow the postgreql.conf file to listen from all IP addresses. You can also list individual address that here. Once the file is updated, restart the server.