IDOL Community Component 23.3

# Set up PostgreSQL to Store Tracking Information

This section describes how to set up the PostgreSQL back end.

The example actions and commands are for IDOL components running on a Microsoft Windows platform, and a PostgreSQL server running on a Linux platform. Other combinations of platforms are possible.

## Install the SQL Database

The following Linux command example installs the latest stable PostgreSQL server, using the default port (`5432`). You can use any recent, stable version (on any port).

    sudo apt-get install postgresql

You can test that your server is up with `psql`.

For Windows, you can install PostgreSQL from the following Web site:

http://www.postgresql.org/download/windows/

For more detailed installation instructions, refer to the PostgreSQL wiki.

https://wiki.postgresql.org/wiki/Main_Page

## Set up the Database and Table

This section describes how to set up the database in PostgreSQL on Linux. On Windows, you can complete the tasks by using `Pgadmin`.

The PostgreSQL installation creates a user for you.

To set up the database and table on Linux

1. Create a database with an arbitrary name. For example:

       sudo -u postgres createdb mydoctrackdb

   You can test it by using the following example command:

       sudo -u postgres psql -d mydoctrackdb

2. Create the tables to store document tracking events, by running the following commands from `psql` or your SQL client:

   Copy

       CREATE TABLE type(
         typeid serial PRIMARY KEY,
         type varchar(64) NOT NULL UNIQUE,
         is_error smallint,
         is_terminal smallint
       );

       -- Broken into several INSERTs here for clarity only
       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(0,'Unknown',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(10,'Committed',0,1),
                      (20,'Deleted',0,1),
                      (30,'Indexed',0,0),
                      (40,'Received',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(50,'Updated',0,1),
                      (-10,'Warning',1,0),
                      (-20,'Error',1,1),
                      (-30,'Rejected',1,1);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(90,'Added',0,0),
                      (100,'Delete received.',0,0),
                      (110,'Update received.',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(120,'Non-importing add received.',0,0),
                      (130,'Import:Queue',0,0),
                      (140,'Import:Importing',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(150,'Import:Pre',0,0),
                      (160,'Import:Post',0,0),
                      (170,'Import:Finished',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (180,'Import:Cancel',0,1);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (190,'Import:Extracting metadata',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (200,'Import:Extracting metadata finished',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (210,'Import:ExtractMetaAbort',1,1);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (220,'Import:Abort',1,1);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (230,'Replaced',0,0);

       CREATE TABLE source(
         sourceid serial PRIMARY KEY,
         source varchar(128) NOT NULL UNIQUE
       );

       CREATE TABLE event(
         eventid serial PRIMARY KEY,
         docuid varchar(128) NOT NULL,
         typeid int NOT NULL,
         sourceid int NOT NULL,
         timestamp bigint NOT NULL,

         CONSTRAINT type_fk FOREIGN KEY(typeid)
            REFERENCES type(typeid)
            ON DELETE CASCADE ON UPDATE CASCADE,

         CONSTRAINT source_fk FOREIGN KEY(sourceid)
            REFERENCES source(sourceid)
            ON DELETE CASCADE ON UPDATE CASCADE
       );

       CREATE TABLE metadata(
         metadataid serial PRIMARY KEY,
         key varchar(32) NOT NULL,
         value varchar(1024) NOT NULL
       );

       CREATE TABLE event_metadata(
         eventid int NOT NULL,
         metadataid int NOT NULL,

         CONSTRAINT event_fk FOREIGN KEY(eventid)
                       REFERENCES event(eventid)
                       ON DELETE CASCADE ON UPDATE CASCADE,

         CONSTRAINT metadata_fk FOREIGN KEY(metadataid)
            REFERENCES metadata(metadataid)
            ON DELETE CASCADE ON UPDATE CASCADE
       );

       CREATE TABLE docuid_reference(
         docuid varchar(128) NOT NULL,
         ref varchar(4096) NOT NULL,

         UNIQUE (docuid, ref)
       );

       CREATE TABLE doctrack_schema_version(
         key varchar(64) NOT NULL,
         value varchar(128) NOT NULL
       );

       INSERT INTO doctrack_schema_version(key,value)
            VALUES('major_version', '1'),
                  ('minor_version','1'),
                  ('IDOL_version','10.9');

       CREATE RULE no_schema_version_insert AS
         ON INSERT TO doctrack_schema_version
         DO INSTEAD NOTHING;

       CREATE RULE no_schema_version_delete AS
         ON DELETE TO doctrack_schema_version
         DO INSTEAD NOTHING;

NOTE: If you want to set up document tracking in an existing IDOL installation that uses the deprecated IndexTasks component, you must also add the following statements for the `type` table:

    INSERT INTO type(typeid, type, is_error, is_terminal)
             VALUES(60,'IndexTasksCompleted',0,0),
                   (70,'IndexTasksStarted',0,0),
                   (80,'IndexTask',0,0);

To set up the database and table on Windows

1. Use `Pgadmin` to run the SQL Create Table command. Right-click databases/mydoctrackdb (or the name of the database that you created) and select Create script.

## Database Access Permissions

On Windows, you can modify the configuration in `Pgadmin` by selecting the appropriate item on the left pane, and then clicking Tools/Server Configuration.

To modify the database access permissions

1. Find the `pg_hba.conf` host-based authentication file by inspecting the PostgreSQL configuration file. The following lines in the `postgresql.conf` file identify the location:

       hba_file = '/etc/postgresql/9.1/main/pg_hba.conf'  # host-based authentication file

   NOTE: The location of the `postgresql.conf` file can vary, depending on your version and operating system. On Linux, you can run the following command to find the path to the configuration file:

       ps -ef | grep postgres

2. Modify the `pg_hba.conf` configuration file, which you located in Step 1, to allow your IDOL components to access the database. Find the following section, and add appropriate lines for your client IP addresses.

       # TYPE  DATABASE    USER           ADDRESS             METHOD
       host    all         my.user.name   10.2.123.123/32     trust

   For simplicity, you can set the `USER` field to `all`. OpenText recommends that you use a secure `METHOD`, such as `md5`, after you have tested the system.

3. Allow PostgreSQL to accept connections. In the `postgresql.conf` file, find the `listen_addresses` parameter and uncomment or modify it:

       listen_addresses = '*'

4. Save and close the `pg_hba.conf` and `postgresql.conf` files.

5. Restart PostgreSQL, by using the following command (on Linux):

       sudo -u postgres /etc/init.d/postgresql restart

Copyright 2023 Open Text
Version 23.3 | Last updated June 2023

Send documentation feedback

Close

Send documentation feedback

To send feedback using your default email client, open an email window.

Otherwise, send your feedback and the information below to swpdl.idoldocsfeedback@microfocus.com.

We appreciate your feedback!

Product: IDOL IDOL Community Component 23.3

Topic Title: Set up PostgreSQL to Store Tracking Information