Migrating a Rails Application from MySQL to PostgreSQL with pgloader

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:
<br />
rake db:create<br />
rake db:schema:load<br />

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

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


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”.