文章/答案/技术大牛

发布

社区首页 >专栏 >使用rds_dbsync同步Mysql数据至Greenplum

使用rds_dbsync同步Mysql数据至Greenplum

原创

岳涛

修改于 2021-09-26 09:18:37

11.4K00

代码可运行

文章被收录于专栏：大数据生态大数据生态

运行总次数：0

代码可运行

说明

本文延续上一篇文章 云数据库MySQL导入云数据仓库PostgreSQL最佳实践，继续介绍云数据库MySQL导入云数据仓库PostgreSQL的使用问题。其中描述的问题及解决方法同样适用于 腾讯云云数据仓库 PostgreSQL（CDWPG）。

背景

在实际数据同步的场景中，大多的需求是迁移。这种情况下，如果表的数量很多的话，那数据同步的成本是非常大的，因为目标端需要提前构建出全部的表结构。这个时候我们可以视情况选择使用数据同步开源工具（rds_dbsync），该工具具有结构化导出的能力。

安装依赖

安装mysql的开发包

安装mysql5.7的yum源：

[root@VM-5-48-centos ~]# rpm -Uvh http://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm
Retrieving http://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm
warning: /var/tmp/rpm-tmp.SdPZez: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql57-community-release-el6-9  ################################# [100%]

卸载服务器自带的mariadb：

[root@VM-5-48-centos ~]# yum remove mariadb-libs
Loaded plugins: fastestmirror, langpacks
Resolving Dependencies
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be erased
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 1:mariadb-devel-5.5.68-1.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-9.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-9.el7.x86_64
--> Processing Dependency: mariadb-libs(x86-64) = 1:5.5.68-1.el7 for package: 1:mariadb-5.5.68-1.el7.x86_64
--> Processing Dependency: mariadb-libs(x86-64) = 1:5.5.68-1.el7 for package: 1:mariadb-devel-5.5.68-1.el7.x86_64
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.68-1.el7 will be erased
---> Package mariadb-devel.x86_64 1:5.5.68-1.el7 will be erased
---> Package postfix.x86_64 2:2.10.1-9.el7 will be erased
--> Finished Dependency Resolution

Remove  1 Package (+3 Dependent packages)

