Use PostgreSQL as Your Repository Database (Archive installation)

Use this procedure to install PostgreSQL as the host database for the Pentaho Repository in an archive installation.

• Before you begin

• Initialize the PostgreSQL Pentaho Repository database

• Configure the PostgreSQL Pentaho Repository database

• Perform Tomcat-specific connection tasks

• Start your server

Before you begin

Prerequisite

Before you prepare your Pentaho Repository, 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.

It consists of these components:

• Jackrabbit

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

• Quartz

  Holds data related to scheduling reports and jobs.

• Hibernate

  Holds data related to audit logging.

• (Optional) Pentaho Operations Mart

  Reports on system usage and performance.

Initialize the PostgreSQL Pentaho Repository database

To initialize PostgreSQL so it serves as the Pentaho Repository, run SQL scripts to create these components:

• Hibernate

• Quartz

• Jackrabbit (JCR)

• (Optional) Pentaho Operations Mart

CAUTION:

Use the ASCII character set when you run these scripts.

Do not use UTF-8.

Text string length limitations might cause the scripts to fail.

CAUTION:

If you use a different password or user, update the examples to match.

CAUTION:

Pentaho 10.2.0.0 and earlier use Quartz 1.x.

Quartz 1.x uses a QRTZ5_ prefix in the database.

Pentaho 10.2.0.1 and later use Quartz 2.x.

Quartz 2.x uses a QRTZ6_ prefix in the database.

When upgrading from an earlier version to Pentaho 10.2.0.1 and later, create the QRTZ6_ tables by repeating the Quartz database creation procedure.

You can optionally migrate existing Quartz schedules using the migration script.

During this upgrade procedure, executing the SQL create script resets the corresponding Quartz database without loss of the original data.

However, the other associated repository databases in 10.2.0.1 will be reset by SQL scripts if run, and data might be deleted.

Always back up your data before proceeding.

Important: If you do not complete the Quartz upgrade, Pentaho Server fails at startup.

You will also see this exception message in catalina.log:

Missing Quartz library database error

Step 1: Change default passwords

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

Note: If you are evaluating Pentaho, you might want to skip this step.

1. Browse to the <your pentaho directory>/pentaho-server/data/postgresql folder.

2. Use a text editor to modify these create scripts as needed for your user, password, database, and other values:

   • create_jcr_postgresql.sql

   • create_quartz_postgresql.sql

   • create_repository_postgresql.sql

   • pentaho_mart_postgresql.sql

3. Save and close the files.

Step 2: Run SQL scripts

To use PostgreSQL as the database for your Pentaho Repository, you must initialize the Jackrabbit, Quartz, Hibernate, and Operations Mart components.

You can run SQL scripts developed by Pentaho to create and initialize these components.

When upgrading from earlier Pentaho versions, including 10.2.0.0 GA to 10.2.0.1 and later, you must manually initialize a new Quartz database.

This creates a new Quartz library in the repository database.

Back up your data before proceeding.

If you want to keep your existing Quartz library data, you can migrate the current tables to the new tables.

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

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

2. If Pentaho Server is running, stop it.

   See Stop and start the Pentaho Server and repository.

3. Ensure your PostgreSQL instance is running.

4. Open a PSQL console window and run these SQL creation scripts in this order:

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

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

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

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

   The Quartz database, Jackrabbit and PostgreSQL repositories, and Pentaho Operations Mart are created.

   The new Quartz scheduler tables prefixed with QRTZ6_ are created.

   Any existing QRTZ5_ scheduler database is retained.

   Starting the Pentaho Server at this point results in an empty schedule.

5. If you want to retain your existing scheduler database, open migrate_old_quartz_data_postgresql.sql in a text editor.

6. Update the migration script as needed for your user, password, database, and other values.

7. Run the migration script:

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

The original Quartz scheduler database is migrated to the new Quartz database.

Note: The original QRTZ5_ data is retained.

You can delete it after you confirm the migration completed successfully.

Note: You unpacked the Pentaho Operations Mart SQL file while preparing your environment for archive installation.

See Prepare your Windows environment for an archive install or Prepare your Linux environment for an archive install for details.

Step 3: Verify PostgreSQL initialization

Note: Unless you changed it in Step 1, the default password for each username is password.

After you run the scripts, verify the databases and user roles were created correctly:

1. Open pgAdminIII or a similar client tool.

2. Log in as hibuser.

3. Verify that you can see the tables under hibernate.

4. If you installed Pentaho Operations Mart, verify that you can see the tables under pentaho_operations_mart.

5. Log in as jcr_user.

6. Verify that you can see the tables under jackrabbit.

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

7. Log in as pentaho_user.

8. Verify that you can see the tables under quartz.

9. Exit from the tool.

You have initialized the PostgreSQL Pentaho Repository database.

Configure the PostgreSQL Pentaho Repository database

After you initialize your repository database, configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for PostgreSQL.

Note: PostgreSQL is configured by default.

If you kept the default passwords and port, you can skip to Perform Tomcat-specific connection tasks.

By default, the examples in this section use a PostgreSQL database running on port 5432 with the default password.

CAUTION:

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

Step 1: Set up Quartz on PostgreSQL

Event information, such as scheduled reports, is stored in the Quartz JobStore.

During installation, specify where the JobStore is located by modifying quartz.properties.

1. Open pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties in a text editor.

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

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

3. Save and close the file.

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, dialects, and connection close and timeout behavior.

Note: Pentaho Server also stores audit logs in the Hibernate database.

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

1. Open hibernate-settings.xml in a text editor.

2. Find the <config-file> tags and confirm this value is set for PostgreSQL:

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

3. Save and close the file if you made changes.

Step 3: Modify Jackrabbit repository information for PostgreSQL

Edit repository.xml to use PostgreSQL for the default Jackrabbit repository.

1. Go to pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit.

2. Open repository.xml in a text editor.

3. Ensure the PostgreSQL lines are not commented out.

4. Ensure the MySQL, Oracle, and MS SQL Server lines are commented out.

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

Perform Tomcat-specific connection tasks

After you configure the repository, configure your web application servers to connect to it.

In this step, you make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.

Note: By default, Pentaho Server is configured to run on Tomcat.

Connections are already specified.

Modify Tomcat context.xml only if you changed the default ports or passwords.

Step 1: Download driver and apply to the Pentaho Server

To connect to a database, including the Pentaho Repository database, you must download and copy a JDBC driver to the correct locations.

Note: Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers.

You must download and install the file yourself.

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

   See JDBC drivers reference for supported drivers.

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

3. If you want to keep the Pentaho sample, copy hsqldb-2.3.2.jar to pentaho-server/tomcat/lib.

Step 2: Modify JDBC connection information in the Tomcat XML file

Database connection and network information is stored in context.xml.

Modify context.xml to match your environment.

CAUTION:

If you use a different port, password, user, driver class, or host name, update the examples to match.

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

2. Go to server/pentaho-server/tomcat/webapps/pentaho/META-INF.

3. Open context.xml in a text editor.

4. Add the following code 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. Update the user name, password, driver class, host name, and port values for your environment.

6. Comment out any resource references that refer to other databases.

7. Set validationQuery to select 1.

8. Save and close context.xml.

Start your server

After you complete the archive installation steps, start the Pentaho Server.

For details, see Starting the Pentaho Server after an archive installation.