mysql和pg等数据库之间的数据迁移实战分享

mysql和pg等数据库之间的数据迁移是常见的问题：比如一开始使用Oracle，后来想使用mysql，而且需要把Oracle数据库的数据迁移到mysql里面；后期有想使用pg数据库，同时需要把Mysql数据库的数据迁移到pgl里面，等等诸如此类数据迁移，都是一个重要问题。基于此，我们今天分享mysql数据迁移到pg数据库的案例。方案如下：

一、客户端迁移

如果数据量比较小，并且win电脑端可以连上数据库，此种情况下可以使用客户端迁移数据，比如使用Navicat 15及以上版本。现在开始演示：

1、工具栏中选择数据传输

2、选择数据源和目标

点击下一步进入 

3、选择表结构

自定义选择或者选择全部数据，再点击下一步，就可以开始传输数据了，如果有错误，有日志实时打印。 

4、创建自增序列

msql数据库迁移到pg之后，字段类型都改成相应的字段了，比如：

常见数据类型的映射：

• TINYINT -> SMALLINT
• INT -> INTEGER
• BIGINT -> BIGINT
• VARCHAR -> VARCHAR
• TEXT -> TEXT
• DATETIME -> TIMESTAMP
• DECIMAL -> NUMERIC

只有主键自增这一块，需要单独处理一下：即每张表的主键添加自增序列

参考文章

二、pgLoader 工具迁移

pgloader 使用文档

1、安装 pgLoader

首先，确保你的系统上安装了 pgLoader。你可以使用包管理器安装，也可以从源代码编译安装。

Ubuntu/Debian 环境
sudo apt-get install pgloader

或者 CentOS/RHEL 环境

sudo yum install pgloader

2、从源代码编译安装

如果你的发行版没有直接提供 pgLoader 包，你可以从源代码安装：

git clone https://github.com/dimitri/pgloader.git
cd pgloader
make build
sudo make install

下载地址

3、Docker 镜像安装

docker pull dimitri/pgloader
运行
docker run --rm dimitri/pgloader:latest pgloader --version

3、命令查看 pgLoader 是否安装成功：

pgloader --version

4、准备 MySQL 和 PostgreSQL 数据库

确保你的 MySQL 数据库和 PostgreSQL 数据库已经准备好，并且你有相应的访问权限。

• MySQL: 确保你知道 MySQL 数据库的连接信息（用户名、密码、数据库名、主机名、端口）。
• PostgreSQL: 确保目标 PostgreSQL 数据库已经创建好，并且你有相应的访问权限。

5、使用 pgLoader 迁移数据

pgloader mysql://mysql_user:mysql_password@mysql_host/mysql_dbname postgresql://pg_user:pg_password@pg_host/pg_dbname

参数注释：

• mysql_user: MySQL 用户名
• mysql_password: MySQL 密码
• mysql_host: MySQL 服务器地址（例如 localhost 或 IP 地址）
• mysql_dbname: 要迁移的 MySQL 数据库名
• pg_user: PostgreSQL 用户名
• pg_password: PostgreSQL 密码
• pg_host: PostgreSQL 服务器地址（例如 localhost 或 IP 地址）
• pg_dbname: 目标 PostgreSQL 数据库名

三、手动导入

1、使用 mysqldump 导出 SQL 脚本

mysqldump -u [username] -p[password] --compatible=postgresql --no-create-info [database_name] > mysql_data.sql

此命令将生成一个 mysql_data.sql 文件，其中包含 MySQL 表的数据，且格式与 PostgreSQL 兼容。 

2、创建 PostgreSQL 表

根据 MySQL 表的结构，在 PostgreSQL 中创建相应的表。需要注意的是，MySQL 和 PostgreSQL 在数据类型方面有一些差异。以下是一些常见数据类型的映射：

• TINYINT -> SMALLINT
• INT -> INTEGER
• BIGINT -> BIGINT
• VARCHAR -> VARCHAR
• TEXT -> TEXT
• DATETIME -> TIMESTAMP
• DECIMAL -> NUMERIC

例如，假设 MySQL 中有以下表：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    salary DECIMAL(10, 2),
    hired_date DATETIME
);

则在 PostgreSQL 中创建相应的表：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    salary NUMERIC(10, 2),
    hired_date TIMESTAMP
);

3、导入数据到 PostgreSQL

psql -U [username] -d [database_name] -f mysql_data.sql

最后，检查数据是否正确迁移到 PostgreSQL，确保表结构、数据类型和数据内容都无误。 

到此，不同数据库之间的数据迁移暂时分享完成，后面我们会持续更新更新的迁移方案，敬请期待！