synapse_port_db fails to port sqlite database

[Sorixelle]

Description

When running synapse_port_db to port my SQLite database to Postgres, the port fails with the following error:

2020-10-25 11:36:25,120 - synapse_port_db - 197 - ERROR - Failed to insert: destination_rooms
Traceback (most recent call last):
  File "/usr/bin/synapse_port_db", line 195, in insert_many_txn
    txn.executemany(sql, rows)
  File "/opt/venvs/matrix-synapse/lib/python3.8/site-packages/synapse/storage/database.py", line 215, in executemany
    self._do_execute(self.txn.executemany, sql, *args)
  File "/opt/venvs/matrix-synapse/lib/python3.8/site-packages/synapse/storage/database.py", line 238, in _do_execute
    return func(sql, *args)
psycopg2.errors.ForeignKeyViolation: insert or update on table "destination_rooms" violates foreign key constraint "destination_rooms_destination_fkey"
DETAIL:  Key (destination)=(dodoid.com) is not present in table "destinations".

After some discussion in #synapse:matrix.org, it appears that this may have been caused by an oversight in #8230.

Steps to reproduce

• Using a homeserver running on an SQLite database, attempt to port the database to Postgres with synapse_port_db
• After some time, the port will fail with an error similar to the above.

Version information

• Homeserver: icodeinht.ml

If not matrix.org:

• Version: 1.21.2

• Install method: apt

• Platform: Ubuntu 20.04

[clokep]

I thought we had an easy-ish way to say that a table depends on another table in the synapse_port_db script, but we do not seem to. 😢 I think we can either:

1. Try to build something that says table A depends on table B and use that graph when porting.
2. Have a list of tables which depend on "other" tables and essentially port the depend-less tables first, then the depends ones.

2 sounds a bit simpler and should just be repeating steps 3 & 4 of the port twice each.