Pentaho on PostgreSQL

Pentaho on PostgreSQL

Pentaho is agnostic when it comes to data sources — if there is a JDBC/ODBC driver for the database, Pentaho will connect to it.

However, the Pentaho platform itself has been optimised to run on top of HSQL or MySQL. Here is how to make it run on PostgreSQL. These instructions have been tested with Pentaho 1.6 and 1.7 on PostgreSQL 8.2. There are a few known problems with running Pentaho on PostgreSQL 8.3 (but it mostly works).

We would like to thank the Fujitsu FAST team that worked with us on the tricky PostgreSQL bits.

There are three main databases that we have focused on to enable the entire Pentaho platform to run on PostgreSQL. There is one that exists that we haven't worked on and that is the shark database. We have not used the shark functionality.

There is additional information on running Pentaho with PostgreSQL on the Pentaho Wiki, but from our experience the method shown below works much better.

Hibernate

1. Create a PostgreSQL user: "hibuser" with password "password"
2. Create PostgreSQL database: "hibernate"
   1. Give the user "hibuser" admin access to that database
   2. Restore the database schema with the following database - or just let hibernate do all the table creation
   3. Either give "hibuser" superuser access, or make sure that all tables in this database are owned by "hibuser"
3. Configure hibernate.cfg.xml in the jboss/server/default/deploy/pentaho.war/WEB-INF/classes directory
   1. Need to modify the following entries
      1. org.postgresql.Driver
      2. jdbc:postgresql://localhost:5432/hibernate
      3. org.hibernate.dialect.PostgreSQLDialect
4. edit PentahoHibernate-ds.xml
   1. Need to modify the following entries
      1. org.postgresql.Driver
      2. jdbc:postgresql://localhost:5432/hibernate

Quartz

1. Create a PostgreSQL user: "pentaho_user" with password "password"
2. Create postgres quartz database
   1. give the user pentaho_user admin access to that database
   2. Restore the database with this backup file (pg_restore -i -p 5432 -U pentaho_user -d quartz quartz.backup)
   3. Either give "pentaho_user" superuser access, or make sure that all tables in this database are owned by "pentaho_user"
3. Edit the quartz.properties file in the pentaho-solutions/system/quartz directory
   1. Add this line
      1. org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
   2. comment out this line
      1. org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
4. Edit the quartz-ds.xml file in the jboss/server/default/deploy directory
   1. Need to modify the following entries
      1. org.postgresql.Driver
      2. jdbc:postgresql://localhost:5432/quartz

SampleData

1. Create a PostgreSQL user: "pentaho_admin" with password "password"
2. Create the sampledata database
   1. give the the user pentaho_admin admin access to the database
   2. Either give "pentaho_admin" superuser access, or make sure that all tables in this database are owned by "pentaho_admin"
3. Edit the sampledata-ds.xml and sampledata_admin-ds.xml files
   1. Need to modify the following entries
      1. org.postgresql.Driver
      2. jdbc:postgresql://localhost:5432/sampledata

Troubleshooting

If Pentaho gives permissions errors when trying to connect to the databases, make sure the users created in the above steps have write access to their related databases.

How to change the default schema in Postgresql

When Postgresql database has multiple schema and Pentaho need explicit which schema is going to be used, you probably need modify your default schema by changing 'search_path' in postgresql.conf file (See details here). The postgresql.conf is normally under /var/lib/postgresql on Linux and /Library/PostgreSQL8/ on Mac (See references 3 & 4)

Alternatively, you can also use "ALTER USER user_name SET search_path to ...;" instead, after logging into the database.

References

• http://wiki.pentaho.com/display/PentahoDoc/Configuring+for+PostgreSQL
• http://wiki.pentaho.com/display/COM/Using+PostgreSQL+8.x+for+Hibernated+Data+in+the+Platform
• http://www.studiotips.net/docs/tips_sql/postgresql/installingpostgresqlonmac.html
• http://developer.apple.com/internet/opensource/postgres.html