MySQL迁移到PostgreSQL操作指南

作者： 分类：所有,mysql 字数：6595

MySQL迁移到PostgreSQL操作指南

一、背景与问题

在现代分布式系统架构中，数据库选型往往需要综合考虑性能、扩展性、生态兼容性等多维度因素。MySQL与PostgreSQL作为两大主流关系型数据库，其技术栈差异在实际应用中会产生显著影响。本文将深入探讨MySQL迁移到PostgreSQL的完整操作流程，重点分析迁移过程中涉及的底层原理、常见问题及解决方案。

二、基本原理

MySQL与PostgreSQL在底层实现上存在本质差异：

  1. 存储引擎差异：MySQL默认使用InnoDB，支持事务和行级锁；PostgreSQL采用MVCC（多版本并发控制）机制，通过版本链实现高并发读写
  2. 索引机制：MySQL支持B+树、哈希索引，PostgreSQL支持B+树、Hash、Gist、SP-GiST等多类型索引
  3. 事务处理：MySQL使用两阶段提交，PostgreSQL通过WAL（Write-Ahead Logging）实现崩溃恢复
  4. 数据类型：PostgreSQL支持JSON、JSONB、HStore等文档类型，MySQL则通过JSON类型实现类似功能
  5. 查询优化器：PostgreSQL采用基于成本的查询优化器，MySQL则基于规则的优化器

三、环境准备

# 安装PostgreSQL
sudo apt-get install postgresql postgresql-contrib

# 创建数据库用户
sudo -u postgres createuser --createdb myuser

# 创建数据库
sudo -u postgres createdb -O myuser mydb

# 配置连接
sudo -u postgres psql -U myuser -d mydb
# Python连接测试
import psycopg2

conn = psycopg2.connect(
    dbname="mydb",
    user="myuser",
    password="mypassword",
    host="localhost",
    port="5432"
)
print(conn.status)

四、核心实现

1. 表结构迁移

-- MySQL表结构
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    created_at DATETIME
);

-- PostgreSQL转换
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMPTZ
);

关键点：

  • 自增主键改为SERIAL类型（PostgreSQL自动管理序列）
  • DATETIME类型改为TIMESTAMPTZ（时区感知）
  • 使用UUID作为主键的替代方案：
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100),
    created_at TIMESTAMPTZ
);

2. 数据类型转换

def mysql_to_pg_type(mysql_type):
    mapping = {
        'tinyint': 'SMALLINT',
        'smallint': 'SMALLINT',
        'mediumint': 'INTEGER',
        'int': 'INTEGER',
        'bigint': 'BIGINT',
        'decimal': 'NUMERIC',
        'datetime': 'TIMESTAMPTZ',
        'timestamp': 'TIMESTAMPTZ',
        'text': 'TEXT',
        'blob': 'BYTEA'
    }
    return mapping.get(mysql_type, mysql_type)

3. 事务处理迁移

-- MySQL事务
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- PostgreSQL事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

五、完整案例

电商系统迁移案例

原始MySQL表结构：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10,2),
    INDEX idx_customer (customer_id)
);

PostgreSQL转换：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date TIMESTAMPTZ,
    total_amount NUMERIC(10,2),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
);

数据迁移脚本：

import psycopg2
import mysql.connector

# 连接配置
mysql_config = {
    'user': 'root',
    'password': 'password',
    'host': 'localhost',
    'database': 'mysql_db'
}

pg_config = {
    'dbname': 'postgres_db',
    'user': 'postgres',
    'password': 'password',
    'host': 'localhost',
    'port': '5432'
}

# 数据迁移
def migrate_data():
    mysql_conn = mysql.connector.connect(**mysql_config)
    pg_conn = psycopg2.connect(**pg_config)
    
    mysql_cursor = mysql_conn.cursor()
    pg_cursor = pg_conn.cursor()
    
    # 获取表结构
    mysql_cursor.execute("SHOW CREATE TABLE orders")
    create_table_sql = mysql_cursor.fetchone()[1]
    
    # 转换表结构
    pg_cursor.execute(create_table_sql.replace('AUTO_INCREMENT', 'SERIAL'))
    pg_conn.commit()
    
    # 迁移数据
    mysql_cursor.execute("SELECT * FROM orders")
    rows = mysql_cursor.fetchall()
    
    for row in rows:
        pg_cursor.execute(
            "INSERT INTO orders (customer_id, order_date, total_amount) VALUES (%s, %s, %s)",
            (row[1], row[2], row[3])
        )
    
    pg_conn.commit()
    mysql_conn.close()
    pg_conn.close()

六、源码解析

关键转换逻辑：

def convert_sql(sql):
    # 处理自增主键
    if 'AUTO_INCREMENT' in sql:
        sql = sql.replace('AUTO_INCREMENT', 'SERIAL')
    
    # 处理datetime类型
    if 'DATETIME' in sql:
        sql = sql.replace('DATETIME', 'TIMESTAMPTZ')
    
    # 处理decimal类型
    if 'DECIMAL' in sql:
        sql = sql.replace('DECIMAL', 'NUMERIC')
    
    return sql

索引处理：

-- MySQL索引
CREATE INDEX idx_customer ON orders (customer_id);

