大批量分布键改造方案
一、方案简介
本方案旨在指导实施人员完成数据库分布键优化改造。
二、适用场景
针对数据库中大量表含有XXX字段并且XXX字段不为分布键的表，将分布键批量刷新为XXX字段。
三、修改分布键
(一) 原数据库数据导出
1. 原数据库用户数据导出
gs_dumpall -s -g -p 25308 -f olddb_all_user.sql > olddb_all_user.log 2>&1 |
正确性验证：
导出完成后检查olddb_all_user.log文件是否有报错。
注：
olddb_all_user.sql是要生成的用户数据文件。
olddb_all_user.log是导出时的日志文件。
2. 原数据库对象元数据导出
gs_dump -p 25308 olddb -s -f olddb_ddl.sql > olddb_ddl.log 2>&1 |
正确性验证：
导出完成后检查olddb_ddl.log文件是否有报错。
注：
olddb替换成要导出元数据的原数据库库名。
olddb_ddl.sql替换成要导出的原数据文件名。
olddb_ddl.log是导出数据的日志文件。
3. 原数据库全量数据导出
使用omm用户登录原数据库服务器，导出数据库全量数据，执行：
gs_dump olddb -p 25308 -a -f olddb_data.sql > olddb_data.log 2>&1 & |
正确性验证：
导出完成后检查olddb_data.log文件是否有报错。
注：
olddb替换成要导出元数据的元数据库库名。
olddb_data.sql替换成要导出的元数据文件名。
olddb_data.log是导出数据的日志文件。
4. 原数据库元数据及数据传输至验证环境
使用omm用户登录验证服务器，创建路径，执行：
mkdir -p /db_backup/provovince/olddb/ |
使用omm用户登录原数据库服务器，
使用以下命令传输用户定义，元数据定义及全量数据至验证集群规划路径，执行：
scp olddb_ddl.sql olddb_data.sql olddb_all_user.sql omm@xx.xx.xx.xx:/db_backup/provovince/olddb/ |
正确性验证：
检查传输过程中有无报错。
(二) 验证环境XXX改造操作
1. 创建数据库用户
gsql -d postgres -p 25308 -f olddb_all_user.sql > import_olddb_all_user.log 2>&1 |
正确性验证：
创建完成后，登录数据库通过\dg命令查看数据库是否创建成功。
注：
newdb替换成新建的数据库名。
2. 创建验证数据库
使用omm用户登录验证环境,创建新database，执行：
gsql -d postgres -p 25308 -c "CREATE DATABASE newdb WITH TEMPLATE = template0 ENCODING = 'GBK' DBCOMPATIBILITY = 'ORA';" gsql -d postgres -p 25308 -c "ALTER DATABASE newdb OWNER to old_user;" |
正确性验证：
创建完成后，登录数据库通过\l命令查看数据库是否创建成功。
注：
newdb替换成新建的数据库名。
old_user 原来数据库对象owner用户。
3. 导入元数据
开启事务执行：
#开启事务执行 sed -i '1i\begin;' olddb_ddl.sql echo "commit;" >>olddb_ddl.sql nohup gsql -d newdb -p 25308 -f olddb_ddl.sql > import_newdb_ddl.log 2>&1 &
nohup gsql -d newdb –p25308 gsql -d newdb –p 25308 -c "GRANT ALL ON SCHEMA old_user TO PUBLIC;" gsql -d newdb –p 25308 -c "GRANT ALL ON SCHEMA old_user TO old_user;" |
正确性验证：
创建完成后，检查import_newdb_ddl.log日志是否有异常报错信息，如有报错请排除报错后，重新执行。
注：
old_user 原来数据库对象owner用户。
newdb替换成新建的数据库名。
olddb_ddl.sql替换成导出的元数据的文件名。
import_newdb_ddl.log替换成要生成导入日志的文件名。
4. 改造XXX表的约束信息
使用omm用户登录验证环境,导出验证数据库的porvince改造表的约束信息，执行：
gsql -d newdb -p 25308 -ar -t \o newdb_alter_index.sql SELECT case when ct.oid is not null then 'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" DROP CONSTRAINT "' || ic.relname || '";' || 'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" ADD CONSTRAINT "' || ic.relname || '" ' || pg_get_constraintdef(ct.oid, true) || ';' else 'DROP INDEX "' || ie.nspname || '"."' || ic.relname || '";' || pg_get_indexdef(i.indexrelid) || ';' end FROM pgxc_class pc LEFT JOIN pg_class c ON c.oid = pcrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_index i ON i.indrelid = c.oid INNER JOIN pg_class ic ON ic.oid = i.indexrelid inner join pg_namespace ie on ie.oid = ic.relnamespace LEFT JOIN pg_constraint ct ON ct.conname = ic.relname WHERE pc.pclocatortype = 'H' AND ( i.indisprimary OR i.indisunique ) AND c.oid IN ( SELECT attrelid FROM pg_attribute WHERE attname = 'XXX' ) AND getdistributekey ( c.oid ) <> 'XXX' AND CASE WHEN ct.oid is not null THEN instr( pg_get_constraintdef ( ct.oid, TRUE ), 'XXX' ) ELSE instr( pg_get_indexdef ( i.indexrelid ), 'XXX' ) END = 0; \o \q |
正确性验证：
脚本生成后，检查newdb_alter_index.sql脚本中导出的约束定义。
注：
newdb_alter_index.sql是生成文件的文件名。
通过sed命令，改造导出的约束定义，加入XXX字段，执行：
cp alter_index.sql alter_index_new.sql sed -i 's/(\(.*\))/(\1,XXX)/g' alter_index_new.sql |
正确性验证：
脚本修改后，检查alter_index_new.sql脚本中是否添加XXX字段。
使用omm用户登录验证环境,修改验证数据库的porvince改造表的约束信息，执行：
nohup gsql -d new_db -p 25308 -f alter_index_new.sql > alter_index_new.log 2>&1 & |
正确性检查:
执行成功后，gsql登录数据库，通过\d+ tablename 检查约束是否修改成功。
注：
new_db是新数据库名。
alter_index.sql是生成的alter index语句。
alter_index_new.sql是修改后的alter index语句。
alter_index_new.log是执行时生成的日志。
5. 导出XXX改造表清单
使用omm用户登录验证环境，导出验证数据库的porvince改造表表清单，执行：
gsql -d newdb -p 25308 -ar -t \o newdb_tablelist.txt select n.nspname||'.'||c.relname from pgxc_class x left join pg_class c on c.oid = x.pcrelid left join pg_namespace n on n.oid = c.relnamespace where pclocatortype = 'H' and c.oid in (select attrelid from pg_attribute where attname= 'XXX') and getdistributekey(c.oid) <> 'XXX'; #删除表清单newdb_tablelist.txt行首空格 sed -i 's/^[ \t]*//' newdb_tablelist.txt |
正确性检查:
检查执行过程中是否有报错以及导出表清单信息。
注：
newdb新数据库名。
6. 导出XXX改造表定义
使用omm用户登录验证环境，导出验证数据库的porvince改造表表定义，执行：
gs_dump newdb -p 25308 -s --include-table-file=newdb_tablelist.txt -f newdb_table_ddl.sql |
正确性检查:
检查执行过程中是否有报错以及导出表定义信息。
注：
newdb新数据库名。
7. 导出XXX改造表依赖数据库对象
使用omm用户登录验证环境，导出验证数据库的porvince改造表依赖对象定义，执行：
gs_dump newdb -p 25308 -s --include-table-file=newdb_tablelist.txt --include-depend-objs --exclude-self -f newdb_depend_obj.sql |
正确性检查:
检查执行过程中是否有报错以及导出依赖对象定义信息。
注：
newdb新数据库名。
8. 删除XXX改造表
使用omm用户登录验证环境，生成删除验证数据库的porvince改造表的脚本及执行删除操作，执行：
gsql -d newdb -p 25308 -ar -t \o newdb_drop_table.sql select 'drop table '||n.nspname||'.'||c.relname||' cascade;' from pgxc_class x left join pg_class c on c.oid = x.pcrelid left join pg_namespace n on n.oid = c.relnamespace where pclocatortype = 'H' and c.oid in (select attrelid from pg_attribute where attname= 'XXX') and getdistributekey(c.oid) <> 'XXX'; #事务执行 sed -i '1i\begin;' newdb_drop_table.sql echo "commit;" >>newdb_drop_table.sql
#执行删除 gsql -d newdb -p 25308 -f newdb_drop_table.sql > newdb_drop_table.log 2>&1 & |
正确性检查:
检查执行过程中是否有报错，删除完成后，登录数据库检查被删除表是否存在。
注：
newdb新数据库名。
newdb_drop_table.sql删除表的sql文件。
newdb_drop_table.log执行删除表的日志。
9. 改造XXX表表定义
使用omm用户登录验证环境，修改XXX表定义，分布列字段替换为XXX，执行：
cp table_ddl.sql table_ddl_new.sql sed -i 's/DISTRIBUTE BY HASH (.*)/DISTRIBUTE BY HASH (XXX)/g' table_ddl_new.sql |
正确性检查:
检查修改过程中有无报错，修改后检车表字段是否替换为XXX。
方案验证完成情况：
10. 创建XXX改造后的表
使用omm用户登录验证环境，验证数据库创建XXX改造后的表，执行：
# newdb_table_ddl_new.sql脚本增加事务执行 sed -i '1i\begin;' newdb_table_ddl_new.sql echo "commit;" >>newdb_table_ddl_new.sql #执行创建表操作
gsql -d newdb -p 25308 -f newdb_table_ddl_new.sql > newdb_table_ddl_new.log 2>&1 & |
正确性检查:
检查执行过程中是否有报错，创建完成后，登录数据库检查创建的表是否存在
注：
newdb新数据库名。
newdb_table_ddl_new.sql新表定义。
newdb_table_ddl_new.log导入时生成的日志。
11. 创建XXX表依赖表的数据库对象
使用omm用户登录验证环境，验证数据库创建XXX改造后的表依赖数据库对象，执行：
#增加事物 sed -i '1i\begin;' newdb_depend_obj.sql echo "commit;" >>newdb_depend_obj.sql #执行创建操作
gsql -d newdb -p 25308 -f newdb_depend_obj.sql > newdb_depend_obj.log 2>&1 & |
正确性检查:
检查执行过程中是否有报错，创建完成后，登录数据库检查创建的表是否存在
12. 导入原数据库数据
使用omm用户登录验证环境，验证数据库导入生产库数据，执行：
nohup gsql -d newdb -p 25308 -f olddb_data.sql > import_newdb_data.log 2>&1 & |
正确性检查:
检查执行过程中是否有报错，导入完成后通过下一章节checksum脚本验证数据一致性。
注：
newdb新数据库名。
password用户的密码
olddb_data.sql原库导出的数据文件。
import_newdb_data.log导入时产生的日志。
13. 测试环境XXX改造前后数据一致性验证
使用checksum函数可以用来验证数据库的备份恢复或者数据迁移操作前后表中的数据是否相同。在备份恢复或者数据迁移操作前后都需要用户通过手工执行SQL命令的方式获取执行结果，通过对比获取的执行结果判断操作前后表中的数据是否相同。
使用checksum函数可以用来验证数据库的备份恢复或者数据迁移操作前后表中的数据是否相同。
omm用户登录原数据库服务器，执行如下操作：
#环境变量生效
#生成checksum检查脚本 gsql -d database -p 25308 -t -c " SELECT 'select '''||n.nspname||'.'||c.relname||''' , CHECKSUM(\"'||c.relname||'\"::TEXT) FROM \"'||n.nspname||'\".\"'||c.relname ||'\";' FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind ='r' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' and n.nspname not in ('sys','pmk','cstore') ORDER BY 1; ">checksum.sql #执行checksum检查 gsql -d database -p 25308 -f checksum.sql>checksum.log #处理checksum日志，删除空行，输出checksum结果 sed -i '/^[[:space:]]*$/d' checksum.log cat checksum.log|grep -v '(\|checksum\|total time\|-'>checksum_result |
此脚本分别对每张表进行checksum校验，分别在导数前后两个库执行此脚本，可通过比较工具对比checksum结果，checksum值不一致的需分析不一致原因。
在excel中进行比对每张表的checksum值，验证改造前后数据一致性。
(三) 新环境XXX方案部署
1. 验证环境导出验证database原数据
使用omm用户登录数据库服务器，导出原数据库数据对象创建语句，执行：
gs_dump -p 25308 newdb -s -f newdb_ddl.sql > newdb_ddl.log 2>&1 |
正确性验证：
导出完成后检查newdb_ddl.log文件是否有报错。
注：
Newdb验证环境database名。
2. 创建新数据库
使用omm用户登录生产环境,创建新database，执行：
gsql -d postgres -p 25308 -c "CREATE DATABASE newdb WITH TEMPLATE = template0 ENCODING = 'GBK' DBCOMPATIBILITY = 'ORA';" gsql -d postgres -p 25308 -c "ALTER DATABASE newdb OWNER to old_user;" |
正确性验证：
创建完成后，登录数据库通过\l命令查看数据库是否创建成功。
注：
newdb替换成新建的数据库名，新数据库名待boss规划及反馈。
3. 新环境导入改造及验证后的全量原数据
使用omm用户登录新环境,导入新database，执行：
#开启事务执行 sed -i '1i\begin;' newdb_ddl.sql echo "commit;" >>newdb_ddl.sql nohup gsql -d newdb -p 25308 -f newdb_ddl.sql > import_newdb_ddl.log 2>&1 & |
正确性验证：
创建完成后，检查import_newdb_ddl.log日志是否有异常报错信息，如有报错请排除报错后，重新执行。
注：
newdb替换成新建的数据库名。
newdb_ddl.sql替换成导出的元数据的文件名。
import_newdb_ddl.log替换成要生成导入日志的文件名。
4. 备份原库数据，导入新数据库
使用omm用户登录生产环境,导出原数据库的全量数据，导入新的数据库库，执行：
#导出原生产库数据
gs_dump olddb -p 25308 -a -f olddb_data.sql > olddb_data.log 2>&1 &
#全量导入到新库
nohup gsql -d newdb -p 25308 -f olddb_data.sql > import_newdb_data.log 2>&1 & |
正确性检查:
检查执行过程中是否有报错，导入完成后通过下一章节checksum脚本验证数据一致性。
注：
newdb新数据库名。
olddb_data.sql原库导出的数据文件。
import_newdb_data.log导入时产生的日志。
方案验证完成情况：
集群IP |
database |
是否完成 |
10.77.20.45 |
yszx_p |
|
10.77.20.45 |
yszx_sb_p |
|
10.77.20.45 |
yszx_sb_p |
|
10.77.20.45 |
yszx_sb_p |
|
10.77.20.45 |
yszx_sb_p |
5. 新环境XXX改造前后数据一致性验证
使用checksum函数可以用来验证GaussDB 300数据库的备份恢复或者数据迁移操作前后表中的数据是否相同。在备份恢复或者数据迁移操作前后都需要用户通过手工执行SQL命令的方式获取执行结果，通过对比获取的执行结果判断操作前后表中的数据是否相同。
使用omm用户分别登录5套生产数据库，生成5套生产原数据库checksum脚本，执行：
gsql -d olddb -p 25308 -r -t -c " SELECT 'select '''||n.nspname||'.'||c.relname||''' , CHECKSUM("'||c.relname||'"::TEXT) FROM "'||n.nspname||'"."'||c.relname ||'";' FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind ='r' AND n.nspname !~ '^pg_toast' and n.nspname not in ('sys','pmk','cstore','information_schema','pg_catalog') ORDER BY 1;" >olddb_checksum.sql gsql -d olddb -p 25308 -f olddb_checksum.sql>olddb_checksum.log |
使用omm用户登录生产数据库，分别生成5个database checksum脚本，执行：
gsql -d olddb -p 25308 -r -t -c " SELECT 'select '''||n.nspname||'.'||c.relname||''' , CHECKSUM("'||c.relname||'"::TEXT) FROM "'||n.nspname||'"."'||c.relname ||'";' FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind ='r' AND n.nspname !~ '^pg_toast' and n.nspname not in ('sys','pmk','cstore','information_schema','pg_catalog') ORDER BY 1;" >olddb_checksum.sql gsql -d olddb -p 25308 -f olddb_checksum.sql>olddb_checksum.log |
在excel中进行比对每张表的checksum值，验证改造前后数据一致性。
6. 新环境XXX改造后数据库操作
使用omm用户登录新数据库，执行全库anzlyze操作，执行：
gsql -d newdb -p 25308 -r -t -c "analyze;" |
正确性检查:
检查执行过程中是否有报错。
- 点赞
- 收藏
- 关注作者
评论（0）