Migrating a Laravel App From MySQL to PostgreSQL: A Practical Guide

Let's start with the honest part: most Laravel apps do not need to switch databases. MySQL powers an enormous share of production Laravel deployments, it's fast, it's well understood, and Eloquent abstracts away most of the differences anyway. If your app is healthy on MySQL and your team knows it well, the highest-value move is usually to stay put and invest in better indexes and query hygiene instead.

But some apps do outgrow MySQL in specific, identifiable ways. Heavy JSON workloads that want real indexing. Migration files that can't be rolled back atomically because MySQL commits DDL implicitly. Deadlocks from gap locking that nobody can quite explain. Reporting queries that would be two lines with a partial index or a proper string_agg. If you've hit two or three of those, PostgreSQL starts earning its migration cost.

This post is a runbook, not advocacy. We've helped teams move Laravel apps between engines on Deploynix, and the pattern is consistent: the data transfer is the easy part, and the dialect differences hiding in your codebase are the hard part. We'll cover how to decide, how to audit, how to convert the schema with pgloader, how to fix the Laravel layer, and how to cut over with a rollback path you'd actually trust at 2 a.m.

Key Takeaways

- Switch for concrete wins (transactional DDL, JSONB, partial indexes), not because Postgres is fashionable - Run your test suite against real PostgreSQL in CI weeks before any data moves - pgloader handles ~90% of schema conversion; enums, fulltext, and spatial need hand-work - Verify with row counts, checksums, and sequence resets before flipping .env - Keep MySQL warm as a rollback target for at least a week

Should You Switch at All?

This decision deserves more scrutiny than it usually gets. A database migration touches every query your app runs, so the reasons need to be specific to your workload, not aspirational. Here are the reasons that actually justify the work, in our experience.

Transactional DDL. PostgreSQL wraps schema changes in transactions. If a Laravel migration fails halfway through on MySQL, you're left with a partially applied schema and a migrations table that disagrees with reality. On Postgres, the whole migration rolls back cleanly. For teams that deploy schema changes frequently, this alone removes a whole category of production incidents.

JSONB with GIN indexes. MySQL's JSON type works, but querying inside it at scale is painful. Postgres jsonb stores a parsed binary representation and supports GIN indexes, so WHERE payload @> '{"status": "failed"}' can use an index instead of scanning the table. If you have JSON columns in WHERE clauses today, this is probably your biggest single win.

Partial and expression indexes. CREATE INDEX ... WHERE deleted_at IS NULL indexes only the rows you query. An index on lower(email) gives you case-insensitive uniqueness without hacks. MySQL has functional indexes now, but Postgres's implementation is more mature and more flexible.

Richer types and stricter conformance. Native arrays, ranges, inet, real booleans, and a query planner that follows the SQL standard closely. Postgres rejects garbage like zero-dates instead of storing it, which surfaces bugs earlier.

No gap-lock surprises. InnoDB's default REPEATABLE READ isolation uses gap locks that cause deadlocks in insert-heavy workloads under conditions that are genuinely hard to reason about. Postgres's MVCC model under READ COMMITTED behaves more predictably for typical web traffic.

Now the reasons not to switch, which are just as real. Your team knows MySQL: its EXPLAIN output, its tuning knobs, its failure modes. That operational knowledge has value, and you're throwing it away. Your ecosystem tooling (backup scripts, monitoring dashboards, that one replication setup someone built in 2023) all assumes MySQL. And "Postgres is trendy" is not a reason. Neither is a conference talk. If you can't name the specific feature that fixes a specific problem you have today, don't migrate. If you're still weighing engines for a new project, our comparison of MySQL vs MariaDB vs PostgreSQL on Deploynix is the better starting point, because greenfield choice and production migration are very different decisions.

What Should You Audit Before Touching Anything?

The pre-flight audit is where migrations are won or lost. Eloquent-generated queries mostly translate cleanly between engines. Raw SQL does not, and every Laravel codebase over a couple of years old has more raw SQL than its authors remember.

Grep for raw SQL

Start by building an inventory of every place your code bypasses the query builder's dialect abstraction:

