# PostgreSQL Storage (pgjsonb)

The backend can store the ZODB in PostgreSQL using the
[zodb-pgjsonb](https://github.com/bluedynamics/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:

1. `ZODB_PGJSONB_DSN` is set -- pgjsonb (PostgreSQL).
2. `ZEO_ADDRESS` is set -- ZEO.
3. 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:

```yaml
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

```bash
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`:

```bash
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](https://github.com/bluedynamics/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:

```bash
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

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

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

   ```text
   <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>
   ```

3. Run the conversion:

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

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