【原】迁移实战：一次AntDB（基于pgxl分布式架构的数据库）数据库迁移经验分享

墨墨导读：AntDB是一款基于PG内核的分布式数据库。根据AntDB官方介绍，该数据具备持续的集群自动高可用，秒级在线扩容，强大的Oracle兼容，异地容灾，sql语句级自定义分片，分布式事务和MVCC，是一款非常强大的企业级国产分布式数据库。由于AntDB在使用过程中，与pgxl很相近，所以本文对pgxl的情况下有一定的帮助。

一、前言

AntDB是一款基于PG内核的分布式数据库。根据AntDB官方介绍，该数据具备持续的集群自动高可用，秒级在线扩容，强大的Oracle兼容，异地容灾，sql语句级自定义分片，分布式事务和MVCC。是一款非常强大的企业级国产分布式数据库。其架构如下（本图参考网络图片）：

在当前的情况下，大部分企业关心的是如何将Oracle数据库迁移到mysql数据库、postgresql数据库等开源及国产数据库中。而很少关注这些开源及国产数据库本身的迁移与升级。

由于AntDB在使用过程中，与pgxl很相近，所以本文对pgxl的情况下应该还是有一定的帮助。

二、背景

最近，某客户现场需要将其之前使用的AntDB进行升级。由于本次升级，涉及的版本不同、节点数量不同。因此，综合考虑只能选用逻辑的方式完成本次数据库的迁移和升级。

三、迁移思路

Postgresql中，我们可以通过pg_dump和pg_restore进行逻辑导入和恢复。但是在本次迁移中，源AntDB存放的表数量以及数据量都非常大（表数量基本由22w张，数据量大约有46T）。所以，如果使用pg_dump和pg_restore的方式，其效率将会非常低。

因此，我们则选择使用postgresql中的copy的方式进行迁移。COPY是PostgreSQL中表和标准文件系统文件之间交换数据的方式，可以理解为直接将文件系统文件中的数据直接装载到数据库中，而不是传统的通过insert语句方式逐条插入数据。因此，在postgreSQL中，通过COPY的方式，将会使数据导入更快。

四、迁移步骤

AntDB的迁移总体分为两个步骤：
1）表结构迁移
2）数据迁移

接下来，我将分别分享这两个步骤中的迁移经验和遇到的问题。

1. 表结构迁移

在本场景的迁移过程中，由于源端是基于PostgreSQL 9.6的数据库，目标端是基于PostgreSQL 11.6的数据库。而9.6和11.6两个大版本数据库中很多特性又是不同的，故也为迁移造成一些难度。其中，在本场景中最重要的就是分区表。

PostgreSQL 9.6数据库中，并没有分区表的概念，其分区表则主要是通过继承表+触发器来实现的，表数据是根据触发器条件来写入到不同子表中的；

PostgreSQL 11.6数据库中则加入了分区表的概念，可以直接创建基于基表的分区表，数据可以根据分区键条件插入到各自的分区中。

那么在迁移中遇到的一个难点就是：如何将9.6中的父表、子表的关系转换成11.6中的分区表。

在postgreSQL中，我们无法像Oracle里面的get_ddl函数一样，获取表的创建语句。但是，我们可以通过pg_dump的方式，将PosgreSQL中的表结构导出，生成SQL语句。

因此，我们首先需要从源端数据库中找出分区表和非分区表，通过以下SQL就可以在PostgreSQL中找到分区表和非分区表。

查询分区表有哪些：

select distinct p.relname fq_tablefrom pg_class p,pg_inherits iwhere p.oid=i.inhparent and reltype<>0 order by fq_table;

查询非分区表有哪些：

select tablename from pg_tables where tablename not in(select partrelid::regclass::text fq_tablefrom pg_partitioned_table order by fq_table) and tablename not in(select c.relnamefrom pg_class a left join pg_inherits b on a.oid=b.inhparentleft join pg_class c on b.inhrelid=c.oid where c.relname is not null) and schemaname='itv' order by tablename;

