Migrate a Laravel app from MySQL to PostgreSQL

Laravel speaks PostgreSQL natively through its query builder, so the framework side of a migration is mostly configuration. The hard part is the data and the few MySQL habits that do not survive the move. Here is the full path, in order.

1. Install the PostgreSQL driver

Laravel uses PDO. Make sure the pdo_pgsql extension is present, then confirm it:

# Debian/Ubuntu
sudo apt-get install php-pgsql
php -m | grep pdo_pgsql

No Composer package is required — pdo_pgsql ships with the PHP runtime, not with Laravel.

2. Point your environment at PostgreSQL

Edit .env. The connection name pgsql already exists in a stock config/database.php, so you only change the variables it reads:

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=myapp
DB_USERNAME=myapp
DB_PASSWORD=secret

The default DB_PORT for MySQL is 3306; PostgreSQL listens on 5432. If you hard-coded the port anywhere, update it. After editing the file, clear the cached config so the new values take effect:

php artisan config:clear

3. Set the default schema and search path

PostgreSQL puts everything in the public schema by default. Confirm the pgsql block in config/database.php matches your server:

'pgsql' => [
    'driver'   => 'pgsql',
    'host'     => env('DB_HOST', '127.0.0.1'),
    'port'     => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'charset'  => 'utf8',
    'search_path' => 'public',
    'sslmode'  => 'prefer',
],

Note charset is utf8 here, not MySQL's utf8mb4 — PostgreSQL's UTF-8 already covers the full Unicode range, including emoji, so there is no four-byte variant to ask for.

4. Fix the gotchas Eloquent papers over

These are the things that compile fine in MySQL and then surprise you on PostgreSQL:

Boolean columns. MySQL stores $table->boolean() as tinyint(1) and happily accepts 0/1. PostgreSQL uses a real boolean type and rejects integers in strict contexts. Add an explicit 'is_active' => 'boolean' entry to your model's $casts so PHP always sends true/false.
Enum columns. Laravel's $table->enum() becomes a native MySQL ENUM but a varchar + CHECK constraint on PostgreSQL. Adding a new value later means altering the constraint, not just editing a migration. Consider a lookup table or a string column if your set changes often.
Case-sensitive LIKE. MySQL's default collation makes LIKE case-insensitive; PostgreSQL's LIKE is case-sensitive. Swap to ILIKE via ->whereRaw() or ->where('col', 'ilike', ...) where you relied on the old behaviour.
Grouped queries. PostgreSQL enforces the SQL standard: every non-aggregated column in a SELECT must appear in GROUP BY. Queries MySQL tolerated will throw here. Eloquent's groupBy() calls may need extra columns.

5. Move the data

Two options. If your schema is small and you have factories or seeders, the cleanest path is to run migrations fresh against PostgreSQL and re-seed:

php artisan migrate:fresh --seed

For a real database with production rows, you cannot just point Laravel at an empty PostgreSQL server — you need the existing data converted, including the type and syntax differences above. Export your MySQL data and run it through a converter that rewrites the dialect for you:

mysqldump --single-transaction --no-tablespaces \
  -u root -p myapp > myapp.sql

Then convert myapp.sql to PostgreSQL and import the result with psql -d myapp -f converted.sql. After importing real data, reset the sequences so Laravel's auto-incrementing IDs do not collide with existing rows — PostgreSQL does not bump a sequence when you insert an explicit primary key.

6. Verify before you switch

Run your test suite against the PostgreSQL connection, then spot-check counts on a few tables (php artisan tinker → User::count()) against the old MySQL numbers. Only flip production DB_CONNECTION once both match.

Skip the dialect headaches — convert your MySQL dump to PostgreSQL now →

Chat with us