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 JDBC Driver

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

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

Run application

As a process into OpenKM startup, the application will create automatically and empty database structure. 

Additional information

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

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