Migrating from MySQL to PostgreSQL in Laravel
A retelling of my experience migrating from MySQL to PostgreSQL in Laravel and all the gotchas I ran into.

Overview

Our strategy to complete the migration was as follows:

  • Step 1. Add support for both MySQL and Postgres to the codebase. Then develop and test using Postgres in our local environment until stable, while production and build pipeline continue to operate on MySQL
  • Step 2. Cut staging over to Postgres to test our migration checklist
  • Step 3. Cut production over to Postgres, with dry-runs first to ensure everything will go smoothly on cut-over day
  • Step 4. Clean up and remove dual support

Step 1. Supporting both MySQL and Postgres

To do this, we started by squashing all of our migrations so we could convert the schema using pgloader and have both a MySQL and Postgres version of the schema. We did this in a long-lived feature branch that we kept integrating main back into while we got everything working locally. We did this by having a config value that could be set to either mysql or postgres and used throughout our code to have MySQL vs Postgres versions of queries where we weren't using Eloquent.

Then we got to work by going through the entire application and seeing what functionality worked and what didn't... Here are some things you'll need to watch out for...

Schema conversion Gotchas

When we converted the schema with pgloader, there were a few tweaks we needed to make to the resulting Postgres schema file. These could also have been addressed with changes to the pgloader config, but updating the schema file directly was simple enough:

  • JSON columns were converted to Postgres JSON; we wanted JSONB for its larger feature set.
  • Timestamps needed to be updated from timestampz to timestamp(0) to match our existing format.
  • We utilized the UUID type for some columns.
  • Enums will be converted to Postgres enum types. This is inconsistent with what Laravel migrations will create, which is a varchar with a check constraint. You may want to consider how you will treat enums in general (use custom enum types, use check constraints, leave validation to Laravel at the model level) and use a consistent approach.

Postgres handles updating Pivots differently than MySQL

Postgres handles updating pivot relations differently - the existing code attempts to update last_read_at on the users table, rather than the participants table (note participants is a named pivot table instead of thread_user)


// Existing Code
$thread->users()->where('user_id', $user_a->id)->update([
    'last_read_at' => now()->subMinutes(5),
]);

// Revised Code
$thread->users()
    ->updateExistingPivot($user_a->id, ['last_read_at' => now()->subMinutes(5)]);

String comparison is case sensitive in Postgres

select 'test' = 'Test' is true in MySQL, but false in Postgres due to case sensitivity. We had to update a bunch of our code to account for this.

LIKE statements are case sensitive in Postgres

Similar to string comparisons, select 'TEST' like '%est' is true in MySQL, but false in Postgres. Using ILIKE gives case-insensitive matching (i.e., select 'TEST' ilike '%est').

JSON functions

Many JSON functions are different in Postgres than in MySQL. If you are utilizing any of these, this is an area to check carefully.

NULL ordering

This was not an issue for us, but MySQL orders NULL values as lower than any other value by default. PostgreSQL orders NULL values as higher by default.

Full Text Search requires you to get specific

You'll need to think through each place you're using full-text search and set up appropriate indices. This is where watching Aaron's course came in handy https://masteringpostgres.com/watch/introduction-to-full-text-search

Step 2. Cut over Staging to Postgres

The procedure for doing the cut over was to:

  • Have a clean Postgres Database ready to go
  • Have pgloader scripts prepared to run in EC2 to migrate the data from MySQL to the Postgres database
  • Put site into maintenance mode
  • Run the pgloader script
  • Deploy where our configs change staging environment to use Postgres instead of MySQL re: dual-support
Our pgloader script we used for the staging migration... 

LOAD DATABASE
FROM mysql://vapor:{{SOURCE_PASSWORD}}@redacted-host-name.us-east-2.rds.amazonaws.com/vapor
INTO postgresql://vapor:{{TARGET_PASSWORD}}@redacted-host-name.us-east-2.rds.amazonaws.com/vapor
ALTER SCHEMA 'vapor' RENAME TO 'public'

CAST type timestamp to "timestamp(0)",
type json to jsonb,
column activity_log.batch_uuid to uuid drop typemod,
column content_classifications.uuid to uuid drop typemod,
column telescope_entries.uuid to uuid drop typemod,
column telescope_entries.batch_id to uuid drop typemod,
column telescope_entries_tags.entry_uuid to uuid drop typemod

SET search_path TO 'public';

Step 3. Cut over Production

While preparing for the production cut over, we did several dry runs to ensure the data could be migrated quickly as our database was approximately 16GB. We ended up making the following changes to our pgloader script:


LOAD DATABASE
FROM mysql://migration:{{SOURCE_PASSWORD}}@redacted-host-name.us-east-2.rds.amazonaws.com/vapor
INTO postgresql://vapor:{{TARGET_PASSWORD}}@redacted-host-name.us-east-2.rds.amazonaws.com/vapor
ALTER SCHEMA 'vapor' RENAME TO 'public'

