migrate mysql to postgresql

Migrating MySQL to PostgreSQL usually involves 4 parts:

1. Schema conversion
2. Data migration
3. App/query changes
4. Validation and cutover

Here’s a practical approach.

---

1) Assess the differences

Common MySQL → PostgreSQL differences:

• AUTO_INCREMENT → GENERATED ... AS IDENTITY or SERIAL
• TINYINT(1) often used as boolean → BOOLEAN
• UNSIGNED types: PostgreSQL has no unsigned integer types
• Backticks `name` → double quotes "name" if needed
• DATETIME → TIMESTAMP
• NOW() exists in both, but behavior and time zone handling can differ
• LIMIT offset, count in MySQL → LIMIT count OFFSET offset in PostgreSQL
• String concatenation:
  • MySQL: CONCAT(a,b)
  • PostgreSQL: a || b
• Functions, joins, JSON, and regex syntax can differ

---

2) Convert schema

Option A: Use migration tools

Popular tools:

• pgloader (best for simple/medium migrations)
• AWS Schema Conversion Tool
• Liquibase / Flyway for managed schema migrations
• Manual conversion if schema is small

Example MySQL → PostgreSQL type mapping

MySQL	PostgreSQL
INT AUTO_INCREMENT	GENERATED ALWAYS AS IDENTITY
BIGINT AUTO_INCREMENT	BIGSERIAL or GENERATED AS IDENTITY
TINYINT(1)	BOOLEAN
DATETIME	TIMESTAMP
TEXT	TEXT
VARCHAR(n)	VARCHAR(n)
DECIMAL(p,s)	DECIMAL(p,s)

Example table conversion

MySQL

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

PostgreSQL

CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

---

3) Migrate the data

Best easy option: pgloader

It can load from MySQL directly into PostgreSQL and handle many conversions automatically.

Example:

LOAD DATABASE
     FROM mysql://user:password@localhost/source_db
     INTO postgresql://user:password@localhost/target_db

WITH include drop, create tables, create indexes, reset sequences

SET maintenance_work_mem to '512MB',
    work_mem to '12MB'

CAST type tinyint when (= precision 1) to boolean

ALTER SCHEMA 'source_db' RENAME TO 'public';

Run:

pgloader migration.load

---

4) Convert application SQL

You’ll likely need to update:

• LIMIT offset, count syntax
• INSERT ... ON DUPLICATE KEY UPDATE
  • PostgreSQL equivalent: INSERT ... ON CONFLICT (...) DO UPDATE
• IFNULL() → COALESCE()
• CURDATE() → CURRENT_DATE
• SUBSTRING() mostly compatible, but test edge cases
• REPLACE INTO has no direct equivalent; use INSERT ... ON CONFLICT
• ENUM and SET need careful handling

Example:

MySQL

INSERT INTO users (id, email)
VALUES (1, 'a@b.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

PostgreSQL

INSERT INTO users (id, email)
VALUES (1, 'a@b.com')
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email;

---

5) Validate

After migration:

• Compare row counts
• Check indexes and constraints
• Test application queries
• Verify date/time behavior
• Check character encoding/collation issues
• Ensure sequences/identity values are in sync

Useful checks:

SELECT COUNT(*) FROM table_name;

and compare between systems.

---

6) Cutover strategy

Common approaches:

Downtime migration

1. Stop writes to MySQL
2. Final data sync
3. Switch app to PostgreSQL
4. Monitor

Near-zero downtime

1. Bulk load data
2. Replicate ongoing changes
3. Switch over after lag is near zero

This is more complex, but better for production systems.

---

Recommended tool choice

• Small/simple DB: manual conversion + pgloader
• Medium/large DB: pgloader + query review
• Enterprise/complex schema: schema conversion tool + staged migration + testing

---

If you want, I can help you with any of these next:

1. Convert a specific MySQL schema to PostgreSQL
2. Write a pgloader migration file
3. Convert your SQL queries
4. Plan a production migration strategy