Installed size: 68 M
Is this ok [y/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction

Removed:
  mariadb-libs.x86_64 1:5.5.68-1.el7                                                                                                                                                           

Dependency Removed:
  mariadb.x86_64 1:5.5.68-1.el7
  mariadb-devel.x86_64 1:5.5.68-1.el7
  postfix.x86_64 2:2.10.1-9.el7                               

Complete!

执行安装：

[root@VM-5-48-centos ~]# yum install mysql-community-devel mysql-community-client
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.7.33-1.el6 will be installed
---> Package mysql-community-devel.x86_64 0:5.7.33-1.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved
Running transaction check
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
 Package    : mysql57-community-release-el6-9.noarch (installed)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Running transaction check
Running transaction test
Transaction test succeeded
Installed:
  mysql-community-client.x86_64 0:5.7.33-1.el6
  mysql-community-devel.x86_64 0:5.7.33-1.el6

Complete!

安装postgresql的开发包

安装postgresql的yum源：

[root@VM-5-48-centos ~]# rpm -vih https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Retrieving https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:pgdg-redhat-repo-42.0-14         ################################# [100%]

执行安装：

[root@VM-5-48-centos ~]# yum install postgresql95-devel
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package postgresql95-devel.x86_64 0:9.5.25-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql95-libs(x86-64) = 9.5.25-1PGDG.rhel7 for package: postgresql95-devel-9.5.25-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql95(x86-64) = 9.5.25-1PGDG.rhel7 for package: postgresql95-devel-9.5.25-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql95-devel-9.5.25-1PGDG.rhel7.x86_64
--> Processing Dependency: libpgtypes.so.3()(64bit) for package: postgresql95-devel-9.5.25-1PGDG.rhel7.x86_64
--> Processing Dependency: libecpg_compat.so.3()(64bit) for package: postgresql95-devel-9.5.25-1PGDG.rhel7.x86_64
--> Processing Dependency: libecpg.so.6()(64bit) for package: postgresql95-devel-9.5.25-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql95.x86_64 0:9.5.25-1PGDG.rhel7 will be installed
---> Package postgresql95-libs.x86_64 0:9.5.25-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved
Running transaction check
Running transaction test
Transaction test succeeded                                                                                                                             3/3 

Installed:
  postgresql95-devel.x86_64 0:9.5.25-1PGDG.rhel7                                                                                                                                               

Dependency Installed:
  postgresql95.x86_64 0:9.5.25-1PGDG.rhel7
  postgresql95-libs.x86_64 0:9.5.25-1PGDG.rhel7                                                   

Complete!

注意

由于后面在数据同步时需要用到pg_config这个命令，但postgresql95在安装好之后默认是不会将pg_config放到环境变量里的：

[root@VM-5-48-centos ~]# pg_config
-bash: pg_config: command not found

所以我们需要将将下面的内容追加到/etc/profile，然后加载pg_config到环境变量：

[root@VM-5-48-centos ~]# tail -2 /etc/profile
export PG_HOME=/usr/pgsql-9.5
PATH=$PATH:$PG_HOME/bin
[root@VM-5-48-centos ~]# source /etc/profile
[root@VM-5-48-centos ~]# which pg_config
/usr/pgsql-9.5/bin/pg_config
[root@VM-5-48-centos ~]# pg_config --version
PostgreSQL 9.5.25

安装c++编译器

由于后面编译需要用到c++编译器，所以这里需要安装一下：

[root@VM-5-48-centos dbsync]# yum install gcc-c++
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package gcc-c++.x86_64 0:4.8.5-44.el7 will be installed
--> Processing Dependency: libstdc++-devel = 4.8.5-44.el7 for package: gcc-c++-4.8.5-44.el7.x86_64
--> Processing Dependency: libstdc++ = 4.8.5-44.el7 for package: gcc-c++-4.8.5-44.el7.x86_64
--> Processing Dependency: gcc = 4.8.5-44.el7 for package: gcc-c++-4.8.5-44.el7.x86_64
--> Running transaction check
---> Package gcc.x86_64 0:4.8.5-39.el7 will be updated
---> Package gcc.x86_64 0:4.8.5-44.el7 will be an update
--> Processing Dependency: libgomp = 4.8.5-44.el7 for package: gcc-4.8.5-44.el7.x86_64
--> Processing Dependency: cpp = 4.8.5-44.el7 for package: gcc-4.8.5-44.el7.x86_64
--> Processing Dependency: libgcc >= 4.8.5-44.el7 for package: gcc-4.8.5-44.el7.x86_64
---> Package libstdc++.x86_64 0:4.8.5-39.el7 will be updated
---> Package libstdc++.x86_64 0:4.8.5-44.el7 will be an update
---> Package libstdc++-devel.x86_64 0:4.8.5-44.el7 will be installed
--> Running transaction check
---> Package cpp.x86_64 0:4.8.5-39.el7 will be updated
---> Package cpp.x86_64 0:4.8.5-44.el7 will be an update
---> Package libgcc.x86_64 0:4.8.5-39.el7 will be updated
---> Package libgcc.x86_64 0:4.8.5-44.el7 will be an update
---> Package libgomp.x86_64 0:4.8.5-39.el7 will be updated
---> Package libgomp.x86_64 0:4.8.5-44.el7 will be an update
--> Finished Dependency Resolution

Running transaction check
Running transaction test
Transaction test succeeded
Installed:
  gcc-c++.x86_64 0:4.8.5-44.el7                                                                                                                                                                

Dependency Installed:
  libstdc++-devel.x86_64 0:4.8.5-44.el7                                                                                                                                                        

Dependency Updated:
  cpp.x86_64 0:4.8.5-44.el7
  gcc.x86_64 0:4.8.5-44.el7
  libgcc.x86_64 0:4.8.5-44.el7
  libgomp.x86_64 0:4.8.5-44.el7
  libstdc++.x86_64 0:4.8.5-44.el7         

Complete!

编译rds_dbsync

下载源码

[root@VM-5-48-centos ~]# wget https://codeload.github.com/aliyun/rds_dbsync/zip/refs/heads/master -O rds_dbsync.zip
--2021-03-25 21:14:49--  https://codeload.github.com/aliyun/rds_dbsync/zip/refs/heads/master
Resolving codeload.github.com (codeload.github.com)... 54.251.140.56
Connecting to codeload.github.com (codeload.github.com)|54.251.140.56|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/zip]
Saving to: ‘rds_dbsync.zip’