WITH batch rows = 500, batch size = 10MB, prefetch rows = 500, workers = 4, concurrency = 1, multiple readers per thread, rows per range = 500

CAST type timestamp to "timestamp(0)",
type json to jsonb,
column activity_log.batch_uuid to uuid drop typemod,
column content_classifications.uuid to uuid drop typemod,
column telescope_entries.uuid to uuid drop typemod,
column telescope_entries.batch_id to uuid drop typemod,
column telescope_entries_tags.entry_uuid to uuid drop typemod

SET search_path TO 'public';

The production migration went off without a hitch when we did it because we tested the full migration of the data from MySQL to Postgres database multiple times in a dry run to know how long it would take and that it would work successfully. When we eventually did the production migration, we scheduled some downtime and were able to do everything with about 45 minutes of downtime.

Step 4. Clean Up

We then started backing out all of the code we wrote to support both MySQL and Postgres. We did this quickly after the production cut over as we were confident that the application would function in Postgres, as we had been developing locally for nearly a month at this point using Postgres. We did notice during the migration that pgloader told us that some index names would be truncated. So as part of our clean-up activity, we did some work to ensure our pgsql-schema.sql would align with the values we had in production so that there wasn't any difference between local and prod. 


    2024-12-17T15:01:01.796030Z WARNING PostgreSQL warning: identifier "idx_659009_content_classifications_expected_classifiable_type_index" will be truncated to "idx_659009_content_classifications_expected_classifiable_type_i"
    2024-12-17T15:01:01.820030Z WARNING PostgreSQL warning: identifier "idx_659009_content_classifications_classifiable_type_classifiable_id_index" will be truncated to "idx_659009_content_classifications_classifiable_type_classifiab"
    2024-12-17T15:01:05.832039Z WARNING PostgreSQL warning: identifier "idx_659017_data_shield_alert_configs_notification_frequency_index" will be truncated to "idx_659017_data_shield_alert_configs_notification_frequency_ind"
    2024-12-17T15:01:05.908039Z WARNING PostgreSQL warning: identifier "idx_659017_data_shield_alert_configs_email_organization_id_unique" will be truncated to "idx_659017_data_shield_alert_configs_email_organization_id_uniq"
    2024-12-17T15:01:06.068040Z WARNING PostgreSQL warning: identifier "idx_659274_personal_access_tokens_tokenable_type_tokenable_id_index" will be truncated to "idx_659274_personal_access_tokens_tokenable_type_tokenable_id_i"
    2024-12-17T15:01:06.236041Z WARNING PostgreSQL warning: identifier "idx_659225_nova_notifications_notifiable_type_notifiable_id_index" will be truncated to "idx_659225_nova_notifications_notifiable_type_notifiable_id_ind"
    2024-12-17T15:03:21.076344Z WARNING PostgreSQL warning: identifier "idx_659078_feedback_user_id_feedbackable_id_feedbackable_type_unique" will be truncated to "idx_659078_feedback_user_id_feedbackable_id_feedbackable_type_u"
    2024-12-17T15:03:21.156344Z WARNING PostgreSQL warning: identifier "idx_659022_data_shield_content_configs_organization_id_pii_type_unique" will be truncated to "idx_659022_data_shield_content_configs_organization_id_pii_type"
    2024-12-17T15:03:24.472350Z WARNING PostgreSQL warning: identifier "idx_659321_roles_scopeable_type_scopeable_id_user_id_role_type_unique" will be truncated to "idx_659321_roles_scopeable_type_scopeable_id_user_id_role_type_"
    2024-12-17T15:03:24.532350Z WARNING PostgreSQL warning: identifier "idx_658949_acceptable_use_policy_documents_organization_id_foreign" will be truncated to "idx_658949_acceptable_use_policy_documents_organization_id_fore"
    2024-12-17T15:03:24.836351Z WARNING PostgreSQL warning: identifier "idx_659219_nova_field_attachments_attachable_type_attachable_id_index" will be truncated to "idx_659219_nova_field_attachments_attachable_type_attachable_id"

Final Commentary

As with any large change you're making to a software system, you want to make the change easy, then make the change. We were meticulous in our notes as we worked through changes, and by having a way to run both MySQL and Postgres in parallel, we could continue active development on the system while we chipped away at all the little details.

If you're going to tackle this course, I would recommend developing a strong understanding of Postgres while you're doing the work, i.e., buying and watching https://masteringpostgres.com/

Note: Aaron didn't pay me to say this, just a fan of the great content. Aaron, if you see this, perhaps you could consider adding a module for migrating from MySQL to Postgres one day. I bet a TON of people would appreciate it.

Thank you to my colleague Gareth Davis for reading drafts of this article and for the heavy lifting he did behind the scenes to actually complete this migration.