PostgreSQL repository

Configure the PostgreSQL Repository and IDM
Configure Searchable Array Fields

Configure the PostgreSQL repository and IDM

This procedure assumes that a supported version of PostgreSQL is installed and running on the local host. Before starting IDM for the first time, configure the server to use a PostgreSQL repository.

The path/to/openidm/db/postgresql/scripts/createuser.pgsql script creates an openidm database and role, with a default password of openidm. The script also grants the appropriate permissions.

Edit this script if you want to change the password of the openidm role, for example:

create database openidm encoding 'utf8';
create role openidm with LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE inherit password 'newPassword';
grant all privileges on database openidm to openidm;

Edit the Postgres client authentication configuration file, pg_hba.conf. Add the following entries for the following users: postgres and openidm:
```
local   all             openidm                                 trust
local   all             postgres                                trust
```

As the postgres user, execute the createuser.pgsql script as follows:

psql -U postgres < /path/to/openidm/db/postgresql/scripts/createuser.pgsql
CREATE DATABASE
CREATE ROLE
GRANT

Run the openidm.pgsql script as the new openidm user that you created in the first step:

psql -U openidm < /path/to/openidm/db/postgresql/scripts/openidm.pgsql
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
...
START TRANSACTION
INSERT 0 1
INSERT 0 1
COMMIT
CREATE INDEX
CREATE INDEX

Your database has now been initialized.

Run the script that creates the tables required by the workflow engine:

psql -d openidm -U openidm < /path/to/openidm/db/postgresql/scripts/flowable.postgres.all.create.sql

If you plan to direct audit logs to this repository, run the script that sets up the audit tables:
```
psql -d openidm -U openidm < /path/to/openidm/db/postgresql/scripts/audit.pgsql
```
Remove the default DS repository configuration file (repo.ds.json) from your project’s conf/ directory. For example:
```
cd /path/to/openidm/my-project/conf/
rm repo.ds.json
```
Copy the database connection configuration file for PostgreSQL (datasource.jdbc-default.json) and the database table file (repo.jdbc.json) to your project’s configuration directory. For example:
```
cp /path/to/openidm/db/postgresql/conf/datasource.jdbc-default.json my-project/conf/
cp /path/to/openidm/db/postgresql/conf/repo.jdbc.json my-project/conf/
```

Update the connection configuration to reflect your PostgreSQL deployment. The default connection configuration in the datasource.jdbc-default.json file is as follows:

{
    "driverClass" : "org.postgresql.Driver",
    "jdbcUrl" : "jdbc:postgresql://&{openidm.repo.host}:&{openidm.repo.port}/openidm",
    "databaseName" : "openidm",
    "username" : "openidm",
    "password" : "openidm",
    "connectionTimeout" : 30000,
    "connectionPool" : {
        "type" : "hikari",
        "minimumIdle" : 20,
        "maximumPoolSize" : 50
    }
}

If you changed the password in step 1 of this procedure, edit the datasource.jdbc-default.json file to set the value for the password field to whatever password you set for the openidm user.

Specify the values for openidm.repo.host and openidm.repo.port in one of the following ways:

Set in an IDM Properties File

Set the values in your resolver/boot.properties file:

openidm.repo.host = localhost
openidm.repo.port = 5432

Set as an Environment Variable

Set the properties in the OPENIDM_OPTS environment variable and export that variable before startup. You must include the JVM memory options when you set this variable. For example:

export OPENIDM_OPTS="-Xmx1024m -Xms1024m -Dopenidm.repo.host=localhost -Dopenidm.repo.port=5432"
/path/to/openidm/startup.sh -p my-project
Executing ./startup.sh...
Using OPENIDM_HOME:   /path/to/openidm
Using PROJECT_HOME:   /path/to/openidm
Using OPENIDM_OPTS:   -Xmx1024m -Xms1024m -Dopenidm.repo.host=localhost -Dopenidm.repo.port=5432
Using LOGGING_CONFIG: -Djava.util.logging.config.file=/path/to/openidm/conf/logging.properties
Using boot properties at /path/to/openidm/resolver/boot.properties
-> OpenIDM version "7.4.1"
OpenIDM ready

PostgreSQL is now set up for use as the internal repository. Make sure that the server starts without errors.

Set up indexes to tune the PostgreSQL repository according to your specific deployment.

No indexes are set by default. If you do not tune the repository correctly by creating the required indexes, the performance of your service can be severely impacted. For example, setting too many indexes can have an adverse effect on performance during managed object creation. Conversely, not indexing fields that are searched will severely impact search performance.

IDM includes a /path/to/openidm/db/postgresql/scripts/default_schema_optimization.pgsql script that sets up a number of indexes. This script includes extensive comments on the indexes that are being created. Review the script before you run it to ensure that all the indexes are suitable for your deployment.

When you have refined the script for your deployment, execute the script as a user with superuser privileges, so that the required extensions can be created. By default, this is the postgres user:

psql -U postgres openidm < /path/to/openidm/db/postgresql/scripts/default_schema_optimization.pgsql
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

Configure Array Fields

Optionally, you can configure arrays in order to perform REST queries. This feature only works for genericMapping objects.

Edit the repo.jdbc.json file to identify which fields/properties are stored as JSON_LIST arrays:

"genericMapping" : {
    "managed/*" : {
        "mainTable" : "managedobjects"
    },
    "managed/role" : {
        "mainTable": "managedobjects",
        "properties" : {
            "/stringArrayField" : {
                "type" : "JSON_LIST"
            }
        }
    },
...

Edit the managed.json file and add the properties to role:

"stringArrayField" : {
    "description" : "An array of strings",
    "title" : "String array field",
    "viewable" : true,
    "returnByDefault" : false,
    "type" : "array",
    "items" : {
        "type" : "string",
        "title" : "Some strings"
    }
}

IDM 7.4.1

PostgreSQL repository

Configure the PostgreSQL repository and IDM

Configure Array Fields