[分享] 迁移 MySQL 到 PostgreSQL 的具体实现

前面讲述了通过何种方式将 MySQL 迁移到 PostgreSQL,本次将给大家介绍详细的操作步骤和示例。

介绍


pgloader 将各种来源的数据加载到 PostgreSQL 中。它可以动态转换它读取的数据,并在加载前后提交原始 SQL。它使用COPY PostgreSQL 协议将数据流式传输到服务器,并通过填充一对reject.datreject.log文件来管理错误。

由于能够直接从数据库源加载数据,pgloader 还支持从其他产品迁移到 PostgreSQL。在这种操作模式下,pgloader 在单个无人命令中处理迁移的架构和数据部分,从而允许实现持续迁移

pgloader 有两种操作模式:从文件加载,迁移数据库。在这两种情况下,pgloader 都使用 PostgreSQL COPY 协议,该协议实现了以非常有效的方式发送数据。

pgloader 支持多种 RDBMS 数据库作为迁移源,并通过从创建的数据库连接中获取目录表,然后在 PostgreSQL 中创建相同的方案。

目前支持以下操作:

  • 从 MySQL 迁移到 PostgreSQL
  • 从 SQLite 迁移到 PostgreSQL
  • 从 MS SQL Server 迁移到 PostgreSQL

功能特性

在单个命令中迁移完整数据库时,pgloader 实现以下功能:

  • 一键迁移

    整个迁移从一个命令行开始,然后在无人看管的情况下运行。pgloader 旨在集成到一个完全自动化的工具中,您可以根据需要多次重复该工具。

  • 模式发现

    使用其 SQL 目录对源数据库进行内省,以获取表、属性(具有数据类型、默认值、非空约束等)、主键约束、外键约束、索引、注释等的列表。这提供了一个内部要从源数据库迁移到目标数据库的所有对象的数据库目录。

  • 用户定义的铸造规则

    某些源数据库对其数据类型的想法可能与等效数据类型的 PostgreSQL 实现不兼容。例如,SQLite 从版本 3 开始就有一个动态类型系统,这当然与Relation的想法不兼容。或者 MySQL 接受零年的日期时间,这在我们的日历中不存在,并且没有布尔数据类型。从其他源数据库技术迁移到 PostgreSQL 时,必须做出数据类型转换选择。pgloader 实现了您可以依赖的可靠默认值,以及针对特定情况的用户定义数据类型转换规则的工具。这个想法是允许用户指定应该如何进行迁移,以便它可以重复并包含在持续迁移过程中。

  • 动态数据转换

    用户定义的转换规则伴随着数据的动态重写。例如0000-00-00,默认情况下,零日期(不仅仅是年份,MySQL 接受 作为有效日期时间)被重写为 NULL 值。

  • 部分迁移

    可以在迁移中仅包含源数据库表的部分列表,或者排除源数据库中的某些表。

  • 仅架构,仅数据

    这是pgloader的ORM 兼容性功能,可以使用您的 ORM 创建模式,然后让 pgloader 迁移针对此已创建模式的数据。这样做时,pgloader 可以重新索引目标模式:在使用 COPY 将数据从源数据库加载到 PostgreSQL 之前,pgloader 删除索引和约束,并在加载数据后重新安装完全相同的定义。以这种方式运行的原因当然是数据加载性能。

  • 可重复 (DROP+CREATE)

    默认情况下,pgloader 在发出任何 CREATE 语句之前在目标 PostgreSQL 数据库中发出 DROP 语句,以便您可以根据需要多次重复迁移,直到迁移规范和规则没有错误。在代码迁移项目的整个持续时间内,将数据迁移计划为每晚运行(甚至更频繁!)。有关该 方法的更多详细信息,请参阅 持续迁移方法。

  • 出错时停止/出错时继续下一步

    从数据库迁移时 pgloader 的默认行为是 . 这个想法是让用户修复迁移规范或源数据,然后再次运行该过程,直到它起作用为止。on error stop在某些情况下,源数据损坏得无法完全迁移,因此可能有必要采取这样的选择,即保存被 PostgreSQL 拒绝的行并继续迁移其他行。on error resume next

  • Pre/Post SQL 命令、Post-Schema SQL 命令

    虽然 pgloader 负责将架构重写为 PostgreSQL 期望,甚至为此提供用户定义的数据类型转换规则 支持,但有时需要在迁移周围添加一些特定的 SQL 命令。它当然由 pgloader 本身支持,无需围绕它编写脚本。

  • 在线 ALTER 模式

    有时,迁移到 PostgreSQL 也是一个很好的机会,可以检查和修复过去做出的错误决策,或者只是与 PostgreSQL 无关的决策。pgloader 命令语法允许 ALTER pgloader 的目标目录的内部表示,以便可以创建与源模式稍有不同的目标模式。支持的更改包括针对不同的架构名称。

  • 物化视图或模式重写

    在某些情况下,模式重写不仅仅是将 SQL 对象重命名为完整的规范化练习。因为 PostgreSQL 擅长在大多数工作负载下在生产中运行规范化模式。pgloader 通过允许从视图定义迁移,在动态模式重写中实现了完全的灵活性。视图属性列表成为PostgreSQL中的表定义,通过在源系统上查询视图来获取数据。SQL 视图允许使用 SELECT 投影子句在列级别实现内容过滤,并使用 WHERE 限制子句在行级别实现内容过滤。由于 JOIN,从参考表回填。

  • 分发到 Citus

    从 PostgreSQL 迁移到 Citus 时,该过程的一个重要部分包括将架构调整为分发键。阅读 Citus 文档中的Preparing Tables and Ingesting Data以获取展示如何执行此操作的完整示例。使用 pgloader 时,可以指定分布键和引用表,并让 pgloader 自行调整表、索引、主键和外键定义。

  • 编码覆盖

    MySQL 实际上并不强制对数据库中的数据进行编码以匹配元数据中已知的编码,这些编码是在数据库、表或属性级别定义的。有时,为了理解文本,有必要覆盖元数据,而 pgloader 可以很容易地做到这一点。

