Migrate to postgres error

[michaelschefczyk]

Having solved this issue: #8538 I would like to migrate from sqlite to postgres. My homeserver runs based on a freshly built synapse-1.21.2 docker container.

Can someone please be so kind to help me to interpret the error message below? It pops up when running synapse_port_db. When I open the sqlite db with an sqlbrowser, table "events" seems to contain "count_as_unread". However, my knowledge of sqlite is just very basic.

Traceback (most recent call last):
File "/usr/local/bin/synapse_port_db", line 624, in run
consumeErrors=True,
twisted.internet.defer.FirstError: FirstError[#51, [Failure instance: Traceback: <class 'psycopg2.errors.UndefinedColumn'>: FEHLER: Spalte »count_as_unread« von Relation »events« existiert nicht
LINE 1: ...gin_server_ts, received_ts, sender, contains_url, count_as_u...
^

/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:501:errback
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:568:_startRunCallbacks
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:654:_runCallbacks
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:1475:gotResult
--- ---
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:1416:_inlineCallbacks
/usr/local/lib/python3.7/site-packages/twisted/python/failure.py:512:throwExceptionIntoGenerator
/usr/local/bin/synapse_port_db:388:handle_table
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:577:runInteraction
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:647:runWithConnection
/usr/local/lib/python3.7/site-packages/twisted/python/threadpool.py:250:inContext
/usr/local/lib/python3.7/site-packages/twisted/python/threadpool.py:266:
/usr/local/lib/python3.7/site-packages/twisted/python/context.py:122:callWithContext
/usr/local/lib/python3.7/site-packages/twisted/python/context.py:85:callWithContext
/usr/local/lib/python3.7/site-packages/twisted/enterprise/adbapi.py:306:_runWithConnection
/usr/local/lib/python3.7/site-packages/twisted/python/compat.py:464:reraise
/usr/local/lib/python3.7/site-packages/twisted/enterprise/adbapi.py:297:_runWithConnection
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:641:inner_func
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:447:new_transaction
/usr/local/bin/synapse_port_db:376:insert
/usr/local/bin/synapse_port_db:195:insert_many_txn
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:215:executemany
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:238:_do_execute
]]
Traceback (most recent call last):
File "/usr/local/bin/synapse_port_db", line 1104, in
sys.stderr.write(end_error)
TypeError: write() argument must be str, not FirstError

[anoadragon453]

Hey @michaelschefczyk, I'm afraid that this looks like the same failure mode as #8538. Please drop the count_as_unread column from the events table in your SQLite3 DB to fix the migration. This was caused by running code introduced by https://github.com/matrix-org/synapse/pull/7736/files#diff-daf7b678dcac1a954e061d21176d63d07f3d2c78ee6d590b91c21167704854efR18 which was then reverted before release.

TypeError: write() argument must be str, not FirstError

I also see we generate an exception due to incorrect handling of exceptions. I've made a PR to fix that: #8585

[richvdh]

@anoadragon453 can we close this?

[michaelschefczyk]

Thank you very much! This can be closed. The system did start after conversion to postgres. I did not verify all issues with my own postgres setup, but that is outside the scope of my question here.