- Mark Dadgar (mark@pdc-racing.net) [210117 20:27]:
On Jan 17, 2021, at 11:01 AM, Andreas Barth <aba@ayous.org> wrote:
Did you document it anywhere? I could not find evidence of a successful migration when I looked 6 months ago, including in this list’s archives.
That would be great information for the community, if only as a cautionary tale.
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).
Or just avoid all of that and start directly with postgresql.
Regards, Andi