MySQL dump to PostgreSQL dump (Bash converter)

A best-effort, portable Bash script that transforms a MySQL SQL dump into a PostgreSQL-compatible SQL file using text-based rules (sed/awk).

Note: SQL dialects differ significantly. This tool covers the most common cases but cannot fully automate complex migrations. Always review and test the output.

Features

• Converts MySQL-specific DDL to PostgreSQL-friendly syntax (best effort)
• Replaces MySQL types with PostgreSQL equivalents (e.g., TINYINT(1) -> boolean, DATETIME -> timestamp)
• Strips MySQL table options (ENGINE, DEFAULT CHARSET, COLLATE, ROW_FORMAT)
• Converts AUTO_INCREMENT to PostgreSQL identity columns
• Converts UNIQUE KEY to table-level UNIQUE (cols) constraints
• Extracts KEY/INDEX into standalone CREATE INDEX statements
• Removes MySQL-only pragmas and meta statements (SET, LOCK/UNLOCK TABLES, DELIMITER, etc.)
• Handles backticks -> double quotes
• Removes inline column comments (COMMENT '...')
• Normalizes boolean DEFAULT values 0/1 to false/true when a column becomes boolean
• Attempts to fix trailing commas before closing parentheses

Limitations

• Not a full SQL parser. Complex constructs (stored procedures, triggers, functions, views, events, advanced collations, generated columns, spatial types, partial indexes, enum constraints, partitioning, foreign key ON UPDATE/DELETE nuances) may need manual editing.
• ENUM types are mapped to text. Consider creating proper CHECK constraints or domains manually.
• UNSIGNED numeric semantics are dropped. Validate ranges if you relied on UNSIGNED.
• TIME/TIMESTAMP timezone semantics may require review.
• Requires GNU sed/awk for full compatibility. On macOS, install gsed via Homebrew or adapt the script.

Requirements

• bash
• sed, awk, mktemp
• GNU sed/awk recommended (macOS: brew install gnu-sed gawk and run with gsed/gawk or adjust PATH)

Usage

Basic usage with file input:

./mysql_to_postgres.sh input_mysql_dump.sql > output_postgres.sql

Or via stdin:

cat input_mysql_dump.sql | ./mysql_to_postgres.sh > output_postgres.sql

If you are on macOS and installed GNU sed/awk as gsed/gawk, edit the script to replace sed/awk with gsed/gawk, or prepend them in PATH:

export PATH="/usr/local/opt/gnu-sed/libexec/gnubin:/usr/local/opt/gawk/libexec/gnubin:$PATH"
./mysql_to_postgres.sh input.sql > output.sql

What the script does (high level)

• Removes MySQL-specific statements and comments (SET, LOCK TABLES, DELIMITER, /*! ... */, etc.)
• Normalizes identifiers by replacing backticks with double quotes
• Maps common data types and attributes:
  • TINYINT(1) -> boolean
  • TINYINT(n) -> smallint
  • MEDIUMINT(n) -> integer
  • INT(n) -> integer
  • BIGINT(n) -> bigint
  • FLOAT -> real
  • DOUBLE -> double precision
  • DATETIME/TIMESTAMP -> timestamp without time zone
  • TEXT/MEDIUMTEXT/LONGTEXT/TINYTEXT -> text
  • BLOB/MEDIUMBLOB/LONGBLOB/TINYBLOB -> bytea
  • ENUM(...) -> text
  • AUTO_INCREMENT -> GENERATED BY DEFAULT AS IDENTITY
  • Drops UNSIGNED
• Strips table options: ENGINE=..., DEFAULT CHARSET=..., COLLATE=..., ROW_FORMAT=...
• Moves inline KEY/INDEX definitions to standalone CREATE INDEX after the table
• Converts UNIQUE KEY to UNIQUE (...) table constraints
• Removes inline column COMMENT '...'
• Fixes trailing commas before );

Tips for a smoother migration

1. Load the converted schema into an empty PostgreSQL database first:

   psql -d yourdb -f output_postgres.sql

2. If you have data inserts in the dump, they should generally work after type mapping. Watch out for:
   • Zero dates like '0000-00-00' or '0000-00-00 00:00:00' converted to NULL
   • Byte sequences in BLOBs -> bytea formatting
3. Review constraints, foreign keys, and indexes manually.
4. Replace ENUMs with domains or CHECK constraints if you need stricter typing.
5. Validate numeric ranges if you depended on UNSIGNED.

Example

Given a MySQL table:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  UNIQUE KEY `idx_users_email` (`email`),
  KEY `idx_users_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The converted output will be similar to:

-- Converted by mysql_to_postgres.sh
SET standard_conforming_strings = on;
SET client_encoding = 'UTF8';
CREATE TABLE "users" (
  "id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  "email" varchar(255) NOT NULL,
  "is_active" boolean NOT NULL DEFAULT true,
  UNIQUE ("email")
);
CREATE INDEX "idx_users_active" ON "users" ("is_active");

Caveats and manual follow-ups

• Default boolean values: MySQL often uses 0/1. PostgreSQL accepts true/false or 0/1 for boolean literals in inserts, but you may prefer to normalize defaults to true/false.
• Quoted identifiers: The script converts backticks to double quotes. Consider removing quotes if you prefer unquoted identifiers.
• Foreign keys: Verify ON DELETE/UPDATE rules and naming conventions.
• Views/Procedures/Triggers: Usually require manual translation.

Contributing

Issues and PRs are welcome. Please attach minimal reproducible examples and describe expected vs. actual output.

License

MIT