servd

If you've ever tried migrating a Postgres database over to MySQL, you'll have likely walked away with at least a few bad memories, if you were successful at all!

The process is notoriously difficult and, although a few tools exist which attempt to automate the process, we've found that they rarely work without significant manual intervention.

In this post we'll run through a migration strategy that we've been using for Servd projects over the last few months. It has worked so well that we'd like to share it with the world to hopefully save you from some of the potential pain.

Why Migrate In the First Place?

That is a good question, and there's several potential reasons:

1. Moving hosts for an existing project

If a host has let you down, or you're just consolidating projects onto a single platform, you might need to migrate an existing project from one that supports postgres to one that doesn't. This would be the case when moving projects over to Servd where we only support MySQL (feel free to ask us why).

In this situation you have no choice but to try to convert the database in order to migrate any existing data over to the new platform.

2. Performance

What a contentious thing to say! That MySQL might be more performant than Postgres! Blasphemy!

Well sometimes it is, and we've tested it ourselves. Specifically Postgres tends to be slower when running count() queries. And Craft executes a lot of count() queries.

If you're migrating purely for performance reasons, then the chances are it won't be worth the effort because the differences are marginal. But you might have a situation where your specific database schema or indexes are performing poorly in Postgres where they might not in MySQL, and there'd be no harm in giving it a try.

3. Ease of use / Ecosystem / Familiarity

MySQL is much more popular as the storage medium for websites, so it's likely you're more familiar with it! As a consequence of its popularity it also has a very robust ecosystem around it, including blog posts and tutorials. Similarly, there tends to be more arbitrary tooling and scripts available for MySQL, from automating backups to support for horizontal scaling.

Sometimes it's ok to choose the technology which simply makes your life easier!

Manual Migration Pitfalls

If you attempt a Postgres to MySQL migration manually you'll run into a few issues which can be lumped into two categories:

• Incompatible table schema definitions
• Incompatible dump file syntax

The table schema definitions are usually the most difficult to overcome. It occurs because Postgres uses different column 'types' along with slightly different methods of defining the parameters on those types in its CREATE TABLE statements. As a simple example the column type 'FLOAT' in MySQL uses the equivalent 'REAL' in Postgres, but there are significantly more complex examples to watch out for too.

Manually updating your CREATE TABLE statements using a script is possible, and there are some that exist in the wild, but our experience (backed up by some of our clients) is that they rarely work without significant manual intervention anyway and often don't convert the tables definitions to exactly what Craft is expecting. That can cause problems immediately or, even worse, later down the line when Craft tries to run some database migrations!

The dump file syntax issues are usually a little easier to work around and can be solved with some specific flags used when creating the data dump along with some carefully crafted find and replace statements to fix remaining incompatibilities. Finding these exact replace statements is the challenge.

Our Postgres -> MySQL Conversion Process

We spent a bit of time thinking about this process to help make migrations from other hosts over to Servd as painless as possible, and we think we've come up with the simplest workflow for getting it done.

Let's start with the table schema definitions. We've established that converting a Postgres dump directly is very difficult, so it'd be great if there were another way to generate our database tables in MySQL for us. It turns out that Craft already has this ability baked in, and it's called Project Config.

You'll likely have used Project Config to sync changes made in local dev up to your production environment. When this process occurs Craft is automating all of the changes needed to the database so that you don't have to apply them manually. It also has the ability to use the Project Config files to completely rebuild the database from scratch. This means that the PC files applied to a postgres database will result in a compatible set of database tables when applied to MySQL. So we can get Craft itself to generate all of our database tables for us!

Achieving this is easy. Simply:

1. Make sure your codebase's project config files are up to date and match the configuration applied to production
2. Set up your codebase within your new MySQL infrastructure
3. Run the Craft install process

That's it, your MySQL database now contains a compatible set of tables that we can use. No conversion from Postgres needed!

Next, we need to move the data over from Postgres to MySQL. This takes a few more steps, but is still pretty straight forward.

First we need to pull a data-only dump from Postgres using a specific set of flags to ensure the data is as close as possible to MySQL's syntax and also exclude a few database tables which can cause problems.

Here's our go-to:

pg_dump --verbose --no-acl --inserts --no-tablespaces --quote-all-identifiers --no-owner -a -F p --column-inserts --exclude-table-data=*_seq --exclude-table-data=searchindex -f ./inserts.sql -h localhost -U project -d project

You'll need to update the -h -U and -d values to specify your host, user and database name as required.

You'll end up with a file called inserts.sql which contains all of the data from your Postgres DB as a series of INSERT statements.

We need to make a few adjustments to this file to allow it to be piped directly into MySQL:

# Remove the 'public' schema assertions
sed -i 's/"public"\.//g' inserts.sql

# Replace double quotes around column names with backticks
perl -pi.bak -e 's/"(?=.*\) VALUES \()/`/g' inserts.sql

# Escape any backslashes - a weird thing that MySQL needs
perl -pi.bak -e 's/\\/\\\\/g' inserts.sql

Once these three commands have been executed your inserts.sql file is almost compatible with MySQL! The last edit required is a manual one. Open up the dump file in an editor that won't crash due to its size and remove all of the SET commands at the top of the file. You should be left with the first command being an INSERT.

There's just one more step to perform before running the import: our MySQL database already contains a lot of data which was added during the Craft install process. We need to TRUNCATE all of our database tables to remove that and reset any primary key counters. There's no single command that will apply that to all database tables in one go, so you can either run it manually for each table in the database, or use a GUI tool to help select all of the tables before TRUNCATEing them.

You're now ready to run the inserts.sql file against the MySQL database. You can use mysql on the command line:

mysql -u myUser -p -h hostname databasename < inserts.sql

Or you can use a GUI tool to run the import. Either should work fine however it can be useful to use a GUI client which allows you to disable foreign key checks during the import process as these can sometimes cause errors if they're left enabled.

With your data now imported your Craft install running against MySQL should be up and running and in the same state as your Postgres version.

Finishing Up

You might have noticed that when we took the data dump from Postgres we excluded the searchindex table. This is because Craft uses a different structure for that table depending on the database in use, so the data doesn't map directly from one to the other. Thankfully, all of that data can be regenerated using a Craft console command though:

./craft resave/entries --update-search-index

You might also need to run this for any other object types that you have search indexes active on. Check out the CLI docs for the resave command to figure out which you might need to run: https://craftcms.com/docs/4.x/...

That's it! You've converted your Postgres Craft CMS project over to MySQL with as little pain as possible. Let us know if you spot any potential improvements for this process, we're using it quite regularly so any shortcuts are always welcome!