100%[=============================================================================>] 105,501     --.-K/s   in 0.09s

2021-03-25 21:25:56 (1.14 MB/s) - ‘rds_dbsync.zip’ saved [69565/69565]

解压源码包

[root@VM-5-48-centos ~]# unzip rds_dbsync.zip 
Archive:  rds_dbsync.zip
8f91a8572e4f7657d34351d78af0f2687d29a701
   creating: rds_dbsync-master/
  inflating: rds_dbsync-master/.dockerignore  
  inflating: rds_dbsync-master/Dockerfile  
  inflating: rds_dbsync-master/LICENSE  
  inflating: rds_dbsync-master/README.md  
   creating: rds_dbsync-master/dbsync/
  inflating: rds_dbsync-master/dbsync/Makefile  
  inflating: rds_dbsync-master/dbsync/dbsync-mysql2pgsql.c  
  inflating: rds_dbsync-master/dbsync/dbsync-pgsql2pgsql.c  
  inflating: rds_dbsync-master/dbsync/demo.cpp  
  inflating: rds_dbsync-master/dbsync/ini.c  
  inflating: rds_dbsync-master/dbsync/ini.h  
  inflating: rds_dbsync-master/dbsync/misc.c  
  inflating: rds_dbsync-master/dbsync/misc.h  
  inflating: rds_dbsync-master/dbsync/my.cfg  
  inflating: rds_dbsync-master/dbsync/mysql2pgsql.c  
  inflating: rds_dbsync-master/dbsync/pg_logicaldecode.c  
  inflating: rds_dbsync-master/dbsync/pg_logicaldecode.h  
  inflating: rds_dbsync-master/dbsync/pgsync.c  
  inflating: rds_dbsync-master/dbsync/pgsync.h  
  inflating: rds_dbsync-master/dbsync/pqformat.c  
  inflating: rds_dbsync-master/dbsync/readcfg.cpp  
  inflating: rds_dbsync-master/dbsync/readcfg.h  
  inflating: rds_dbsync-master/dbsync/stringinfo.c  
   creating: rds_dbsync-master/dbsync/test/
  inflating: rds_dbsync-master/dbsync/test/decode_test.sql  
  inflating: rds_dbsync-master/dbsync/utils.c  
  inflating: rds_dbsync-master/dbsync/utils.h  
   creating: rds_dbsync-master/doc/
  inflating: rds_dbsync-master/doc/design.md  
  inflating: rds_dbsync-master/doc/mysql2gp.md  
  inflating: rds_dbsync-master/doc/mysql2pgsql_ch.md  
  inflating: rds_dbsync-master/doc/mysql2pgsql_en.md  
  inflating: rds_dbsync-master/doc/pgsql2pgsql_ch.md  
  inflating: rds_dbsync-master/doc/pgsql2pgsql_en.md

执行编译

[root@VM-5-48-centos ~]# cd rds_dbsync-master/dbsync/
[root@VM-5-48-centos dbsync]# make
Makefile:32: warning: overriding recipe for target `clean'
/usr/pgsql-9.5/lib/pgxs/src/makefiles/pgxs.mk:219: warning: ignoring old recipe for target `clean'
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o pg_logicaldecode.o pg_logicaldecode.c
pg_logicaldecode.c: In function ‘bdr_process_remote_action’:
pg_logicaldecode.c:49:7: warning: variable ‘rc’ set but not used [-Wunused-but-set-variable]
  bool rc = false;
       ^
pg_logicaldecode.c: In function ‘process_remote_begin’:
pg_logicaldecode.c:89:9: warning: variable ‘flags’ set but not used [-Wunused-but-set-variable]
  int    flags = 0;
         ^
