Migrating from MySQL to Postgres

I am having some trouble on the RPI 4 to get Rails installed and finding the right version of MYSQL (MariaDB) and ActiveRecord while running an older version of Rails and a newer version of Sphinx (search index). Anyway I always wanted to move to PostgreSQL...thats the time to do.

The tool of my choise: PGLoader

I realized pgloader does not like to run on the RPI, but the solution is easy:

SourceDB (Cubietrack) <-> Linux PC (my desktop) = Running PG-Loader <-> TargetDB (RPI4)

I wanted to have the schema on the target-db, as original as possible for Rails, so I used rake:

rake db:create
rake db:schema:load

to have an original Rails DB set-up. PGLoader is used for data-migration.

Script:

LOAD DATABASE
FROM mysql://export:xxx@ct/CTCD2Server_production
INTO pgsql://docbox:xxx@pi:5432/docbox_production
with data only
ALTER SCHEMA 'CTCD2Server_production' RENAME TO 'public';


Two important points:

1. The last statement "alter schema" is important, as pgloader creates a schema with the same name as the source db and creates all table in this schema - and rails may not expect this.
2. "with data only" - tells pgloader that the schema is already existing (rake db:schema:load)

Once, I got the script working - data migration is just a "button press".