Feature SQLite MySQL MS SQL PostgreSQL Redshift
One-command migration
Continuous Migration
Schema discovery
Partial Migrations
Schema only
Data only
Repeatable (DROP+CREATE)
User defined casting rules
Encoding Overrides
On error stop
On error resume next
Pre/Post SQL commands
Post-Schema SQL commands
Primary key support
Foreign key support
Online ALTER schema
Materialized views
Distribute to Citus

迁移 MySQL 到 PostgreSQL

将数据从 MySQL 迁移到 PostgreSQL 主要通过在线迁移的方式实现,即通过同时连接 MySQL 和 PostgreSQL 实现在线实时的数据转换和传输。在传输过程中pgloader支持动态转换源数据库的schema和索引等的构建。
将数据从 MySQL 迁移到 PostgreSQL 主要通过在线迁移的方式实现,即通过同时连接 MySQL 和 PostgreSQL 实现在线实时的数据转换和传输。在传输过程中pgloader支持动态转换源数据库的schema和索引等的构建。

请从此处获取pgloader程序。

  1. 安装 pgloader

    yum install -y sudo openssl-libs
    ln -s /usr/lib64/libcrypto.so.1.0.2k /usr/lib64/libcrypto.so
    yum install -y pgloader
    
  2. 验证可用性

    如果报错,很有可能是缺少运行所必须的库文件,报错中一般会提示出来

    pgloader --help
    

迁移到自管理的 PostgreSQL

  1. 创建参数文件~postgres/commands.load
    这是一种只迁移数据的方式,不迁移元数据,前提是自管理的数据库中所有表结构已经初始化完成。
  • no drop 不删除表 、truncate 截断表 、disable triggers 禁用触发器、create no tables 不创建表、create no indexes 不创建索引、preserve index names 保留原有索引命令、no foreign keys不创建外键
  • data only 仅迁移数据
  • 此配置文件需要在 PostgreSQL 数据库侧执行,采用了本地连接数据库的方式进行连接,如果采用远程连接的方式请修改为如下postgresql://gitlab-psql:password@host:port/gitlabhq_production
LOAD DATABASE
     FROM mysql://username:password@host/gitlabhq_production
     INTO postgresql://gitlab-psql@unix://var/opt/gitlab/postgresql:/gitlabhq_production

WITH include no drop, truncate, disable triggers, create no tables,
     create no indexes, preserve index names, no foreign keys,
     data only

SET MySQL PARAMETERS
net_read_timeout = '90',
net_write_timeout = '180'

ALTER SCHEMA 'gitlabhq_production' RENAME TO 'public'

