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 pgAdmin4 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 project's core pom.xml, find the following in the <dependencies> section:

<dependency>
    <groupId>com.broadleafcommerce</groupId>
    <artifactId>broadleaf-boot-starter-hsql-database</artifactId>
</dependency>

and replace it with

<dependency>
    <groupId>com.broadleafcommerce</groupId>
    <artifactId>broadleaf-boot-starter-database</artifactId>
</dependency>

Finally add the PostgreSQL JDBC connector dependency to the <dependencies> section:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

Update the Hibernate dialect

Update the runtime properties to use the correct PostgreSQL dialect. In core/src/main/resources/runtime-properties/common-shared.properties, you will want to update the three persistence unit dialects to say:

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 as well:

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

Update the SQL Command Extractor

If you wish to utilize the heat clinic demo import scripts, edit core/src/main/resources/runtime-properties/common-shared.properties. Update the following properties to cause the system to modify the import scripts at runtime for PostgreSQL Server compatibility:

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

You will need to update a 4th location as well:

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

Add the database connection properties

In core/src/main/resources/runtime-properties/common-shared.properties, add your database connection properties like so:

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

Note: I named my database broadleaf, make sure you use your database name in the url.

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 admin/src/main/resources/runtime-properties/default.properties:

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

After starting up the admin once, you can change all of these back to update so that any data changes stay intact. Also, add the same properties set to update to site/src/main/resources/runtime-properties/default.properties so that starting up site doesn't wipe your changes.

Create Missing Foreign Key Indexes

By default, PostgrSQL does not create foreign key (FK) indexes. For query performance reasons, these indexes should be created. The instructions below outline a 2-step process - 1) find the missing indexes 2) create the missing indexes:

1) First run the below SQL script to get the list of all FK indexes that need to be created:

SELECT concat('create index ', c.conname, ' on ', c.conrelid::regclass, '(', string_agg(a.attname, ',' ORDER BY x.n), ');')
FROM pg_catalog.pg_constraint c
   /* enumerated key column numbers per foreign key */
   CROSS JOIN LATERAL
      unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each key column */
   JOIN pg_catalog.pg_attribute a
      ON a.attnum = x.attnum
         AND a.attrelid = c.conrelid
WHERE NOT EXISTS
        /* is there a matching index for the constraint? */
        (SELECT 1 FROM pg_catalog.pg_index i
         WHERE i.indrelid = c.conrelid
           /* the first index columns must be the same as the
              key columns, but order doesn't matter */
           AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
               @> c.conkey)
  AND c.contype = 'f'
GROUP BY c.conrelid, c.conname, c.confrelid
ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;

2) Copy the list of create index scripts that were created from step #1 and execute those statements. This step will create the missing indexes.

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.