Relstorage struggle

Plone Support
1

We/I began a long-term plan that includes migrating our remaining Plone 5 sites to Plone 6.

I found that modifying the Plone 5 buildout to pull in version 6.0.11 worked, I was able to run the migration and successfully update my ZODB.

I then modified the buildout to add relstorage into the mix so that I could run zodbconvert. Once relstorage and utils were installed I created a zodbconvert.conf file like the following (I need to convert encrypted dataFS to unencrypted relstorage).

my buildout

FWIW: I am using Postgres 16 in a cluster formation of 3 machines: 1 main 1 replica and 1 hot spare. Also Python 3.10.15

%import cipher.encryptingstorage
<encryptingstorage source>
  config /opt/plone52x/zeocluster/encryption.conf
  # FileStorage database
  <filestorage>
    path /opt/plone52x/zeocluster/var/filestorage/encrypted.fs
    blob-dir /opt/plone52x/zeocluster/var/blobstorage-enc/
  </filestorage>
</encryptingstorage>

<relstorage destination>
  keep-history false
  shared-blob-dir false
  blob-dir /opt/plone52x/zeocluster/var/blob-cache
  blob-cache-size 768mb
  <postgresql>
  dsn dbname='psqlDB4' host='172.16.140.100' port='5000' user='plone' password=''XXXXXXX" 
  </postgresql>
</relstorage>

When I run --dry-run there are no complaints from zodbconvert.

When I run zodbconvert for real I use the --clear and --trace flags. FYI - It took about 21hours to transfer 520gb to the new database.

Once zodbconvert is done, I try to run the instance to test the conversion, both relstorage & psycopg2 complain:

complete traceback

psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "object_state"

2024-09-27 12:45:02,305 WARNING [relstorage.adapters.scriptrunner:84][MainThread] script statement failed: 'CREATE TABLE current_object (\nzoid BIGINT NOT NULL PRIMARY KEY,\ntid BIGINT NOT NULL,\nFOREIGN KEY (zoid, tid)\nREFERENCES object_state (zoid, tid)\n) '; parameters: ()

I must be doing something wrong as I cannot seem to find any examples of anyone else having these issues. I've been working too long and too close to this that any new eyes on this would be welcome.

my PostgreSQL logs have an error for the DB:

2024-09-27 16:58:03 UTC [991261-5] [local] plone@psqlDB4 ERROR: there is no unique constraint matching given keys for referenced table "object_state" 2024-09-27 16:58:03 UTC [991261-6] [local] plone@psqlDB4 STATEMENT: CREATE TABLE current_object ( zoid BIGINT NOT NULL PRIMARY KEY, tid BIGINT NOT NULL, FOREIGN KEY (zoid, tid) REFERENCES object_state (zoid, tid) )

I've been able to validate that my relstorage works with a new site. Plone can create the proper schema in a new PSQL table fine. Something is off with my conversion process. Any ideas are welcome. --thanks!

2

E.S. Tyrer via Plone Community wrote at 2024-9-27 18:09 +0000:

...
2024-09-27 16:58:03 UTC [991261-5] [local] plone@psqlDB4 ERROR: there is no unique constraint matching given keys for referenced table "object_state" 2024-09-27 16:58:03 UTC [991261-6] [local] plone@psqlDB4 STATEMENT: CREATE TABLE current_object ( zoid BIGINT NOT NULL PRIMARY KEY, tid BIGINT NOT NULL, FOREIGN KEY (zoid, tid) REFERENCES object_state (zoid, tid) )

To my understanding, the error message means:
the table object_state lacks a constraint declaring
that the combined colums zoid and tid identify the records uniquely;
e.g. because (zoid, tid) is a key for this table.

I would add a corresponding constraint and see what happens.

1 Like
3

Thanks Dieter!

I knew I was looking at this too long.

Refering to the tables created from a vanilla site, I could see that table object_state was missing columns and settings. As suggested, I added the missing zoid & tid and then I got:

ERROR: duplicate key value violates unique constraint "commit_row_lock_pkey"

logs said DETAIL: Key (tid)=(0) already exists.

So i truncated that table and then miraculously plone started up and was able to connect to the DB.

I can't log in yet I think I've got a password mismatch for the admin at the moment but I think I'd call this progress!

-est2 :beers:

4

Not sure what I'm doing wrong here. Migrating a site to RelStorage should be straightforward right?

I was able to login to the ZMI but my plone site is missing. The data is still around according to the controlpanel:

Database information main Database Location RelStorage: <PostgreSQLAdapter at 7f408eb01be0 history preserving,dsn="dbname='xxx' host='172.16.140.100' port='5000' user='plone'"> Database Size 531733.0M Total number of objects in the database 7336 Total number of objects in memory from all caches 7547 Target number of objects in memory per cache 30000 Target memory size per cache in bytes 0

trying to pack the DB for giggles results in more psycopg2 errors

psycopg2.errors.NotNullViolation: null value in column "zoid" of relation "object_refs_added" violates not-null constraint DETAIL: Failing row contains (null, 0).

i've been working on this all summer and I had things working before i was able to test --incremental with zodbconvert. Now it seems that when you run zodbconvert it's not configuring the schema correctly so that the (re)serialized data is structured for retrieval.

maybe I'll file it as a bug over at the relstorgage github page?

-est2

5

E.S. Tyrer via Plone Community wrote at 2024-9-27 22:04 +0000:

...
trying to pack the DB for giggles results in more psycopg2 errors

psycopg2.errors.NotNullViolation: null value in column "zoid" of relation "object_refs_added" violates not-null constraint DETAIL: Failing row contains (null, 0).

I cannot tell you (from the distance) what went wrong but
apparently, the conversion to relstorage resulted in a strange (data) state:
zoids are 8 bytes (maybe represented as a large integer) and
can definitely not be 'NULL`.

My suggestion: create a small FileStorage (similar to your
productive one but much smaller) and check the conversion to relstorage
(similar to your productive target) with it.
Check especially that the created (relstorage) data model is as expected.