PostgreSQL Repository

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.

Note

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;
  1. 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
  2. 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
  3. 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.

  4. 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
  5. 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
  6. 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
  7. 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/
  8. 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 the values in your resolver/boot.properties file:

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

    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.1.6"
    OpenIDM ready
  9. PostgreSQL is now set up for use as the internal repository. Make sure that the server starts without errors.

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

    Important

    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.

  1. 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"
                }
            }
        },
    ...
  2. 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"
        }
    }
Read a different version of :