Skip to main content

Pentaho+ documentation is moving!

The new product documentation portal is here. Check it out now at docs.hitachivantara.com

 

Hitachi Vantara Lumada and Pentaho Documentation

Use PostgreSQL as your repository database (Manual installation)

Parent article

This article includes the steps for installing PostgreSQL as the host database for the Pentaho Server in a manual installation of Pentaho.

Before you begin

Prerequisite

Before you prepare your Pentaho Repository, you must prepare either a Windows or Linux environment.

Components

The Pentaho Repository resides on the database that you installed during the Windows or Linux environment preparation step, and consists of the following components:

  • Jackrabbit

    Contains the solution repository, examples, security data, and content data from reports that you use Pentaho software to create.

  • Quartz

    Holds data that is related to scheduling reports and jobs.

  • Hibernate

    Holds data that is related to audit logging.

  • (Optional) Pentaho Operations Mart

    To report on system usage and performance.

Initialize PostgreSQL Pentaho Repository database

To initialize PostgreSQL so that it serves as the Pentaho Repository, you will need to run several SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart components.

CautionUse the ASCII character set when you run these scripts. Do not use UTF-8 because there are text string length limitations that might cause the scripts to fail.

The sections in this article take you through the steps to initialize the PostgreSQL Pentaho Repository database.

Step 1: Change default passwords

For your production server, we highly recommend that you change the default passwords in the following SQL script files to make the databases more secure.
NoteIf you are evaluating Pentaho, then you might want to skip this step.

Procedure

  1. Browse to the pentaho/server/pentaho-server/data/postgresql directory.

  2. Use any text editor to change the passwords in these SQL scripts:

    • create_jcr_postgresql.sql
    • create_quartz_postgresql.sql
    • create_repository_postgresql.sql
    • pentaho_mart_postgresql.sql

Step 2: Run SQL scripts

Run the SQL scripts in the table below.

NoteThese scripts require administrator permissions on the server to run them.
CautionIf you have a different port or different password, make sure that you change the password and port numbers in these examples to match the ones in your configuration.

Run these scripts from the PSQL Console window in the pgAdminIII tool:

ActionSQL Script
Create Quartz\i <your filepath>/data/postgresql/create_quartz_postgresql.sql
Create Hibernate repository\i <your filepath>/data/postgresql/create_repository_postgresql.sql
Create Jackrabbit\i <your filepath>/data/postgresql/create_jcr_postgresql.sql
Create Pentaho Operations mart\i <your filepath>/data/postgresql/pentaho_mart_postgresql.sql
NoteYou unpacked the Pentaho Operations mart SQL file while preparing your environment for the manual installation process. Depending on your environment, see Prepare your Windows environment for a manual installation or Prepare your Linux environment for a manual installation for details.

Step 3: Verify PostgreSQL initialization

After you run the scripts, perform the following steps to verify that databases and user roles have been created:

Procedure

  1. Open the pgAdminIII tool.

  2. Verify that you can log on as: hibuser

  3. Once logged on, check that the Quartz, Jackrabbit (JCR), Hibernate, and Pentaho Operations Mart are present.

  4. Exit from the pgAdminIII tool.

Configure PostgreSQL Pentaho Repository database

Now that you have initialized your repository database, you will need to configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for a PostgreSQL database.

NotePostgreSQL is configured by default; if you kept the default passwords and port, you will not need to set up Quartz, Hibernate, Jackrabbit or the Pentaho Operations Mart. You can skip ahead to the Tomcat-specific connection tasks or the JBoss connection tasks.

By default, the examples in this section are for a PostgreSQL database that runs on port 5432. The default password is also in these examples.

CautionIf you have a different port or different password, make sure that you change the password and port number in these examples to match the ones in your configuration.

Step 1: Set up Quartz on PostgreSQL

Event information, such as scheduled reports, is stored in the Quartz JobStore. During the installation process, you must indicate where the JobStore is located by modifying the quartz.properties file.

  1. Open the pentaho/server/pentaho-server/pentaho-solutions/system/quartz/quartz.properties file in any text editor.

  2. Locate the #_replace_jobstore_properties section and set the org.quartz.jobStore.driverDelegateClass as shown:

    org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
  3. Locate the # Configure Datasources section and set the org.quartz.dataSource.myDS.jndiURL equal to Quartz, as shown:

    This setting varies depending on the web server you have chosen for this installation:
    • If using JBoss, this setting should be:
      org.quartz.dataSource.myDS.jndiURL = jboss/datasources/Quartz
    • If using Tomcat, this setting should be:
      org.quartz.dataSource.myDS.jndiURL = Quartz
  4. Save the file and close the text editor.

Step 2: Set Hibernate settings for PostgreSQL

Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository’s Hibernate configuration file. The Hibernate configuration file specifies driver and connection information, as well as dialects and how to handle connection closes and timeouts.
NoteThe Hibernate database is also where the Pentaho Server stores the audit logs that act as source data for the Pentaho Operations Mart.

The files in this section are located in the pentaho/server/pentaho-server/pentaho-solutions/system/hibernate directory.

Perform the following steps to specify where Pentaho can find the Hibernate configuration file.

Procedure

  1. Open the hibernate-settings.xml file in a text editor. Find the <config-file> tags and confirm that it is configured for PostgreSQL.

    From:

    <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
  2. Save the file if you made changes, then close the file.

Step 3: Modify Jackrabbit repository information for PostgreSQL

Edit the following code to change the default Jackrabbit repository to PostgreSQL.

