Migrating sqlite to postgres

[valankar]

Is there perhaps a good way to migrate from sqlite to postgres? I used sqlite for a bit and it works well, though now I'm wondering if I should migrate. Thanks.

[tezlm]

It might be as simple as sqlite3 /path/to/database.db .dump | psql databasename, since it doesn't look like any fancy datatypes are used. Though if sqlite3 is working fine, you don't really need to migrate since there isn't much data or any psql-specific features (unless you want all data in one place, ie. for backups.)

[valankar]

Yeah I thought I should try that, but I noticed there might be some table differences between sqlite and postgres, e.g.:

https://github.com/mautrix/discord/blob/8d01c30014978e2360d5cb102e96542ecb2402b6/database/upgrades/03-spaces.sql#L33

[tezlm]

It looks like it's so that the same sql schema can be used with both postgres and sqlite while making use of each one's featureset. The data itself should be portable, you should https://tryitands.ee/