MySQL 数据迁移到 PostgreSQL 实战教程

大家好，我是讯享网，很高兴认识大家。这里提供最前沿的Ai技术和互联网信息。

写在前面：

在数据库国产化或架构升级的过程中，从 MySQL 迁移到 PostgreSQL 是常见需求。MySQL 8.0 由于引入了新的字符集校验规则，传统的迁移工具（如pgloader）常会遇到兼容性报错。本文将分享两种实战方案：pgloader 自动化方案 （适合旧版 MySQL）以及 Python 自研脚本方案（适合 MySQL 8.0 且解决长度超限问题）。

虽然本文以 Linux 服务器 环境为主要演示背景，但其中提供的 Python 迁移方案具有天然的跨平台性。无论你的开发环境是 Windows 10/11、macOS 还是 Linux，只需配置好 Python 环境，即可实现零成本平替迁移。

💡注意：本文演示的 MySQL 和 PostgreSQL 在同一个服务器中。

1、系统要求

Linux 方案：适用于 Ubuntu、CentOS、Debian 等主流发行版。
Windows 方案：适用于 Windows 10/11，建议使用 PowerShell 或 CMD 执行。

2、安装Python依赖

（1）安装pip（如果没有）

Linux系统：

 
  
    
     
     sudo apt-get install -y python3-pip 
    
 Windows:系统：安装 Python 时勾选 "Add to PATH"，pip 会自动安装。

（2）安装依赖（两个系统相同）

无论在哪个系统下，执行前请统一安装必要的数据库驱动：

 
  
    
     
     pip install mysql-connector-python psycopg2-binary

💡注意：Windows 用户如遇到psycopg2安装失败，请确保已安装Visual C++ Build Tools，或者直接使用pip install psycopg2-binary。

3、安装pgloader

pgloader是一款基于 Common Lisp 开发的开源工具，它能够解析 MySQL 的 DDL 并自动在 PostgreSQL 中重建表结构，通过COPY命令进行流式迁移，速度极快。

（1）Linux系统（以 Ubuntu/CentOS 为例）

Linux 是pgloader的原生运行环境，安装最为简便。

Ubuntu/Debian 系列：

 
  
    
     
     sudo apt-get update  
     sudo apt-get install -y pgloader

 CentOS/RHEL 系列：

CentOS 官方源通常不包含 pgloader，建议通过 EPEL 源安装或直接从 GitHub 下载二进制包：

 
  
    
     
     # 安装 EPEL 源  
     sudo yum install -y epel-release 
     安装 pgloader 
     sudo yum install -y pgloader

 （2）Windows系统（借助Docker或WSL）

方法 A：使用 Docker

如果你的 Windows 安装了 Docker Desktop，只需一行命令即可运行，无需配置环境变量：

 
  
    
     
     # 从 Docker Hub 拉取镜像  
     docker pull dimitri/pgloader:latest 
     运行迁移命令（通过挂载本地配置文件） 
     docker run –rm -v C:your_path:/data dimitri/pgloader:latest pgloader /data/migrate.load

 方法 B：使用 WSL (Windows Subsystem for Linux)

如果你启用了 WSL（建议 Ubuntu 22.04 LTS），可以在 WSL 的终端中直接按照 Linux 的方式安装：

 
  
    
     
     sudo apt update  
     sudo apt install pgloader

 4、网络与权限校验

（1）MySQL 端：开启远程访问与迁移账号

MySQL 默认通常只监听127.0.0.1，且 root 账号默认禁止远程登录。编辑 MySQL配置文件修改监听配置：

【Linux系统】

Ubuntu/Debian : /etc/mysql/mysql.conf.d/mysqld.cnf
CentOS/RHEL : /etc/my.cnf 或 /etc/mysql/my.cnf

使用vim或者nano开始管理员权限编辑：

 
  
    
     
     sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 
    
 修改关键行：

按下 i 进入编辑模式，找到 [mysqld] 模块下的bind-address：