确定好分区表和非分区表以后，我们可以将查出的表名写到一个txt文本文件中，然后通过以下脚本来生成pg_dump语句（当然也可以自己写脚本或者程序进行生成，其核心就是生成：pg_dump -h $host_name_ip -p $port_number -d $db_name -U $user_name -n $schema_name -s -t $x -f $x_MyPartition.sql的语句）：

vi pg_dump_table.sh
read -p "请输入读取的文件:" read_fileread -p "是否为分区表 ?yes/no:" partition_tableread -p "请输入主机名或者IP:" host_name_ipread -p "请输入端口:" port_numberread -p "请输入数据库名字：" db_nameread -p "请输入用户名:" user_nameread -p "请输入模式名:" schema_name
if [ "$partition_table" = "yes" ] || [ "$partition_table" = "y" ]; then echo '' > $read_file.sh for x in `cat $read_file.txt` do echo pg_dump -h $host_name_ip -p $port_number -d $db_name -U $user_name -n $schema_name -s -t $x -f $x\_MyPartition.sql >> $read_file.sh done echo script complete.elif [ "$partition_table" = "no" ] || [ "$partition_table" = "no" ]; then echo '' > $read_file.sh for x in `cat $read_file.txt` do echo pg_dump -h $host_name_ip -p $port_number -d $db_name -U $user_name -n $schema_name -s -t $x -f $x.sql >> $read_file.sh done echo script complete.else echo 'please input yes/no'fi

生成的.sh脚本中的内容即为pg_dump导出表结构的语句。

但是，按照我们在上面提到的PostgreSQL 9.6中没有分区表概念。所以，我们导出的表结构也不会有分区键在里面。

因此，我们就需要手动去修改这些表结构创建语句，根据其实际的分区键添加partition by (segment_name)。这个工作，需要对所有表的分区情况和分区键比较熟悉，整个过程是一个体力活，所以不展开叙述。但当我们了解所有分区表的分区定义后，也可以直接通过脚本在文本上进行增加，举例如下（核心就是通过sed匹配建表语句中的结尾括号，然后进行替代）：

