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.
-
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
-
Create the tables to store document tracking events, by running the following commands from
psql
or your SQL client:CopyCREATE 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);
-
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
-
Find the
pg_hba.conf
host-based authentication file by inspecting the PostgreSQL configuration file. The following lines in thepostgresql.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
-
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 toall
. OpenText recommends that you use a secureMETHOD
, such asmd5
, after you have tested the system. -
Allow PostgreSQL to accept connections. In the
postgresql.conf
file, find thelisten_addresses
parameter and uncomment or modify it:listen_addresses = '*'
-
Save and close the
pg_hba.conf
andpostgresql.conf
files. -
Restart PostgreSQL, by using the following command (on Linux):
sudo -u postgres /etc/init.d/postgresql restart