使用PGLOADER工具迁移PG表到MogDB

操作系统版本：centos7.6
源数据库版本：postgres 11.12
目标数据库版本：mogdb2.0
PGLOADER版本：3.6.1
注：PostgreSQL 版本必须为11或以下版本

一、安装PGLOADER工具

参考文章：使用PGLOADER工具向mogdb数据库导入csv格式数据

二、迁移PostgreSQL数据库表数据到Mogdb数据库

1.准备配置文件

迁移PG的db1数据库中user1模式下的表名包含’t2’的所有表数据到mogdb的pgloader数据库中：

# cat pg.load
load database
  from postgresql://user1:user_123@192.168.2.151:10000/db1
  into postgresql://pg:pg_12345@192.168.2.156:26000/pgloader
WITH  include drop, create tables, create indexes, reset no sequences

including only table names matching ~/t2/ in schema 'user1'
;

2.迁移

# pgloader pg.load
2021-07-14T08:52:16.024000+08:00 LOG pgloader version "3.6.1"
2021-07-14T08:52:16.225000+08:00 LOG Migrating from #<PGSQL-CONNECTION pgsql://user1@192.168.2.151:10000/db1 {1007DF7F03}>
2021-07-14T08:52:16.225000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://pg@192.168.2.156:26000/pgloader {1007DF93A3}>
2021-07-14T08:52:16.812000+08:00 LOG report summary reset
             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
        fetch meta data          0          3                     0.105s
         Create Schemas          0          0                     0.004s
       Create SQL Types          0          0                     0.012s
          Create tables          0          6                     0.051s
         Set Table OIDs          0          3                     0.001s
-----------------------  ---------  ---------  ---------  --------------
           "user1"."t2"          0          4     0.0 kB          0.163s
          "user1"."ct2"          0          4     0.0 kB          0.149s
          "user1"."t21"          0          4     0.0 kB          0.081s
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          4                     0.188s
 Index Build Completion          0          0                     0.000s
           Primary Keys          0          0                     0.000s
    Create Foreign Keys          0          0                     0.000s
        Create Triggers          0          0                     0.001s
       Install Comments          0          0                     0.000s
-----------------------  ---------  ---------  ---------  --------------
      Total import time          ✓         12     0.0 kB          0.189s

3.源端和目标端迁移后结果比对

源端：

db1=> \dt
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | csv  | table | user1
 user1  | ct2  | table | user1
 user1  | t1   | table | user1
 user1  | t12  | table | user1
 user1  | t2   | table | user1
 user1  | t21  | table | user1
(6 rows)
db1=> select * from user1.t2;
 id
----
  1
  2
  3
  4
(4 rows)

db1=>

目标端

pgloader=> \dt user1.*
                        List of relations
 Schema | Name | Type  | Owner |             Storage
--------+------+-------+-------+----------------------------------
 user1  | ct2  | table | pg    | {orientation=row,compression=no}
 user1  | t2   | table | pg    | {orientation=row,compression=no}
 user1  | t21  | table | pg    | {orientation=row,compression=no}
(3 rows)

pgloader=> select * from user1.t2;
 id
----
  1
  2
  3
  4
(4 rows)