mysql迁移至postgresql_迁移实战：一次 AntDB 数据库迁移经验分享

一、前言

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.shread -p "请输入读取的文件:" read_fileread -p "是否为分区表 ?yes/no:" partition_tableread -p "请输入主机名或者IP:" host_name_ipread -p "请输入端口:" port_numberread -p "请输入数据库名字：" db_nameread -p "请输入用户名:" user_nameread -p "请输入模式名:" schema_nameif [ "$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.shmkdir -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/$xdonemv *.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.shfor 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.shfor 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. 表数据迁移

...

✨ 接下来内容请访问原文(https://www.modb.pro/db/29946?YYF)进行查看~

更多数据库相关内容，可访问墨天轮(https://www.modb.pro/?YYF)进行浏览。