Error: cannot insert a non-DEFAULT value into column "connection_key" during migration from SQLite to Postgres

[nbabenko]

Description

Hi,

I have an old version of the synapse server that I installed myself with SQLite database. I have set up a new one with the https://github.com/spantaleev/matrix-docker-ansible-deploy and trying to migrate my old SQLite to Postgres.

Getting error when it tries to insert values in identity column.

Steps to reproduce

1. Install a fresh server with a script https://github.com/spantaleev/matrix-docker-ansible-deploy:
   ansible-playbook -i inventory/hosts setup.yml --tags=install-all
2. Have a SQLite database from existing server and import it to the server.
3. Execute migration script:

docker run --rm -it \
  --name synapse-migration \
  --network matrix-postgres \
  --mount type=bind,src=/matrix/synapse/storage/media-store/homeserver.db,dst=/homeserver.db \
  --mount type=bind,src=/matrix/synapse/config/homeserver.yaml,dst=/homeserver.yaml \
  --mount type=bind,src=/matrix/synapse/config/matrix.example.com.signing.key,dst=/data/matrix.example.com.signing.key \
  python:3.12 bash -c "
    pip install matrix-synapse lxml psycopg2-binary && \
    python3 -m py_compile /usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py && \
    python3 -m synapse._scripts.synapse_port_db \
      --sqlite-database /homeserver.db \
      --postgres-config /homeserver.yaml
  "

5. Receive an error:

********
2024-12-27 06:20:20,269 - synapse_port_db - 428 - INFO - Table user_stats_current: 0/10 (rows 1-0) already ported
2024-12-27 06:20:20,270 - synapse_port_db - 428 - INFO - Table e2e_cross_signing_signatures: 0/37 (rows 1-0) already ported
2024-12-27 06:20:20,271 - synapse_port_db - 428 - INFO - Table room_retention: 0/0 (rows 1-0) already ported
2024-12-27 06:20:20,271 - synapse_port_db - 428 - INFO - Table server_keys_json: 0/9 (rows 1-0) already ported
2024-12-27 06:20:20,272 - synapse_port_db - 428 - INFO - Table refresh_tokens: 0/0 (rows 1-0) already ported
2024-12-27 06:20:20,274 - synapse_port_db - 428 - INFO - Table event_labels: 0/0 (rows 1-0) already ported
2024-12-27 06:20:20,275 - synapse_port_db - 428 - INFO - Table federation_inbound_events_staging: 0/0 (rows 1-0) already ported
2024-12-27 06:20:20,275 - synapse_port_db - 428 - INFO - Table user_daily_visits: 0/734 (rows 1-0) already ported
user_directory_stream_pos: 100% (1/1)
federation_stream_position: 100% (2/2)
server_signature_keys: 100% (8/8)
users: 100% (10/10)
e2e_room_keys_versions: 100% (10/10)
open_id_tokens: 100% (22/22)
timeline_gaps: 100% (26/26)
user_signature_stream: 100% (11/11)
device_lists_remote_cache: 100% (31/31)
stream_positions: 100% (12/12)
pushers: 100% (8/8)
room_stats_current: 100% (55/55)
e2e_fallback_keys_json: 100% (35/35)
event_push_actions: 100% (69/69)
user_filters: 100% (18/18)
remote_media_cache_thumbnails: 100% (131/131)
event_search: 100% (302/302)
room_tags: 100% (2/2)
device_lists_outbound_last_success: 100% (13/13)
state_group_edges: 100% (859/859)
current_state_delta_stream: 100% (844/844)
event_auth_chains: 100% (906/906)
state_groups_state: 74% (1000/1346)
device_lists_changes_converted_stream_position: 100% (1/1)
e2e_device_keys_json: 100% (35/35)
2024-12-27 06:20:21,554 - synapse_port_db - 283 - ERROR - Failed to insert: sliding_sync_connections
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py", line 281, in insert_many_txn
    txn.executemany(sql, rows)
  File "/usr/local/lib/python3.12/site-packages/synapse/storage/database.py", line 443, in executemany
    self._do_execute(self.txn.executemany, sql, *args)
  File "/usr/local/lib/python3.12/site-packages/synapse/storage/database.py", line 488, in _do_execute
    return func(sql, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.GeneratedAlways: cannot insert a non-DEFAULT value into column "connection_key"
DETAIL:  Column "connection_key" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

2024-12-27 06:20:21,562 - synapse_port_db - 931 - ERROR - 
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py", line 908, in run
    await make_deferred_yieldable(
  File "/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py", line 1183, in __iter__
    yield self
twisted.internet.defer.FirstError: FirstError[#47, [Failure instance: Traceback: <class 'psycopg2.errors.GeneratedAlways'>: cannot insert a non-DEFAULT value into column "connection_key"
DETAIL:  Column "connection_key" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:532:handle_table
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:952:runInteraction
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:918:_runInteraction
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1047:runWithConnection
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:269:inContext
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:285:<lambda>
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:117:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:82:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/enterprise/adbapi.py:282:_runWithConnection
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1040:inner_func
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:780:new_transaction
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:520:insert
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:281:insert_many_txn
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:443:executemany
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:488:_do_execute
]]
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py", line 908, in run
    await make_deferred_yieldable(
  File "/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py", line 1183, in __iter__
    yield self
twisted.internet.defer.FirstError: FirstError[#47, [Failure instance: Traceback: <class 'psycopg2.errors.GeneratedAlways'>: cannot insert a non-DEFAULT value into column "connection_key"
DETAIL:  Column "connection_key" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:532:handle_table
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:952:runInteraction
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:918:_runInteraction
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1047:runWithConnection
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:269:inContext
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:285:<lambda>
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:117:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:82:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/enterprise/adbapi.py:282:_runWithConnection
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1040:inner_func
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:780:new_transaction
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:520:insert
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:281:insert_many_txn
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:443:executemany
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:488:_do_execute
]]
FirstError[#47, [Failure instance: Traceback: <class 'psycopg2.errors.GeneratedAlways'>: cannot insert a non-DEFAULT value into column "connection_key"
DETAIL:  Column "connection_key" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:532:handle_table
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:952:runInteraction
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:918:_runInteraction
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1047:runWithConnection
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:269:inContext
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:285:<lambda>
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:117:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:82:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/enterprise/adbapi.py:282:_runWithConnection
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1040:inner_func
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:780:new_transaction
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:520:insert
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:281:insert_many_txn
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:443:executemany
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:488:_do_execute
]]root@localhost:/matrix/synapse/config# 

