从PostgreSQL迁移到YashanDB如何进行数据行数比对
简介
目前从PostgreSQL迁移到YashanDB后,需要进行数据校验。下面给出user1模式从PostgreSQL迁移到YashanDB进行数据行数比对的示例。
详情
获取PostgreSQL精确行数
创建table_count,用于存储行数(建议:使用现有的迁移模式user1,并把table_count创建在user1用户下)
create table user1.table_count (owner varchar(200),table_name varchar(200),num_rows int); |
获取user1模式下的所有表的行数
DO $$ DECLARE one_row record; stmt varchar(200); num_rows int; BEGIN
EXECUTE 'truncate table table_count'; FOR one_row IN (select schemaname, tablename from pg_tables where upper(schemaname)=upper('user1') and upper(tablename)!=upper('table_count')) loop
stmt := 'select count(*) from ' || one_row.schemaname || '.' || one_row.tablename || ''; raise notice '%', stmt; EXECUTE stmt into num_rows; raise notice '%', num_rows; insert into table_count values(upper(one_row.schemaname), upper(one_row.tablename), num_rows); END loop; commit; END $$; |
获取YashanDB精确行数
从PostgreSQL迁移到YashanDB迁移user1模式,迁移完成后可以创建table_count ,用于存储行数(建议:创建新的用户db_yashan,并把table_count创建在db_yashan用户下)
create table db_yashan.table_count (owner varchar(200),table_name varchar(200),num_rows int); |
获取user1用户下的所有表的行数
declare v_owner VARCHAR2(100); v_tabname VARCHAR2(100); stmt VARCHAR2(200); num_rows number; begin EXECUTE IMMEDIATE 'truncate table db_yashan.table_count'; for rec in (select owner,table_name from dba_tables where owner=upper('user1') and tablename!=upper('table_count') order by 1, 2) loop select rec.owner,rec.table_name into v_owner,v_tabname from dual; stmt := 'select count(*) from "' || v_owner || '"."' || v_tabname || '"'; EXECUTE IMMEDIATE stmt INTO num_rows; insert into db_yashan.table_count values(v_owner,v_tabname,num_rows); end loop; end; / |
对比PostgreSQL和YashanDB的精确行数
SELECT owner, table_name, p_num_rows, y_num_rows FROM ( SELECT nvl(p.owner, y.owner) owner, nvl(p.table_name, y.table_name) table_name, p.num_rows p_num_rows, y.num_rows y_num_rows FROM ( SELECT owner, table_name, num_rows FROM user1.table_count) p FULL OUTER JOIN ( SELECT owner, table_name, num_rows FROM db_yashan.table_count) y ON p.owner = y.owner AND p.table_name = y.table_name ORDER BY p.owner, p.table_name ) WHERE nvl(p_num_rows, 999999999999999999999999999999) != nvl(y_num_rows, 999999999999999999999999999999) ; |