PostgreSQL Storage (pgjsonb)¶

The backend can store the ZODB in PostgreSQL using the zodb-pgjsonb storage, as an alternative to FileStorage or ZEO. Objects are stored as JSONB rows in PostgreSQL, and blobs are stored in PostgreSQL as well. zodb-pgjsonb is a regular dependency of wcs.backend, so it is always installed.

pgjsonb requires a PostgreSQL server (tested with PostgreSQL 17).

Docker Image¶

The webcloud7/backend-plone image enables pgjsonb when ZODB_PGJSONB_DSN is set. On startup the entrypoint picks the storage configuration in this order:

ZODB_PGJSONB_DSN is set – pgjsonb (PostgreSQL).
ZEO_ADDRESS is set – ZEO.
Neither is set – the default local FileStorage.

The image runs pgjsonb in history-free mode by default.

Variable	Default	Meaning
`ZODB_PGJSONB_DSN`	– (required to enable)	libpq connection string, e.g. `host=db dbname=plone user=plone password=secret`
`ZODB_PGJSONB_HISTORY_PRESERVING`	`false`	Keep old object revisions (`true`) or only the current state (`false`)
`ZODB_PGJSONB_POOL_SIZE`	`2`	Minimum number of PostgreSQL connections kept open in the pool
`ZODB_PGJSONB_CACHE_SHARED_MB`	`256`	Size in MB of the process-wide shared object cache
`ZODB_CACHE_SIZE`	`50000`	ZODB connection cache size in objects (same variable as for ZEO/FileStorage)

Example docker-compose services:

services:
  cms:
    image: webcloud7/backend-plone:<tag>
    environment:
      ZODB_PGJSONB_DSN: "host=postgres dbname=plone user=plone password=plone"
  postgres:
    image: postgres:17
    environment:
      POSTGRES_USER: plone
      POSTGRES_PASSWORD: plone
      POSTGRES_DB: plone

The database schema is created automatically on first start. The database and the user must already exist – the postgres image creates both from the POSTGRES_* variables.

Local Development¶

Prerequisites¶

A local PostgreSQL server on localhost:5432, and psql able to connect as a user that is allowed to create roles and databases.

Creating the pgjsonb instance¶

make instance-pgjsonb

This creates the PostgreSQL role plone and the database plone (only if they are missing) and generates a separate instance in instance-pgjsonb/ from instance-pgjsonb.yaml. The regular FileStorage instance in instance/ is not touched.

Running against pgjsonb¶

Every instance and run target works against the pgjsonb instance when called with STORAGE=pgjsonb:

make run STORAGE=pgjsonb
make develop STORAGE=pgjsonb
make fg STORAGE=pgjsonb
make debug STORAGE=pgjsonb
make run-all STORAGE=pgjsonb

Without STORAGE, the targets use the FileStorage instance as before.

Configuration¶

The pgjsonb instance settings live in instance-pgjsonb.yaml, using the db_pgjsonb_* options of cookiecutter-zope-instance. The defaults are:

Option	Default
`db_pgjsonb_dsn`	`dbname='plone' user='plone' host='localhost' port='5432'`
`db_pgjsonb_history_preserving`	`false` (history-free)
`db_pgjsonb_blob_temp_dir`	`instance-pgjsonb/var/blobtemp`

After editing the yaml file, regenerate the instance:

make instance STORAGE=pgjsonb

Migrating from FileStorage¶

An existing FileStorage database (including its blobs) can be copied into PostgreSQL with zodbconvert, which copies all transactions from a source storage into a destination storage. zodbconvert ships with RelStorage:

In the Docker image it is available as /app/bin/zodbconvert.
Locally, install it with bin/pip install relstorage.

Steps¶

Stop the instance. The destination should be a new, empty database.

Create a config file, e.g. migrate.cfg, and adapt the paths and the DSN:

<filestorage source>
    path /path/to/instance/var/filestorage/Data.fs
    blob-dir /path/to/instance/var/blobs
</filestorage>

%import zodb_pgjsonb
<pgjsonb destination>
    dsn dbname=plone user=plone host=localhost port=5432
</pgjsonb>

Run the conversion:

bin/zodbconvert migrate.cfg
# or, inside the container
/app/bin/zodbconvert migrate.cfg

Start the instance against the pgjsonb database, e.g. with make run STORAGE=pgjsonb locally or with the Docker environment variables described above.

Elasticsearch content is not affected by the storage change.