;
  1. 开始迁移
# 命令文件 commands.load 位于目录 ~gitlab-psql
cd ~gitlab-psql
sudo -u gitlab-psql -H pgloader commands.load
  1. 查看迁移结果
    因为数据转换过程中会执行数据类型的转换,可能会出现数据类型转换的警告,一般忽略即可。
2021-12-02T12:22:14.026000+08:00 LOG pgloader version "3.6.1"
2021-12-02T12:22:14.078000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://git@rm-j6csdphu1mfou7592.mysql.rds.aliyuncs.com:3306/gitlabhq_production {1008BB8983}>
2021-12-02T12:22:14.078000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://gitlab-psql@unix://var/run/postgresql:5432/gitlabhq_production {1008BBA093}>
2021-12-02T12:22:14.823000+08:00 WARNING Source column "public"."abuse_reports"."id" is casted to type "bigserial" which is not the same as "integer", the type of current target database column "public"."abuse_reports"."id".
2021-12-02T12:22:14.823000+08:00 WARNING Source column "public"."abuse_reports"."reporter_id" is casted to type "bigint" which is not the same as "integer", the type of current target database column "public"."abuse_reports"."reporter_id".
......
2021-12-02T12:22:15.676000+08:00 LOG report summary reset
                                 table name     errors       rows      bytes      total time
-------------------------------------------  ---------  ---------  ---------  --------------
                            fetch meta data          0         75                     0.063s
                                   Truncate          0         75                     0.449s
-------------------------------------------  ---------  ---------  ---------  --------------
                       public.abuse_reports          0          0                     0.031s
                         public.appearances          0          0                     0.023s
                public.application_settings          0          1     0.4 kB          0.021s
                                     ......
                               public.todos          0          1     0.1 kB          0.117s
                             public.uploads          0          0                     0.141s
                   public.u2f_registrations          0          0                     0.125s
                 public.users_star_projects          0          1     0.0 kB          0.141s
                               public.users          0          2     0.8 kB          0.120s
                           public.web_hooks          0          0                     0.130s
                  public.user_agent_details          0          1     0.2 kB          0.130s
-------------------------------------------  ---------  ---------  ---------  --------------
                    COPY Threads Completion          0          4                     0.317s
                            Reset Sequences          0         73                     0.028s
                           Install Comments          0          0                     0.000s
-------------------------------------------  ---------  ---------  ---------  --------------
                          Total import time          ✓        670    32.8 kB          0.345s
    1. 通过页面或者数据库验证。
      注意: pgloader 不支持 PostgreSQL 12 or later 的版本采用只迁移数据的方式。会出现以下报错,此问题是因为 pgloader 在获取表字段缺省值时失败,从 PostgreSQL 12 开始 pg_attrdef.adsrc已经被移除。请采用以下方式进行迁移。
[root@upgrade ~]# pgloader pg1.load
2021-12-02T16:12:13.058000+08:00 LOG pgloader version "3.6.1"
2021-12-02T16:12:13.190000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://git@localhost:3306/gitlabhq_production {1008C1CA13}>
2021-12-02T16:12:13.190000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://git@unix://var/run/postgresql:5432/gitlabhq_production {1008C1DE53}>
2021-12-02T16:12:14.587000+08:00 ERROR Database error 42703: column "adsrc" does not exist
....

迁移到外部的 PostgreSQL

  1. 准备 PostgreSQL 数据库
    如果使用外置的数据库,那么整个准备过程需要执行以下操作
# 添加yum仓库
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 安装数据库
yum install postgresql13-server postgresql13-contrib

# 初始化数据库
/usr/bin/postgresql-13-setup initdb 

# 启动数据库
systemctl start postgresql-13
systemctl enable postgresql-13

# 创建用户
## 在后面的版本中常用的数据库用户是 gitlab, 而非 git
sudo -u postgres psql -d template1 -c "CREATE USER git CREATEDB;"
## 此用户可以不创建,但需修改以上配置文件连接到数据库的用户为 git 或 gitlab,且赋予配置用户 SUPERUSER 权限
sudo -u postgres psql -d template1 -c 'CREATE USER "gitlab-psql" SUPERUSER;'

# 创建扩展
sudo -u postgres psql -d template1 -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
## 如果 gitlab 版本低于 13.1,不需要创建此扩展
sudo -u postgres psql -d template1 -c "CREATE EXTENSION IF NOT EXISTS btree_gist;"

