Use PostgreSQL as your repository database (Manual installation)

Legacy page. Content moved to the main topic.

This page is kept for existing links. Go to Manual installation process.

Install PostgreSQL as the host database for the Pentaho Server repository.

Before you begin

Prerequisites

Prepare your environment first:

• Prepare your Windows environment for a manual installation

• Prepare your Linux environment for a manual installation

Repository components

The Pentaho Repository resides on the database you installed during environment setup.

It includes these components:

• Jackrabbit Contains the solution repository, examples, security data, and report content data.

• Quartz Stores scheduler data for reports and jobs.

• Hibernate Stores audit logging data.

• (Optional) Pentaho Operations Mart Stores system usage and performance data.

Initialize the PostgreSQL Pentaho Repository database

Run PostgreSQL initialization scripts to create the Pentaho Repository schemas.

You create schemas for Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart.

Use an ASCII database encoding when you run these scripts. Do not use UTF-8. UTF-8 can cause script failures due to text length limits.

Update usernames, passwords, and port numbers in examples and scripts to match your environment.

Quartz 1.x vs Quartz 2.x upgrade note (Pentaho 10.2.0.1+)

Pentaho 10.2.0.0 and earlier versions use Quartz 1.x tables with a QRTZ5_ prefix.

Pentaho 10.2.0.1 and later versions use Quartz 2.x tables with a QRTZ6_ prefix.

If you upgrade to Pentaho 10.2.0.1 or later, create the QRTZ6_ tables.

Optionally migrate your existing schedules using the provided migration script.

If you do not complete the Quartz upgrade, Pentaho Server can fail at startup.

You may see Missing Quartz library database error in catalina.log.

1

Step 1: Change default passwords

For production, follow best practices and change the default passwords in the SQL script files.

If you are evaluating Pentaho, you can skip this step.

1. Browse to <your-pentaho-dir>/pentaho-server/data/postgresql.

2. Edit these scripts for your user, password, database, and any other required values:

   • create_jcr_postgresql.sql

   • create_quartz_postgresql.sql

   • create_repository_postgresql.sql

   • pentaho_mart_postgresql.sql

3. Save the files.

2

Step 2: Run SQL scripts

Run the PostgreSQL initialization scripts from the Pentaho software distribution.

You may need administrator permissions to run these scripts on the host OS.

If you are upgrading from earlier Pentaho versions (including 10.2.0.0 GA → 10.2.0.1+), you must create the new Quartz 2.x tables.

If you want to keep existing scheduler data, migrate QRTZ5_ data to the new QRTZ6_ tables.

1. Back up your data. See Back up your existing Pentaho products and install Pentaho 11.0.

2. If Pentaho Server is running, stop it. See Stop and start the Pentaho Server and repository.

3. Make sure PostgreSQL is running.

4. Open a psql console and run these scripts in order:

   1. <your-pentaho-dir>/pentaho-server/data/postgresql/create_quartz_postgresql.sql

   2. <your-pentaho-dir>/pentaho-server/data/postgresql/create_jcr_postgresql.sql

   3. <your-pentaho-dir>/pentaho-server/data/postgresql/create_repository_postgresql.sql (Hibernate)

   4. <your-pentaho-dir>/pentaho-server/data/postgresql/pentaho_mart_postgresql.sql

   This creates the Quartz database, Jackrabbit repository, PostgreSQL repository, and Operations Mart schema.

   It also creates the new Quartz scheduler tables prefixed with QRTZ6_.

   Any existing QRTZ5_ scheduler tables are retained.

5. If you want to retain existing scheduler data, open migrate_old_quartz_data_postgresql.sql.

6. Update the migration script for your current user, password, database, and any other required values.

7. Run the migration script:

   <your-pentaho-dir>/pentaho-server/data/postgresql/migrate_old_quartz_data_postgresql.sql

The original QRTZ5_ data is retained.

Delete it after you confirm the migration is successful.

If you do not have pentaho_mart_postgresql.sql, make sure you unpacked the Pentaho Operations Mart SQL file during installation.

For manual installs, see Prepare your Windows environment for a manual installation or Prepare your Linux environment for a manual installation.

3

Step 3: Verify PostgreSQL initialization

Unless you changed it in Step 1, the default password for the users below is password.

1. Open pgAdmin (or a similar client).

2. Log in as hibuser, then verify you can see the tables under hibernate.

3. If you installed Operations Mart, verify you can see the tables under pentaho_operations_mart.

4. Log in as jcr_user, then verify you can see the tables under jackrabbit.

   The Jackrabbit tables may not appear until after you start Pentaho for the first time.

5. Log in as pentaho_user, then verify you can see the tables under quartz.

You have initialized the PostgreSQL Pentaho Repository database.

Configure the PostgreSQL Pentaho Repository database

Configure Quartz, Hibernate, Jackrabbit, and Operations Mart for PostgreSQL.

PostgreSQL is configured by default. If you kept the default password and port, skip this section and continue to Perform Tomcat-specific connection tasks.

If you use a different port or password, update the examples to match your environment.

Set up Quartz on PostgreSQL

Quartz stores scheduler data, such as scheduled reports.

You point Pentaho at the correct JobStore by updating quartz.properties.

1. Open pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties.

2. In #_replace_jobstore_properties, set org.quartz.jobStore.driverDelegateClass:

   org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

3. Save the file.

Set Hibernate settings for PostgreSQL

Hibernate settings point Pentaho to the correct Hibernate configuration file.

Hibernate also stores the audit logs used by Operations Mart.

Files are in pentaho/server/pentaho-server/pentaho-solutions/system/hibernate.

1. Open hibernate-settings.xml.

2. Find the <config-file> entry, then confirm it uses the PostgreSQL config:

   <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

3. Save the file if you made changes.

Modify Jackrabbit repository information for PostgreSQL

Update Jackrabbit to use the PostgreSQL repository.

1. Open pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml.

2. Comment out resource references for databases you are not using.

3. Verify these sections use the PostgreSQL values.

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 (workspaces)

<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 (versioning)

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

Perform Tomcat-specific connection tasks

After you configure the Pentaho Repository, configure Tomcat to connect to it.

This step sets up JDBC and JNDI connections for Hibernate, Jackrabbit, and Quartz.

Pentaho Server is configured for Tomcat by default. Only edit Tomcat context.xml if you changed default ports or passwords.

1

Download the JDBC driver and apply it to Pentaho Server

To connect to a database, you must install a JDBC driver JAR.

Install it for Pentaho Server and Tomcat.

Pentaho cannot redistribute some third-party database drivers. Download the driver from your database vendor.

1. Download a JDBC driver JAR from your database vendor.

   See JDBC drivers reference.

2. Copy the JDBC driver JAR to pentaho/server/pentaho-server/tomcat/lib.

3. Optional: Copy hsqldb-2.3.2.jar to pentaho/server/pentaho-server/tomcat/lib.

   Do this if you want to keep the Pentaho sample data.

2

Modify JDBC connection information in the Tomcat context.xml file

Database connection settings are stored in Tomcat context.xml.

Update the file to match your environment.

If your port, password, username, driver class, or host differs from the defaults, update the examples.

1. Consult your database documentation for:

   • JDBC driver class name

   • JDBC connection string

2. Open server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml in a text editor.

3. Add the following XML 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"/>

4. Update the username, password, driver class, host, and port to match your environment.

5. Comment out any resources for databases you are not using.

6. Verify validationQuery="select 1".

7. Save the file.

Next steps

Continue with Configure and start the Pentaho Server after manual installation.