# Every raw SQL escape hatch in the codebase
grep -rEn "DB::raw|whereRaw|orderByRaw|selectRaw|havingRaw|groupByRaw|DB::statement|DB::select\(|DB::unprepared" \
  app/ database/ --include="*.php" > raw-sql-inventory.txt

# Known MySQL-isms that will break or silently misbehave on Postgres
grep -rEin "FIELD\(|GROUP_CONCAT|IFNULL\(|DATE_FORMAT\(|ON DUPLICATE KEY|STRAIGHT_JOIN|SQL_CALC_FOUND_ROWS|RAND\(\)|UNIX_TIMESTAMP" \
  app/ database/ --include="*.php" >> raw-sql-inventory.txt

wc -l raw-sql-inventory.txt

Every line in that file needs a decision: rewrite portably, rewrite for Postgres, or delete. Don't guess at the count. We've seen a "we barely use raw SQL" app produce 140 hits.

Inventory the behavior differences

Beyond raw SQL, the engines simply behave differently in ways that Eloquent cannot paper over. This table is the checklist we work from:

Note that upsert() in Laravel's query builder abstracts the upsert syntax correctly on both engines. The danger is exclusively in raw statements someone wrote by hand.

Check package compatibility and JSON usage

Go through composer.json and confirm every package that touches the database supports Postgres. Most first-party and popular packages do, but audit anything that ships its own migrations or raw queries: search packages, analytics packages, anything doing spatial work. While you're in there, list every json column and how it's queried. Columns that only store-and-retrieve are a non-event. Columns queried with whereJsonContains or ->>' extraction are your JSONB-plus-GIN upgrade candidates, and worth flagging now so you add the indexes after import.

Finally, confirm your backups are current and tested before you change anything. If your backup story is "we think cron is running it," fix that first; our guide to automated database backups covers the setup for both engines.

Converting the Schema With pgloader

pgloader is the workhorse for MySQL-to-Postgres moves, and it's genuinely good. It reads the MySQL schema, creates equivalent Postgres tables, streams the data across in parallel, converts types on the fly, rebuilds indexes, and resets sequences. For a straightforward schema it can be a one-command migration.

A production run deserves a real config file rather than command-line flags, because you'll run it several times (staging dry-runs, then the final sync) and you want it reproducible:

LOAD DATABASE
     FROM mysql://app:secret@10.0.0.11:3306/app_production
     INTO postgresql://app:secret@10.0.0.12:5432/app_production

 WITH include drop, create tables, create indexes,
      reset sequences, foreign keys,
      workers = 8, concurrency = 1,
      rows per range = 50000

 SET PostgreSQL PARAMETERS
      maintenance_work_mem TO '512MB',
      work_mem TO '64MB'

 SET MySQL PARAMETERS
      net_read_timeout  = '600',
      net_write_timeout = '600'

 CAST type json to jsonb drop typemod,
      type tinyint when (= precision 1) to boolean
           drop typemod using tinyint-to-boolean,
      type datetime to timestamptz drop typemod

 ALTER SCHEMA 'app_production' RENAME TO 'public'
;

The CAST rules are where you encode the decisions from your audit. json becomes jsonb (take the win). tinyint(1) becomes real boolean, which is correct but is exactly why you audited raw = 1 comparisons earlier. Whether datetime becomes timestamp or timestamptz depends on your app; if everything runs in UTC (as Laravel defaults encourage), timestamptz is the safer long-term choice.

What pgloader handles well, and what it mangles

It handles tables, data, most types, standard indexes, foreign keys, and sequence resets reliably. Three areas need hand-review every time:

Enums. MySQL ENUM columns become custom Postgres enum types by default, which are awkward to alter later. Most Laravel teams are better off casting them to varchar with a check constraint, or just varchar if validation lives in a PHP enum anyway. Add an explicit CAST rule so the choice is deliberate.

Fulltext indexes. FULLTEXT indexes don't translate. pgloader will skip them, and your MATCH ... AGAINST queries were going to break regardless. Plan a tsvector column with a GIN index as follow-up work, or move that feature to Meilisearch via Scout and sidestep the problem.

Spatial columns. MySQL spatial types need PostGIS on the Postgres side and manual mapping. If you have them, budget real time here.

