This article is about developing with Magnolia and PostgreSQL as persistence layer under MacOS. It show’s how to setup your machine and provide a Magnolia configuration to make use of a Postgres database.

Install PostgreSQL

If you don’t already have a running Postgres database engine, I recommend to download the following tools:

Install Postgres.app, initialize the server and start the Postgres service.

Prepare the Magnolia database

In Postgres.app, double click on the “postgres” database to open a shell which is already connected to the database server. Here you can enter all the necessary commands to operate needed database configurations.

For your convenience you might find the following commands helpful:

  \list               # list all databases
  \dt                 # list all tables in a database
  \du                 # show roles and their permissions
  \h CREATE ROLE      # show help for a specific command
  \q                  # quit the postgres shell

We will create a role mgnl and a database magnolia to be used with Magnolia CMS. Enter in the shell:

  CREATE ROLE mgnl WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'mgnlpass';
  CREATE DATABASE magnolia OWNER mgnl;  

Now we created the database magnolia and the user mgnl with the password mgnlpass. The magnolia database should be displayed in Postgres.app.

You don’t need a graphical interface to manage your Postgres databases as you can use the shell for all operations. The two tools described below can be used if you prefer to have a GUI.

Option1: Use pgAdmin as GUI for Postgres

I recommend pgAdmin if you use Postgres regularly or because is more sophisticated than pgweb.

After opening pgAdmin, create a new database in the tree displayed on the left and enter the needed connection details for your local Postgres instance:

You can then browse all the database objects in your “magnolia” database.

Option2: Use pgweb as GUI for Postgres

If you want to save space or have a very slim graphical tool for database management, you can use pgweb as a light weight tool.

Double click on the file pgweb_darwin_amd64 and your browser will open. In the upper right corner click on “connect” and enter your credentials:

This should connect you to the magnolia database (if not, fix the problem…).

Now that PostgreSQL has been prepared, we can continue with our Magnolia project.

Configure your Magnolia project

In this example we use a typical Magnolia project based on Maven - please read the official documentation for details: Magnolia Maven module.

Include the database driver for Postgres in your project dependencies

The JDBC driver for Postgres must be included in your Magnolia war artifact to make the connection to the database possible.

Edit the dependencies section of the pom.xml of your Magnolia webapp configuration and add the needed dependency:

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

Go to the PostgreSQL JDBC Driver homepage to get details about the different versions available, you might also have to change the driver type depending on your actual configuration.

Provide a webapp configuration

You can configure all Magnolia instances within a single development project, read WAR file with multiple configurations if this is a new concept for you.

In your Magnolia webapp artifact, under WEB-INF/config create a new folder for storing the configuration file specific for our Postgres variant of Magnolia. The name of the folder should match the context where name you want to access Magnolia in your browser on your development environment.

Name the file magnolia.properties and overwrite (besides other things you might want) the configuration for the Jackrabbit repository:

magnolia.repositories.jackrabbit.config=WEB-INF/config/repo-conf/jackrabbit-bundle-postgres-search.xml

This way Magnolia uses a different configuration for database persistence.

Prepare the configuration for Postgres

The file specified above already exists in default Maven project layouts so you can just open it - if not, create the file.

Under DataSources provide the information for the database we created above:

<DataSources>
  <DataSource name="magnolia">
    <param name="driver" value="org.postgresql.Driver" />
    <param name="url" value="jdbc:postgresql://localhost:5432/magnolia" />
    <param name="user" value="mgnl" />
    <param name="password" value="mgnlpass" />
    <param name="databaseType" value="postgresql"/>
  </DataSource>
</DataSources>

You can find the whole JCR configuration file on GitHub.

Build the Magnolia bundle and start it

Now build your Maven project and start it (don’t forget to startup Postgres before that). If everything is OK, Magnolia should be up and running and data should be stored in Postgres.

Note: In the current configuration, not everything is stored in the database but also on the filesystem:

There is a threshold in the Jackrabbit configuration file when to put data in the database (see “minRecordLength” below):

<DataStore class="org.apache.jackrabbit.core.data.FileDataStore">
  <param name="path" value="${rep.home}/repository/datastore"/>
  <param name="minRecordLength" value="1024"/>
</DataStore>

From the Jackrabbit documentation - Magnolia uses 1024 bytes as default.

minRecordLength: The minimum object length. The default is 100 bytes; smaller objects are stored inline (not in the data store). Using a low value means more objects are kept in the data store (which may result in a smaller repository, if the same object is used in many places). Using a high value means less objects are stored in the datastore (which may result in better performance, because less datastore access is required). There is a limitation on the minRecordLength: the maximum value is around 32000. The reason for this is that Java doesn’t support strings longer than 64 KB in writeUTF.

If you use the previous configuration of PostgreSQL combined with a filesystem storage, don’t forget to backup the repository on the filesystem - otherwise you will miss a lot of content!

Store all content in the RDBMS

We will modify the database configuration to store everything (in the sense of content) in the database. This way you will be able to do atomic backups and you don’t have to care about persistent file system storage - which is always something you have to consider in a cloud or container context (Docker).

Modify the configuration

We can reuse the previous configuration and modify the following parts - previous configuration has been commented out so you can find where to place the modifications.

<!--FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
  <param name="path" value="${rep.home}/repository" />
</FileSystem-->
<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
  <param name="dataSourceName" value="magnolia"/>
  <param name="schemaObjectPrefix" value="fs_"/>
</FileSystem>

<!--DataStore class="org.apache.jackrabbit.core.data.FileDataStore">
  <param name="path" value="${rep.home}/repository/datastore"/>
  <param name="minRecordLength" value="1024"/>
</DataStore-->
<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
  <param name="dataSourceName" value="magnolia"/>
  <param name="schemaObjectPrefix" value="ds_"/>
</DataStore>

<!--FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
  <param name="path" value="${wsp.home}/default" />
</FileSystem-->
<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
  <param name="dataSourceName" value="magnolia"/>
  <param name="schemaObjectPrefix" value="fs_${wsp.name}_"/>
</FileSystem>

<!--FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
  <param name="path" value="${rep.home}/workspaces/version" />
</FileSystem-->
<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
  <param name="dataSourceName" value="magnolia"/>
  <param name="schemaObjectPrefix" value="version_"/>
</FileSystem>

Again you can find the modified JCR configuration file on GitHub.

Now delete the file based repository folder from the first configuration on your disk, rebuild the WAR artifact and startup the Magnolia project.

Note: There will still be files left on your disk that will not be stored in the RDBMS. Those are indexes that are not needed for backing up the content. You can delete these files but then they will be re-created by Magnolia which can cause delays when starting up the system (depending on the amount of content you have).