13-PGLoader

PGLoader

我们可以方便的将其他数据源的数据库迁移到PGSQL。以mysql迁移到pgsql为例。g

首先准备一个mysql数据库test，创建一个表student。

MariaDB [test]> select * from student;
+----+------+---------------------+--------+
| id | name | date                | site   |
+----+------+---------------------+--------+
|  1 | haha | 2026-01-05 13:05:24 | 北京   |
|  2 | wawa | 2026-01-05 13:05:33 | 天津   |
+----+------+---------------------+--------+
2 rows in set (0.000 sec)

同时pgsql也要创建对应的库。

pgloader需要单独下载.编写以下的简单脚本，可以在官网查询

https://pgloader.readthedocs.io/en/latest/index.html

然后执行命令

pgloader mysql://user@localhost/dbname pgsql://user@localhost/dbname

这个命令较为简单，如果数据库类型映射，数据格式等比较复杂，可能无法处理，最好编写脚本。

LOAD DATABASE
  FROM mysql://root:password@localhost:3306/test			-- mysql数据库
  INTO pgsql://postgres:password@localhost:5432/test	-- pgsql位置

WITH include drop, create tables, create indexes, reset sequences,
      workers = 8, concurrency = 1 						-- 规则

ALTER SCHEMA 'test' RENAME TO 'public'					-- schema
;

执行脚本，数据迁移

❯ pgloader mysql2pgsql.loader
2026-01-05T13:53:15.013000+08:00 LOG pgloader version "3.6.e350156"
2026-01-05T13:53:15.117000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@localhost:3306/test {1205C04CE3}>
2026-01-05T13:53:15.117000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@localhost:5432/test {1205C04D63}>
2026-01-05T13:53:15.473000+08:00 LOG report summary reset
             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
        fetch meta data          0          2                     0.107s
         Create Schemas          0          0                     0.001s
       Create SQL Types          0          0                     0.007s
          Create tables          0          2                     0.021s
         Set Table OIDs          0          1                     0.008s
-----------------------  ---------  ---------  ---------  --------------
         public.student          0          2     0.1 kB          0.030s
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          8                     0.033s
 Index Build Completion          0          1                     0.024s
         Create Indexes          0          1                     0.002s
        Reset Sequences          0          0                     0.026s
           Primary Keys          0          1                     0.003s
    Create Foreign Keys          0          0                     0.000s
        Create Triggers          0          0                     0.001s
       Install Comments          0          0                     0.000s
-----------------------  ---------  ---------  ---------  --------------
      Total import time          ✓          2     0.1 kB          0.089s

这时候我们可以去查看我们的test库

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \dt
           List of tables
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | student | table | postgres
(1 row)

test=# select * from student;
 id | name |          date          | site 
----+------+------------------------+------
  1 | haha | 2026-01-05 13:05:24+08 | 北京
  2 | wawa | 2026-01-05 13:05:33+08 | 天津
(2 rows)