pg_logicaldecode.c: In function ‘process_remote_update’:
pg_logicaldecode.c:191:8: warning: variable ‘pkey_sent’ set but not used [-Wunused-but-set-variable]
  bool  pkey_sent;
        ^
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o pqformat.o pqformat.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o stringinfo.o stringinfo.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o utils.o utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o misc.o misc.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o pgsync.o pgsync.c
pgsync.c: In function ‘logical_decoding_apply_thread’:
pgsync.c:807:13: warning: variable ‘type’ set but not used [-Wunused-but-set-variable]
     Oid     type[1];
             ^
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o ini.o ini.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC -shared -o ali_recvlogical.so pg_logicaldecode.o pqformat.o stringinfo.o utils.o misc.o pgsync.o ini.o -L/usr/pgsql-9.5/lib  -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.5/lib',--enable-new-dtags  
g++  -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o demo.o demo.cpp
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o dbsync-pgsql2pgsql.o dbsync-pgsql2pgsql.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o mysql2pgsql.o mysql2pgsql.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o dbsync-mysql2pgsql.o dbsync-mysql2pgsql.c
g++  -DFRONTEND -I./ -I/usr/pgsql-9.5/include -I/usr/include/mysql -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o readcfg.o readcfg.cpp
g++ -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC demo.o pg_logicaldecode.o pqformat.o stringinfo.o utils.o misc.o pgsync.o ini.o -L/usr/pgsql-9.5/lib -lpgcommon -lpgport -L/usr/pgsql-9.5/lib -lpq '-Wl,-rpath,$ORIGIN,-rpath,$ORIGIN/lib,-rpath,$ORIGIN/../lib,-rpath,/usr/lib64/mysql,-rpath,/usr/pgsql-9.5/lib' -L/usr/pgsql-9.5/lib  -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.5/lib',--enable-new-dtags  -lpthread -o demo 
g++ -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC readcfg.o dbsync-pgsql2pgsql.o pg_logicaldecode.o pqformat.o stringinfo.o utils.o misc.o pgsync.o ini.o -L/usr/pgsql-9.5/lib -lpgcommon -lpgport -L/usr/pgsql-9.5/lib -lpq '-Wl,-rpath,$ORIGIN,-rpath,$ORIGIN/lib,-rpath,$ORIGIN/../lib,-rpath,/usr/lib64/mysql,-rpath,/usr/pgsql-9.5/lib' -L/usr/pgsql-9.5/lib  -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.5/lib',--enable-new-dtags  -lpthread -o pgsql2pgsql
g++ -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -fPIC readcfg.o ini.o mysql2pgsql.o dbsync-mysql2pgsql.o misc.o stringinfo.o -L/usr/pgsql-9.5/lib -lpgcommon -lpgport -L/usr/pgsql-9.5/lib -lpq '-Wl,-rpath,$ORIGIN,-rpath,$ORIGIN/lib,-rpath,$ORIGIN/../lib,-rpath,/usr/lib64/mysql,-rpath,/usr/pgsql-9.5/lib' -L/usr/pgsql-9.5/lib  -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.5/lib',--enable-new-dtags  -lpthread -L/usr/lib64/mysql -lmysqlclient -o mysql2pgsql

执行安装

[root@VM-5-48-centos dbsync]# make package
Makefile:32: warning: overriding recipe for target `clean'
/usr/pgsql-9.5/lib/pgxs/src/makefiles/pgxs.mk:219: warning: ignoring old recipe for target `clean'
mkdir -p install
mkdir -p install/bin
mkdir -p install/lib
cp -fr pgsql2pgsql install/bin
cp -fr demo install/bin
cp -fr ali_recvlogical.so install/lib
cp -fr mysql2pgsql install/bin
cp -fr /usr/lib64/mysql/libmysqlclient.so* install/lib
cp -fr /usr/pgsql-9.5/lib/libpq.so* install/lib

配置任务

修改配置文件

这里贴一下我的配置文件。

[root@VM-5-48-centos dbsync]# egrep -v "^#" my.cfg 
[src.mysql]
host = "10.0.5.21"
port = "3306"
user = "dts_user"
password = "dts_admin"
db = "dts_demo"
encodingdir = "share"
encoding = "utf8"

