该文章最初发表在JFrog博客中。
1.迁移MySQL到PostgreSQL的简介。
作为JFrog的SaaS策略的一部分，仅支持PostgreSQL数据库（DB），JFrog已面临将我们的SaaS中现有客户数据库从MySQL迁移到PostgreSQL的需求。
必须开发一个完全自动化，可靠和稳定的迁移过程，数据丢失零和停机时间为零，能够迁移数十个/数百个GB的DB，并确保现有应用程序可以与迁移的DBS透明地工作。
我们开发了一个基于pgloader开源实用程序[https://github.com/dimitri/pgloader]的mySQL2PG流程来解决此需求。
2.将DB从MySQL迁移到PostgreSQL的组件/拓扑是什么？
数据库迁移过程的基本拓扑包括源数据库（MySQL），目标数据库（PostgreSQL）和迁移计算机。 PGLOADER从迁移计算机运行，从源数据库（MySQL）读取数据并将其写入目标数据库（PostgreSQL）。
迁移计算机应具有直接，可靠和高吞吐量连接到源和目标数据库。迁移计算机的典型配置包括16个CPU和64 GB RAM，至少10 Gbps的网络带宽以及至少500GB的存储。
保证数据库对象的兼容性，其结构以及与特定数据类型的对应关系，DB迁移过程运行了适用于产品的特定类型/版本的第一个DDL脚本。然后，它将数据复制到预先准备的目标db架构中。
PGLOADER不会将数据导出到转储中，将转储放在迁移计算机上，也不会通过DBS数据占用存储空间。它在迁移计算机RAM中创建了一种管道过程，并读取数据从源db中选择它并将其复制到目标db中。
3.迁移机的典型操作系统是什么？
OS：Ubuntu 20.04
lsb_release -a
没有LSB模块。
发行器ID：Ubuntu
描述：Ubuntu 20.04.2 LTS
发行：20.04
代号：焦点
4.建议在迁移机上安装什么？
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql-13
sudo apt-get install -y pgloader
sudo apt-get install mysql-client
5.如何运行PGLOADER将DB从MySQL迁移到PostgreSQL？
创建pgloader配置文件：
cat pgloader.conf
LOAD DATABASE
FROM mysql://src_db_user:src_db_pwd@src_db_host/src_db_name?sslmode=<...>
INTO postgresql://dest_db_user:dest_db_pwd@dest_db_host/dest_db_name;
运行pgloader：
pgloader pgloader.conf
6.如何将MySQL2PG PGLOADER作为背景过程运行？
为避免可能中断MySQL2PG迁移过程，建议将PGLOADER作为背景过程运行。该过程包括以下步骤。
创建pgloader配置文件：
cat pgloader.conf
LOAD DATABASE
FROM mysql://src_db_user:src_db_pwd@src_db_host/src_db_name?sslmode=<...>
INTO postgresql://dest_db_user:dest_db_pwd@dest_db_host/dest_db_name;
创建一个shell脚本以将pgloader作为背景过程运行：
cat run_pgloader_background.sh
nohup pgloader pgloader.conf 2>&1 &
运行脚本：
run_pgloader_background.sh
运行跟踪将放置在文件上：
nohup.out
注意：
通常
src_db_user = dest_db_user,
src_db_pwd = dest_db_pwd,
src_db_name = dest_db_name
7. PGLOADER的典型输出跟踪是什么样的？
2022-01-08T20:47:55.046000+02:00 LOG report summary reset
table name errors read imported bytes total time read write
---------------------------------------- --------- --------- --------- --------- -------------- --------- ---------
fetch meta data 0 278 278 0.808s
Create Schemas 0 0 0 0.018s
Create SQL Types 0 0 0 0.010s
Create tables 0 144 144 1.137s
Set Table OIDs 0 72 72 0.011s
---------------------------------------- --------- --------- --------- --------- -------------- --------- ---------
my_db.my_table_number_1 0 319387354 319387354 38.0 GB 37m40.265s 37m40.242s 28m41.397s
my_db.my_table_number_2 0 15748659 15748659 1.9 GB 1m50.060s 1m50.039s 1m22.053s
my_db.my_table_number_3 0 3989089 3989089 336.5 MB 20.059s 20.009s 14.337s
(etc)
---------------------------------------- --------- --------- --------- --------- -------------- --------- ---------
COPY Threads Completion 0 4 4 45m10.569s
Create Indexes 0 165 165 1h2m48.214s
Index Build Completion 0 165 165 6m12.345s
Reset Sequences 0 1 1 0.123s
Primary Keys 0 69 69 0.789s
Create Foreign Keys 0 41 41 1m23.456s
Create Triggers 0 0 0 0.005s
Set Search Path 0 1 1 0.012s
Install Comments 0 0 0 0.000s
---------------------------------------- --------- --------- --------- --------- -------------- --------- ---------
Total import time ✓ 363230932 363230932 123.4 GB 1h23m45.678s
8.如何确保迁移成功完成？如何确保零数据丢失？
验证pgloader的跟踪：它应该具有零错误，并且应匹配读取和导入值。
9.如何从PGLOADER流程中排除表？
如果源db包含可以安全排除在迁移过程中的表
示例：
LOAD DATABASE
FROM mysql://src_db_user:src_db_pwd@src_db_host/src_db_name?sslmode=<...>
INTO postgresql://dest_db_user:dest_db_pwd@dest_db_host/dest_db_name
EXCLUDING TABLE NAMES MATCHING 'table_to_exclude_one','table_to_exclude_two','table_to_exclude_three';
10.堆疲惫，游戏问题。
堆已耗尽，PGLOADER的游戏可能表明PGLOADER默认专用4GB RAM不足以用于给定的DB迁移。
在这种情况下，解决方案是使用具有更广泛的Dynsize参数编译的PGLOADER。 dynsize参数允许修改PGLOADER映像将允许在通过数据运行时使用的默认内存量。
11.如何编译PGLOADER以使用更多RAM？
PGLOADER实用程序没有使用主机上的所有可用RAM。 PGLOADER可以使用的RAM量在编译阶段定义。为了使更多RAM应编译定制版本的PGLOADER。以下分步指南说明了如何使用自定义参数dynsize编译PGLOADER。 dynsize参数允许在通过数据运行时修改PGLOADER图像。
pgloader compilation
--------------------
Build pgloader from sources
(1) Check OS:
dima@dima-VirtualBox:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 20.04.3 LTS
Release: 20.04
Codename: focal
dima@dima-VirtualBox:~$
(2) Install git:
sudo apt update
sudo apt install git
(3) Clone pgloader:
mkdir my_pgloader
cd my_pgloader/
git clone https://github.com/dimitri/pgloader.git
(4) install packages necessary for build:
sudo apt-get install sbcl unzip libsqlite3-dev make curl gawk freetds-dev libzip-dev
(5) make build:
make DYNSIZE=10240 pgloader
(6) make outputs a ./build/bin/pgloader file for us to use.
dima@dima-VirtualBox:~/my_pgloader/pgloader/build/bin$ pwd
/home/dima/my_pgloader/pgloader/build/bin
dima@dima-VirtualBox:~/my_pgloader/pgloader/build/bin$ ls -rtogla
total 69160
-rw-rw-r-- 1 70 Jan 7 17:17 .gitignore
drwxrwxr-x 5 4096 Jan 7 17:29 ..
-rwxr-xr-x 1 41918800 Jan 7 17:29 buildapp.sbcl
-rwxr-xr-x 1 29036008 Jan 7 17:29 pgloader
drwxrwxr-x 2 4096 Jan 7 17:29 .
dima@dima-VirtualBox:~/my_pgloader/pgloader/build/bin$
cp pgloader pgloader_dima_dynsize_10240
dima@dima-VirtualBox:~/my_pgloader/pgloader/build/bin$ ls -rtogla
total 97396
-rw-rw-r-- 1 70 Jan 7 17:17 .gitignore
drwxrwxr-x 5 4096 Jan 7 17:29 ..
-rwxr-xr-x 1 41918800 Jan 7 17:29 buildapp.sbcl
-rwxr-xr-x 1 29036008 Jan 7 17:29 pgloader
-rwxr-xr-x 1 29036008 Jan 7 17:33 pgloader_dima_dynsize_10240
drwxrwxr-x 2 4096 Jan 7 17:33 .
dima@dima-VirtualBox:~/my_pgloader/pgloader/build/bin$
dima@dima-VirtualBox:~/my_pgloader/pgloader/build/bin$ ./pgloader_dima_dynsize_10240 --version
pgloader version "3.6.a94a0a3"
compiled with SBCL 2.0.1.debian
dima@dima-VirtualBox:~/my_pgloader/pgloader/build/bin$
12.如何管理PEERâPGOLATER问题的连接重置？
PGLOADER运行期间通过PEER错误消息的连接重置通常表示连接到MySQL [源DB]的PGLOADER的超时。
要管理此问题，我们建议将以下参数添加到pgloader配置脚本：
SET MySQL PARAMETERS
net_read_timeout = '5000',
net_write_timeout = '5000'
13.如何改善PGLOADER迁移过程的持续时间？
为了改善PGLOADER迁移过程的持续时间，有几种方法：
扩展源和目标数据库
`确保源和目标数据库上的无负载 /重型活动 /足够的存储，RAM，连接和资源
使用定制的参数dynsize
使用PGLOADER
<扩展迁移机以允许更多RAM
使用以下参数运行PGLOADER：
SET PostgreSQL PARAMETERS
maintenance_work_mem to '512MB',
work_mem to '48MB'
14.如何配置PGLOADER以在大数据库 /大量数据上可靠地运行稳定且可靠地运行？
使用定制Dynsize参数编译的PGLOADER。它将允许将更多的RAM用于PGLOADER流程
使用以下PGLOADER配置：
cat pgloader.conf
FROM LOAD DATABASE
FROM mysql://db_user:db_pwd@src_db_host/db_name?sslmode=<...>
INTO postgresql://db_user:db_pwd@dest_db_host/db_name
WITH
data only, create no indexes,
workers = 8, concurrency = 1,
multiple readers per thread, rows per range = 10000,
batch rows = 10000
SET PostgreSQL PARAMETERS
maintenance_work_mem to '512MB',
work_mem to '48MB'
SET MySQL PARAMETERS
net_read_timeout = '5000',
net_write_timeout = '5000'
EXCLUDING TABLE NAMES MATCHING 'table_to_exclude_one','table_to_exclude_two','table_to_exclude_three'
ALTER SCHEMA 'db_schema_name' RENAME TO 'public';
15.最小PGLOADER配置是什么样的？
FROM LOAD DATABASE
FROM mysql://db_user:db_pwd@src_db_host/db_name?sslmode=<...>
INTO postgresql://db_user:db_pwd@dest_db_host/db_name
with
batch size = 2048 kB,
batch rows = 2000,
prefetch rows = 2000
SET PostgreSQL PARAMETERS
maintenance_work_mem to '512MB',
work_mem to '48MB'
SET MySQL PARAMETERS
net_read_timeout = '5000',
net_write_timeout = '5000'
EXCLUDING TABLE NAMES MATCHING 'table_to_exclude_one','table_to_exclude_two','table_to_exclude_three'
ALTER SCHEMA 'db_schema_name' RENAME TO 'public';
16.为什么在此过程之前，之中和之后监视源，目标数据库和迁移计算机很重要？
保证PGLOADER的稳定，可靠和最佳工作至关重要，在迁移之前，之中和之后，监视源和目标数据库。 DB的机器应该足够强大，并且需要有足够的资源来进行PGLOADER密集活动。来源和目的地DBS机器都不应通过重型活动或强化CPU操作或高读数或（和）写入。
注意：源或目标数据库机器上的任何负载或重型活动都直接影响数据库迁移的性能和成功。
此外，在此过程中监视迁移计算机的CPU，IOPS，内存和网络很重要。
17.拆分到源数据库，目标数据库和迁移机的重要性。
保持源DB实例，目标DB实例和迁移计算机的分别保留。结合这些组件将影响数据库的迁移性能和稳定性。
18.可以在迁移过程中查询目标后Ql DB吗？我们会通过查询飞行的复制表来看到数据库迁移的进展吗？
尽管我们可以在迁移期间在目标postgresql db上看到实时会话，但实际数据仅在迁移的末尾投入。
在PGLOADER中间的查询运行任何填充表都没有用：它们的数据放在肮脏的，尚未承诺的块上。只有PGLOADER完成后，我们才能在目标数据库查询上看到实际记录。