Categories
blog

Databases: MySQL to PostgreSQL conversion

This isn’t an attempt to write an authoritative or exhaustive guide, but should help you find the way to get (the title) done. More importantly, so next time I have to do it… I’ll not have to google so much for mysql to postgresql conversion.

Here’s the scenario: an old Rails 4/5 app is sitting on a server which doesn’t cost a lot to run, but it’s the only thing left on the server. Ruby and Rails have both become somewhat more efficient over the years. Using my Dokku Rails 6 server will only add undesired RAM requirement.

One proposed solution is to drop the old site onto Heroku. Let their magic beans keep it operational until such a time as the site can be upgraded though more likely migrated and retired.

Challenges:

  • Heroku uses PostgreSQL by default, and MySQL only comes as a premium add-on. You can get a whole 5MB database for free… <sarc/>.
  • Getting old databases from MySQL into PostgreSQL format for migration isn’t straightforward as there is no “export to Postgres” option in Sequel Pro, for example.

Step 1: Set up Postgres locally

Setup on mac is easy using the Homebrew package manager, if you don’t have it installed check out the Homebrew homepage info.

# install postgres, it'll likely
# update Homebrew itself at the same time
brew install postgresql

Have a read of the release notes which may alter the following and will tell you how to run an on-boot server service. The below command will fire up postgres for now.

# then start the database
brew services start postgresql

Handily, the brew installer creates an account in your user’s name with a blank password.

Step 2: Set up a local database

This article elaborates on the entire PSQL setup process, handy if you want to know more.

Login into the psql prompt by typing ‘psql postgres’:

➜  ~ psql postgres
psql (12.4)
Type "help" for help.

postgres=#

Then, to find which users exist and confirm yours has been created as expected:

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 david     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgres  | Superuser, Create role, Create DB                          | {}

Next create a database, though if you need to create a custom user don’t skip the steps in between in the article.

CREATE DATABASE databasename;

Step 3: install the migration tool

PGLoader is a command line tool for doing exactly what we’re looking for. Installation for linux via apt or any platform via Docker is covered in the Github repo. With our homebrew setup it’s easy:

brew install --HEAD pgloader

Step 4: Migrate your database

migration
Migration photo by Barth Bailey on Unsplash

Scaleway (not an affiliate link) a hosting company with some interesting offerings on their platform, have an article on the migration element.

The format is as follows:

pgloader mysql://mysqluser:password@<mysql-server>:<mysql-port>/<source-database> postgresql://<pgsql-role>:password@<pgsql_server>:<postgresql-port>/<target-database>

So if your MySQL data is in your local database here’s a sample:

pgloader mysql://localuser:localpass@localhost:3306/mysqldatabasename postgresql://david:@localhost:5432/psqldbname

Your database is probably on your VPS, firewalled from public port access (at least it should be) so you’ll need to use an SSH tunnel.

Information on how to test your local connection and more can be found here but generally the tunnel command follows this format.

ssh -L tunnelport:thismachine:localmysqlport remoteuser@remoteserver

In practical terms that means executing something like the following means you can access your remote database’s port 3306 using local port 3307.

ssh -L 3307:localhost:3306 deploy@myvps-server.com

Now that we have our tunnel in place we make a very minor adjustment to the pgloader script:

# replace port number 3306 with 3307
pgloader mysql://localuser:localpass@localhost:3307/mysqldatabasename postgresql://david:@localhost:5432/psqldbname
focus photography of gray and black pigeons behind Eiffel Tower
Voila. Photo by Fabrizio Verrecchia on Unsplash

Et voilà, mysql to postgresql conversion complete.

Bonus:

The whole point of this is to get your legacy data into your new Heroku database. The heroku documentation on importing to postgres is the place to start.