[src.pgsql]

[local.pgsql]

[desc.pgsql]
connect_string = "host=10.0.5.18 dbname=dts_demo port=5436  user=dts_user password=dts_admin"

[binlogloader]

数据同步

mysql2pgsql用法

mysql2pgsql的用法如下所示：

./mysql2pgsql -l <tables_list_file> -d -n -j <number of threads> -s <schema of target table>

参数说明：

-l：可选参数，指定一个文本文件，文件中含有需要同步的表；如果不指定此参数，则同步配置文件中指定数据库下的所有表。<tables_list_file>为一个文件名，里面含有需要同步的表集合以及表上查询的条件，其内容格式示例如下：

table1 : select * from table_big where column1 < '2016-08-05'
table2 : 
table3
table4: select column1, column2 from tableX where column1 != 10
table5: select * from table_big where column1 >= '2016-08-05'

-d：可选参数，表示只生成目的表的建表DDL语句，不实际进行数据同步。
-n：可选参数，需要与-d一起使用，指定在DDL语句中不包含表分区定义。
-j：可选参数，指定使用多少线程进行数据同步；如果不指定此参数，会使用5个线程并发。
-s：可选参数，指定目标表的schema，目前仅支持设定为public。

全库迁移

1. 通过如下命令，获取目的端对应表的DDL。

[root@VM-5-48-centos dbsync]# ./mysql2pgsql -d
ignore copy error count 0 each table

-- Reference commands to create target tables (Please choose a distribution key and replace it with <distribution key> for each table): 
---------------

CREATE TABLE user_info (id int4, c_user_id text, c_name text, c_province_id int4, c_city_id int4, create_time timestamp) with (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=1, BLOCKSIZE=1048576, OIDS=false) DISTRIBUTED BY (<distribution key>) PARTITION BY RANGE (<partition key>) (START (date '<YYYY-MM-DD>') INCLUSIVE END (date '<YYYY-MM-DD>') EXCLUSIVE EVERY (INTERVAL '<1 month>' ));

---------------

-- Number of tables: 1

2. 根据这些DDL，再加入Distribution Key等信息，在目的端创建表。

[root@VM-5-48-centos ~]# psql -d dts_demo -U dts_user -h 10.0.5.18 -p 5436 
Password for user dts_user: 
psql (9.5.25, server 9.4.24)
Type "help" for help.

dts_demo=> CREATE TABLE user_info (id int4, c_user_id text, c_name text, c_province_id int4, c_city_id int4, create_time timestamp) with (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=1, BLOCKSIZE=1048576, OIDS=false) DISTRIBUTED BY (id);
CREATE TABLE

3. 执行如下命令，同步所有表。

[root@VM-5-48-centos dbsync]# time ./mysql2pgsql
ignore copy error count 0 each table
Starting data sync
Query to get source data for target table user_info: select * from `dts_demo`.`user_info` 
-- Reference DDL to create the target table:
CREATE TABLE user_info (id int4, c_user_id text, c_name text, c_province_id int4, c_city_id int4, create_time timestamp) with (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=1, BLOCKSIZE=1048576, OIDS=false) DISTRIBUTED BY (<distribution key>) PARTITION BY RANGE (<partition key>) (START (date '<YYYY-MM-DD>') INCLUSIVE END (date '<YYYY-MM-DD>') EXCLUSIVE EVERY (INTERVAL '<1 month>' ));

