Integrating Plone With External Databases In Ubuntu 20.04

Plone is a powerful open-source Content Management System (CMS) that allows users to easily create, manage, and publish content on the web. One of the key features of Plone is its flexibility, which allows it to be integrated with a wide range of databases, including PostgreSQL.

Integrating Plone with PostgreSQL offers several benefits, including improved performance, scalability, and security. We will explore the steps required to integrate Plone with PostgreSQL.

Connecting to a PostgreSQL database from ZopeDB (the database used by Plone) allows for several benefits, including

1. Scalability: PostgreSQL is a robust and powerful database that can handle large amounts of data and high levels of concurrency, making it well-suited for large and complex Plone sites.

2. Durability: PostgreSQL has built-in support for data durability and recovery, ensuring that your data is safe in the event of a failure or power outage.

3. Flexibility: PostgreSQL supports a wide range of data types and can be easily integrated with other systems, making it a versatile choice for use with Plone.

4. Performance: PostgreSQL can be configured to optimize performance for specific workloads and has support for advanced features such as full-text search, spatial data, and JSON datatype, which can be useful for Plone.

By using RelStorage, a third-party storage adapter, we can use a PostgreSQL database as the backend storage for ZopeDB and Plone, which allows us to take advantage of the features and benefits of PostgreSQL while still using the familiar ZopeDB API to access the data.

Step 1: Install PostgreSQL

The first step in integrating Plone with PostgreSQL is to install the database software on your server. This can be done by downloading the appropriate installer for your operating system from the PostgreSQL website and running it.

Install postgresql using the below command

sudo apt-get install postgresql


Step 2: Configure Postgresql

Create a Postgres user to manage the database in the following step. Later, the conf file requires the user and the provided password.

To carry out the tasks, Postgres utilizes a separate system user named “Postgres.” So the following command will change the Postgres user:

sudo -su postgres


To create a Plone user, complete the steps below.

The Plone user’s password needs to be changed at that time as well.

After the installation is complete, you must change the password in the Plone configuration file.

createuser --createdb --username postgres --no-createrole --no-superuser --pwprompt plone


Then, in order to get more rights, the user must be made a superuser.

psql
ALTER USER plone WITH SUPERUSER;

Step 3: Then create a database in psql for plone by entering the command below.

CREATE DATABASE plonecmsdb;

After creating the database, make sure the database is created by

\l
plonecmsdb | postgres | UTF8 | en_IN | en_IN |

Exit from psql and Postgres user:

\q
exit

After configuring the database in Postgres, next we need to edit the buildout.cfg

buildout.cfg
[buildout]
extends =
   https://dist.plone.org/release/6.0.0/versions-ecosystem.cfg
   https://dist.plone.org/release/6.0.0/versions-extra.cfg
   https://dist.plone.org/release/6.0.0/versions.cfg
find-links = https://dist.plone.org/release/6.0.0
eggs-directory = ~/.buildout/eggs
download-cache = ~/.buildout/download-cache
show-picked-versions = true
abi-tag-eggs = true
newest = false
parts =
   instance
custom-eggs =
   Plone
   Pillow
   relstorage
   psycopg2-binary==2.8.6
[instance]
recipe = plone.recipe.zope2instance
user = admin:admin
eggs = ${buildout:custom-eggs}
zodb-storage-type = relstorage
rel-storage =
   type postgresql
   dbname plonecmsdb
   host localhost
   user plone
   password testpass

In the above buildout, we have added the python package ‘relstorage’ and ‘psycopg2-binary’, these are the packages needed integrate with the external database. Only refer to the above buildout and configure as per your requirements.

Under the [instance] add ‘zodb-storage-type = relstorage’, it set’s the storage type relstorage.

Under rel-storage
type: any database type supported (postgresql, mysql, or oracle)
dbname: Specify the database name here instead of the mynewdb
Host: set your host
User: here specify the username
password : enter the password

After editing the buildout.cfg run ./bin/buildout from the same directory, then run the plone instance by ./bin/instance fg .

Make sure the plone is running without any error,

2023-01-25 15:15:55,210 INFO [Zope:42][MainThread] Ready to handle requests
Starting server in PID 330886.
2023-01-25 15:15:55,211 INFO [waitress:486][MainThread] Serving on http://0.0.0.0:8080

If the log appear as above, then it looks like the Plone instance is configured with the SQL database.

In some system’s there might be an ModuleNotFoundError: No module named ‘psycopg2’, in that case, pip install psycopg2-binary.