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 is postgres and creates a user and db named myschemarepodb. If you are creating a new database schema for storage, the database username and schema name should be the same. If the names are not the same, the public schema will be used for storage. This is not recommended.

  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.

4. Creating a new MASTR/user database:

   
   CRMMD1229E Unable to initialize database "tst2" with schema "repodb" version 3. Reason: CRMDB0001E SQLExecute: RETCODE=-1, State=42501, Native Error=1
   ERROR: permission denied for schema public;
   Error while executing the query
   SQL statement="create table entitydef (id integer, version integer, schema_id integer, schema_rev integer, package_ownership integer, name varchar(255), type integer, entity_ownership integer, default_formdef_id integer, db_name varchar(255), entity_select_proc varchar(255), is_default integer, is_family integer )"
   
   The logfile C:\Users\%username%\AppData\Local\Temp\tst2_repodb_3_init.log may be useful in resolving this problem. 
   ---------------------------
   OK  
   ---------------------------

   If you encounter this error, the following may have happened:

   1. The user was created with a schema that doesn't match his username.
   2. The user was created without public access if no schema was specified at creation time.

   To resolve the issue, either give the user public access or create a schema with the same name as the user.