thread 3 migrate task 0 table dts_demo.user_info 1000000 rows complete, time cost 3110.404 ms
Number of rows migrated: 1000000 (number of source tables' rows: 1000000) 
Data sync time cost 3178.732 ms

real	0m3.184s
user	0m1.109s
sys	0m0.232s

100万条数据，同步花了3秒，可见速度很快。

[root@VM-5-48-centos ~]# psql -d dts_demo -U dts_user -h 10.0.5.18 -p 5436 
Password for user dts_user: 
psql (9.5.25, server 9.4.24)
Type "help" for help.

dts_demo=> \d
                 List of relations
  Schema  |       Name       |   Type   |  Owner   
----------+------------------+----------+----------
 dts_demo | user_info        | table    | dts_user
(1 rows)

dts_demo=> SELECT COUNT(1) FROM user_info;
  count  
---------
 1000000
(1 row)

dts_demo=> SELECT * FROM user_info LIMIT 20;
 id |              c_user_id               |        c_name        | c_province_id | c_city_id |     create_time     
----+--------------------------------------+----------------------+---------------+-----------+---------------------
  2 | 1afd300e-88bc-11eb-9c30-0c42a125994e | Nj27hTrqAwIQUPiO0qXo |           727 |        95 | 2028-03-19 22:05:05
  3 | 1afd4041-88bc-11eb-9c30-0c42a125994e | J9rzo41MCC2dM5Whp4Zy |           482 |        22 | 2026-03-19 22:05:05
  4 | 1afd4562-88bc-11eb-9c30-0c42a125994e | RX3eSuFHkqXmNJ8hSoas |           517 |        67 | 2023-03-19 22:05:05
  6 | 1afd4ebd-88bc-11eb-9c30-0c42a125994e | ydfrgRm1VlPX8FLFSeo5 |           968 |         3 | 2027-03-19 22:05:05
  7 | 1afd530c-88bc-11eb-9c30-0c42a125994e | rsMpwgyPk0TiBXO2AFr3 |           585 |        25 | 2027-03-19 22:05:05
  8 | 1afd574a-88bc-11eb-9c30-0c42a125994e | H5aqu0qT4xgB06i1341J |           293 |        73 | 2027-03-19 22:05:05
  9 | 1afd5cf9-88bc-11eb-9c30-0c42a125994e | Y10PZgc4AzTDjxyY5ke0 |            31 |        60 | 2025-03-19 22:05:05
 10 | 1afd61a8-88bc-11eb-9c30-0c42a125994e | 761DXGqU7GUjHpKns2E0 |           732 |        12 | 2022-03-19 22:05:05
 13 | 1afd6f01-88bc-11eb-9c30-0c42a125994e | pNCyKUaVYVyQqowgB3kl |           370 |        31 | 2028-03-19 22:05:05
 16 | 1afd7bcf-88bc-11eb-9c30-0c42a125994e | j8zjGigivtHUhwDq2OK9 |           172 |        90 | 2025-03-19 22:05:05
 18 | 1afd842c-88bc-11eb-9c30-0c42a125994e | 0DZUqdFwtEGifda3AA4p |           480 |        67 | 2028-03-19 22:05:05
 19 | 1afd886b-88bc-11eb-9c30-0c42a125994e | 6SRyZ7v0mCP981zBaSIL |           374 |         5 | 2022-03-19 22:05:05
 21 | 1afd913b-88bc-11eb-9c30-0c42a125994e | JHbEzIIg037fKPJ0FbK4 |           730 |        93 | 2027-03-19 22:05:05
 22 | 1afd9596-88bc-11eb-9c30-0c42a125994e | FSemWreIG6i3eQm7k7qE |           673 |        87 | 2028-03-19 22:05:05
 24 | 1afd9ea8-88bc-11eb-9c30-0c42a125994e | YnMBwEvqqI8mg4oAzZ25 |           960 |         4 | 2026-03-19 22:05:05
 27 | 1afdab41-88bc-11eb-9c30-0c42a125994e | z1eUGAFq0zFvl2ZFf2ie |           953 |        53 | 2028-03-19 22:05:05
 28 | 1afdafc8-88bc-11eb-9c30-0c42a125994e | ZJvabgB2dPk0TfrhVB7D |           500 |         6 | 2028-03-19 22:05:05
 29 | 1afdb407-88bc-11eb-9c30-0c42a125994e | Y4fRnay6I454UaZgaSJS |           848 |        10 | 2028-03-19 22:05:05
 32 | 1afdc0bc-88bc-11eb-9c30-0c42a125994e | Lam7pt0r0zFs9dqnoJi6 |           300 |        68 | 2028-03-19 22:05:05
 33 | 1afdc4fc-88bc-11eb-9c30-0c42a125994e | HPPx2oG7mid4xiGpRSEu |           190 |        94 | 2028-03-19 22:05:05
(20 rows)