Database migration is one of the most challenging tasks in software engineering. While both PostgreSQL and MySQL are powerful relational database systems, they differ significantly in SQL syntax, data types, indexing strategies, and advanced features.

This guide explains how to migrate a PostgreSQL database to MySQL safely, with schema illustrations and practical examples.

Why Migrate?

Organizations migrate from PostgreSQL to MySQL for reasons such as:

• Existing infrastructure already uses MySQL
• Lower operational complexity
• Team familiarity with MySQL
• Hosting provider preferences
• Application compatibility requirements
• Performance characteristics for specific workloads

Before You Start

Create a migration inventory:

Item	Description
Tables	All database tables
Views	Stored views
Indexes	Primary, unique, composite indexes
Constraints	Foreign keys and checks
Functions	User-defined functions
Triggers	Database triggers
Stored Procedures	Business logic
Extensions	PostgreSQL-specific features
Data Volume	Total records and size

Example:

-- PostgreSQL
SELECT table_name
FROM information_schema.tables
WHERE table_schema='public';

Architecture Overview

Before Migratio

After Migration

Application
     │
     ▼
┌──────────────┐
│ MySQL        │
└──────────────┘

Step 1: Analyze PostgreSQL Schema

Example PostgreSQL schema:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) UNIQUE,
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

Schema Illustration

users
├── id (SERIAL)
├── username
├── email
└── created_at

Step 2: Convert Data Types

One of the biggest migration challenges is data type conversion.

Common Mappings

PostgreSQL	MySQL
SERIAL	INT AUTO_INCREMENT
BIGSERIAL	BIGINT AUTO_INCREMENT
BOOLEAN	TINYINT(1)
TEXT	LONGTEXT
JSONB	JSON
UUID	CHAR(36)
TIMESTAMP	TIMESTAMP
BYTEA	BLOB
NUMERIC	DECIMAL

Example

PostgreSQL

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    active BOOLEAN,
    metadata JSONB
);

MySQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    active TINYINT(1),
    metadata JSON
);

Step 3: Convert Auto Increment Fields

PostgreSQL:

id SERIAL PRIMARY KEY

Internally PostgreSQL creates:

Sequence
    │
    ▼
Table ID

MySQL uses:

id INT AUTO_INCREMENT PRIMARY KEY

Architecture:

Table
 └── AUTO_INCREMENT
       │
       ▼
  Generates IDs

Step 4: Convert UUID Columns

PostgreSQL:

id UUID PRIMARY KEY

Often generated using:

gen_random_uuid()

MySQL:

id CHAR(36) PRIMARY KEY

Example:

INSERT INTO users(id)
VALUES(UUID());

Step 5: Convert Boolean Values

PostgreSQL:

active BOOLEAN

Values:

TRUE
FALSE

MySQL:

active TINYINT(1)

Values:

1 = TRUE
0 = FALSE

Step 6: Convert Date and Time Functions

Current Timestamp

PostgreSQL:

NOW()

MySQL:

NOW()

Compatible.

Date Difference

PostgreSQL:

SELECT AGE(NOW(), created_at);

MySQL:

SELECT TIMESTAMPDIFF(DAY, created_at, NOW());

Date Truncation

PostgreSQL:

DATE_TRUNC('month', created_at)

MySQL:

DATE_FORMAT(created_at,'%Y-%m-01')

Step 7: Convert JSON Operations

PostgreSQL is famous for JSONB support.

PostgreSQL:

SELECT metadata->>'name'
FROM products;

Example JSON:

{
  "name":"Laptop",
  "price":500
}

MySQL:

SELECT JSON_UNQUOTE(
    JSON_EXTRACT(metadata,'$.name')
)
FROM products;

Step 8: Convert Sequences

PostgreSQL:

CREATE SEQUENCE invoice_seq;

Used as:

nextval('invoice_seq')

MySQL has no direct equivalent.

Use:

AUTO_INCREMENT

or a custom sequence table:

CREATE TABLE sequences (
    next_id BIGINT
);

Step 9: Convert PostgreSQL ENUMs

PostgreSQL:

CREATE TYPE order_status AS ENUM (
    'pending',
    'paid',
    'cancelled'
);

status order_status

MySQL:

status ENUM(
    'pending',
    'paid',
    'cancelled'
)