Run pgloader against staging first, read its summary table line by line, and treat every warning as a defect to investigate. The dry-run is not optional. It's where you discover the zero-dates in a 2019-era table that Postgres will refuse, and you fix the source data before cutover night instead of during it.

Fixing the Laravel Layer

With a converted staging schema in hand, the work moves into the application, and it should start weeks before any production data moves.

Point CI at real Postgres first

Most Laravel test suites run on SQLite in memory. That's fast, and it's also exactly why your tests will pass while your app is broken: SQLite hides the dialect differences between MySQL and Postgres. The single highest-leverage step in this whole migration is switching CI to run the suite against a real PostgreSQL service and fixing failures one by one:

# .github/workflows/tests.yml (excerpt)
services:
  postgres:
    image: postgres:16
    env:
      POSTGRES_DB: testing
      POSTGRES_USER: app
      POSTGRES_PASSWORD: secret
    ports: ["5432:5432"]

Every red test is a dialect bug found in CI instead of in production. Strict GROUP BY errors, case-sensitivity assumptions, boolean comparisons: they all surface here, on your schedule. Budget two to four weeks of this running in parallel with normal feature work.

The config change

The Laravel side of the connection is refreshingly boring. Add (or update) the pgsql connection in config/database.php and drive it from .env:

// config/database.php
'pgsql' => [
    'driver' => 'pgsql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE', 'app_production'),
    'username' => env('DB_USERNAME', 'app'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'search_path' => 'public',
    'sslmode' => 'require',
],

// .env, at cutover time
// DB_CONNECTION=pgsql
// DB_HOST=10.0.0.12
// DB_PORT=5432

Make sure the pdo_pgsql PHP extension is installed on every app server before cutover night, not during it.

Fixing the queries CI surfaces

The fixes follow a handful of patterns. Prefer the portable version where one exists, so you're not just trading one dialect lock-in for another:

