Hitachi Vantara Pentaho Community Wiki
Child pages
  • Configuring for PostgreSQL

Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin


Setting up the databases required by



Pentaho requires 3 databases which are more or less mandatory


. These are:

Wiki Markup
\|\| Database \|\| Description \|\| Sql Script \|\| Mandatory \|\|
\| \*Shark\* \| is used for bursting and similar tasks \| \[Shark.sql\|\] \| YES \|
\| \*Quarz\* \| database which is the main pentaho scheduler (think about it as a cron like tool that stores its cron jobs in the database) \| \[Quarz.sql\|\] \| YES \|
\| \*Hibernate\* \| is the persistence layer used by pentaho \| \[hibernate.sql\|\] \| YES \|
\| \*sampladata\* \\ \| if you are on production you may not need it but for demonstration and seeing whats new in pentaho you will need it \| \[sampledata.sql\|\] \| No \|




Sql Script



Used for bursting and similar tasks




Database which is the main pentaho scheduler (think about it as a cron-like tool that stores its cron jobs in the database)




The persistence layer used by pentaho




If you are on production you may not need it, but you will need it for demonstration and seeing what is new in Pentaho.



Please note that the sql files above REQUIRES you to edit them since the database are created and are owned by the tomcat userso . So, if you dont have an other another user in postgresql PostgreSQL who you want to use then you need will have to replace every occurance occurrence of tomcat with your own user *before importing the files.

one One point i I would like to add is how i I have created this sql SQL files: first i I converted them from hsqldb HSQLDB to mysql MySQL and than then from mysql to postgresql it MySQL to PostgreSQL. It was a bit pain full painfull process, but now pentaho Pentaho works and thats that's good. please Please be warned that the sampledata is not 100% well converted so you may encounter some problem with it, this . This is a task that still needs to be done to finalize the sampledata.

loading the data into postgresql:

create Create the dabases above empty and assuming databases. Assuming your user is tomcat you would do that as following:

Code Block
postgres@anfatech:~$ createdb shark \-O tomcat
 postgres@anfatech:~$ createdb quartz \-O tomcat
 postgres@anfatech:~$ createdb hibernate \-O tomcat
 postgres@anfatech:~$ createdb sampledata \-O tomcat

 and import the scripts above into the appropriate database as following

Code Block
tomcat@anfatech:~$ psql \-U tomcat \-d hibernate < hibernate.sql
tomcat@anfatech:~$ psql \-U tomcat \-d quarzquartz < quarzquartz.sql
tomcat@anfatech:~$ psql \-U tomcat \-d shark < shark.sql
tomcat@anfatech:~$ psql \-U tomcat \-d hibernate < sampledata.sql

now Now your postgresql PostgreSQL should be ready the . The next step is to configure the datasources required to access the data

Wiki Markup
you can these are my \[samples for jboss\|\] i will add tomcat context.xml in the near future&nbsp;

make .

You can use my settings as an example: PentahoDoc:samples for jboss

 sorry for the dead link (my homepage has been token over) now i uploaded the templates to the wiki so they should be fine there!

Configuring pentaho's datasources for Tomcat 5.5.x and Higher, using context.xml

if you plan to deploy on tomcat then you better use what tomcat offers you:

instead of messing with config files in conf/server.xml  or even worse creating a file under $CATALINA_HOME/conf/Catalina/localhost/yourApplication.xml

you could use what tomcat expects in your Application archive

Code Block

|   `-- context.xml  <---(***1***)
    `-- web.xml

(**1*): this file "context.xml" will be processed by tomcat *at deployment time and if you define your datasources there, tomcat
will do the necessary configurations for you.

An example of a valid context.xml file should be looking like the following one:

Code Block

<Context     path="/pentaho"
               docBase="path/to/pentaho" <---(***2***)
    <Resource   name="jdbc/Quartz"
    You can add as many datasources as you need here


(**2**) : if you deploy your application to tomcat default webapps so your docbase shoud be like the following one:

Code Block


when you finished creating your context.xml you can then package your war and deploy it to tomcat. the context.xml of your application will get converted to applicationName.xml for the application name pentaho as shown above the resulting file will be this one:

Code Block


if your database are correctly set and everything went fine pentaho should be able to access the databases using the datasource defined in the context.xml

Make sure your driver is in the right location and start up startup your application server where pentaho Pentaho is deployed into .

Configuring the Quartz Scheduler:

go to the file solution/system/quartz/ and change the line and search for the property "
org.quartz.jobStore.driverDelegateClass".  Change that line so that it reads:

Code Block

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