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

select distinct p.relname fq_table
from pg_class p,pg_inherits i
where 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_table
from pg_partitioned_table order by fq_table) and tablename not in(select c.relname
from pg_class a left join pg_inherits b  on a.oid=b.inhparent
left  join pg_class c on b.inhrelid=c.oid where c.relname is not null) and schemaname='itv' order by tablename;

vi pg_dump_table.sh

read -p "请输入读取的文件:" read_file
read -p "是否为分区表 ?yes/no:" partition_table
read -p "请输入主机名或者IP:" host_name_ip
read -p "请输入端口:" port_number
read -p "请输入数据库名字：" db_name
read -p "请输入用户名:" user_name
read -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

vi modify_partition_sql.sh

mkdir -p ./partition_table_by_date_no
mkdir -p ./partition_table_by_date_no_result
mv ./*_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/$x
done

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.log
done
--非分区表
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.log
done

select a.relname,c.relname
from pg_class a left join pg_inherits b  on a.oid=b.inhparent
left  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

create table partition_table_default partition of partition_base_table_name default;

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.log
set search_path=&schema_name;
\timing on
\copy partition_table_name to '/dir/partition_table_name.csv' with (format csv);
\q
EOF

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.log
set search_path=&schema_name;
\timing on
\copy partition_table_name from '/dir/partition_table_name.csv' with (format csv);
\q
EOF

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

nohup  migrate_table.sh &