Procedure

  1. Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit and open the repository.xml file with any text editor.

  2. In each of the sections, comment out any resource references that refer to other databases.

    ItemCode Section
    Repository
    <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
        <param name="driver" value="javax.naming.InitialContext"/>
        <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
        <param name="schema" value="postgresql"/>
        <param name="schemaObjectPrefix" value="fs_repos_"/>
      </FileSystem>
    DataStore
    <DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
        <param name="driver" value="javax.naming.InitialContext"/>
        <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
        <param name="databaseType" value="postgresql"/>
        <param name="minRecordLength" value="1024"/>
        <param name="maxConnections" value="3"/>
        <param name="copyWhenReading" value="true"/>
        <param name="tablePrefix" value=""/>
        <param name="schemaObjectPrefix" value="ds_repos_"/>
      </DataStore>
    Workspaces
    <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
          <param name="driver" value="javax.naming.InitialContext"/>
          <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
          <param name="schema" value="postgresql"/>
          <param name="schemaObjectPrefix" value="fs_ws_"/>
        </FileSystem>
    PersistenceManager (1st part)
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager">
          <param name="driver" value="javax.naming.InitialContext"/>
          <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
          <param name="schema" value="postgresql"/>
          <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
        </PersistenceManager>
    Versioning
    <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
          <param name="driver" value="javax.naming.InitialContext"/>
          <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
          <param name="schema" value="postgresql"/>
          <param name="schemaObjectPrefix" value="fs_ver_"/>
        </FileSystem>
    PersistenceManager (2nd part)
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager">
          <param name="driver" value="javax.naming.InitialContext"/>
          <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
          <param name="schema" value="postgresql"/>
          <param name="schemaObjectPrefix" value="pm_ver_"/>
        </PersistenceManager>
    DatabaseJournal
    <Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">
    	<param name="revision" value="${rep.home}/revision.log" />
    	<param name="driver" value="javax.naming.InitialContext"/>
    	<param name="url" value="java:comp/env/jdbc/jackrabbit"/>
    	<param name="schema" value="postgresql"/>
    	<param name="schemaObjectPrefix" value="cl_j_"/>
    	<param name="janitorEnabled" value="true"/>
    	<param name="janitorSleep" value="86400"/>
    	<param name="janitorFirstRunHourOfDay" value="3"/>
         </Journal>

Using JBoss

If you are installing your own JBoss web application server, skip the Tomcat section, and move on to Prepare JBOSS connections and web app servers.

Perform Tomcat-specific connection tasks

After your repository has been configured, you must configure the web application servers to connect to the Pentaho Repository. In this step, you will make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.

NoteBy default, the Pentaho Server software is configured to be deployed and run on the Tomcat server. As such, connections have already been specified and the Tomcat context.xml file must be modified ONLY if you have changed the default ports or passwords.

The next couple of sections guide you through the process of working with the JDBC drivers and connection information for Tomcat.

Step 1: Download driver and apply to the Pentaho Server

To connect to a database, including the Pentaho Repository database, you will need to download and copy a JDBC driver to the appropriate places for the Pentaho Server as well as on the web application server.
NoteDue to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You must download and install the file yourself.

Procedure

  1. Download a JDBC Driver JAR from your database vendor or a third-party driver developer.

  2. Copy the JDBC driver JAR you just downloaded to the pentaho/server/pentaho-server/tomcat/lib folder.

  3. Copy the hsqldb-2.3.2.jar file to pentaho-server/tomcat/lib if you want to retain the sample provided by Pentaho.

Step 2: Modify JDBC connection information in the Tomcat context.xml File

Database connection and network information, such as the username, password, driver class information, IP address or domain name, and port numbers for your Pentaho Repository database are stored in the context.xml file. Modify this file to reflect the database connection and network information to reflect your operating environment.

If you have chosen to use an Pentaho Repository database other than PostgreSQL, you also need to modify the values for the validationQuery parameters in this file.

CautionIf you have a different port, password, user, driver class information, or IP address, make sure that you change the password and port number in these examples to match the ones in your configuration environment.

Procedure

  1. Consult your database documentation to determine the JDBC class name and connection string for your Pentaho Repository database.

  2. Navigate to the pentaho-server/tomcat/webapps folder and use a ZIP extraction utility (such as 7-Zip, Winzip, or Archive) to view the contents of the pentaho.war file.

  3. Open the context.xml file with any text editor.

  4. Add the following code to the file if it does not already exist.

    <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource" factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000" username="hibuser" password="password" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate" validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
    <Resource name="jdbc/Audit" auth="Container" type="javax.sql.DataSource" factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000" username="hibuser" password="password" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate" validationQuery="select 1" />
    <Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource" factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000" username="pentaho_user" password="password" testOnBorrow="true" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/quartz" validationQuery="select 1"/>
    <Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource" factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000" username="hibuser" password="password" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate" validationQuery="select 1"/>
    <Resource name="jdbc/pentaho_operations_mart" auth="Container" type="javax.sql.DataSource" factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000" username="hibuser" password="password" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate" validationQuery="select 1"/>
    <Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource" factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000" username="hibuser" password="password" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate?searchpath=pentaho_dilogs" validationQuery="select 1"/>
    <Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource" factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000" username="pentaho_user" password="password" driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata" validationQuery="select 1"/>
    <Resource name="jdbc/SampleDataAdmin" auth="Container" type="javax.sql.DataSource" factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000" username="pentaho_admin" password="password" driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata" validationQuery="select 1"/>
    <Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource" factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000" username="jcr_user" password="password" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/jackrabbit" validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
  5. Modify the username, password, driver class information, IP address (or domain name), and port numbers so they reflect the correct values for your environment.

  6. Make sure that the validationQuery parameter for your database is set to this: validationQuery="select 1"

  7. Save the context.xml file, then close it.

Next steps

After installing the database that will host the Pentaho Repository, the next step depends on which type of web application server you are using, either JBoss or Tomcat.