# 创建数据库
sudo -u postgres psql -d template1 -c "CREATE DATABASE gitlabhq_production OWNER git;"

# 创建系统用户映射
## $PGDATA/pg_ident.conf
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
git             postgres                gitlab

## $PGDATA/pg_hba.conf
# "local" is for Unix domain socket connections only
local   all             all                                     peer map=git

# 重启数据库
systemctl restart postgresql-13

# 如果本地登录数据库
sudo -u postgres -H psql -U gitlab -d gitlabhq_production
  1. 创建参数文件
LOAD DATABASE
     FROM mysql://git:Git123@localhost/gitlabhq_production
     INTO postgresql://gitlab@unix://var/run/postgresql:/gitlabhq_production

WITH include drop, truncate, create tables, create indexes, foreign keys, preserve index names

SET MySQL PARAMETERS
net_read_timeout = '90',
net_write_timeout = '180'

ALTER SCHEMA 'gitlabhq_production' RENAME TO 'public'

;
  1. 执行迁移
# commands.load文件位于 ~postgres 或者 ~gitlab-psql
# 如果是自建数据库
cd ~ postgres
sudo -u postgres -H pgloader commands.load
  1. 确认迁移结果
    此种方式会迁移源数据库,从结果上看此种方式和以上的差别主要在表字段类型。pgloader 在转换字段类型时将字段类型替换为最接近原有精度的数据类型,这是目前发现的一个明显的差异。
    例如:
    对于此种方式迁移后表 abuse_report.id 字段数据类型为 bigint,MySQL中类型为int(11),显然如果PostgreSQL 设置为integer(-2147483648 to +2147483647)是不合适的,但是按照 GitLab 当前最新版本设置 integer 是可以被允许的。
2021-12-02T18:20:41.031000+08:00 LOG pgloader version "3.6.1"
2021-12-02T18:20:41.152000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://git@localhost:3306/gitlabhq_production {1008BC5B33}>
2021-12-02T18:20:41.152000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://gitlab@unix://var/run/postgresql:5432/gitlabhq_production {1008BC6FB3}>
2021-12-02T18:20:46.057000+08:00 LOG report summary reset
                                 table name     errors       rows      bytes      total time
-------------------------------------------  ---------  ---------  ---------  --------------
                            fetch meta data          0        365                     0.302s
                             Create Schemas          0          0                     0.000s
                           Create SQL Types          0          0                     0.004s
                              Create tables          0        150                     0.261s
                             Set Table OIDs          0         75                     0.004s
-------------------------------------------  ---------  ---------  ---------  --------------
                       public.abuse_reports          0          0                     0.033s
                         public.appearances          0          0                     0.031s
                public.application_settings          0          1     0.4 kB          0.036s
                        public.audit_events          0          6     0.9 kB          0.034s
                              public.boards          0          0                     0.035s
                         public.award_emoji          0          2     0.1 kB          0.033s
                          public.chat_names          0          0                     0.048s
                  public.broadcast_messages          0          0                     0.049s
                           public.ci_builds          0          0                     0.066s
                                     ......
                   public.schema_migrations          0        573     8.4 kB          0.363s
                               public.users          0          2     0.8 kB          0.671s
                            public.services          0          2     0.2 kB          0.445s
                  public.user_agent_details          0          1     0.2 kB          0.685s
                           public.spam_logs          0          0                     0.501s
                            public.taggings          0          0                     0.465s
                            public.timelogs          0          0                     0.530s
                   public.trending_projects          0          0                     0.555s
                             public.uploads          0          0                     0.551s
                 public.users_star_projects          0          1     0.0 kB          0.611s
                           public.web_hooks          0          0                     0.618s
-------------------------------------------  ---------  ---------  ---------  --------------
                    COPY Threads Completion          0          4                     0.948s
                             Create Indexes          0        265                     2.387s
                     Index Build Completion          0        265                     0.882s
                            Reset Sequences          0         73                     0.046s
                               Primary Keys          0         73                     0.028s
                        Create Foreign Keys          0         25                     0.019s
                            Create Triggers          0          0                     0.000s
                           Install Comments          0          0                     0.000s
-------------------------------------------  ---------  ---------  ---------  --------------
                          Total import time          ✓        670    32.8 kB          4.310s