> For the complete documentation index, see [llms.txt](https://docs.pentaho.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.pentaho.com/install/legacy-redirects/manual-installation-process-archived-content/use-postgresql-as-your-repository-database-manual-installation.md).

# Use PostgreSQL as your repository database (Manual installation)

{% hint style="info" %}
This page is kept for existing links. Go to [Manual installation process](/install/legacy-redirects/manual-installation-process.md#use-postgresql-as-your-repository-database-manual-installation).
{% endhint %}

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](/install/legacy-redirects/manual-installation-process-archived-content/prepare-your-windows-environment-for-a-manual-installation.md)
* [Prepare your Linux environment for a manual installation](/install/legacy-redirects/manual-installation-process-archived-content/prepare-your-linux-environment-for-a-manual-installation.md)

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

{% hint style="warning" %}
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.
{% endhint %}

{% hint style="warning" %}
Update usernames, passwords, and port numbers in examples and scripts to match your environment.
{% endhint %}

<details>

<summary>Quartz 1.x vs Quartz 2.x upgrade note (Pentaho 10.2.0.1+)</summary>

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

</details>

{% stepper %}
{% step %}

### 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.
   {% endstep %}

{% step %}

### Step 2: Run SQL scripts

Run the PostgreSQL initialization scripts from the Pentaho software distribution.

{% hint style="info" %}
You may need administrator permissions to run these scripts on the host OS.
{% endhint %}

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](/install/legacy-redirects/back-up-your-pentaho-products-and-install-pentaho-9.1-pentaho-upgrade.md).
2. If Pentaho Server is running, stop it. See [Stop and start the Pentaho Server and repository](/install/legacy-redirects/tasks-to-be-performed-by-an-it-administrator-legacy-redirects/configure-the-pentaho-server-legacy-pages/stop-and-start-the-pentaho-server-and-repository.md).
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.

{% hint style="info" %}
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](/install/legacy-redirects/manual-installation-process-archived-content/prepare-your-windows-environment-for-a-manual-installation.md) or [Prepare your Linux environment for a manual installation](/install/legacy-redirects/manual-installation-process-archived-content/prepare-your-linux-environment-for-a-manual-installation.md).
{% endhint %}
{% endstep %}

{% step %}

### Step 3: Verify PostgreSQL initialization

{% hint style="info" %}
Unless you changed it in Step 1, the default password for the users below is `password`.
{% endhint %}

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.
{% endstep %}
{% endstepper %}

### Configure the PostgreSQL Pentaho Repository database

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

{% hint style="info" %}
PostgreSQL is configured by default. If you kept the default password and port, skip this section and continue to [Perform Tomcat-specific connection tasks](#perform-tomcat-specific-connection-tasks).
{% endhint %}

{% hint style="warning" %}
If you use a different port or password, update the examples to match your environment.
{% endhint %}

#### 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`:

   ```properties
   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.

{% hint style="info" %}
Hibernate also stores the audit logs used by Operations Mart.
{% endhint %}

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:

   ```xml
   <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**

```xml
<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**

```xml
<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**

```xml
<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)**

```xml
<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**

```xml
<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)**

```xml
<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**

```xml
<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.

{% hint style="info" %}
Pentaho Server is configured for Tomcat by default. Only edit Tomcat `context.xml` if you changed default ports or passwords.
{% endhint %}

{% stepper %}
{% step %}

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

{% hint style="info" %}
Pentaho cannot redistribute some third-party database drivers. Download the driver from your database vendor.
{% endhint %}

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

   See [JDBC drivers reference](/install/jdbc-drivers-reference.md).
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.
   {% endstep %}

{% step %}

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

{% hint style="warning" %}
If your port, password, username, driver class, or host differs from the defaults, update the examples.
{% endhint %}

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:

   ```xml
   <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.
   {% endstep %}
   {% endstepper %}

### Next steps

Continue with [Configure and start the Pentaho Server after manual installation](/install/legacy-redirects/manual-installation-process-archived-content/configure-and-start-the-pentaho-server-after-manual-installation.md).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.pentaho.com/install/legacy-redirects/manual-installation-process-archived-content/use-postgresql-as-your-repository-database-manual-installation.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.