Migrating Ghost Blog from PostgreSQL to MySQL

I have been running my site and blog on Heroku with free PostgreSQL database. Last year, Ghost team announced that they would drop the support of this great database engine and gave justified reasons why they're doing so. To avoid all sorts of weird issues in the future, I migrated my blog to the MySQL which Ghost officially supports.

Getting everything ready for the data transfer

I started the migration process by taking backups of the database and environment variables that define database connection (host, username, and password) so that I can go back to old database host if things go wrong.

I then exported the data using export functionality found in the Labs section (the path is /ghost/settings/labs/).

Now that I have everything backed up and export data available, I can create a new database.

ClearDB MySQL

I am using Heroku, so provisioning new database instance is a breeze.

In the context of Heroku, these 3rd party services are called Heroku Add-Ons. Once you have added a service, you can find new environment variables in the app config. ClearDB provisioning documentation is easy to read.

The idea is to change the connection configs to point to the MySQL database instead of PostgreSQL.

The switch from PostgreSQL to MySQL

I have defined my environment variables such as BLOG_DATABASE_URL, BLOG_DATABASE_USERNAME and BLOG_DATABASE_PASSWORD that I needed to change.

The Ghost blogging platform would then run its scripts to create database tables according to the schema, and I would then run Import (also found from the Labs section).

There is one thing I had completely forgotten to verify: what data is being exported.

Ghost has an Import and Export section in their documentation but is lacking some information. It doesn't mention that all the blog's basic information such as name, description, header images, etc. are not transferred.

Also, if you have header or footer scripts defined, those are not part of the process either.

I sent a request to their support to add that information to their documentation.

My suggestion is to take a backup (copy-paste) of those fields also.

Conclusion

The whole operation went smoothly and I can now sleep peacefully knowing that I am using supported database engine.

You can get a 5-megabyte ClearDB MySQL database for free. Heroku's PostgresSQL had a little bit bigger database in their free tier, but I can get pretty far with 5-megabytes as the current database size is about 3 megs.