-- PostgreSQL索引
CREATE INDEX idx_customer ON orders (customer_id);

七、进阶使用

1. 分区表策略

-- 按时间分区
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date DATE,
    amount NUMERIC(10,2)
) PARTITION BY RANGE (sale_date);

-- 分区定义
CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

2. 函数索引

-- 创建函数索引
CREATE INDEX idx_json_search ON orders 
    USING GIN(to_jsonb(order_details));

八、性能与工程实践

1. 性能优化策略

  • 索引优化：使用部分索引、覆盖索引
  • 分区策略：按时间/地域划分
  • 配置调优：调整shared_buffers、work_mem参数
  • 并行查询：使用并行查询加速大数据量处理

2. 安全考虑

  • SSL连接：sslmode=require配置
  • 行级权限：GRANT SELECT ON orders TO user
  • 数据加密：使用pgcrypto模块

3. 异常处理

DO $$
BEGIN
    BEGIN
        -- 执行可能出错的操作
        UPDATE orders SET total_amount = 100 WHERE order_id = 1;
    EXCEPTION WHEN others THEN
        -- 异常处理逻辑
        RAISE NOTICE 'Error occurred: %', SQLERRM;
        -- 回滚事务
        ROLLBACK;
    END;
END $$;

九、常见问题与踩坑

1. 典型错误案例

-- 错误示例：未处理时区转换
SELECT * FROM orders WHERE order_date > '2023-01-01';

问题：PostgreSQL的TIMESTAMPTZ类型会自动转换时区，可能导致查询结果不准确

解决方案：

-- 正确处理时区
SELECT * FROM orders 
WHERE order_date AT TIME ZONE 'UTC' > '2023-01-01';

2. 索引失效问题

-- 错误示例：使用函数索引失效
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023;

原因：未使用TO_CHAR函数

解决方案：

-- 正确使用索引
SELECT * FROM orders 
WHERE TO_CHAR(order_date, 'YYYY') = '2023';

十、最佳实践

  1. 迁移工具选择：

    • pgloader：适合大规模数据迁移
    • mysqldump + psql：适合小规模迁移
    • ETL工具：适合复杂数据转换

  2. 数据校验策略：

    • 使用CHECKSUM校验数据完整性
    • 使用pg_trgm扩展进行文本相似度校验

  3. 版本兼容性：

    • MySQL 5.7 → PostgreSQL 11
    • MySQL 8.0 → PostgreSQL 14
    • 注意JSON类型处理差异

  4. 监控策略：

    • 使用pg_stat_activity监控连接
    • 使用pg_stat_statements监控查询性能

十一、总结

MySQL迁移到PostgreSQL是一项复杂的系统工程，需要深入理解两者的底层差异。本文从表结构转换、数据类型处理、事务机制、索引优化等多个维度进行了深入探讨。在实际应用中，当需要处理复杂查询、高并发写入、大规模数据时，PostgreSQL的MVCC机制和丰富的索引类型会带来显著优势。但需要注意：对于简单的CRUD操作，MySQL的简单性可能更具优势。迁移过程中要特别注意时区转换、索引失效、数据一致性等常见问题，通过合理的性能优化和安全措施，可以确保迁移的平滑进行。

评论已关闭

推荐阅读

AIGC实战——Transformer模型
2024年12月01日
python , AI , 机器学习
Socket TCP 和 UDP 编程基础（Python）
2024年11月30日
python , tcp , udp
如何使用 ChatGPT 进行学术润色？你需要这些指令
2024年12月01日
AI
最新 Python 调用 OpenAi 详细教程实现问答、图像合成、图像理解、语音合成、语音识别（详细教程）
2024年11月24日
python , AI
ChatGPT 和 DALL·E 2 配合生成故事绘本
2024年12月01日
python , AI
omegaconf，一个超强的 Python 库！
2024年11月24日
python
【视觉AIGC识别】误差特征、人脸伪造检测、其他类型假图检测
2024年12月01日
python , AI
［超级详细］如何在深度学习训练模型过程中使用 GPU 加速
2024年11月29日
python , AI , 机器学习
Python 物理引擎pymunk最完整教程
2024年11月27日
python
MediaPipe 人体姿态与手指关键点检测教程
2024年11月27日
python
深入了解 Taipy：Python 打造 Web 应用的全面教程
2024年11月26日
python
基于Transformer的时间序列预测模型
2024年11月25日
python , Matplotlib , 机器学习
Python在金融大数据分析中的AI应用（股价分析、量化交易）实战
2024年11月25日
python , Matplotlib , 机器学习
AIGC Gradio系列学习教程之Components
2024年12月01日
python , AI
Python3 `asyncio` — 异步 I/O，事件循环和并发工具
2024年11月30日
python
llama-factory SFT系列教程：大模型在自定义数据集 LoRA 训练与部署
2024年12月01日
python , AI , 机器学习
Python 多线程和多进程用法
2024年11月24日
python
Python socket详解，全网最全教程
2024年11月27日
python , http , websocket
python之plot()和subplot()画图
2024年11月26日
python , Matplotlib
理解 DALL·E 2、Stable Diffusion 和 Midjourney 工作原理
2024年12月01日
python , AI , 机器学习
Copyright © 2026 Tech Blog
All Right Reserved | 京ICP备19053165号