This article explains how to use Magnolia CMS and PostgreSQL as persistence layer under macOS. It shows how to set up your machine and give a Magnolia configuration to make use of a Postgres database.

PostgreSQL installation

You should download these tools if you don’t already have a running Postgres database engine:

• Postgres.app
• Optional: pgAdmin
• Optional: pgweb (make sure to get the Darwin version)

Start the Postgres service after installing Postgres.app and initializing the server.

Prepare the database

In Postgres.app, you can open a shell by double-clicking the postgres database, which is already connected to the database server. You will find here all the necessary commands for managing database configurations.

Here are some commands that may be helpful for you:

  \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

A role mgnl and a database magnolia will be created for Magnolia CMS. Please enter the following into the shell.

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

We have now created the database magnolia and the user mgnl, utilizing the password mgnlpass. The magnolia database should show up 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 the PostgreSQL GUI

If you use Postgres frequently, I recommend using pgAdmin. After opening pgAdmin, create a new database in the tree displayed on the left and enter the required connection details for your local Postgres instance:

After that, you can browse all the database objects in your magnolia database.

Option2: Use pgweb as the PostgreSQL GUI

If you want a lightweight tool for database management, you can use pgweb.

Just click the file pgweb_darwin_amd64 and your browser will open. You can enter your credentials by clicking on “Connect” in the upper-right corner.

If this doesn’t connect you to the database, resolve the issue. Now that PostgreSQL has been prepared, we can proceed with our Magnolia project.

Configure your project

Please refer to the official documentation for further guidance on configuring your Magnolia project. In this example, we will be utilizing a typical Magnolia project that is based on Maven.

Include the Postgres driver

The connection to the database is possible only if the JDBC driver for Postgres is included in your Magnolia WAR artifact. Add the needed dependency by editing the dependencies section of the pom.xml of your Magnolia webapp configuration:

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.4</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.

Set up the web app

You can set up all Magnolia instances in a single development project, and you should read the documentation if this is new to you.

Under WEB-INF/config, create a new folder for storing the configuration file specific to our Postgres variant of Magnolia. The name of the folder must match the name you want to use to access Magnolia in your browser on your development computer.

Name the file magnolia.properties and overwrite the Jackrabbit configuration:

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

This approach employs a database persistence configuration for Postgres.

Prepare the PostgreSQL configuration

The file specified above already exists in the default Maven project layouts, so you can just open it. If not, create the file. Under DataSources, enter the information for the database we made before:

<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 JCR file on Codeberg.

Build and start the bundle

Now, build your Maven project and start it. Please remember to start Postgres before that. If everything is fine, Magnolia should be up and running, and data should be stored in Postgres.

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

<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.

Keep all the content in the RDBMS

We will modify the database configuration to ensure that everything is stored in the database. You can do atomic backups and don’t have to worry about persistent file system storage in a cloud or container context.

Create a configuration

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

<!--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>

You will also find the modified JCR configuration file on Codeberg.

Rebuild the WAR artifact and start the Magnolia project by deleting the file-based repository folder from the first configuration on your disk.