Laravel MySQL to PostgreSQL migration checklist: free checker v2 (TIMESTAMP shift, increments, unsigned)

Laravel MySQL to PostgreSQL migration checklist: free checker v2 (TIMESTAMP shift, increments, unsigned)

TuskShift
New, free, in your browser: paste your SHOW CREATE TABLE and see what breaks on Postgres (int unsigned overflow, TIMESTAMP shift, _ci collations, sequences, tinyint(1), ENUM/SET, zero dates, FULLTEXT). Nothing is uploaded.

A free, single-file PHP script (v2, 26 Oct 2026) that tells you in about two minutes what will break when you switch a Laravel app from DB_CONNECTION=mysql to pgsql. No Composer, no dependencies, PHP 8.1+, MIT licence. The database part only runs SELECTs. From TuskShift (brand account; the script and this page were written with AI help and tested by hand on MariaDB 11.8 + PostgreSQL 17).

Already on Postgres and hitting errors? The Laravel Postgres Error Fix Pack ($19) has a re-runnable fix and short guide for the six errors that show up first: duplicate key on insert (sequence reset), created_at off by hours, GROUP BY 42803, boolean = integer, zero dates, unsigned overflow. Each fix was run on PostgreSQL 17 + MariaDB 11.8 before release.

New in v2

  • TIMESTAMP time-zone shift. mysqldump / mariadb-dump write SET TIME_ZONE='+00:00' and export TIMESTAMP columns in UTC. If your server isn't on UTC, every value lands in Postgres shifted by the offset (12:00 becomes 17:00 on a UTC-5 server) and row counts still match. v2 lists your TIMESTAMP columns when the session zone isn't UTC.
  • `increments()` keys. increments() is int(10) unsigned on MySQL but a plain integer on pgsql, so the ceiling drops from 4,294,967,295 to 2,147,483,647. v2 flags it in migrations and checks each auto-increment counter: at 50% or more of 2,147,483,647 it tells you to move the key to bigint first.
  • `bigint unsigned` overflow. Values above 9,223,372,036,854,775,807 don't fit Postgres bigint. Use numeric(20,0).

Full checklist it runs

Code (15 patterns): where(..., 'like', ...) turning case-sensitive, backtick identifiers in raw SQL, IFNULL, GROUP_CONCAT, DATE_FORMAT, DATE_ADD/DATEDIFF, ON DUPLICATE KEY / INSERT IGNORE / REPLACE INTO, ORDER BY FIELD(), FOREIGN_KEY_CHECKS, zero-date literals, REGEXP, IF() in raw expressions, unsigned integers, increments(), and groupBy() calls to review.

Live data and schema (optional, read-only): zero or partial dates, NUL bytes in text, tinyint(1) holding values other than 0/1, unsigned int/bigint values over the Postgres limits, unique columns on a _ci collation (email logins become case-sensitive), TIMESTAMP zone shift, auto-increment headroom, and triggers that no loader ports.

Still not checked (do these by hand): SQL built across variables; sequences after the data load (run setval to MAX(id) on every table before writes resume); foreign keys a loader skipped; row-by-row verification.

Run it

Save the script below as tuskshift-quickcheck.php, then:

php tuskshift-quickcheck.php /path/to/laravel-app

# add the data checks (password from env, never on the command line):
TUSK_MYSQL_PASSWORD=secret php tuskshift-quickcheck.php /path/to/laravel-app \
  --dsn="mysql:host=127.0.0.1;dbname=app" --user=readonly_user

Exit code is 1 if anything high-severity or a data issue turns up, so it works as a CI gate.

Sample output (test app + seeded MariaDB 11.8 on a UTC-5 server)

TuskShift quickcheck v2 — 2 PHP files scanned
Code: 1 high, 2 medium, 0 low
Data/schema: 5 issue(s)
HIGH   LIKE_CASE     app/Models/U.php:1
MEDIUM INCREMENTS    database/migrations/2026_01_01_create.php:2
MEDIUM UNSIGNED      database/migrations/2026_01_01_create.php:2
DATA   CI_UNIQUE         users.email
DATA   UNSIGNED_OVERFLOW hits.n
       1 row(s) exceed Postgres integer. Use bigint.
DATA   UNSIGNED_OVERFLOW hits.big
       1 row(s) exceed Postgres bigint. Use numeric(20,0).
DATA   TZ_SHIFT          1 TIMESTAMP col(s)
       Session zone SYSTEM (UTC-300 min). A default dump exports these in UTC ...