// FIELD() ordering: MySQL only. Rewrite with CASE WHEN (portable).
->orderByRaw("FIELD(severity, 'critical', 'warning', 'ok')")          // before
->orderByRaw("CASE severity WHEN 'critical' THEN 0
              WHEN 'warning' THEN 1 ELSE 2 END")                      // after

// Case-insensitive search: MySQL LIKE ignores case, Postgres doesn't.
->where('email', 'like', "%{$term}%")                                 // silently case-sensitive now
->whereRaw('email ILIKE ?', ["%{$term}%"])                            // Postgres-native
->whereRaw('lower(email) like ?', ['%'.mb_strtolower($term).'%'])     // portable

// Booleans in raw SQL: tinyint(1) took 0/1, Postgres boolean won't.
->whereRaw('is_active = 1')                                           // fails on Postgres
->where('is_active', true)                                            // builder handles both engines

// String aggregation
DB::raw("GROUP_CONCAT(name SEPARATOR ', ')")                          // MySQL
DB::raw("string_agg(name, ', ')")                                     // Postgres

Two more to check by hand. First, unique email columns: if your app relied on MySQL's case-insensitive collation to prevent Sam@ and sam@ registering twice, add a unique index on lower(email) (or use citext) after import, or that protection is gone. Second, any raw ON DUPLICATE KEY UPDATE statements need rewriting as ON CONFLICT; queries using Laravel's upsert() are already fine.

Which Cutover Strategy Fits Your App?

There are two sane ways to move the data, and the right one depends on how much downtime you can buy and how big the database is.

The maintenance window approach.

Strengths: Simple, rehearsable, and verifiable in one sitting. One tool, one data pass, one moment where the truth moves. The rollback story is as clean as it gets because MySQL never stops being complete and consistent; it's just frozen.

Best for: The majority of Laravel apps. If pgloader moves your staging copy in under an hour, you can buy a window (we schedule 3x the rehearsed duration) and your users tolerate a short read-only period, do this.

Considerations: The window must be rehearsed on staging with a production-sized dataset, and you need a hard abort time agreed in advance. If the rehearsal takes four hours, this strategy is telling you something.

The dual-run approach.

Strengths: Near-zero downtime. You replicate changes from MySQL to Postgres continuously (pgloader for the initial load, then CDC tooling or an application-level double-write for the delta), verify at leisure, and flip when the two sides have matched for days.

Best for: Large databases where a full pgloader pass takes many hours, or apps with genuine 24/7 write traffic and no tolerable window.

Considerations: Substantially more moving parts, and every moving part is a place for silent drift. Double-writing from the app is deceptively hard to get right under failure conditions. Don't choose this for a 20 GB database because zero downtime sounds nicer; you're trading a one-hour window for weeks of operational risk.

The runbook for the window

Rehearse this end to end on staging, with timings written down, before you book the window:

1. Announce the window; put the app in maintenance mode or flip it read-only. Freeze all writes, including queue workers and scheduled jobs. Verify the freeze by watching MySQL's write counters go flat.

2. Run the final pgloader pass against the frozen source.

3. Verify. Row counts, checksums, sequences (queries below). This is the go/no-go gate.

4. Flip .env to the pgsql connection and deploy the config change to all app servers.

5. Smoke test the critical paths with real requests: login, checkout or the equivalent, one queued job, one scheduled command.

6. Reopen traffic. Watch error rates and slow-query logs like a hawk for the first hour.

Step 3 deserves its own code block, because "it looks right" is not verification:

-- 1. Row counts: run per table on BOTH sides and diff the results.
--    (information_schema.TABLES row counts are estimates; use COUNT(*).)
SELECT COUNT(*) FROM orders;

-- 2. Checksums on critical tables: order-independent XOR of row hashes.
-- MySQL
SELECT BIT_XOR(CRC32(CONCAT_WS('|', id, user_id, status, total))) FROM orders;
-- PostgreSQL (compare that both sides produce stable, matching values
-- for a seeded staging dataset before trusting this in production)
SELECT BIT_XOR(hashtext(concat_ws('|', id, user_id, status, total))) FROM orders;

-- 3. Sequences: pgloader's "reset sequences" should handle this. Verify anyway,
--    because an unreset sequence means duplicate-key errors on the first INSERT.
SELECT setval(
    pg_get_serial_sequence('orders', 'id'),
    (SELECT COALESCE(MAX(id), 1) FROM orders)
);

-- 4. Fresh planner statistics before any traffic hits the database.
VACUUM ANALYZE;

Write down your rollback criteria before the window: which failures trigger an abort, and who makes the call. And read our post on recovering from a failed database migration in production before you need it, not after.

Running the Migration on Deploynix

Here's how this looks in practice on our own platform, since the database server type supports MySQL, MariaDB, and PostgreSQL and the pieces line up naturally with the runbook above.

Provision a new PostgreSQL database server alongside your existing MySQL one. Nothing about the MySQL server changes; both run in parallel for the whole migration. Because app servers can connect to any database server, staging can point at the new Postgres box weeks early for the CI-and-fixes phase while production traffic continues on MySQL untouched.

Lock the new server down before any data lands on it. Use firewall rules to restrict the Postgres port to your app servers' IPs only, exactly as your MySQL server should already be configured. A half-migrated database with production data on it is still production data.

Enable automated backups to S3-compatible storage on the Postgres server from day one, so your first pgloader import is followed by your first restorable backup, and keep the MySQL backup schedule running unchanged. During the transition you're effectively running the 3-2-1 backup rule across two engines, which is mildly annoying and entirely worth it.

The cutover itself is a config change, which means zero-downtime deploys handle the flip: update the environment to point at the Postgres host, deploy, and every app server picks up the new connection on release. If smoke tests fail, deploy the previous environment and you're back on MySQL in one release cycle. Keep the MySQL server provisioned, firewalled, and backed up for at least a week after cutover. It's your rollback target, and a week of monitoring data is what earns the decision to decommission it.

What Happens After the Cutover?

The migration isn't done when the site comes back up. The first week on Postgres is an active monitoring period, not a victory lap.

Statistics first. If you skipped it during the window, run VACUUM ANALYZE immediately. Postgres's planner is flying blind on a freshly imported database until statistics exist, and "Postgres is slower than MySQL was" complaints in week one are very often just missing stats or a missing index, not the engine.

Index review. Diff the index list on both sides table by table. Fulltext indexes didn't translate, prefix indexes (INDEX (col(10))) didn't translate, and anything pgloader warned about needs rebuilding by hand. Then go further: this is the moment to add what MySQL couldn't give you, like partial indexes on soft-deleted tables and GIN indexes on the JSONB columns you flagged in the audit. The principles from our MySQL indexing guide carry over almost entirely; the syntax and the extra options are what changed.

Watch query performance daily. Enable pg_stat_statements, sort by total time each morning, and compare against your mental model of what was slow on MySQL. The planners are different, and a query MySQL handled fine can pick a bad plan on Postgres (and vice versa, pleasantly often). The workflow we describe in finding and fixing slow queries in Laravel applies directly; only the EXPLAIN output format changes.

Keep MySQL warm. Frozen, firewalled, backed up, and one config deploy away. After a full week of clean error rates, stable p95 latencies, and successful Postgres backups you've actually test-restored, decommission it and reclaim the spend.

When Should You Abort?

Every good runbook has abort criteria, and deciding them under pressure is how bad nights get worse. Ours, roughly in order of when they'd trigger:

Before the window: if the staging rehearsal can't produce matching row counts and checksums twice in a row, you don't have a migration, you have a science experiment. Fix it in staging or don't book the window. Likewise if the raw-SQL fix list is still growing the week before cutover.

During the window: any checksum mismatch on a critical table is an automatic abort. Don't debug data integrity at 1 a.m. against a ticking clock; unfreeze MySQL, reopen, and investigate in daylight. Same if you blow through the hard time limit you set in advance. The whole point of the big-bang strategy is that aborting costs almost nothing: MySQL was never modified.

After the window: the calculus shifts the moment real writes land in Postgres, because rolling back now means losing or hand-porting that data. This is why the smoke-test step exists between the flip and reopening traffic. If errors surface in the first minutes, roll back and lose nothing. If a subtle bug surfaces on day three, you'll almost always fix forward on Postgres rather than roll back, and that's precisely why the week of monitoring with MySQL warm matters: it converts "we think it works" into evidence before the rollback option quietly expires.

An aborted migration that reruns cleanly a month later is a success story. A forced migration that limps into production is not.

FAQ

How long does a MySQL to PostgreSQL migration take for a typical Laravel app?

Plan for four to eight weeks end to end, with most of it in the audit and CI-fixing phases, not the data move. pgloader itself typically streams tens of gigabytes per hour depending on hardware and index counts. Your staging rehearsal gives you the real number for the window itself.

Can I keep using SQLite for local tests after moving to Postgres?

For fast local feedback, sure, but CI must run the full suite against real PostgreSQL, permanently. SQLite hides dialect behavior like strict GROUP BY, ILIKE, and boolean handling, which is exactly the class of bug this migration introduces. Postgres-in-CI is the safety net; keep it forever.

Does Eloquent just work on PostgreSQL?

Eloquent and the query builder support Postgres as a first-class driver, so standard where, join, upsert(), and pagination calls translate correctly. Every DB::raw, whereRaw, orderByRaw, and selectRaw in your codebase is on you, which is why the grep audit comes before everything else.

What's the single most common post-migration bug?

Case sensitivity. MySQL's default collations compare strings case-insensitively; Postgres does not. Logins, email uniqueness, and search boxes all quietly change behavior. Audit every LIKE, add lower() unique indexes where MySQL's collation was doing invisible work, and test authentication flows explicitly during the smoke test.

Should I migrate MySQL to MariaDB instead as a smaller step?

If your pain is licensing or minor feature gaps, maybe, since MariaDB is a much smaller jump. But it won't give you transactional DDL, JSONB, or partial indexes; those are the Postgres-specific reasons to migrate. If your driver list looks like this post's, MariaDB just postpones the same project.

Where to Go From Here

You don't need to commit to anything today. The first step is cheap and reversible: run the grep audit from this post against your codebase and count the raw SQL you'd have to touch. If the inventory is short and the reasons-to-switch list resonated, spin up a Postgres server next to your MySQL one, point a staging copy at it, and let pgloader and your CI pipeline tell you how hard this migration really is for your app. The data will make the decision more honestly than any blog post can, ours included.