synapse_port_db: foreign key violation on table refresh_tokens (possible regression?)

[clorteau]

Description

Hello,

Trying to port from sqlite to postgre fails with error: twisted.internet.defer.FirstError: FirstError[#41, [Failure instance: Traceback: <class 'psycopg2.errors.ForeignKeyViolation'>: insert or update on table "refresh_tokens" violates foreign key constraint "refresh_tokens_next_token_id_fkey" DETAIL: Key (next_token_id)=(70) is not present in table "refresh_tokens".

All I could find was issue #8652, fixed by #8730. Note that attempting a second time, the target DB is bigger at the end of the 2nd attempt than at the end of the 1st, showing the 2nd attempt was able to go further.

Steps to reproduce

• port from sqlite to a new postgresql database: sudo synapse_port_db --sqlite-database /var/lib/matrix-synapse/homeserver.db --postgres-config /etc/matrix-synapse/homeserver.yaml --curses
• process fails after some time with the error mentioned

Homeserver

matrix.lorteau.fr

Synapse Version

1.141.0

Installation Method

Debian packages from packages.matrix.org

Database

Trying to migrate from sqlite to a single postgresql server on a different host. I don't know the version of the db. I haven't attempted such port before. No backup was restored (but see note in platform below)

Workers

Single process

Platform

Synapse was installed using the packages.matrix.org repository and runs on Ubuntu 24.04.3 LTS, inside a VM dedicated to it. As part of moving away from testing mode the VM was moved to a different host. It is possible that during the life of the VM at some point a snapshot was restored (hence my note on backups). I honestly can't recall. I have been in "testing" mode a while and my .db file reached 15GB. This is a very much a little home lab setup.

Configuration

Vanilla install + mautrix-discord + mautrix-gmessages + mautrix-whatsapp.

Relevant log output

synapse_port_db.log

Anything else that would be useful to know?

Can't think of any.