Configuring PostgreSQL

Database Creation

Create a database and user

DROP DATABASE IF EXISTS okmdb;
CREATE USER openkm WITH PASSWORD '*secret*';
CREATE DATABASE okmdb WITH OWNER openkm ENCODING 'UTF8';

or from the command line 

More info at:

• PostgreSQL 9.1 Documentation
• Guía Ubuntu: PostgreSQL

Configure your OpenKM.cfg

Edit the file $TOMCAT_HOME/OpenKM.cfg

hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.hbm2ddl=create

Configure Tomcat datasources

Edit the file $TOMCAT_HOME/conf/server.xml and enable the resource named jdbc/OpenKMDS

<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
          maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"
          username="openkm" password="*secret*" driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://localhost:5432/okmdb"/>

Configure application login

Edity the file $TOMCAT_HOME/OpenKM.xml

<security:authentication-manager alias="authenticationManager">
  <security:authentication-provider>
    <security:password-encoder hash="md5"/>
    <security:jdbc-user-service 
        data-source-ref="dataSource"
        users-by-username-query="select usr_id, usr_password, 1 from OKM_USER where usr_id=? and usr_active='T'"
        authorities-by-username-query="select ur_user, ur_role from OKM_USER_ROLE where ur_user=?"/>
  </security:authentication-provider>
</security:authentication-manager>

Check for the JDBC Driver

Go to $TOMCAT_HOME/lib and check for the JDBC postgresql-9.1-902.jdbc4.jar Driver.

If it is not present, download the PostgreSQL JDBC driver from PostgreSQL Driver Page and move it.

Run application

As a process in the OpenKM startup, the application will automatically create an empty database structure. 

Additional information

If you want to obtain the maximun performance from PostgreSQL, pgFouine will help.

PostgreSQL 9.6 and upper

From PostgreSQL version 9.6 and upper, has been changed some tables and columns of the schema. Check that you are using the right JDBC driver.

You can find the current JDBC driver in $TOMCAT_HOME/libs