DATA   AUTOINC_HEADROOM  hits
       Next id 1500000002 = 70% of Postgres integer max (int(10) unsigned key) ...

The script (v2)

#!/usr/bin/env php
<?php
/**
 * tuskshift-quickcheck v2 (free) — a 2-minute MySQL→PostgreSQL readiness check for a Laravel app.
 *
 *   php tuskshift-quickcheck.php /path/to/laravel-app
 *   php tuskshift-quickcheck.php /path/to/laravel-app --dsn="mysql:host=127.0.0.1;dbname=app" --user=app
 *     (password via TUSK_MYSQL_PASSWORD env var; the DB part only runs SELECTs)
 *
 * Part 1 greps app/, routes/, database/ and config/ for the 15 MySQL-only patterns that break most often.
 * Part 2 (optional) checks live data/schema for the 8 things that make a Postgres load fail or silently change.
 * v2 (2026-10-26): + increments() rule, + TIMESTAMP time-zone shift, + auto-increment headroom, + bigint unsigned overflow.
 * No dependencies. PHP 8.1+. MIT licence. Exit code 1 if anything "high" or a data blocker is found.
 *
 * Free sampler from TuskShift. The full kit (52 code rules, full preflight, resumable copy,
 * per-row checksum verify, FK snapshot/restore, 10-chapter playbook): https://tuskshift.gumroad.com/l/tuskshift
 */
declare(strict_types=1);

$path = null; $o = ['dsn' => null, 'user' => 'root'];
foreach (array_slice($argv, 1) as $a) {
    if (preg_match('/^--(dsn|user)=(.*)$/s', $a, $m)) { $o[$m[1]] = $m[2]; } elseif ($a[0] !== '-') { $path = rtrim($a, '/'); }
}
if (!$path || !is_dir($path)) { fwrite(STDERR, "Usage: php tuskshift-quickcheck.php <laravel-app> [--dsn=mysql:...] [--user=...]\n"); exit(2); }

// [id, severity, regex, why/fix]
$rules = [
    ['LIKE_CASE',   'high',   "/['\"](not\\s+)?like['\"]\\s*,/i", "where(col, 'like', ...) turns case-SENSITIVE on Postgres. Use ->whereLike() (Laravel 11+) or ilike behind a driver check."],
    ['BACKTICK',    'high',   '/(DB::|Raw\(|select\(|statement\().*`[A-Za-z_]+`/', 'Backtick identifiers in raw SQL. Postgres uses "double quotes"; usually just drop them.'],
    ['IFNULL',      'high',   '/\bIFNULL\s*\(/i', 'IFNULL() does not exist. COALESCE() works on both engines.'],
    ['GROUP_CONCAT','high',   '/\bGROUP_CONCAT\s*\(/i', "GROUP_CONCAT() does not exist. string_agg(col::text, ',') on pgsql."],
    ['DATE_FORMAT', 'high',   '/\bDATE_FORMAT\s*\(/i', "DATE_FORMAT() does not exist. to_char(col, 'YYYY-MM-DD')."],
    ['DATE_MATH',   'high',   '/\b(DATE_ADD|DATE_SUB|DATEDIFF|TIMESTAMPDIFF)\s*\(/i', 'MySQL date math. Bind a Carbon value or use col + INTERVAL \'7 days\'.'],
    ['UPSERT',      'high',   '/ON\s+DUPLICATE\s+KEY|INSERT\s+IGNORE|REPLACE\s+INTO/i', 'MySQL-only upsert. Use ->upsert() / ->insertOrIgnore() (compile to ON CONFLICT).'],
    ['FIELD_ORDER', 'high',   '/ORDER\s+BY\s+FIELD\s*\(/i', 'ORDER BY FIELD() is MySQL-only. Use a CASE expression or array_position().'],
    ['FK_CHECKS',   'high',   '/FOREIGN_KEY_CHECKS/i', 'Use Schema::disableForeignKeyConstraints() instead.'],
    ['ZERO_DATE',   'high',   "/'0000-00-00/", "Zero dates are rejected by Postgres. Use NULL."],
    ['REGEXP',      'high',   '/\s(NOT\s+)?(REGEXP|RLIKE)\s/i', 'REGEXP/RLIKE: use ~ or ~* on Postgres.'],
    ['IF_FUNC',     'medium', '/(selectRaw|DB::raw|orderByRaw|havingRaw)\(.*(?<![A-Za-z_])IF\s*\(/i', 'IF(cond,a,b) is MySQL-only. CASE WHEN ... END works on both.'],
    ['UNSIGNED',    'medium', '/->(unsignedInteger|unsignedMediumInteger)\(/', 'No unsigned ints on Postgres: unsignedInteger max drops from 4.29B to 2.14B.'],
    ['INCREMENTS',  'medium', '/->(increments|integerIncrements|mediumIncrements)\(/', 'increments() is an int(10) unsigned key on MySQL but a plain integer (max 2,147,483,647) on pgsql. Use id()/bigIncrements() for big tables.'],
    ['GROUP_BY',    'low',    '/->groupBy\(/', 'Postgres rejects selected columns that are neither grouped nor aggregated. Check each one.'],
];

