pgloader的使用

pgloader 使用rpm包安装的话，很简单。它在pgdg-common 这个repo源里面。
yum install pgloader 即可完成安装。


$ pgloader
pgloader [ option ... ] command-file ...
pgloader [ option ... ] SOURCE TARGET
  --help -h                       boolean  Show usage and exit.
  --version -V                    boolean  Displays pgloader version and exit.
  --quiet -q                      boolean  Be quiet
  --verbose -v                    boolean  Be verbose
  --debug -d                      boolean  Display debug level information.
  --client-min-messages           string   Filter logs seen at the console (default: "warning")
  --log-min-messages              string   Filter logs seen in the logfile (default: "notice")
  --summary -S                    string   Filename where to copy the summary
  --root-dir -D                   string   Output root directory. (default: #P"/tmp/pgloader/")
  --upgrade-config -U             boolean  Output the command(s) corresponding to .conf file for
                                           v2.x
  --list-encodings -E             boolean  List pgloader known encodings and exit.
  --logfile -L                    string   Filename where to send the logs.
  --load-lisp-file -l             string   Read user code from files
  --dry-run                       boolean  Only check database connections, don't load anything.
  --on-error-stop                 boolean  Refrain from handling errors properly.
  --no-ssl-cert-verification      boolean  Instruct OpenSSL to bypass verifying certificates.
  --context -C                    string   Command Context Variables
  --with                          string   Load options
  --set                           string   PostgreSQL options
  --field                         string   Source file fields specification
  --cast                          string   Specific cast rules
  --type                          string   Force input source type
  --encoding                      string   Source expected encoding
  --before                        string   SQL script to run before loading the data
  --after                         string   SQL script to run after loading the data
  --self-upgrade                  string   Path to pgloader newer sources
  --regress                       boolean  Drive regression testing



$ pgloader --version
pgloader version "3.6.2"
compiled with SBCL 1.4.0-1.el7

我这里演示版本是 3.6.2

vim mysql2pg.ini  内容如下：

LOAD DATABASE
FROM mysql://dts:dts@192.168.31.181:3306/dbatest
INTO postgresql://dts:dts@192.168.31.181:5432/dbatest
WITH
    create tables,include no drop,no truncate,create indexes, preserve index names, foreign keys,
    reset sequences, multiple readers per thread,
    workers = 8, concurrency = 2, rows per range = 5000

SET PostgreSQL PARAMETERS
    maintenance_work_mem to '1GB'
    work_mem to '64MB'

SET MySQL PARAMETERS
    net_read_timeout  = '31536000',
    net_write_timeout = '31536000',
    lock_wait_timeout = '31536000'

;

这个配置文件的一些参数说明：
 参数include no drop 当目标里面这个schema的表和源端的表重复的时候，会自动停止数据同步，并在日志中记录下来。
 更详细的介绍，见官方文档：https://pgloader.readthedocs.io/en/latest/ref/mysql.html
 

$ pgloader ./mysql2pg.ini
2023-12-22T05:45:04.044000Z LOG pgloader version "3.6.2"
2023-12-22T05:45:04.137000Z LOG Migrating from #<MYSQL-CONNECTION mysql://dts@192.168.31.181:3306/dbatest {100673B783}>
2023-12-22T05:45:04.137000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://dts@192.168.31.181:5432/dbatest {100673CB13}>
2023-12-22T05:45:04.729000Z ERROR PostgreSQL Database error 42P07: relation "k_2" already exists
QUERY: CREATE INDEX k_2 ON dbatest.sbtest1 (k);
2023-12-22T05:45:04.756000Z ERROR PostgreSQL Database error 42P07: relation "k_2" already exists
QUERY: CREATE INDEX k_2 ON dbatest.sbtest2 (k);
2023-12-22T05:45:04.885000Z LOG report summary reset
            table name     errors       rows      bytes      total time
-------------------------  ---------  ---------  ---------  --------------
        fetch meta data          0          9                     0.141s
        Create Schemas          0          0                     0.003s
        Create SQL Types          0          0                     0.008s
            Create tables          0          6                     0.040s
        Set Table OIDs          0          3                     0.008s
Check Concurrency Support          0          0                     0.000s
-------------------------  ---------  ---------  ---------  --------------
        dbatest.sbtest2          0      10000     1.8 MB          0.238s
        dbatest.sbtest1          0      10000     1.8 MB          0.234s
        dbatest.sbtest3          0      10000     1.8 MB          0.222s
-------------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          8                     0.240s
Index Build Completion          0          6                     0.097s
        Create Indexes          2          4                     0.063s
        Reset Sequences          0          0                     0.049s
            Primary Keys          0          3                     0.015s
    Create Foreign Keys          0          0                     0.000s
        Create Triggers          0          0                     0.002s
        Set Search Path          0          1                     0.003s
        Install Comments          0          0                     0.000s
-------------------------  ---------  ---------  ---------  --------------
        Total import time          ✓      30000     5.5 MB          0.469s
(base) 

附录的percona博客上提到，在使用pgloader可能遇到的问题：

问题#1：不兼容的值/数据类型，MySQL（日期时间）-> Postgres（时间戳）
许多列中的值从“ 0000-00-00 00:00:00 ”更改为“ 1970-01-01 00:00:00 ”。这些值已显式更新，以便 Postgres 接受该值。对于 MySQL DBA 来说，这是旧版 MySQL DBMS 中的一个已知问题。较新的版本不允许这种行为。
TIPS：备注：本人在参照percona博客实验的时候，测试结果和上面的有所不同。我这里mysql时间字段值为0000-00-00 00:00:00，到pg里面变成了null

问题#2：不兼容的值/数据类型，MySQL（时间）-> Postgres（时间戳）
table.column“ pro_game_reports.game_time_tomorrow ”已从数据类型“ time ”切换为“ integer ”。
TIPS：备注：本人在参照percona博客实验的时候，测试结果和上面的有所不同。我这里mysql time类型的列，到pg里面是time without time zone 类型的列。

问题#3：MySQL 表名太长 【这个可能遇到的概率比较小】
由于 MySQL 的名称可能比 Postgres 合法可接受的名称更长，因此 pgLoader 必须将它们重命名为更短的名称。然而，有一个警告，当源关系的前 63 个以上字符相同时可能会遇到错误，即尝试生成重复的名称。

问题#4：MySQL 索引名称太长 【这个可能遇到的概率比较小】
与问题 #3 类似，pgloader 在将索引重新构建到 PostgreSQL 中时自动重命名索引，即在 PostgreSQL 中非法的重复命名索引。假设名称长度在 Postgres 中是合法的，则可以使用选项保留索引名称来解决此问题，保留原始索引名称。

问题 #5：MySQL 索引名称重复
原因：MySQL多个表的索引名称可以重复，但是PG不可以。
例如 mysql库里有3个表，都有 idx_k 这个索引。 那么使用pgloader迁移到pg后，因为idx_k这个索引只能单次出现，因此其余表的idx_k索引都创建失败！ 
这种情况下，需要手动去PG上创建索引，或者把源端MySQL的索引名称都改成不重复的。（建议去PG上补创建索引，不建议对上游的MySQL进行改动）

问题#6：表中检测到缺失数据，导致外键约束失败 【生产上MySQL一般不使用外键，这个可能遇到的概率比较小】

配置则类似如下：
LOAD DATABASE
FROM mysql://root:#####@192.168.150.224:3306/dev
INTO postgresql://postgres:#####@localhost:5432/dev
WITH
   DATA ONLY
SET PostgreSQL PARAMETERS
    maintenance_work_mem to '1GB'
    work_mem to '64MB'

SET MySQL PARAMETERS
    net_read_timeout  = '31536000',
    net_write_timeout = '31536000',
    lock_wait_timeout = '31536000'
;

1、不会同步MySQL的视图到pg中
2、不会同步MySQL的触发器到pg中
3、geometric 数据类型的迁移目前还不支持

LOAD DATABASE
    FROM postgresql://dts:dts@192.168.31.181:5432/sbtest
    INTO postgresql://dts:dts@192.168.31.181:5432/dbatest
WITH
    create tables,no truncate,create indexes, preserve index names, foreign keys
    
SET PostgreSQL PARAMETERS
    maintenance_work_mem to '1GB',
    work_mem to '64MB'
;