Migration from SQLite to Postgres

[matrixbot]

This issue was originally created by @Semisol at https://github.com/matrix-org/dendrite/issues/2276.

Description: A script to migrate data from SQLite to Postgres

This would, to the extent that is possible, attempt to copy data from SQLite to Postgres so that the database engine can be switched.

[matrixbot]

This comment was originally posted by @neilalexander at https://github.com/matrix-org/dendrite/issues/2276#issuecomment-1077788495.

This requires a bit of thought, because the database schemas across the two engines aren't exactly the same. We have different value formats for anything array-based and we have no sequences in SQLite so have had to workaround in other ways.

[matrixbot]

This comment was originally posted by @DanielHons at https://github.com/matrix-org/dendrite/issues/2276#issuecomment-1094126547.

I think dendrite would not need to take care for this. pgloader should do this well.

[matrixbot]

This comment was originally posted by @kegsay at https://github.com/matrix-org/dendrite/issues/2276#issuecomment-1339373276.

We don't have any plans to support this currently. I appreciate that kinda sucks, but unfortunately we don't have the bandwidth to develop and maintain a migration system. The tables/columns are subtly different, such that it isn't possible to automatically map from one format to the other.

[matrixbot]

This comment was originally posted by @phtmgt at https://github.com/matrix-org/dendrite/issues/2276#issuecomment-1529532958.

You should check out this one:

https://github.com/spantaleev/matrix-docker-ansible-deploy/blob/master/docs/importing-synapse-sqlite.md