原配置：bind-address = 127.0.0.1（仅限本机）
修改后：bind-address = 0.0.0.0（允许所有 IP 访问）

💡注意：如果该行前面有 #（注释符），请删掉它。

保存并重启服务：按下Esc ，输入:wq保存后退出，然后执行：

 
  
    
     
     # 重启服务  
     sudo systemctl restart mysql 
     检查是否监听成功（看到 0.0.0.0:3306 即成功） 
     sudo netstat -plnt | grep 3306

 【Windows系统】

在 Windows 中，MySQL 通常作为服务运行，配置文件通常位于安装目录或隐藏的 ProgramData 目录下。

定位配置文件my.ini，通常位于以下路径之一：

C:Program FilesMySQLMySQL Server 8.xmy.ini
C:ProgramDataMySQLMySQL Server 8.xmy.ini（注意：ProgramData 是隐藏文件夹）

在开始菜单搜索"记事本"，点击以管理员身份运行 ，在记事本中打开该文件，修改关键行bind-address，如果没有这一行，可以在 [mysqld] 标签下手动添加一行：

 
  
    
     
     bind-address = 0.0.0.0 
    
 如果有，将其值从 127.0.0.1 改为 0.0.0.0。

重启MySQL服务，按下Win+R，输入services.msc并回车，在列表中找到MySQL 或 MySQL80，右键点击，选择重启。

（2）PostgreSQL 端：配置访问策略与目标库

PostgreSQL 的权限校验分为物理连接（hba文件）和逻辑权限（角色授权）两层。

除了修改pg_hba.conf，还需要确保 PostgreSQL 正在监听所有 IP 地址，否则远程连接会被拒绝。

不同系统查找配置文件路径：

Linux (Ubuntu/Debian): /etc/postgresql/[版本号]/main/
Linux (CentOS/RHEL): /var/lib/pgsql/[版本号]/data/
Windows: C:Program FilesPostgreSQL[版本号]data

【Linux系统】

修改监听地址，编辑 postgresql.conf：

 
  
    
     
     sudo vim /etc/postgresql/15/main/postgresql.conf 
    
 找到listen_addresses 行，修改为：listen_addresses = '*'

修改访问策略，编辑 pg_hba.conf：

 
  
    
     
     sudo vim /etc/postgresql/15/main/pg_hba.conf 
    
 配置示例（修改为md5）： 
  
    
     
      # TYPE DATABASE USER ADDRESS METHOD  
     # IPv4 本地连接 host all all 127.0.0.¹⁄₃₂ md5

💡认证方式说明：

方式说明适用场景 trust 无需密码开发环境，本地测试 md5 MD5 密码认证通用，推荐 scram-sha-256 SHA-256 密码认证高安全要求 peer 系统用户匹配 Linux 本地连接 sspi Windows 集成认证 Windows 域环境

重启服务：

 
  
    
     
     sudo systemctl restart postgresql 
    
 【Windows系统】

修改监听地址：使用管理员权限打开记事本，打开 data 目录下的 postgresql.conf，搜索 listen_addresses，取消注释并将值改为 '*'。

修改访问策略：用记事本打开 data 目录下的 pg_hba.conf，配置示例（修改为md5）：

 
  
    
     
      # TYPE DATABASE USER ADDRESS METHOD  
     # IPv4 本地连接 host all all 127.0.0.¹⁄₃₂ md5

 重启服务，按下Win+R，输入services.msc，找到 PostgreSQL-x64-[版本号]，右键选择"重新启动"。

新建数据库与用户部分在各系统上操作一致，均需要通过 psql 命令行工具或图形化界面（如PgAdmin）完成。

进入PostgreSQL终端：

Linux: 执行 sudo -u postgres psql。
Windows : 在开始菜单搜索 SQL Shell (psql) 并打开，或者在 CMD 中输入 psql -U postgres。