Workaround attempts:

---

I also tried to adjust the migration script:

docker run --rm -it \
  --name synapse-migration \
  --network matrix-postgres \
  --mount type=bind,src=/matrix-restore/homeserver.db,dst=/homeserver.db \
  --mount type=bind,src=/matrix/synapse/config/homeserver.yaml,dst=/homeserver.yaml \
  --mount type=bind,src=/matrix/synapse/config/matrix.example.com.signing.key,dst=/data/matrix.example.com.signing.key \
  python:3.12 bash -c "
    pip install matrix-synapse lxml psycopg2-binary && \
    sed -i \"/def insert_many_txn/,/raise/ {
      s/INSERT INTO %s (%s) VALUES (%s)/INSERT INTO %s (%s) OVERRIDING SYSTEM VALUE VALUES (%s)/g
    }\" /usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py && \
    python3 -m py_compile /usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py && \
    python3 -m synapse._scripts.synapse_port_db \
      --sqlite-database /homeserver.db \
      --postgres-config /homeserver.yaml
  "

The migration itself went well but then my synapse wont start with it and I've got a database with an issue:

journalctl -xeu matrix-synapse.service
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/pusher.py", line 82, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/roommember.py", line 99, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/pusher.py", line 578, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/receipts.py", line 149, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/stream.py", line 595, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/receipts.py", line 1164, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/client_ips.py", line 414, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/client_ips.py", line 84, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/deviceinbox.py", line 92, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          self._to_device_msg_id_gen: MultiWriterIdGenerator = MultiWriterIdGenerator(
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:                                                               ^^^^^^^^^^^^^^^^^^^^^^^
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/util/id_generators.py", line 295, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          self._sequence_gen.check_consistency(
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/util/sequence.py", line 192, in check_consistency
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          raise IncorrectDatabaseSetup(
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      synapse.storage.engines._base.IncorrectDatabaseSetup:
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      Postgres sequence 'device_inbox_sequence' is inconsistent with associated stream position
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      of 'to_device' in the 'stream_positions' table.
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      This is likely a programming error and should be reported at
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      https://github.com/matrix-org/synapse.
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      A temporary workaround to fix this error is to shut down Synapse (including
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      any and all workers) and run the following SQL:
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          DELETE FROM stream_positions WHERE stream_name = 'to_device';
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      This will need to be done every time the server is restarted.
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  There may be more information in the logs.
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]: **********************************************************************************
Dec 26 16:23:11 matrix-example-com systemd[1]: matrix-synapse.service: Main process exited, code=exited, status=1/FAILURE
░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░ 
░░ An ExecStart= process belonging to unit matrix-synapse.service has exited.
░░ 
░░ The process' exit code is 'exited' and its exit status is 1.
Dec 26 16:23:13 matrix-example-com systemd[1]: matrix-synapse.service: Failed with result 'exit-code'.
░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░ 
░░ The unit matrix-synapse.service has entered the 'failed' state with result 'exit-code'.
Dec 26 16:23:13 matrix-example-com systemd[1]: Failed to start matrix-synapse.service - Synapse server.
░░ Subject: A start job for unit matrix-synapse.service has failed
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░ 
░░ A start job for unit matrix-synapse.service has finished with a failure.
░░ 
░░ The job identifier is 55830 and the job result is failed.
lines 1086-1147/1147 (END)

Homeserver

personal homeserver

Synapse Version

1.121.1

Installation Method

Default configuration using https://github.com/spantaleev/matrix-docker-ansible-deploy

Database

PostgreSQL

Workers

I don't know

Platform

Ubuntu 24.04

Configuration

Standard configuration set up by the default setup of https://github.com/spantaleev/matrix-docker-ansible-deploy

Relevant log output

• see above

Anything else that would be useful to know?

No response