$findings = []; $files = 0;
foreach (['app', 'routes', 'database', 'config'] as $dir) {
    if (!is_dir("$path/$dir")) { continue; }
    $it = new RecursiveIteratorIterator(new RecursiveDirectoryIterator("$path/$dir", FilesystemIterator::SKIP_DOTS));
    foreach ($it as $f) {
        if (!str_ends_with($f->getFilename(), '.php')) { continue; }
        $files++;
        foreach (file($f->getPathname()) as $n => $line) {
            foreach ($rules as [$id, $sev, $re, $fix]) {
                if (preg_match($re, $line)) { $findings[] = [$sev, $id, substr($f->getPathname(), strlen($path) + 1) . ':' . ($n + 1), $fix]; }
            }
        }
    }
}

$blockers = [];
if ($o['dsn']) {
    try {
        $pdo = new PDO($o['dsn'], $o['user'], getenv('TUSK_MYSQL_PASSWORD') ?: '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
    } catch (Throwable $e) { fwrite(STDERR, "DB connection failed: {$e->getMessage()}\n"); exit(2); }
    $qi = fn(string $s) => '`' . str_replace('`', '``', $s) . '`';
    $cols = $pdo->query("SELECT c.TABLE_NAME t, c.COLUMN_NAME c, c.DATA_TYPE d, c.COLUMN_TYPE ct, c.COLUMN_KEY k, c.COLLATION_NAME co
        FROM information_schema.COLUMNS c JOIN information_schema.TABLES tb ON tb.TABLE_SCHEMA=c.TABLE_SCHEMA AND tb.TABLE_NAME=c.TABLE_NAME
        WHERE c.TABLE_SCHEMA = DATABASE() AND tb.TABLE_TYPE='BASE TABLE'")->fetchAll(PDO::FETCH_ASSOC);
    $tsCols = [];
    foreach ($cols as $r) {
        [$t, $c, $d] = [$r['t'], $r['c'], $r['d']]; $col = $qi($c); $tab = $qi($t); $obj = "$t.$c";
        $count = fn(string $where) => (int) $pdo->query("SELECT COUNT(*) FROM $tab WHERE $where")->fetchColumn();
        if (in_array($d, ['date', 'datetime', 'timestamp'], true) && ($n = $count("CAST($col AS CHAR) LIKE '0000-%' OR CAST($col AS CHAR) LIKE '%-00-%' OR CAST($col AS CHAR) LIKE '%-00%'")))
            { $blockers[] = ['ZERO_DATE', $obj, "$n row(s) with zero/partial dates. Postgres rejects them."]; }
        if (in_array($d, ['char', 'varchar', 'text', 'mediumtext', 'longtext'], true) && ($n = $count("LOCATE(CHAR(0), $col) > 0")))
            { $blockers[] = ['NUL_BYTE', $obj, "$n row(s) contain a 0x00 byte. Postgres text cannot store it."]; }
        if ($r['ct'] === 'tinyint(1)' && ($n = $count("$col NOT IN (0,1)")))
            { $blockers[] = ['BOOL_NOT_01', $obj, "$n row(s) hold values other than 0/1. A boolean cast fails or loses data."]; }
        if ($d === 'int' && str_contains($r['ct'], 'unsigned') && ($n = $count("$col > 2147483647")))
            { $blockers[] = ['UNSIGNED_OVERFLOW', $obj, "$n row(s) exceed Postgres integer. Use bigint."]; }
        if ($d === 'bigint' && str_contains($r['ct'], 'unsigned') && ($n = $count("$col > 9223372036854775807")))
            { $blockers[] = ['UNSIGNED_OVERFLOW', $obj, "$n row(s) exceed Postgres bigint. Use numeric(20,0)."]; }
        if ($d === 'timestamp') { $tsCols[] = $obj; }
        if (in_array($r['k'], ['PRI', 'UNI'], true) && $r['co'] && str_ends_with($r['co'], '_ci'))
            { $blockers[] = ['CI_UNIQUE', $obj, "Unique on {$r['co']}: lookups become case-sensitive on Postgres (logins by email!). Use citext or a lower() index."]; }
    }
    // TIMESTAMP columns are stored in UTC and shown in the session zone. mysqldump/mariadb-dump write
    // SET TIME_ZONE='+00:00', so a dump loaded into a timestamp-without-time-zone column shifts by the offset.
    $off = (int) $pdo->query("SELECT TIMESTAMPDIFF(MINUTE, UTC_TIMESTAMP(), NOW())")->fetchColumn();
    if ($tsCols && $off !== 0) {
        $tz = $pdo->query("SELECT @@session.time_zone")->fetchColumn();
        $blockers[] = ['TZ_SHIFT', count($tsCols) . ' TIMESTAMP col(s)', sprintf("Session zone %s (UTC%+d min). A default dump exports these in UTC, so values shift by the offset in Postgres while row counts match. Dump with --skip-tz-utc or convert per column. e.g. %s", $tz, $off, implode(', ', array_slice($tsCols, 0, 3)))];
    }
    foreach ($pdo->query("SELECT t.TABLE_NAME t, t.AUTO_INCREMENT ai, c.COLUMN_TYPE ct FROM information_schema.TABLES t
        JOIN information_schema.COLUMNS c ON c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME AND c.EXTRA LIKE '%auto_increment%'
        WHERE t.TABLE_SCHEMA = DATABASE() AND t.AUTO_INCREMENT IS NOT NULL AND c.DATA_TYPE IN ('int','mediumint','smallint')") as $r) {
        $pct = (int) $r['ai'] / 2147483647 * 100;
        if ($pct >= 50) { $blockers[] = ['AUTOINC_HEADROOM', $r['t'], sprintf("Next id %s = %.0f%% of Postgres integer max (%s key). Change the key to bigint before you migrate.", $r['ai'], $pct, $r['ct'])]; }
    }
    if ($n = (int) $pdo->query("SELECT COUNT(*) FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = DATABASE()")->fetchColumn())
        { $blockers[] = ['TRIGGERS', 'schema', "$n trigger(s): no loader ports these; rewrite in PL/pgSQL."]; }
}

$rank = ['high' => 0, 'medium' => 1, 'low' => 2];
usort($findings, fn($a, $b) => [$rank[$a[0]], $a[1]] <=> [$rank[$b[0]], $b[1]]);
$tally = array_count_values(array_column($findings, 0)) + ['high' => 0, 'medium' => 0, 'low' => 0];
echo "TuskShift quickcheck v2 — $files PHP files scanned\n";
echo "Code: {$tally['high']} high, {$tally['medium']} medium, {$tally['low']} low\n";
if ($o['dsn']) { echo 'Data/schema: ' . count($blockers) . " issue(s)\n"; }
echo str_repeat('-', 72), "\n";
foreach ($findings as [$sev, $id, $where, $fix]) { printf("%-6s %-13s %s\n       %s\n", strtoupper($sev), $id, $where, $fix); }
foreach ($blockers as [$id, $obj, $why]) { printf("DATA   %-17s %s\n       %s\n", $id, $obj, $why); }
echo str_repeat('-', 72), "\n";
echo "Not checked here: SQL built across variables, sequences after a data load (run setval),\n";
echo "FKs dropped by pgloader, row-level verification. Full kit: https://tuskshift.gumroad.com/l/tuskshift\n";
exit(($tally['high'] > 0 || $blockers) ? 1 : 0);

When the checker finds things

The paid TuskShift Migration Kit ($49) is the full version: 52 code rules, a full data preflight, resumable copy, per-row checksum verify, FK snapshot/restore and a 10-chapter cutover playbook. Code LAUNCH30 takes 30% off until 5 Nov: tuskshift.gumroad.com/l/tuskshift/LAUNCH30. Related free reads: the two bugs you get in the first hour , a minute-by-minute cutover runbook, fixing duplicate key errors after the import (sequence reset) , timestamps off by hours after the move and "must appear in the GROUP BY clause" errors.

Fixing errors on a live app right now: Error Fix Pack, $19.

Not affiliated with Laravel, MySQL, MariaDB or PostgreSQL.

Report Page