Configuring PostgreSQL
To use HCL Compass with PostgreSQL databases, you must install a supported release of PostgreSQL on your database server and create and customize the database container.
For detailed instructions configuring a PostgreSQL database server, see the PostgreSQL
documentation.
- 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 namedmyschemarepodb
. 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.
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 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;
Troubleshooting: Here is how to diagnose and resolve some common configuration issues.
pdsql -v postgresql -s compassserver1 -db postgres -u postgres -p mypostgrespassword
- 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.
- 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
- 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.
- 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:- The user was created with a schema that doesn't match his username.
- 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.