- Michael Richardson (mcr@sandelman.ca) [250418 18:55]:
So I tried to switch last week from sqlite3 to postgresql.
I did that migration and cannot recommend it, rather direct start with postgres. Having said this, I wrote in 2021
==<== Stop all external connections to your systems for the whole migration.
Loading the database with any of the tools from postgresql in a fresh database, e.g. pgloader.
Setting up another database and dump both databases.
Merge the data from the pgloader-database and structure (sequences etc) from the new database.
Happy fixing of anything not loading into yet another database ( = the actual database), e.g. broken tables - especially the tables with intervals won't load without manual action. And make sure at least this database has encoding UTF8 (and especially not SQL_ASCII). E.g. by adding -E UTF8 to the createdb call. Or call your createcluster with e.g. --locale de_DE.UTF-8. That's recommended also if you start directly with postgresql as backend BTW :)
And then, just look where you get exceptions and keep on fixing until there are no anymore. And continue to look at your logfiles. Reenable connections sometimes during that and see what great exceptions can happen if e.g. lmtp doesn't recognire an list because there are non-ascii characters somewhere and your locale is broken (see above). ==>==
Good luck.
Andi