Trouble converting relstorage to python3

Plone Support
, ,
1

I'm trying to convert a PostgreSQL 11.12 database from python 2.7 to python 3 on Plone 5.2.

I'm running:
bin/zodbupdate --pack --convert-py3 --encoding utf8 --encoding-fallback latin1 -c migration.conf

And get the error psycopg2.ProgrammingError: named cursor isn't valid anymore. I'm not sure if the issue is relstorage, psycopg2, or somewhere else. I suspect it may be a relstorage bug, so I also created an issue here psycopg2.ProgrammingError: named cursor isn't valid anymore · Issue #486 · zodb/relstorage · GitHub. I really want to run this migration against the relstorage, as converting to filestorage has presented its own set of challenges.

Full Traceback:

Committing changes (#1).
Using store connection <StoreConnection at 0x7f70ae438f90 active=False description={'backend_pid': xxx} conn=<connection object at 0x7f70ae4b4440; dsn: '[...]', closed: 0> cur=<cursor object at 0x7f70ae422650; closed: 0>>
Function _flush_temps_to_db took 1.660s.
Function _lock_and_move took 3.991s.
Objects were locked by tpc_finish for 3.992s
Function tpc_finish took 4.475s.
Exception closing <cursor object at 0x7f70b38d5450; closed: 0>
Traceback (most recent call last):
  File "/srv/plone/cache/eggs/RelStorage-3.4.5-py3.7-linux-x86_64.egg/relstorage/adapters/connections.py", line 279, in server_side_cursor
    yield ss_cursor
  File "/srv/plone/cache/eggs/RelStorage-3.4.5-py3.7-linux-x86_64.egg/relstorage/storage/__init__.py", line 586, in __record_iternext_gen
    for record in self._adapter.dbiter.iter_current_records(ss_cursor, start_oid_int):
  File "/srv/plone/cache/eggs/RelStorage-3.4.5-py3.7-linux-x86_64.egg/relstorage/adapters/dbiter.py", line 96, in iter_current_records
    for oid_int, tid_int, state_bytes in cursor:
psycopg2.ProgrammingError: named cursor isn't valid anymore

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/srv/plone/cache/eggs/RelStorage-3.4.5-py3.7-linux-x86_64.egg/relstorage/adapters/connmanager.py", line 186, in close
    obj.close()
psycopg2.ProgrammingError: named cursor isn't valid anymore
An error occured
Traceback (most recent call last):
  File "/srv/plone/cache/eggs/zodbupdate-1.5-py3.7.egg/zodbupdate/main.py", line 223, in main
    updater()
  File "/srv/plone/cache/eggs/zodbupdate-1.5-py3.7.egg/zodbupdate/update.py", line 81, in __call__
    for oid, serial, current in self.records:
  File "/srv/plone/cache/eggs/zodbupdate-1.5-py3.7.egg/zodbupdate/update.py", line 147, in records
    oid, tid, data, next = storage.record_iternext(next)
  File "/srv/plone/cache/eggs/RelStorage-3.4.5-py3.7-linux-x86_64.egg/relstorage/storage/__init__.py", line 643, in record_iternext
    new_oid, new_tid, new_state = self.__next(cursor)
  File "/srv/plone/cache/eggs/RelStorage-3.4.5-py3.7-linux-x86_64.egg/relstorage/storage/__init__.py", line 586, in __record_iternext_gen
    for record in self._adapter.dbiter.iter_current_records(ss_cursor, start_oid_int):
  File "/srv/plone/cache/eggs/RelStorage-3.4.5-py3.7-linux-x86_64.egg/relstorage/adapters/dbiter.py", line 96, in iter_current_records
    for oid_int, tid_int, state_bytes in cursor:
psycopg2.ProgrammingError: named cursor isn't valid anymore
Stopped processing, due to: named cursor isn't valid anymore
Traceback (most recent call last):
  File "/srv/plone/cache/eggs/zodbupdate-1.5-py3.7.egg/zodbupdate/main.py", line 223, in main
    updater()
  File "/srv/plone/cache/eggs/zodbupdate-1.5-py3.7.egg/zodbupdate/update.py", line 81, in __call__
    for oid, serial, current in self.records:
  File "/srv/plone/cache/eggs/zodbupdate-1.5-py3.7.egg/zodbupdate/update.py", line 147, in records
    oid, tid, data, next = storage.record_iternext(next)
  File "/srv/plone/cache/eggs/RelStorage-3.4.5-py3.7-linux-x86_64.egg/relstorage/storage/__init__.py", line 643, in record_iternext
    new_oid, new_tid, new_state = self.__next(cursor)
  File "/srv/plone/cache/eggs/RelStorage-3.4.5-py3.7-linux-x86_64.egg/relstorage/storage/__init__.py", line 586, in __record_iternext_gen
    for record in self._adapter.dbiter.iter_current_records(ss_cursor, start_oid_int):
  File "/srv/plone/cache/eggs/RelStorage-3.4.5-py3.7-linux-x86_64.egg/relstorage/adapters/dbiter.py", line 96, in iter_current_records
    for oid_int, tid_int, state_bytes in cursor:
psycopg2.ProgrammingError: named cursor isn't valid anymore

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/srv/plone/bin/zodbupdate", line 294, in <module>
    sys.exit(zodbupdate.main.main())
  File "/srv/plone/cache/eggs/zodbupdate-1.5-py3.7.egg/zodbupdate/main.py", line 227, in main
    raise AssertionError()
AssertionError

Pinned Versions:

psycopg2 = 2.9.2
relstorage = 3.4.5

Any help or thoughts are greatly appreciated.

2

ok O In Place Migration Guru @pbauer ... surely you've upgraded RelStorage-powered sites before. You haven't run into this?

3

Is the source using the same RelStorage version and pgsql version as the target?

4

I think I get your question, but correct me if I've misunderstood.

We're migrating in several steps. Plone 5.1.7/py2 and Plone 5.2.5/py2 both use psycopg2 = 2.8.6 and relstorage = 3.4.5. Plone 5.2.5/py3 uses relstorage = 3.4.5 as well. I've tried psycopg2 2.8.6 up to 2.9.2 while troubleshooting this.

Process-wise, I have been using these steps as a guide: Flame alert: the Plone Python 3 upgrade story - #13 by jensens

5

I've never migrated relstorage sites directly. Before migration I would switch to filestorage and then back. Mostly because zodbverify does not work in RelStorage and is a invaluable tool to debug the DB after a migration.

6

For anyone that comes across this thread, as I did, in the future:

This is actually caused by a bug/bad assumption between zodbupdate versus RelStorage. As far as I can tell, no person ever will have successfully migrated a PostgreSQL RelStorage database via zodbupdate unless the number of objects is very small and the entire update can be done with only a single transaction batch (which means < 100000 objects in the database). I document the bug in the RelStorage github issue tracker as Issue ID#529 (I'm not allowed to link it in here) and in that report I cross-reference to a solution (patch) in the zodbupdate issue tracker under Issue ID#51

7

While I'm here:
There is a further issue with packing of post-migration RelStorage databases because the process wants to read the contents of old (pre-migration) object states. I document that issue along with a fix (patch) in RelStorage github issue tracker as Issue ID#530.

8

The best migration path I have found is a true export of the database to json (or some non-python representation) and an import back in to the new plone install. The Export/Import process @pbauer wrote made this exceptionally simple. GitHub - plone/plone.exportimport: Plone content export / import support · GitHub

For my custom content types that the stock exportimport addon could not handle, or handled in an odd way, I registered my own serializers, and it worked like a charm.

I'll admit, I fought using this. I thought I could big-brain my way through an in-place migration. I couldn't. Surrender to the easy method.

9

Unfortunately, 200GB database plus ~2TB of blobs. This one is going to happen in-place one way or the other...

10

I wonder if zodbconvert out to FileStorage (and back in after zodbupdate) is feasible?

11

The initial import from ZODB to RelStorage took about a week (literally); there are some 23,000,000 transactions if I recall correctly. I think around 98,000,000 objects in the database. It's a Plone site with associated custom datastore. A day or so of outage is achievable, weeks no. Looks like I've got the zodbupdate going OK. By my reckoning there are at least three showstopper problems with migrating a stock Plone 5.2.6 site on RelStorage to Plone 6 + Python 3, all of which I haven't seen solutions to anywhere. Even with Relstorage removed from the equation, the Products.MimetypesRegistry product in a stock Plone 5.2.6 creates database records that can not be migrated Python 2 -> Python 3.10+ as it stores compiled regex expressions that can't be unpickled in Python 3.10+ as they have global flags at the end of the pattern. This is actually a potential landmine even for people that successfully migrate Python 2 -> Python <3.10 for whenever they eventually update to Python 3.10+.

Overall though, I've brought the process into an achievable state for our client. We'll see how it all goes as it progresses in to the pre-production environment.