Documentation Home
Changelog

Switch to PostgreSQL Tutorial

This tutorial will detail how to set up PostgreSQL for use with Broadleaf. By the end of this tutorial you will have completed:

  • PostgreSQL running locally on your development machine
  • Heat Clinic demo data loaded into PostgreSQL
  • Some idea of tooling around how to view the Heat Clinic data in PostgreSQL

Broadleaf works with a minimum PostgreSQL version of 9.2. We recommend using the latest version.

Download and install PostgreSQL

Windows

  1. Download the PostgreSQL installer from enterprisedb.com
  2. Execute the installer. As apart of setup, take note of the password for the postgres user that is created and the port number PostgreSQL listens on (the default is 5432)
  3. At the end, the installer will prompt you to run Stackbuilder. This is not a required step but will allow you to install additional tools

To verify the installation, open the pgadmin3 application and connect to the database with the postgres user and the previously-set password. Once you have logged in, you can add an additional login user to the database for Broadleaf to use.

OSX

The easiest way is to use Homebrew. After Homebrew is installed, you can install PostgreSQL by opening a Terminal and executing:

brew install postgresql
brew tap homebrew/services
brew services restart postgresql

The last 2 commands install the Homebrew services module that allows for easier manipulation of the Postgresql daemon.

To verify the installation, download and install the latest pgAdmin3 GUI. You can connect to the running PostgreSQL database with your OSX user and password. Once you login, you can modify the "Login roles" to change the password associated with your user, or create a new user for Broadleaf.

Linux

Use your favorite package installer to install PostgreSQL e.g.

apt-get install postgresql postgresql-contrib

After installation, the server should already be started up and able to be manipulated as a service (e.g. sudo service postgresql restart). No password is set by default, so run this command to connect to the server as the postgres user with sudo -u postgres psql postgres and change the password with \password postgres, entering it when prompted. For example:

ยป sudo -u postgres psql postgres
psql (9.4.7)
Type "help" for help.

postgres=# \password postgres
Enter new password:
Enter it again:

Create a new database schema

Create a new schema in the database (e.g. broadleaf). This is not required, you can also use the default public schema.

Update the poms to use PostgreSQL instead of HSQL

In your root pom.xml, find the following in the <dependencies> section under the <plugin> with <groupId>org.apache.tomcat.maven</groupId>

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>${hsqldb.version}</version>
    <type>jar</type>
</dependency>

and replace it with

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.4-1206-jdbc4</version>
</dependency>

Remove the HSQLDB startup execution

Since you will no longer be using HSQLDB, in the root pom.xml file, you can remove the task definitions that start and shutdown HSQLDB. Look for <id>hsqldb-start</id> and <id>hsqldb-stop</id>. Delete both of those executions.

Also, if you are using the start.sh commands in the site and admin projects, you should delete the antrun:run@hsqldb-start section. For instance, given the line that looks like:

MAVEN_OPTS="$MAVEN_OPTS $BROADLEAF_OPTS" mvn antrun:run@hsqldb-start tomcat7:run-war

Replace it with:

MAVEN_OPTS="$MAVEN_OPTS $BROADLEAF_OPTS" mvn tomcat7:run-war

Update the Hibernate dialect

Add the following dialect definitions to core/src/main/resource/runtime-properties/common-shared.properties to use the correct dialect for PostgreSQL

blPU.hibernate.dialect=org.hibernate.dialect.PostgreSQL82Dialect
blSecurePU.hibernate.dialect=org.hibernate.dialect.PostgreSQL82Dialect
blCMSStorage.hibernate.dialect=org.hibernate.dialect.PostgreSQL82Dialect

Enterprise and Multi-Tenant

You will need to update a 4th location:

blEventPU.hibernate.dialect=org.hibernate.dialect.PostgreSQL82Dialect

Use the correct Heat Clinic demo SQL extractor

  1. In order to correctly import the Heat Clinic demo data, you will need to add a special extractor that ensures compatibility with Postgres in the demo SQL imports. Add this property to core/src/main/resources/runtim-properties/common-shared.properties
blPU.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoPostgresSingleLineSqlCommandExtractor
blSecurePU.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoPostgresSingleLineSqlCommandExtractor
blCMSStorage.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoPostgresSingleLineSqlCommandExtractor

Enterprise and Multi-Tenant

blEventPU.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoPostgresSingleLineSqlCommandExtractor

Update the database connection properties

Update your database.properties to connect to your PostgreSQL database. In database.properties, you will want to update the following:

# your local database username, just a user that has readwrite permissions
database.user=postgres
# local database password
database.password=broadleaf
database.driver=org.postgresql.Driver
# this connection URL assumes that it is connecting to a schema called broadleaf
database.url=jdbc:postgresql://localhost:5432/postgres

Note: database.driver and database.url have been changed from HSQLDB to PostgreSQL.
Note: this assumes that you are using the default postgres database. Make sure you use your database name in the url if it is different.

Update runtime properties to create the initial schema

It is likely that you will still need to initialize and seed the new PostgreSQL schema. Ensure that these properties are set in either site/src/main/resources/runtime-properties/development.properties:

blPU.hibernate.hbm2ddl.auto=create
blEventPU.hibernate.hbm2ddl.auto=create
blSecurePU.hibernate.hbm2ddl.auto=create
blEventPU.hibernate.hbm2ddl.auto=create

After starting up the site project once, you can change all of these back to update so that any data changes stay intact.

And that's it! You should now be up and running with PostgreSQL.

FAQ

I cannot connect to PostgreSQL with pgAdmin

Check the log files in to see if there are any errors.

- Windows - C:\Program Files\PostgreSQL\9.5\data\pg_log
- OSX - /usr/local/var/postgres/server.log
- Linux - /var/log/postgresql

I get an error in my PostgreSQL logs FATAL: database files are incompatible with server

If you still need the data in this directory, you will need to use the pg_upgrade utility to upgrade your data. If you do not care about the data in the PostgreSQL data directory, remove it with rm -rf /usr/local/var/postgres/*. Then, initialize the data with initdb /usr/local/var/postgres.