说明
本文延续上一篇文章 云数据库MySQL导入云数据仓库PostgreSQL最佳实践，继续介绍云数据库MySQL导入云数据仓库PostgreSQL的使用问题。其中描述的问题及解决方法同样适用于 腾讯云 云数据仓库 PostgreSQL（CDWPG）。
背景
在实际数据同步的场景中，大多的需求是迁移。这种情况下，如果表的数量很多的话，那数据同步的成本是非常大的，因为目标端需要提前构建出全部的表结构。这个时候我们可以视情况选择使用数据同步开源工具 （rds_dbsync），该工具具有结构化导出的能力。
安装依赖
安装mysql的开发包
安装mysql5.7的yum源：
代码语言：javascript复制[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：
代码语言：javascript复制[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!执行安装：
代码语言：javascript复制[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源：
代码语言：javascript复制[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%]执行安装：
代码语言：javascript复制[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放到环境变量里的：
代码语言：javascript复制[root@VM-5-48-centos ~]# pg_config
-bash: pg_config: command not found所以我们需要将将下面的内容追加到/etc/profile，然后加载pg_config到环境变量：
代码语言：javascript复制[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 编译器，所以这里需要安装一下：
代码语言：javascript复制[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
下载源码
代码语言：javascript复制[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]解压源码包
代码语言：javascript复制[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 执行编译
代码语言：javascript复制[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执行安装
代码语言：javascript复制[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配置任务
修改配置文件
这里贴一下我的配置文件。
代码语言：javascript复制[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的用法如下所示：
代码语言：javascript复制./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。
代码语言：javascript复制[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等信息，在目的端创建表。
代码语言：javascript复制[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 TABLE3. 执行如下命令，同步所有表。
代码语言：javascript复制[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.232s100万条数据，同步花了3秒，可见速度很快。
代码语言：javascript复制[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)简单验证了一下，确认同步完成。