Step 10: Convert Constraints

PostgreSQL:

ALTER TABLE users
ADD CONSTRAINT email_check
CHECK (
    email LIKE '%@%'
);

MySQL 8.0 supports CHECK constraints:

ALTER TABLE users
ADD CONSTRAINT email_check
CHECK (
    email LIKE '%@%'
);

For older MySQL versions:

Use triggers or application validation.

Step 11: Convert Indexes

PostgreSQL:

CREATE INDEX idx_email
ON users(email);

MySQL:

CREATE INDEX idx_email
ON users(email);

Mostly identical.

Step 12: Convert Full-Text Search

PostgreSQL:

CREATE INDEX idx_search
ON articles
USING GIN(
    to_tsvector('english',content)
);

Search:

SELECT *
FROM articles
WHERE to_tsvector(content)
@@ plainto_tsquery('database');

MySQL:

ALTER TABLE articles
ADD FULLTEXT(content);

Search:

SELECT *
FROM articles
WHERE MATCH(content)
AGAINST('database');

Step 13: Convert Triggers

PostgreSQL:

CREATE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER users_update
BEFORE UPDATE
ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

MySQL:

DELIMITER //

CREATE TRIGGER users_update
BEFORE UPDATE
ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//

DELIMITER ;

Step 14: Export PostgreSQL Data

Export schema:

pg_dump -U postgres \
--schema-only \
mydb > schema.sql

Export data:

pg_dump -U postgres \
--data-only \
mydb > data.sql

Step 15: Transform SQL

Many teams use tools such as:

• pgloader
• DBeaver
• AWS DMS
• Talend
• Apache NiFi

Popular choice:

pgloader postgres://user:pass@host/db \
mysql://user:pass@host/db

Migration Flow:

PostgreSQL
      │
      ▼
   pgloader
      │
      ▼
    MySQL

Step 16: Import into MySQL

Create database:

CREATE DATABASE appdb;

Import:

mysql -u root -p appdb < schema.sql
mysql -u root -p appdb < data.sql

Step 17: Verify Data Integrity

Compare row counts.

PostgreSQL:

SELECT COUNT(*)
FROM users;

MySQL:

SELECT COUNT(*)
FROM users;

Compare:

PostgreSQL = 150,000 rows
MySQL      = 150,000 rows

Check:

• Foreign keys
• Null values
• UUIDs
• JSON fields
• Dates
• Monetary values

Step 18: Application Changes

Update connection strings.

PostgreSQL:

DATABASE_URL=postgres://user:password@localhost/app

MySQL:

DATABASE_URL=mysql://user:password@localhost/app

Example Node.js connection:

// PostgreSQL
const { Pool } = require('pg');

becomes

// MySQL
const mysql = require('mysql2');

Common Problems and Solutions

Problem	Solution
SERIAL not working	Replace with AUTO_INCREMENT
JSONB operators fail	Use JSON_EXTRACT
UUID generation missing	Use UUID()
Sequences unavailable	Use AUTO_INCREMENT
GIN indexes unsupported	Use FULLTEXT indexes
PL/pgSQL functions fail	Rewrite as MySQL procedures
Array columns unsupported	Normalize tables or use JSON

Example Complete Conversion

PostgreSQL

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id UUID,
    amount NUMERIC(10,2),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

MySQL

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id CHAR(36),
    amount DECIMAL(10,2),
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Schema Illustration

orders
├── id
│    └── AUTO_INCREMENT
├── customer_id
├── amount
├── metadata
└── created_at

Recommended Migration Workflow

1. Backup PostgreSQL
          │
          ▼
2. Analyze Schema
          │
          ▼
3. Convert Data Types
          │
          ▼
4. Convert Functions
          │
          ▼
5. Convert Triggers
          │
          ▼
6. Migrate Data
          │
          ▼
7. Verify Integrity
          │
          ▼
8. Update Application
          │
          ▼
9. Performance Testing
          │
          ▼
10. Production Cutover

For large production databases (millions of records), tools such as pgloader, AWS Database Migration Service (DMS), or dual-write replication strategies are generally safer than exporting SQL dumps manually, because they can handle schema conversion, large datasets, and minimal downtime more effectively.

About the Author

codeflare

Administrator

Latest tech news and coding tips.

Visit Website View All Posts