On Apr 28, 2020, at 6:49 AM, Stephen J. Turnbull <turnbull.stephen.fw@u.tsukuba.ac.jp> wrote:
Mark Dadgar writes:
I’ve seen mentioned that it’s not as simple as dumping the sqlite3 database and restoring it to, say, Postgres because some of the data types are different between them (which seems like an odd design choice).
It's not really a design "choice". It's implied by using a object-relational manager (ORM, in the case of Mailman SQLAlchemy) for the database.
As for helping with the problem, I'm not a real SQL expert, but I think it's probably possible to come really close by dumping the database and restoring it to PostgreSQL.
Also, here's an interesting project:
https://pgloader.readthedocs.io/en/latest/
It's just the first open source one I looked at after searching for "migrate sqlite3 to postgres". I have no recommendation, but I'll note that this particular software does log errors of various kinds, so it's not just a quick hack -- they've thought about some of these issues. There may be better ones, or you may want to go with a commercial product.
I finally found some time to try migrating my sqlite3 dbs to postgres using pgload (which is a pretty cool tool, frankly).
I get a bunch of these errors because identifiers in postgres are limited to 63 characters and mm3 creates some in sqlite that are longer than that:
2020-05-20T02:16:20.574000Z WARNING PostgreSQL warning: identifier "idx_44731_auth_user_user_permissions_user_id_permission_id_14a6b632_uniq" will be truncated to "idx_44731_auth_user_user_permissions_user_id_permission_id_14a6" 2020-05-20T02:16:20.583000Z WARNING PostgreSQL warning: identifier "idx_44719_auth_group_permissions_group_id_permission_id_0cd325b0_uniq" will be truncated to "idx_44719_auth_group_permissions_group_id_permission_id_0cd325b" 2020-05-20T02:16:20.644000Z WARNING PostgreSQL warning: identifier "idx_44821_socialaccount_socialtoken_app_id_account_id_fca4e0ac_uniq" will be truncated to "idx_44821_socialaccount_socialtoken_app_id_account_id_fca4e0ac_" 2020-05-20T02:16:20.662000Z WARNING PostgreSQL warning: identifier "idx_44839_socialaccount_socialaccount_provider_uid_fc810c6e_uniq" will be truncated to "idx_44839_socialaccount_socialaccount_provider_uid_fc810c6e_uni" 2020-05-20T02:16:20.711000Z WARNING PostgreSQL warning: identifier "idx_44764_auth_permission_content_type_id_codename_01ab375a_uniq" will be truncated to "idx_44764_auth_permission_content_type_id_codename_01ab375a_uni"
etc.
I killed the job before it finished and have not actually tried running against this database, but my question is this:
How does mailman3 handle this limitation with postgres if I set up an instance from scratch using postgres? Does it just truncate the identifiers identically, in which case migrating via pgloader should work?
For reference my pgloader script looks like:
load database from sqlite:///var/lib/dbconfig-common/sqlite3/mailman3-web/mailman3web.db into postgresql://mailman3:XXREDACTEDXX@localhost/mailman
with include drop, create tables, create indexes, reset sequences, no truncate
Thanks!
- Mark
mark@pdc-racing.net | 408-348-2878