vi modify_partition_sql.sh
mkdir -p ./partition_table_by_date_nomkdir -p ./partition_table_by_date_no_resultmv ./*_MyPartition.sql ./partition_table_by_date_no/
for x in `ls ./partition_table_by_date_no/`do echo $x sed -i "s/^)$/) partition by list(date_no)/g" ./partition_table_by_date_no/$x mv ./partition_table_by_date_no/$x ./partition_table_by_date_no_result/$xdone
mv *.sql ./partition_table_by_date_no/tar -cvf partition_table_by_date_no.tar partition_table_by_date_no/tar -cvf partition_table_by_date_no_result.tar partition_table_by_date_no_result/

修改完分区表信息后，则可以通过以下脚本直接连接到数据库中执行：

--分区表vi create_partitionBase_table.sh
for x in `ls partition_table_by_date_no_result`do echo $x >> ./create_partitionBase_table.log psql -h antdb01 -p 15432 -Udctest -d dcrptdb1 -f ./partition_table_by_date_no_result/$x >> ./create_partitionBase_table.logdone--非分区表vi create_nopartition_table.sh
for x in `ls partition_table_by_date_no`do echo $x >> ./create_nopartition_table.log psql -h antdb01 -p 15432 -Udctest -d dcrptdb1 -f ./partition_table_by_date_no/$x >> ./create_nopartition_table.logdone

到这里所有分区基表和非分区表的表结构就创建好了，那么下一步针对分区表我们就要创建对应的分区了。

创建分区首先我们需要从源库将原来的分区信息查出来，故通过以下语句可以查出表的分区信息：

select a.relname,c.relnamefrom pg_class a left join pg_inherits b on a.oid=b.inhparentleft join pg_class c on b.inhrelid=c.oid where a.relname = table_name::text;

找到这些分区后，则需要手动根据分区名字创建分区表，以下举例说明：

create table partition_table_name partition of partition_base_table_name for values in('20200201');partition_base_table

另外，在导数过程中，源端数据可能有变化，对分区进行删除，那么部分数据可能已经查到，但不属于任何一个分区，这样在导入数据时则会报错，因此为了保证导数不报错，我们则可以考虑创建一个default分区，用来存放不属于任何分区的数据，以下举例说明：

create table partition_table_default partition of partition_base_table_name default;

至此，所有的表结构就都创建好了。

2. 表数据迁移

表数据迁移过程相对来说比较简单，主要时通过copy from/copy to方式，从源端将数据导出，然后在目标端再进行导入即可。

起初，指定的方案是从目标端登录，以目标端的psql为客户端，远程登录源端的postgreSQL数据库，然后通过以下脚本语句，将数据导为csv格式（脚本模板，&开头都为实际情况下的IP、端口、表名等值）：

vi partition_table_name_copy_in_csv.sh
psql -h &host -p &port_number -d &database_name -U &database_name << EOF >> /data_dir/partition_table_name_copy_in_csv.logset search_path=&schema_name;\timing on\copy partition_table_name to '/dir/partition_table_name.csv' with (format csv);\qEOF

然后再通过以下脚本，将数据导入：

vi partition_table_name_copy_out_csv.sh
psql -h &host -p &port_number -d &database_name -U &database_name << EOF >> /data_dir/partition_table_name_copy_out_csv.logset search_path=&schema_name;\timing on\copy partition_table_name from '/dir/partition_table_name.csv' with (format csv);\qEOF

但是该方案中有个缺点，就是将数据落地为csv格式，会占用实际的空间，1T表可能会生成1T左右的CSV，而在导入过程中，该csv数据是不能删除的。那么实际就会占用2倍的空间；而在实际情况下，单台机器也没有这么打的空间存放csv。所以只能部分表导出后，再执行导入脚本，导入成功后，删除csv文件，再次导出/导入。

这样操作，就会增加迁移的复杂程度和时间。

那么，有没有一种方式可以不把数据进行落地就导入导出呢？

通过对COPY语法的研究，发现在postgreSQL中，存在copy…to stdout和copy…to stdin两种方式，这两种方式表示将数据copy后输出到标准输出（在psql中执行，则会直接打印在屏幕上），而copy…to stdin则表示从标准输入中导入数据（在psql中，会将打印在屏幕上的输出导入导库中）。

所以，通过这两个特性，我们就可以结合Linux的管道符，将两种方式进行连接，然后就可以实现数据不落地的导入。

那么我们该如何判断copy成功了呢？其实，只需要在第二个语句后,把导入的结果指向到一个日志文件即可。示例如下：

psql -h &source_host -p &port_number -U &user -d &user -c "\copy (select * from &table_name where &partition_con<'202009') to stdout"|psql -h &target_host -p &port_number -U &user_name -d &database_name -c "\copy table_name from stdin" >> &table_name.log

将所有表全部拼写成上述类似的语句后，全部放在migrate_table.sh的脚本文件中，直接通过以下命令执行脚本即可：

nohup migrate_table.sh &

需要注意的是，这种实际上还是执行了两个语句，一个是copy to，另一个是copy from。那么当源端copy出问题后，错误信息会在nohup.out中输出，如果目标端copy出问题后，则会在table_name.log中生成COPY 0的记录。这样，当某张表迁移出问题，就可以重新根据表名记录来确定并重新导入了。

最后，还有一个需要注意的点：
COPY from命令在Postgersql中，会直接起一个事务。即当其中一条数据插入失败，整个事务就会回滚。所以只要有COPY 0的记录，可以在源端查一下是不是该表真的没有数据。如果是导入失败的，则该表可以直接重新导，里面不会有上次导入的数据记录。

墨天轮原文链接：https://www./db/29946（复制到浏览器中打开或者点击“阅读原文”）