人大金仓数据库转换

##人大金仓数据库 V9转到本地V8

1.提取原始数据

使用人大金仓转换工具 Kingbase\ES\V8\KESRealPro\V008R006C008B0014\ClientTools\guitools\KDts\KDTS-WEB\bin

startup.bat

浏览器访问 http://localhost:54523（端口可能发生变化）

2.修改表数据结构清理数据

##修改时间戳 "timestamptz"

DO $$

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT table_name, column_name,data_type

FROM information_schema.columns

where table_schema = 'public'

AND data_type = 'timestamp with time zone'

LOOP

EXECUTE 'ALTER TABLE ' || rec.table_name || ' ALTER COLUMN ' || rec.column_name || ' TYPE timestamp';

END LOOP;

END $$;

##删除无用数据

DELETE FROM infra_api_access_log ;

DELETE FROM infra_api_error_log;

DELETE FROM infra_codegen_column;

DELETE FROM infra_codegen_table;

DELETE FROM infra_file;

DELETE FROM infra_file_content;

DELETE FROM system_login_log;

DELETE FROM system_mail_log;

DELETE FROM system_operate_log;

DELETE FROM system_sms_log;

##修改数据库字段

##"system_role" data_scope_dept_ids not null

##"infra_config" "value" not null

##"system_menu" permission NOT NULL

ALTER TABLE system_role

ALTER COLUMN data_scope_dept_ids DROP NOT NULL;

ALTER TABLE infra_config

ALTER COLUMN "value" DROP NOT NULL;

ALTER TABLE system_menu

ALTER COLUMN permission DROP NOT NULL;

3.Navicat 导出sql文件