执行权限配置SQL，在 postgres=#提示符下，以此输入并执行以下语句：

 
  
    
     
     -- 1. 创建目标数据库（存放迁移过来的数据）  
     CREATE DATABASE database_name; 
     – 2. 创建一个拥有登录权限的专用迁移用户 CREATE USER pg_migrator WITH PASSWORD ‘YourStrongPassword’; 
     – 3. 将数据库的所有权赋予该用户（这允许该用户创建表、索引等） ALTER DATABASE database_name OWNER TO pg_migrator; 
     – 4. 关键：连接到该目标数据库进行内部授权 c database_name 
     – 5. 授予该用户在 public 模式下的所有权限（确保可以创建表结构） GRANT ALL PRIVILEGES ON SCHEMA public TO pg_migrator;

 验证配置是否成功：

在你的迁移发起机器上（Windows 或 Linux），尝试用 psql 远程登录：

 
  
    
     
     # 格式：psql -h [服务器IP] -U pg_migrator -d database_name  
     psql -h 172.27.XX.XX -U pg_migrator -d database_name

 如果能弹出密码输入提示，则说明权限与网络配置已完美打通！

创建 migrate.load文件：

 
  
    
     
     LOAD DATABASE FROM mysql://user::3306/database_name INTO pgsql://user::5432/database_name  
     WITH create tables, 
      create indexes, reset sequences  
     CAST type datetime to timestamptz, 
      type date to date  
     BEFORE LOAD DO 
      $$ DROP SCHEMA IF EXISTS public CASCADE; $$, $$ CREATE SCHEMA public; $$;

💡解释.load文件：

 
  
    
     
     LOAD DATABASE FROM mysql://user::3306/database_name INTO pgsql://user::5432/database_name

FROM : 指定源数据库的连接字符串。注意格式为 mysql://用户名:密码@主机名:端口/数据库名。

INTO: 指定目标 PostgreSQL 的连接字符串。

💡 提示 ：如果密码包含特殊字符（如 @ 或 :），需要进行 URL 编码。

create tables: 自动根据 MySQL 的表结构在 PostgreSQL 中创建对应的表。它会自动处理表名映射和基本类型的转换。

create indexes: 在数据装载完成后，自动在 PostgreSQL 中重建索引。

优化逻辑：pgloader 通常先导数据再建索引，这样比带索引导数据的效率高得多。

reset sequences: 迁移完成后，自动计算每个表主键的最大值，并更新 PostgreSQL 的序列（Sequence）。这样能确保后续业务插入新数据时，自增 ID 不会冲突。

type datetime to timestamptz : 将 MySQL 的 datetime 类型转换为 PostgreSQL 带时区的日期时间类型（timestamp with time zone）。这对于处理跨时区业务非常重要。

type date to date : 显式指定 date 类型保持不变（通常 pgloader 会默认处理，这里写出来是为了确保准确性）。

DROP SCHEMA ... CASCADE : 删除 PostgreSQL 中现有的 public 模式。CASCADE 表示级联删除该模式下的所有表、视图和函数。

CREATE SCHEMA public : 重新创建一个空的 public 模式。

\)$ 符号: 这是 PostgreSQL 中定义字符串常量的符号，在这里用于包裹原始 SQL 语句，防止与 pgloader 自身的语法冲突。

执行迁移：

 
  
    
     
     pgloader migrate.load

💡注意：pgloader 3.6.10 版本对 MySQL 8.0 支持不佳，可能报错 76 fell through ECASE expression，如遇此错误请使用方法二。

核心逻辑图：

完整代码migrate.py：

 
  
    
     
     import mysql.connector  
     import psycopg2 import sys 
     — 1. 配置参数 — 
     MYSQL_CFG = { 
     'host': '172.27.XX.XX', 'user': 'user_name', 'password': 'password', 'database': 'database_name', 'auth_plugin': 'mysql_native_password'  
     } 
     PG_CFG = { 
     'host': '172.27.XX.XX', 'port': '5432', 'database': 'database_name', 'user': 'user_name', 'password': 'password'  
     } 
     — 2. 字段类型转换逻辑 (已针对长度超限优化) — 
     def get_pg_type(mysql_type): 
     t = mysql_type.lower() # 强制将所有字符类型转为 TEXT，解决 "值太长" 的问题 if 'char' in t or 'text' in t: return 'TEXT' if 'tinyint(1)' in t: return 'BOOLEAN' if 'tinyint' in t or 'smallint' in t: return 'SMALLINT' if 'mediumint' in t or 'int' in t: return 'INTEGER' if 'bigint' in t: return 'BIGINT' if 'float' in t: return 'REAL' if 'double' in t: return 'DOUBLE PRECISION' if 'decimal' in t: return 'NUMERIC' if 'datetime' in t or 'timestamp' in t: return 'TIMESTAMP' if 'date' in t: return 'DATE' if 'blob' in t: return 'BYTEA' if 'json' in t: return 'JSONB' return 'TEXT'  
     — 3. 迁移主程序 — 
     def run_migration(): 
     try: print(">> 正在连接 MySQL...") mysql_conn = mysql.connector.connect(MYSQL_CFG) mysql_cur = mysql_conn.cursor(dictionary=True) print(">> 正在连接 PostgreSQL...") pg_conn = psycopg2.connect(PG_CFG) pg_conn.autocommit = True pg_cur = pg_conn.cursor() mysql_cur.execute("SHOW TABLES") tables = [list(t.values())[0] for t in mysql_cur.fetchall()] for table in tables: print(f"  
      mysql_cur.execute(f"DESCRIBE `{table}`") cols = mysql_cur.fetchall() col_defs = [] col_names = [] pk_col = None for c in cols: name = c['Field'] col_names.append(name) t = get_pg_type(c['Type']) if 'auto_increment' in c['Extra'].lower(): t = 'SERIAL' if c['Key'] == 'PRI' and not pk_col: pk_col = name nullable = 'NOT NULL' if c['Null'] == 'NO' else '' col_defs.append(f'"{name}" {t} {nullable}') if pk_col: col_defs.append(f'PRIMARY KEY ("{pk_col}")') # 重建结构 pg_cur.execute(f'DROP TABLE IF EXISTS "{table}" CASCADE') pg_cur.execute(f'CREATE TABLE "{table}" ({", ".join(col_defs)})') # 批量读取并迁移数据 mysql_cur.execute(f"SELECT * FROM `{table}`") rows = mysql_cur.fetchall() if rows: placeholders = ', '.join(['%s'] * len(col_names)) insert_query = f'INSERT INTO "{table}" ("' + '","'.join(col_names) + f'") VALUES ({placeholders})' success_count = 0 for row in rows: vals = [row[n] for n in col_names] try: pg_cur.execute(insert_query, vals) success_count += 1 except Exception as e: # 如果还报错，打印具体的数据内容以便排查 print(f" ! 失败行数据参考: {vals[:1]}...") print(f" ! 错误详情: {e}") print(f" ✅ 同步完成: {success_count} 行") print("  
     ✨ 迁移任务全部结束!") 
     except Exception as err: print(f"  
     ❌ 致命错误: {err}") 
     finally: if 'mysql_conn' in locals() and mysql_conn.is_connected(): mysql_conn.close() if 'pg_conn' in locals(): pg_conn.close()  
     if name == "main": 
     run_migration() 
    
 运行脚本：

Linux系统：

 
  
    
     
     python3 migrate.py 
    
 Windows系统： 
  
    
     
     python migrate.py

欢迎交流！！🌹🌹

MySQL 数据迁移到 PostgreSQL 实战教程

1、系统要求

2、安装Python依赖

3、安装pgloader

安装 pgloader

运行迁移命令（通过挂载本地配置文件）

4、网络与权限校验

检查是否监听成功（看到 0.0.0.0:3306 即成功）

— 1. 配置参数 —

— 2. 字段类型转换逻辑 (已针对长度超限优化) —

— 3. 迁移主程序 —

相关推荐