迁移需求
xxx项目适配人大金仓，测试环境195pgsql数据库需要进行迁移至192.168.3.29 人大金仓数据库；
数据库信息
| ip | os登录账号密码 | 数据库类型 | 数据库端口 | 数据库 | 数据库用户密码 | |
| 源库 | 192.168.3.195 | root/top@123 | PG14.2 | 5432 | ahtjtestnew | ahtjtest/123456 |
| 目标库 | 192.168.3.175 | root/top@123 | Kingbase V008R006C008B0014 | 54321 | ahtjtestnew01 | ahtjtest01/123456 |
迁移前准备
查数据库版本
源库PG
--查看数据库版本
select version(); --PostgreSQL 14.2
或
--查进程
-bash-4.2$ ps -ef | grep postmaster
postgres 1504 1 0 2023 ? 00:09:48 /usr/pgsql-14/bin/postmaster -D /opt/postgreSQL/pgsqlData
postgres 15048 14988 0 13:53 pts/2 00:00:00 grep --color=auto postmaster
--查版本
-bash-4.2$ psql -V
psql (PostgreSQL) 14.2
目标库Kingbase
[kingbase@topnet29 ~]$ ksql -Usystem -W test
口令：
输入 "help" 来获取帮助信息.
test=# select version();
version
----------------------------------------------------------------------------------------------------------------------
KingbaseES V008R006C008B0014 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 行记录)
或
--查进程
[root@topnet29 ~]# ps -ef | grep Kingbase
root 15597 15531 0 13:20 pts/0 00:00:00 grep --color=auto Kingbase
kingbase 25325 1 0 2023 ? 00:10:16 /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/Server/bin/kingbase -D /opt/Kingbase/ES/V8/data
--查版本
[root@topnet29 ~]# su - kingbase
上一次登录：五 1月 12 15:17:29 CST 2024pts/4 上
[kingbase@topnet29 ~]$ ksql -V
ksql (Kingbase) V008R006C008B0014
查字符集
源库PG
--登录数据库
-bash-4.2$ psql -U ahtjtest -W -d ahtjtestnew
口令:
psql (14.2)
输入 "help" 来获取帮助信息.
--服务器实例字符集
ahtjtestnew=> show server_encoding;
server_encoding
-----------------
UTF8
(1 行记录)
--数据库字符集
ahtjtestnew=> \l+
数据库列表
hbzscq | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 1591 MB | pg_default |
hbzscqfx | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 374 MB | pg_default |
hmzlyth | hmqzj | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/hmqzj +| 13 MB | pg_default |
ahtjtestnew | ahtjtest | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjtest +| 422 MB | pg_default |
| | | | | ahtjtest=CTc/ahtjtest | | |
--客户端字符集
ahtjtestnew=> show client_encoding;
client_encoding
-----------------
UTF8
(1 行记录)目标库Kingbase
--登录数据库
[kingbase@topnet29 ~]$ ksql -Usystem -W test
口令：
输入 "help" 来获取帮助信息.
--服务器实例字符集
test=# show server_encoding;
server_encoding
-----------------
UTF8
(1 行记录)
--数据库字符集
test=# \l+
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限 | 大小 | 表空间 | 描述
-----------------------+----------------+----------+-------------+-------------+-----------------------------------+---------+-------------+--------------------------------------------
data_quality_dev | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 6115 MB | sys_default |
data_quality_kingbase | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 4648 MB | sys_default |
data_quality_zh | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/system +| 15 MB | sys_default |
--客户端字符集
test=> show client_encoding;
client_encoding
-----------------
UTF8
(1 行记录)查源库用户权限PG
--查看用户权限
ahtjtestnew=> SELECT * FROM pg_roles WHERE rolname='ahtjtest';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
----------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+---------
ahtjtest | f | t | f | f | t | f | -1 | ******** | | f | | 1007879
(1 行记录)查函数和扩展
源库PG
--查函数
ahtjtestnew=> \df
函数列表
架构模式 | 名称 | 结果数据类型 | 参数数据类型 | 类型
----------+-----------------------------+-------------------+----------------------------------------------------------------------------------------------------------+------
public | deal_pending_item_user_null | void | | 函数
public | dealunclosedpendingitem | void | querycount numeric, rootorgid text, rootorgname text | 函数
public | fun_compute_holiday | bigint | start_date text, end_date text, is_lastday_contains text | 函数
public | instr | integer | character varying, character varying | 函数
public | instr | integer | string character varying, string_to_search_for character varying, beg_index integer | 函数
public | instr | integer | string character varying, string_to_search_for character varying, beg_index integer, occur_index integer | 函数
public | partitiontableprocedure | void | | 函数
public | sys_guid | character varying | | 函数
(8 行记录)
--查扩展
ahtjtestnew=> \dx
已安装扩展列表
名称 | 版本 | 架构模式 | 描述
---------+------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 行记录)
目标库Kingbase
--查函数
test=# \x
扩展显示已打开.
test=# \df
函数列表
-[ RECORD 1 ]+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
架构模式 | public
名称 | sys_stat_statements
结果数据类型 | SETOF record
参数数据类型 | showtext boolean, OUT userid oid, OUT dbid oid, OUT queryid bigint, OUT query text, OUT parses bigint, OUT total_parse_time double precision, OUT min_parse_time double precision, OUT max_parse_time double precision, OUT mean_parse_time double precision, OUT stddev_parse_time double precision, OUT plans bigint, OUT total_plan_time double precision, OUT min_plan_time double precision, OUT max_plan_time double precision, OUT mean_plan_time double precision, OUT stddev_plan_time double precision, OUT calls bigint, OUT total_exec_time double precision, OUT min_exec_time double precision, OUT max_exec_time double precision, OUT mean_exec_time double precision, OUT stddev_exec_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision
类型 | 函数
-[ RECORD 2 ]+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
架构模式 | public
名称 | sys_stat_statements_all
结果数据类型 | SETOF record
参数数据类型 | showtext boolean, OUT userid oid, OUT dbid oid, OUT queryid bigint, OUT parent_queryid bigint, OUT query text, OUT parses bigint, OUT total_parse_time double precision, OUT min_parse_time double precision, OUT max_parse_time double precision, OUT mean_parse_time double precision, OUT stddev_parse_time double precision, OUT plans bigint, OUT total_plan_time double precision, OUT min_plan_time double precision, OUT max_plan_time double precision, OUT mean_plan_time double precision, OUT stddev_plan_time double precision, OUT calls bigint, OUT total_exec_time double precision, OUT min_exec_time double precision, OUT max_exec_time double precision, OUT mean_exec_time double precision, OUT stddev_exec_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision
类型 | 函数
-[ RECORD 3 ]+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
架构模式 | public
名称 | sys_stat_statements_limit_len
结果数据类型 | SETOF record
参数数据类型 | showtext boolean, limit_query_len integer, OUT userid oid, OUT dbid oid, OUT queryid bigint, OUT query text, OUT parses bigint, OUT total_parse_time double precision, OUT min_parse_time double precision, OUT max_parse_time double precision, OUT mean_parse_time double precision, OUT stddev_parse_time double precision, OUT plans bigint, OUT total_plan_time double precision, OUT min_plan_time double precision, OUT max_plan_time double precision, OUT mean_plan_time double precision, OUT stddev_plan_time double precision, OUT calls bigint, OUT total_exec_time double precision, OUT min_exec_time double precision, OUT max_exec_time double precision, OUT mean_exec_time double precision, OUT stddev_exec_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision
类型 | 函数
-[ RECORD 4 ]+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
架构模式 | public
名称 | sys_stat_statements_reset
结果数据类型 | void
参数数据类型 | userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0
类型 | 函数
--查扩展
test=# \dx
已安装扩展列表
名称 | 版本 | 架构模式 | 描述
---------------------+------+--------------+---------------------------------------------------------------------------------------------------------------------
kdb_license | 1.0 | pg_catalog | kdb_license extension
kingbase_version | 1.0 | pg_catalog | This is a utility that provides function related to version number, it is used to get the Kingbase version number.
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
src_restrict | 1.0 | src_restrict | src restrict plugin
sys_anon | 1.0 | anon | provides data masking functionality
sys_hm | 1.0 | pg_catalog | Kingbase Healthy Check
sys_stat_statements | 1.10 | public | track parsing, planning and execution statistics of all SQL statements executed
sysaudit | 1.0 | sysaudit | provides auditing functionality
sysmac | 1.0 | sysmac | Mac for Kingbase
xlog_record_read | 1.0 | pg_catalog | xlog_record_read functions
(10 行记录)查源库数据量PG
查数据库大小
-bash-4.2$ psql -U ahtjtest -W -d ahtjtestnew
口令:
psql (14.2)
输入 "help" 来获取帮助信息.
ahtjtestnew=> \l+ ahtjtestnew
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限 | 大小 | 表空间 | 描述
-------------+----------+----------+-------------+-------------+-----------------------+--------+------------+------
ahtjtestnew | ahtjtest | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjtest +| 422 MB | pg_default |
| | | | | ahtjtest=CTc/ahtjtest | | |
(1 行记录
或
ahtjtestnew=> select pg_size_pretty(pg_database_size('ahtjtestnew'));
pg_size_pretty
----------------
422 MB
(1 行记录)
或
查单个数据库大小
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END as "Size",
t.spcname as "Tablespace",
pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid where d.datname='ahtjtestnew' order by 1;
注：如果查多个库或者所有库大小，更改或去掉where 后面的d.datname='ahtjtestnew'查不同对象数量
不是特别准确，仅作参考，记录数不一样的源库和目标库再分别查。
SELECT
nsp.nspname AS SchemaName,
CASE
cls.relkind
WHEN 'r' THEN
'TABLE'
WHEN 'm' THEN
'MATERIALIZED_VIEW'
WHEN 'i' THEN
'INDEX'
WHEN 'S' THEN
'SEQUENCE'
WHEN 'v' THEN
'VIEW'
WHEN 'c' THEN
'composite type'
WHEN 't' THEN
'TOAST'
WHEN 'f' THEN
'foreign table'
WHEN 'p' THEN
'partitioned_table'
WHEN 'I' THEN
'partitioned_index' ELSE cls.relkind :: TEXT
END AS ObjectType,
COUNT ( * ) cnt
FROM
pg_class cls
JOIN pg_namespace nsp ON nsp.oid = cls.relnamespace
WHERE
nsp.nspname NOT IN ( 'information_schema', 'pg_catalog' )
AND nsp.nspname NOT LIKE'pg_toast%'
GROUP BY
nsp.nspname,
cls.relkind UNION ALL
SELECT
n.nspname AS "Schema",
CASE
P.prokind
WHEN 'a' THEN
'agg'
WHEN 'w' THEN
'window'
WHEN 'p' THEN
'proc' ELSE'func'
END AS "Type",
COUNT ( * ) cnt
FROM
pg_catalog.pg_proc
P LEFT JOIN pg_catalog.pg_namespace n ON n.oid = P.pronamespace
WHERE
pg_catalog.pg_function_is_visible ( P.oid )
AND n.nspname NOT IN ( 'information_schema', 'pg_catalog' )
GROUP BY
n.nspname,
P.prokind;
schemaname | objecttype | cnt
------------+------------+-----
public | VIEW | 7
public | INDEX | 478
public | SEQUENCE | 4
public | TABLE | 375
public | func | 8
(5 行记录)
查表记录数
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables
where schemaname='public'
ORDER BY relname;
schemaname | relname | n_live_tup
------------+---------------------------------------------------------+------------
public | a_organ_jc | 0
public | aaf_change_his | 0
public | aaf_dh_account_mapping | 0
public | aaf_digitization | 0
public | aaf_job | 6
public | aaf_job_20230704 | 0
public | aaf_job_role | 576
public | aaf_menu | 0
public | aaf_menu0628 | 0
public | aaf_menu_20230704 | 0
public | aaf_organ | 11
public | aaf_organ_20230704 | 0
public | aaf_organ_copy1 | 0
public | aaf_organ_ext | 11
public | aaf_organ_mapping | 0
public | aaf_organ_merge | 0
public | aaf_organ_sub | 0
public | aaf_organ_sync | 11
public | aaf_resource | 1
public | aaf_resource0628 | 0
public | aaf_resource_20230704 | 0
public | aaf_role | 3
public | aaf_role_20230704 | 0
public | aaf_role_resource | 43
public | aaf_role_resource0628 | 0
public | aaf_role_resource_20230704 | 0
public | aaf_sync_mapping | 0
public | aaf_sync_version | 0
public | aaf_sys_param | 0
public | aaf_sys_param_his | 0
public | aaf_third_user_relation | 0
public | aaf_user | 16
public | aaf_user_20230704 | 0
public | aaf_user_info | 0
public | aaf_user_job | 12
public | aaf_user_job_20230704 | 0
public | aaf_user_sub | 0
public | aaf_user_sync | 12
public | aaf_visible_permission | 0
public | app_carousel | 0
public | app_login_config | 0
public | app_navigation | 0
public | app_version | 0
public | archive_dir | 24
public | code_priority | 0
public | code_source | 0
public | code_sys_param | 0
public | common_file | 0
public | dict_item | 0
public | dict_main | 0
public | dim_xzqh | 0
public | dm_material_code | 0
public | dm_xzqh | 0
public | file | 74731
public | file_20231226 | 12702
public | file_classification | 0
public | file_content | 0
public | file_extend | 0
public | file_info | 0
public | file_permission | 0
public | file_type | 0
public | gov_annex | 0
public | gov_baseinfo | 0
public | gov_basic_ind_filter_condition | 0
public | gov_basic_ind_filter_group | 0
public | gov_cfg_omit_field | 0
public | gov_conf_pendinginfo | 0
public | gov_custom_report | 0
public | gov_data_entry | 0
public | gov_data_entry_indicator_bind | 0
public | gov_dataset_alter_record | 0
public | gov_enterprise_directory_library | 0
public | gov_final_stat_report | 0
public | gov_group_field_relation | 0
public | gov_group_main | 0
public | gov_help | 0
public | gov_importdata_log | 0
public | gov_ind_derived_basic_relation | 0
public | gov_indicator | 0
public | gov_indicator_class | 0
public | gov_indicator_data | 0
public | gov_indicator_data_import | 0
public | gov_indicator_data_open | 0
public | gov_indicator_entry_data_bind | 0
public | gov_indicator_group | 0
public | gov_institution | 0
public | gov_key_enterprises_info | 0
public | gov_key_project_info | 0
public | gov_log_data_processing | 0
public | gov_materials_field_check_result | 0
public | gov_materials_field_conf | 0
public | gov_mid_data_agri_animal_pro_situation | 0
public | gov_mid_data_agri_economic_crop_area | 0
public | gov_mid_data_agri_fishery_pro_situation | 0
public | gov_mid_data_agri_fruits_fore_pro_situation | 0
public | gov_mid_data_agri_output_value | 0
public | gov_mid_data_agri_vege_fruits_pro_situation | 0
public | gov_mid_data_comp_construction_busi_conditions | 0
public | gov_mid_data_comp_construction_finance_season | 0
public | gov_mid_data_comp_construction_finance_year | 0
public | gov_mid_data_ent_baseinfo | 0
public | gov_mid_data_ent_develop_and_research | 0
public | gov_mid_data_enterprise_tables_s204_1 | 0
public | gov_mid_data_four_down_sampling_211 | 0
public | gov_mid_data_four_down_sampling_e224 | 0
public | gov_mid_data_industry_economic_benefit | 0
public | gov_mid_data_industry_energy_consumption | 0
public | gov_mid_data_industry_energy_output | 0
public | gov_mid_data_industry_product_output | 0
public | gov_mid_data_industry_product_value | 0
public | gov_mid_data_invest_estate_develop_sale | 0
public | gov_mid_data_invest_estate_funds_land | 0
public | gov_mid_data_invest_fixed_asset | 6887
public | gov_mid_data_labor_employee_and_salary | 0
public | gov_mid_data_labor_wages_i201_2 | 0
public | gov_mid_data_labor_wages_i202_2 | 0
public | gov_mid_data_restricted_individual_e107 | 0
public | gov_mid_data_restricted_individual_e204_3 | 0
public | gov_mid_data_restricted_individual_s107 | 0
public | gov_mid_data_restricted_individual_s204_3 | 0
public | gov_mid_data_service_finance_month | 0
public | gov_mid_data_service_finance_year | 0
public | gov_mid_data_trade_sales_and_stock | 0
public | gov_monitoring_data | 0
public | gov_monitoring_data_detail | 0
public | gov_monitoring_indicator_data | 0
public | gov_monitoring_info | 0
public | gov_monitoring_info_audit_log | 0
public | gov_monitoring_info_relation | 0
public | gov_monitoring_prediction_model_conf | 0
public | gov_monitoring_proj_ent_data | 0
public | gov_monitoring_proj_ent_detail | 0
public | gov_monitoring_rule | 0
public | gov_monitoring_task | 0
public | gov_monitoring_task_execute | 0
public | gov_monitoring_template | 0
public | gov_monitoring_templatet_indicator_relation | 0
public | gov_msg_read | 0
public | gov_msg_unread | 0
public | gov_ocr_bank_receipt | 0
public | gov_ocr_central_unified | 0
public | gov_ocr_contract | 0
public | gov_ocr_financial_voucher | 0
public | gov_ocr_invoice | 0
public | gov_ocr_progress_confirmation | 0
public | gov_ocr_record_certificate | 0
public | gov_ocr_seal | 0
public | gov_online_apply | 0
public | gov_online_apply_reply | 0
public | gov_original_data_2017 | 0
public | gov_original_data_2018 | 0
public | gov_original_data_2019 | 0
public | gov_original_data_2020 | 0
public | gov_original_data_2021 | 0
public | gov_original_data_2022 | 0
public | gov_original_data_temp | 0
public | gov_original_mid_col_relation | 0
public | gov_original_mid_tab_relation | 0
public | gov_pendinginfo | 0
public | gov_project_application_audit | 0
public | gov_project_application_form_investment | 0
public | gov_project_application_form_investment_hx | 0
public | gov_project_application_info_check_detail | 0
public | gov_project_directory_library | 0
public | gov_project_invest_fixed_asset | 0
public | gov_project_month_check | 0
public | gov_project_month_check_hx | 0
public | gov_projectmanage | 0
public | gov_report | 0
public | gov_report_catalogue | 0
public | gov_report_catalogue_detail | 0
public | gov_report_data | 0
public | gov_report_explain | 0
public | gov_report_indicator_bind | 0
public | gov_report_indicator_relation | 0
public | gov_report_load | 0
public | gov_report_period_bind | 0
public | gov_reportmanage | 0
public | gov_reportmanage_read_record | 0
public | gov_resource_category | 0
public | gov_resource_content | 0
public | gov_resource_content_audit | 0
public | gov_role_group_relation | 0
public | gov_snapshot_mid_data_agri_animal_pro_situation | 0
public | gov_snapshot_mid_data_agri_economic_crop_area | 0
public | gov_snapshot_mid_data_agri_fishery_pro_situation | 0
public | gov_snapshot_mid_data_agri_fruits_fore_pro_situation | 0
public | gov_snapshot_mid_data_agri_output_value | 0
public | gov_snapshot_mid_data_agri_vege_fruits_pro_situation | 0
public | gov_snapshot_mid_data_comp_construction_busi_conditions | 0
public | gov_snapshot_mid_data_comp_construction_finance_season | 0
public | gov_snapshot_mid_data_comp_construction_finance_year | 0
public | gov_snapshot_mid_data_ent_baseinfo | 0
public | gov_snapshot_mid_data_ent_develop_and_research | 0
public | gov_snapshot_mid_data_enterprise_tables_s204_1 | 0
public | gov_snapshot_mid_data_four_down_sampling_211 | 0
public | gov_snapshot_mid_data_four_down_sampling_e224 | 0
public | gov_snapshot_mid_data_industry_economic_benefit | 0
public | gov_snapshot_mid_data_industry_energy_consumption | 0
public | gov_snapshot_mid_data_industry_energy_output | 0
public | gov_snapshot_mid_data_industry_product_output | 0
public | gov_snapshot_mid_data_industry_product_value | 0
public | gov_snapshot_mid_data_invest_estate_develop_sale | 0
public | gov_snapshot_mid_data_invest_estate_funds_land | 0
public | gov_snapshot_mid_data_invest_fixed_asset | 0
public | gov_snapshot_mid_data_labor_employee_and_salary | 0
public | gov_snapshot_mid_data_labor_wages_i201_2 | 0
public | gov_snapshot_mid_data_labor_wages_i202_2 | 0
public | gov_snapshot_mid_data_restricted_individual_e107 | 0
public | gov_snapshot_mid_data_restricted_individual_e204_3 | 0
public | gov_snapshot_mid_data_restricted_individual_s107 | 0
public | gov_snapshot_mid_data_restricted_individual_s204_3 | 0
public | gov_snapshot_mid_data_service_finance_month | 0
public | gov_snapshot_mid_data_service_finance_year | 0
public | gov_snapshot_mid_data_trade_sales_and_stock | 0
public | gov_submitted_materials | 0
public | gov_table_conf | 0
public | gov_table_field_conf | 0
public | gov_topbi_dataset | 0
public | image | 2
public | imagecodetable | 0
public | inc_kettle_etl_sjdz | 0
public | inc_kettle_etl_sjdz_hist | 0
public | log_email_record | 0
public | log_full_record | 0
public | log_interface_record | 0
public | log_sms_record | 0
public | msg_read | 0
public | msg_read_hist | 0
public | msg_setting | 0
public | msg_sys_param | 0
public | msg_unread | 0
public | music | 0
public | notice | 0
public | oauth_client_details | 0
public | oauth_login_client | 0
public | operation_log | 92
public | pending_history | 0
public | pending_item | 0
public | pending_msg_compensate | 0
public | pending_param | 0
public | pending_sms_record | 0
public | pending_status | 0
public | pending_task | 0
public | pending_temporary_msg | 0
public | permission | 0
public | persistent_logins | 0
public | picture_file | 0
public | portal_bill_board | 0
public | portal_browser_package_manage | 0
public | portal_calendar_mark | 0
public | portal_common_service | 0
public | portal_header_menu | 0
public | portal_notepad | 0
public | portal_notepad_item | 0
public | portal_other_website | 0
public | portal_quick_login | 0
public | portal_regist_holiday | 0
public | portal_release_explain | 0
public | portal_topic | 0
public | portal_topic_category | 0
public | portal_topic_skin | 0
public | portal_topic_user | 0
public | recovery_file | 24
public | repo_archive_organ | 53
public | repo_business_attribute | 4535
public | repo_business_file | 79362
public | repo_search_words | 103
public | role | 0
public | role_permission | 0
public | share | 29
public | share_file | 117
public | sis_sysparam | 0
public | sis_verhis | 0
public | sso_client_scope | 0
public | sso_open_api | 0
public | sso_scope | 0
public | sso_sms_record | 0
public | stat_audit_priority_conf | 0
public | stat_audit_process_node | 0
public | stat_audit_rule | 0
public | stat_check_project_list | 4535
public | stat_check_report_data | 4535
public | stat_check_report_data_h | 78
public | stat_check_report_materials | 1181
public | stat_check_report_materials_h | 114
public | stat_check_report_ocr_detail | 48175
public | stat_check_report_ocr_detail_h | 1876
public | stat_check_report_ocr_summary | 905
public | stat_check_report_ocr_summary_h | 78
public | stat_check_report_total_info | 879
public | stat_check_report_total_info_h | 75
public | stat_dict_item | 11
public | stat_dict_main | 0
public | stat_import_excel_header | 883
public | stat_material_verify_score_conf | 0
public | stat_materials | 0
public | stat_materials_audit_record | 891
public | stat_materials_field_check_result | 121447
public | stat_materials_field_conf | 0
public | stat_materials_ocr_detail | 1
public | stat_ocr_accounts_chart | 88
public | stat_ocr_bank_receipt | 3219
public | stat_ocr_business_license | 0
public | stat_ocr_central_unified | 138
public | stat_ocr_composition | 0
public | stat_ocr_construction_contract | 1172
public | stat_ocr_construction_land_use_permit | 0
public | stat_ocr_construction_permit_for_construction_project | 0
public | stat_ocr_document_filing | 0
public | stat_ocr_equip_buy_contract | 1211
public | stat_ocr_equipment_in_place_photo | 0
public | stat_ocr_fapc | 0
public | stat_ocr_invoice | 36742
public | stat_ocr_main_construction_contents | 0
public | stat_ocr_organization_code_certificate | 0
public | stat_ocr_progress_confirmation | 3297
public | stat_ocr_project_approval_documents | 0
public | stat_ocr_project_site_verification_form | 0
public | stat_ocr_public_institution_legal_person_certificate | 0
public | stat_ocr_quantities_valuation | 441
public | stat_ocr_real_estate_sales | 2
public | stat_ocr_sale_contract | 0
public | stat_ocr_site_construction_photos | 0
public | stat_ocr_state_owned_land_use_right_certificate | 0
public | stat_ocr_tax_statement | 2
public | stat_ocr_tcpv | 720
public | stat_ocr_total_schedule | 0
public | stat_ocr_tpc | 458
public | stat_origon_report_data_construction | 0
public | stat_origon_report_data_x2041 | 0
public | stat_picket_mark | 16
public | stat_project_apply_report | 0
public | stat_project_apply_report_h202 | 2615
public | stat_project_apply_report_verify_summary | 0
public | stat_project_apply_report_x202 | 0
public | stat_project_audit_record | 10
public | stat_report_data_temp | 0
public | stat_sys_param | 37
public | storage | 12
public | sys_param | 0
public | t_sys_param | 0
public | topdp_api_app_client | 0
public | topdp_api_open_api | 0
public | topdp_api_scope | 0
public | topdp_api_scope_app | 0
public | topdp_dict | 0
public | topdp_org_job | 0
public | topdp_org_job_role | 0
public | topdp_org_menu | 0
public | topdp_org_organ | 0
public | topdp_org_resource | 0
public | topdp_org_role | 0
public | topdp_org_role_resource | 0
public | topdp_org_sys_param | 0
public | topdp_org_user | 0
public | topdp_org_user_job | 0
public | topdp_user | 0
public | topdp_user_info | 0
public | topre_calculation_variable | 0
public | topre_dept | 0
public | topre_person | 0
public | topre_rule_engine | 0
public | topre_rule_engine_item | 0
public | topre_rule_engine_lable | 0
public | topre_rule_engine_list | 0
public | topre_sys_menu | 0
public | topre_sys_name | 0
public | topre_sys_param | 0
public | upload_task | 10796
public | upload_task_detail | 2626
public | user_file | 66
public | user_login_info | 0
public | user_role | 0
public | xss_additional_host_port | 0
(375 行记录)
目录库创建用户和数据库
--登录数据库
[kingbase@topnet29 ~]$ ksql -Usystem -W test
口令：
输入 "help" 来获取帮助信息.
--查询是否有要创建的用户
test=# \du ahtjtest01
角色列表
角色名称 | 属性 | 成员属于
----------+------+----------
--查询是否有要创建的数据库
test=# \l ahtjtestnew01
(0 行记录)
--创建用户
create user ahtjtest01 with password '123456';
--创建数据库
create database ahtjtestnew01 owner ahtjtest01;
grant all privileges on database ahtjtestnew01 to ahtjtest01;迁移过程
运行迁移web程序
该迁移场景是在windows电脑上启动人大金仓自带的迁移工具web端
位置：C:\Program Files\Kingbase\V8R6\KESRealPro\V008R006C007B0012\ClientTools\guitools\KDts\KDTS-WEB\bin
双击启动startup.bat
启动过程如下：
web登录地址http://192.168.16.122:8080/ 账号密码默认，直接点登录即可
配置数据源
源数据库数据源
目前pg版本最高12版本
创建数据源后如下：
目标库数据源
注意选择兼容模式
创建数据源后如下：
创建迁移任务
选择数据源
选择模式
全选所有对象
选择迁移对象
配置迁移参数
查看迁移结果
处理迁移报错
主键约束报错处理
查看报错日志
单击失败数查看详情
ALTER TABLE
"public"."gov_snapshot_mid_data_agri_fishery_pro_situation" DROP CONSTRAINT IF EXISTS "gov_snapshot_mid_data_agri_fishery_pro_situation";
ALTER TABLE
"public"."gov_snapshot_mid_data_agri_fishery_pro_situation"
ADD
CONSTRAINT "gov_snapshot_mid_data_agri_fishery_pro_situation" PRIMARY KEY ("id");查看表上约束
SELECT con.*
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
WHERE nsp.nspname = 'public'
AND rel.relname = 'gov_snapshot_mid_data_agri_fishery_pro_situation';
说明：
nsp.nspname = '{schema name}'
rel.relname = '{table name}'无输出
解决办法
在目标库中手动执行修改后的语句，如下：
将
ALTER TABLE
"public"."gov_snapshot_mid_data_agri_fishery_pro_situation" DROP CONSTRAINT IF EXISTS "gov_snapshot_mid_data_agri_fishery_pro_situation";
ALTER TABLE
"public"."gov_snapshot_mid_data_agri_fishery_pro_situation"
ADD
CONSTRAINT "gov_snapshot_mid_data_agri_fishery_pro_situation" PRIMARY KEY ("id");
修改为
ALTER TABLE
"public"."gov_snapshot_mid_data_agri_fishery_pro_situation"
ADD
CONSTRAINT "gov_snapshot_mid_data_agri_fishery_pro_situation_pri" PRIMARY KEY ("id");逐个按此方法操作
唯一性约束报错处理
查看报错日志
ALTER TABLE
"public"."topdp_org_sys_param"
ADD
CONSTRAINT "uk_org_sys_param_3t38v_669E488A" UNIQUE ("key_code") enable validate;查看表上的唯一性约束
--查询唯一性约束
SELECT con.*
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
WHERE nsp.nspname = 'public'
AND rel.relname = 'topdp_org_sys_param';
说明：
nsp.nspname = '{schema name}'
rel.relname = '{table name}' 无uk_org_sys_param_3t38v_669E488A这个约束
解决办法
在目标库中手动执行修改后的语句，如下：
将
ALTER TABLE
"public"."topdp_org_sys_param"
ADD
CONSTRAINT "uk_org_sys_param_3t38v_669E488A" UNIQUE ("key_code") enable validate;
更改为
ALTER TABLE
"public"."topdp_org_sys_param"
ADD
CONSTRAINT "uk_org_sys_param_3t38v_669E488A" UNIQUE ("key_code");逐个按此方法操作
视图报错处理
点“二次迁移”执行二次迁移后视图自动实现了迁移
点“详情”查看二次迁移后结果发现视图自动实现了迁移，如下图：
补充
迁移后，随机找到一个表，生成ddl，发现约束的名字是是以表名做的截断
CREATE TABLE public.gov_snapshot_mid_data_agri_vege_fruits_pro_situation (
id varchar(36) NOT NULL,
original_line_num varchar(36) NOT NULL,
zone_code varchar(15),
town_name varchar(60),
province_name varchar(60),
city_name varchar(60),
country_name varchar(60),
tel varchar(50),
written_by varchar(60),
statistics_leader varchar(60),
unit_leader varchar(60),
submit_year varchar(4),
submit_month varchar(2),
submit_day varchar(2),
vegetable_total_aera float4,
vegetable_total_output float4,
melon_fruits_total_area float4,
melon_fruits_total_output float4,
report_year varchar(4),
report_season varchar(1),
report_month varchar(2),
create_time timestamp,
operator_user_id varchar(36),
update_time timestamp,
row_state bpchar(2),
period int2,
CONSTRAINT gov_snapshot_mid_data_agri_vege_fruits_pro_situa PRIMARY KEY (id)
);
表名： gov_snapshot_mid_data_agri_vege_fruits_pro_situation
约束名： gov_snapshot_mid_data_agri_vege_fruits_pro_situa查目标库数据量PG
查数据库大小
-bash-4.2$ psql -U ahtjtest -W -d ahtjtestnew
口令:
psql (14.2)
输入 "help" 来获取帮助信息.
ahtjtestnew=> \l+ ahtjtestnew
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限 | 大小 | 表空间 | 描述
-------------+----------+----------+-------------+-------------+-----------------------+--------+------------+------
ahtjtestnew | ahtjtest | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjtest +| 424 MB | pg_default |
| | | | | ahtjtest=CTc/ahtjtest | | |
(1 行记录
或
ahtjtestnew=> select pg_size_pretty(pg_database_size('ahtjtestnew'));
pg_size_pretty
----------------
424 MB
(1 行记录)
或
查单个数据库大小
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END as "Size",
t.spcname as "Tablespace",
pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid where d.datname='ahtjtestnew' order by 1;
注：如果查多个库或者所有库大小，更改或去掉where 后面的d.datname='ahtjtestnew'查不同对象数量
SELECT
nsp.nspname AS SchemaName,
CASE
cls.relkind
WHEN 'r' THEN
'TABLE'
WHEN 'm' THEN
'MATERIALIZED_VIEW'
WHEN 'i' THEN
'INDEX'
WHEN 'S' THEN
'SEQUENCE'
WHEN 'v' THEN
'VIEW'
WHEN 'c' THEN
'composite type'
WHEN 't' THEN
'TOAST'
WHEN 'f' THEN
'foreign table'
WHEN 'p' THEN
'partitioned_table'
WHEN 'I' THEN
'partitioned_index' ELSE cls.relkind :: TEXT
END AS ObjectType,
COUNT ( * ) cnt
FROM
pg_class cls
JOIN pg_namespace nsp ON nsp.oid = cls.relnamespace
WHERE
nsp.nspname NOT IN ( 'information_schema', 'pg_catalog' ) and nsp.nspname='public'
AND nsp.nspname NOT LIKE'pg_toast%'
GROUP BY
nsp.nspname,
cls.relkind UNION ALL
SELECT
n.nspname AS "Schema",
CASE
P.prokind
WHEN 'a' THEN
'agg'
WHEN 'w' THEN
'window'
WHEN 'p' THEN
'proc' ELSE'func'
END AS "Type",
COUNT ( * ) cnt
FROM
pg_catalog.pg_proc
P LEFT JOIN pg_catalog.pg_namespace n ON n.oid = P.pronamespace
WHERE
pg_catalog.pg_function_is_visible ( P.oid )
AND n.nspname NOT IN ( 'information_schema', 'pg_catalog' ) and n.nspname='public'
GROUP BY
n.nspname,
P.prokind;
schemaname | objecttype | cnt
------------+------------+-----
public | VIEW | 9
public | INDEX | 475
public | TABLE | 375
public | SEQUENCE | 5
public | func | 12
(5 行记录)查表记录数
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables
where schemaname='public'
ORDER BY relname;
schemaname | relname | n_live_tup
------------+---------------------------------------------------------+------------
public | a_organ_jc | 0
public | aaf_change_his | 0
public | aaf_dh_account_mapping | 0
public | aaf_digitization | 0
public | aaf_job | 213
public | aaf_job_20230704 | 61
public | aaf_job_role | 576
public | aaf_menu | 95
public | aaf_menu0628 | 66
public | aaf_menu_20230704 | 88
public | aaf_organ | 325
public | aaf_organ_20230704 | 51
public | aaf_organ_copy1 | 313
public | aaf_organ_ext | 11
public | aaf_organ_mapping | 0
public | aaf_organ_merge | 0
public | aaf_organ_sub | 0
public | aaf_organ_sync | 65
public | aaf_resource | 138
public | aaf_resource0628 | 112
public | aaf_resource_20230704 | 123
public | aaf_role | 79
public | aaf_role_20230704 | 58
public | aaf_role_resource | 546
public | aaf_role_resource0628 | 394
public | aaf_role_resource_20230704 | 437
public | aaf_sync_mapping | 0
public | aaf_sync_version | 0
public | aaf_sys_param | 108
public | aaf_sys_param_his | 22
public | aaf_third_user_relation | 0
public | aaf_user | 232
public | aaf_user_20230704 | 85
public | aaf_user_info | 13
public | aaf_user_job | 0
public | aaf_user_job_20230704 | 61
public | aaf_user_sub | 0
public | aaf_user_sync | 104
public | aaf_visible_permission | 0
public | app_carousel | 0
public | app_login_config | 0
public | app_navigation | 0
public | app_version | 0
public | archive_dir | 24
public | code_priority | 0
public | code_source | 11
public | code_sys_param | 60
public | common_file | 0
public | dict_item | 166
public | dict_main | 9
public | dim_xzqh | 152
public | dm_material_code | 96
public | dm_xzqh | 22867
public | file | 74730
public | file_20231226 | 12702
public | file_classification | 71
public | file_content | 17
public | file_extend | 69
public | file_info | 17
public | file_permission | 0
public | file_type | 6
public | gov_annex | 0
public | gov_baseinfo | 0
public | gov_basic_ind_filter_condition | 0
public | gov_basic_ind_filter_group | 0
public | gov_cfg_omit_field | 0
public | gov_conf_pendinginfo | 0
public | gov_custom_report | 0
public | gov_data_entry | 0
public | gov_data_entry_indicator_bind | 0
public | gov_dataset_alter_record | 0
public | gov_enterprise_directory_library | 0
public | gov_final_stat_report | 0
public | gov_group_field_relation | 0
public | gov_group_main | 0
public | gov_help | 0
public | gov_importdata_log | 0
public | gov_ind_derived_basic_relation | 0
public | gov_indicator | 0
public | gov_indicator_class | 0
public | gov_indicator_data | 0
public | gov_indicator_data_import | 0
public | gov_indicator_data_open | 0
public | gov_indicator_entry_data_bind | 0
public | gov_indicator_group | 0
public | gov_institution | 0
public | gov_key_enterprises_info | 0
public | gov_key_project_info | 0
public | gov_log_data_processing | 0
public | gov_materials_field_check_result | 0
public | gov_materials_field_conf | 0
public | gov_mid_data_agri_animal_pro_situation | 0
public | gov_mid_data_agri_economic_crop_area | 0
public | gov_mid_data_agri_fishery_pro_situation | 0
public | gov_mid_data_agri_fruits_fore_pro_situation | 0
public | gov_mid_data_agri_output_value | 0
public | gov_mid_data_agri_vege_fruits_pro_situation | 0
public | gov_mid_data_comp_construction_busi_conditions | 0
public | gov_mid_data_comp_construction_finance_season | 0
public | gov_mid_data_comp_construction_finance_year | 0
public | gov_mid_data_ent_baseinfo | 0
public | gov_mid_data_ent_develop_and_research | 0
public | gov_mid_data_enterprise_tables_s204_1 | 0
public | gov_mid_data_four_down_sampling_211 | 0
public | gov_mid_data_four_down_sampling_e224 | 0
public | gov_mid_data_industry_economic_benefit | 0
public | gov_mid_data_industry_energy_consumption | 0
public | gov_mid_data_industry_energy_output | 0
public | gov_mid_data_industry_product_output | 0
public | gov_mid_data_industry_product_value | 0
public | gov_mid_data_invest_estate_develop_sale | 0
public | gov_mid_data_invest_estate_funds_land | 0
public | gov_mid_data_invest_fixed_asset | 6887
public | gov_mid_data_labor_employee_and_salary | 0
public | gov_mid_data_labor_wages_i201_2 | 0
public | gov_mid_data_labor_wages_i202_2 | 0
public | gov_mid_data_restricted_individual_e107 | 0
public | gov_mid_data_restricted_individual_e204_3 | 0
public | gov_mid_data_restricted_individual_s107 | 0
public | gov_mid_data_restricted_individual_s204_3 | 0
public | gov_mid_data_service_finance_month | 0
public | gov_mid_data_service_finance_year | 0
public | gov_mid_data_trade_sales_and_stock | 0
public | gov_monitoring_data | 0
public | gov_monitoring_data_detail | 0
public | gov_monitoring_indicator_data | 0
public | gov_monitoring_info | 0
public | gov_monitoring_info_audit_log | 0
public | gov_monitoring_info_relation | 0
public | gov_monitoring_prediction_model_conf | 0
public | gov_monitoring_proj_ent_data | 0
public | gov_monitoring_proj_ent_detail | 0
public | gov_monitoring_rule | 0
public | gov_monitoring_task | 0
public | gov_monitoring_task_execute | 0
public | gov_monitoring_template | 0
public | gov_monitoring_templatet_indicator_relation | 0
public | gov_msg_read | 0
public | gov_msg_unread | 0
public | gov_ocr_bank_receipt | 0
public | gov_ocr_central_unified | 0
public | gov_ocr_contract | 0
public | gov_ocr_financial_voucher | 0
public | gov_ocr_invoice | 0
public | gov_ocr_progress_confirmation | 0
public | gov_ocr_record_certificate | 0
public | gov_ocr_seal | 0
public | gov_online_apply | 0
public | gov_online_apply_reply | 0
public | gov_original_data_2017 | 0
public | gov_original_data_2018 | 0
public | gov_original_data_2019 | 0
public | gov_original_data_2020 | 0
public | gov_original_data_2021 | 0
public | gov_original_data_2022 | 0
public | gov_original_data_temp | 0
public | gov_original_mid_col_relation | 0
public | gov_original_mid_tab_relation | 0
public | gov_pendinginfo | 0
public | gov_project_application_audit | 0
public | gov_project_application_form_investment | 0
public | gov_project_application_form_investment_hx | 0
public | gov_project_application_info_check_detail | 0
public | gov_project_directory_library | 0
public | gov_project_invest_fixed_asset | 0
public | gov_project_month_check | 0
public | gov_project_month_check_hx | 0
public | gov_projectmanage | 0
public | gov_report | 0
public | gov_report_catalogue | 0
public | gov_report_catalogue_detail | 0
public | gov_report_data | 0
public | gov_report_explain | 0
public | gov_report_indicator_bind | 0
public | gov_report_indicator_relation | 0
public | gov_report_load | 0
public | gov_report_period_bind | 0
public | gov_reportmanage | 0
public | gov_reportmanage_read_record | 0
public | gov_resource_category | 0
public | gov_resource_content | 0
public | gov_resource_content_audit | 0
public | gov_role_group_relation | 0
public | gov_snapshot_mid_data_agri_animal_pro_situation | 0
public | gov_snapshot_mid_data_agri_economic_crop_area | 0
public | gov_snapshot_mid_data_agri_fishery_pro_situation | 0
public | gov_snapshot_mid_data_agri_fruits_fore_pro_situation | 0
public | gov_snapshot_mid_data_agri_output_value | 0
public | gov_snapshot_mid_data_agri_vege_fruits_pro_situation | 0
public | gov_snapshot_mid_data_comp_construction_busi_conditions | 0
public | gov_snapshot_mid_data_comp_construction_finance_season | 0
public | gov_snapshot_mid_data_comp_construction_finance_year | 0
public | gov_snapshot_mid_data_ent_baseinfo | 0
public | gov_snapshot_mid_data_ent_develop_and_research | 0
public | gov_snapshot_mid_data_enterprise_tables_s204_1 | 0
public | gov_snapshot_mid_data_four_down_sampling_211 | 0
public | gov_snapshot_mid_data_four_down_sampling_e224 | 0
public | gov_snapshot_mid_data_industry_economic_benefit | 0
public | gov_snapshot_mid_data_industry_energy_consumption | 0
public | gov_snapshot_mid_data_industry_energy_output | 0
public | gov_snapshot_mid_data_industry_product_output | 0
public | gov_snapshot_mid_data_industry_product_value | 0
public | gov_snapshot_mid_data_invest_estate_develop_sale | 0
public | gov_snapshot_mid_data_invest_estate_funds_land | 0
public | gov_snapshot_mid_data_invest_fixed_asset | 0
public | gov_snapshot_mid_data_labor_employee_and_salary | 0
public | gov_snapshot_mid_data_labor_wages_i201_2 | 0
public | gov_snapshot_mid_data_labor_wages_i202_2 | 0
public | gov_snapshot_mid_data_restricted_individual_e107 | 0
public | gov_snapshot_mid_data_restricted_individual_e204_3 | 0
public | gov_snapshot_mid_data_restricted_individual_s107 | 0
public | gov_snapshot_mid_data_restricted_individual_s204_3 | 0
public | gov_snapshot_mid_data_service_finance_month | 0
public | gov_snapshot_mid_data_service_finance_year | 0
public | gov_snapshot_mid_data_trade_sales_and_stock | 0
public | gov_submitted_materials | 0
public | gov_table_conf | 0
public | gov_table_field_conf | 0
public | gov_topbi_dataset | 0
public | image | 79582
public | imagecodetable | 0
public | inc_kettle_etl_sjdz | 0
public | inc_kettle_etl_sjdz_hist | 2
public | log_email_record | 0
public | log_full_record | 0
public | log_interface_record | 0
public | log_sms_record | 0
public | msg_read | 0
public | msg_read_hist | 0
public | msg_setting | 2
public | msg_sys_param | 11
public | msg_unread | 0
public | music | 45
public | notice | 0
public | oauth_client_details | 5
public | oauth_login_client | 0
public | operation_log | 159458
public | pending_history | 0
public | pending_item | 0
public | pending_msg_compensate | 0
public | pending_param | 0
public | pending_sms_record | 0
public | pending_status | 0
public | pending_task | 2
public | pending_temporary_msg | 0
public | permission | 0
public | persistent_logins | 0
public | picture_file | 0
public | portal_bill_board | 1
public | portal_browser_package_manage | 0
public | portal_calendar_mark | 0
public | portal_common_service | 2
public | portal_header_menu | 15
public | portal_notepad | 0
public | portal_notepad_item | 0
public | portal_other_website | 2
public | portal_quick_login | 0
public | portal_regist_holiday | 366
public | portal_release_explain | 0
public | portal_topic | 3
public | portal_topic_category | 0
public | portal_topic_skin | 8
public | portal_topic_user | 0
public | recovery_file | 24
public | repo_archive_organ | 53
public | repo_business_attribute | 4535
public | repo_business_file | 79362
public | repo_search_words | 103
public | role | 0
public | role_permission | 0
public | share | 47
public | share_file | 1560
public | sis_sysparam | 0
public | sis_verhis | 9
public | sso_client_scope | 8
public | sso_open_api | 3
public | sso_scope | 2
public | sso_sms_record | 0
public | stat_audit_priority_conf | 33
public | stat_audit_process_node | 2
public | stat_audit_rule | 0
public | stat_check_project_list | 4535
public | stat_check_report_data | 4535
public | stat_check_report_data_h | 78
public | stat_check_report_materials | 1124
public | stat_check_report_materials_h | 114
public | stat_check_report_ocr_detail | 48354
public | stat_check_report_ocr_detail_h | 1876
public | stat_check_report_ocr_summary | 905
public | stat_check_report_ocr_summary_h | 78
public | stat_check_report_total_info | 879
public | stat_check_report_total_info_h | 75
public | stat_dict_item | 3288
public | stat_dict_main | 89
public | stat_import_excel_header | 883
public | stat_material_verify_score_conf | 317
public | stat_materials | 0
public | stat_materials_audit_record | 891
public | stat_materials_field_check_result | 129924
public | stat_materials_field_conf | 358
public | stat_materials_ocr_detail | 0
public | stat_ocr_accounts_chart | 88
public | stat_ocr_bank_receipt | 3219
public | stat_ocr_business_license | 0
public | stat_ocr_central_unified | 138
public | stat_ocr_composition | 0
public | stat_ocr_construction_contract | 1172
public | stat_ocr_construction_land_use_permit | 0
public | stat_ocr_construction_permit_for_construction_project | 0
public | stat_ocr_document_filing | 0
public | stat_ocr_equip_buy_contract | 1211
public | stat_ocr_equipment_in_place_photo | 0
public | stat_ocr_fapc | 4
public | stat_ocr_invoice | 36741
public | stat_ocr_main_construction_contents | 0
public | stat_ocr_organization_code_certificate | 0
public | stat_ocr_progress_confirmation | 3297
public | stat_ocr_project_approval_documents | 0
public | stat_ocr_project_site_verification_form | 0
public | stat_ocr_public_institution_legal_person_certificate | 0
public | stat_ocr_quantities_valuation | 441
public | stat_ocr_real_estate_sales | 2
public | stat_ocr_sale_contract | 0
public | stat_ocr_site_construction_photos | 0
public | stat_ocr_state_owned_land_use_right_certificate | 0
public | stat_ocr_tax_statement | 62
public | stat_ocr_tcpv | 720
public | stat_ocr_total_schedule | 0
public | stat_ocr_tpc | 458
public | stat_origon_report_data_construction | 0
public | stat_origon_report_data_x2041 | 0
public | stat_picket_mark | 142
public | stat_project_apply_report | 0
public | stat_project_apply_report_h202 | 2615
public | stat_project_apply_report_verify_summary | 0
public | stat_project_apply_report_x202 | 0
public | stat_project_audit_record | 97
public | stat_report_data_temp | 0
public | stat_sys_param | 0
public | storage | 162
public | sys_param | 15
public | t_sys_param | 62
public | topdp_api_app_client | 2
public | topdp_api_open_api | 1
public | topdp_api_scope | 2
public | topdp_api_scope_app | 2
public | topdp_dict | 35
public | topdp_org_job | 1
public | topdp_org_job_role | 3
public | topdp_org_menu | 7
public | topdp_org_organ | 1
public | topdp_org_resource | 10
public | topdp_org_role | 4
public | topdp_org_role_resource | 11
public | topdp_org_sys_param | 0
public | topdp_org_user | 1
public | topdp_org_user_job | 1
public | topdp_user | 1
public | topdp_user_info | 1
public | topre_calculation_variable | 0
public | topre_dept | 0
public | topre_person | 1
public | topre_rule_engine | 0
public | topre_rule_engine_item | 0
public | topre_rule_engine_lable | 20
public | topre_rule_engine_list | 0
public | topre_sys_menu | 9
public | topre_sys_name | 0
public | topre_sys_param | 0
public | upload_task | 10795
public | upload_task_detail | 2623
public | user_file | 66
public | user_login_info | 0
public | user_role | 0
public | xss_additional_host_port | 9
(375 行记录)
数据记录不是特别准确，和源库记录数进行对比，记录数不一样的查询下。此次查询发现记录数不一样的其实是
源库和目标库数据对比结果
不同对象数量差异
源库比目标库索引对象数据量中索引多3个
SELECT
nsp.nspname AS SchemaName,
CASE
cls.relkind
WHEN 'r' THEN
'TABLE'
WHEN 'm' THEN
'MATERIALIZED_VIEW'
WHEN 'i' THEN
'INDEX'
WHEN 'S' THEN
'SEQUENCE'
WHEN 'v' THEN
'VIEW'
WHEN 'c' THEN
'composite type'
WHEN 't' THEN
'TOAST'
WHEN 'f' THEN
'foreign table'
WHEN 'p' THEN
'partitioned_table'
WHEN 'I' THEN
'partitioned_index' ELSE cls.relkind :: TEXT
END AS ObjectType,
COUNT ( * ) cnt
FROM
pg_class cls
JOIN pg_namespace nsp ON nsp.oid = cls.relnamespace
WHERE
nsp.nspname NOT IN ( 'information_schema', 'pg_catalog' ) and nsp.nspname='public'
AND nsp.nspname NOT LIKE'pg_toast%'
GROUP BY
nsp.nspname,
cls.relkind UNION ALL
SELECT
n.nspname AS "Schema",
CASE
P.prokind
WHEN 'a' THEN
'agg'
WHEN 'w' THEN
'window'
WHEN 'p' THEN
'proc' ELSE'func'
END AS "Type",
COUNT ( * ) cnt
FROM
pg_catalog.pg_proc
P LEFT JOIN pg_catalog.pg_namespace n ON n.oid = P.pronamespace
WHERE
pg_catalog.pg_function_is_visible ( P.oid )
AND n.nspname NOT IN ( 'information_schema', 'pg_catalog' ) and n.nspname='public'
GROUP BY
n.nspname,
P.prokind;
--源库pg库
schemaname | objecttype | cnt
------------+------------+-----
public | VIEW | 7
public | INDEX | 478
public | SEQUENCE | 4
public | TABLE | 375
public | func | 8
(5 行记录)
--目标库Kingbase
schemaname | objecttype | cnt
------------+------------+-----
public | VIEW | 9
public | INDEX | 475
public | TABLE | 375
public | SEQUENCE | 5
public | func | 12
(5 行记录)
--通过以下语句结合excel工具比对aaf_user_job索引数量不一致
select tablename,indexname,indexdef from pg_indexes where schemaname = 'public' order by 1;
--查看源库aaf_user_job表结构 pg库
CREATE TABLE public.aaf_user_job (
id varchar(36) NOT NULL,
create_time timestamp(6) NULL,
operate_user_id varchar(36) NULL,
state varchar(2) NULL,
update_time timestamp(6) NULL,
end_date timestamp(6) NULL,
isdefault_job varchar(2) NOT NULL,
job_id varchar(36) NOT NULL,
job_type varchar(2) NOT NULL,
start_date timestamp(6) NULL,
user_id varchar(36) NOT NULL,
create_user_id varchar(36) NULL,
update_user_id varchar(36) NULL,
deleted varchar(1) NOT NULL DEFAULT '0'::character varying,
row_state varchar(2) NOT NULL DEFAULT '01'::character varying,
description varchar(255) NULL,
CONSTRAINT sys_c007779 CHECK ((id IS NOT NULL)),
CONSTRAINT sys_c007780 CHECK ((isdefault_job IS NOT NULL)),
CONSTRAINT sys_c007781 CHECK ((job_id IS NOT NULL)),
CONSTRAINT sys_c007782 CHECK ((job_type IS NOT NULL)),
CONSTRAINT sys_c007783 CHECK ((user_id IS NOT NULL)),
CONSTRAINT sys_c007877 CHECK ((id IS NOT NULL)),
CONSTRAINT sys_c007878 CHECK ((isdefault_job IS NOT NULL)),
CONSTRAINT sys_c007879 CHECK ((job_id IS NOT NULL)),
CONSTRAINT sys_c007880 CHECK ((job_type IS NOT NULL)),
CONSTRAINT sys_c007881 CHECK ((user_id IS NOT NULL)),
CONSTRAINT sys_c007882 PRIMARY KEY (id),
CONSTRAINT sys_c007967 CHECK ((deleted IS NOT NULL)),
CONSTRAINT sys_c007968 CHECK ((row_state IS NOT NULL)),
CONSTRAINT unique_user_job_isdefault UNIQUE (user_id, job_id, isdefault_job)
);
CREATE UNIQUE INDEX aaf_user_job_pkey ON public.aaf_user_job USING btree (id);
CREATE INDEX idx_job_job_id ON public.aaf_user_job USING btree (job_id);
CREATE INDEX idx_job_user_id ON public.aaf_user_job USING btree (user_id);
--查看目标库aaf_user_job表结构 Kingbase库
CREATE TABLE public.aaf_user_job (
id varchar(36) NOT NULL,
create_time timestamp,
operate_user_id varchar(36),
state varchar(2),
update_time timestamp,
end_date timestamp,
isdefault_job varchar(2) NOT NULL,
job_id varchar(36) NOT NULL,
job_type varchar(2) NOT NULL,
start_date timestamp,
user_id varchar(36) NOT NULL,
create_user_id varchar(36),
update_user_id varchar(36),
deleted varchar(1) NOT NULL,
row_state varchar(2) NOT NULL,
description varchar(255),
CONSTRAINT "sys_c007882_DE31C607" PRIMARY KEY (id)
);
CREATE UNIQUE INDEX "unique_user_job_isdefault_8E532681" ON public.aaf_user_job (isdefault_job,job_id,user_id);
--解决办法
目标库aaf_user_job表中创建以下索引 Kingbase库
CREATE UNIQUE INDEX aaf_user_job_pkey ON public.aaf_user_job USING btree (id);
CREATE INDEX idx_job_job_id ON public.aaf_user_job USING btree (job_id);
CREATE INDEX idx_job_user_id ON public.aaf_user_job USING btree (user_id);补充:源库和目标库建表语句差异
--查看源库aaf_user_job表结构 pg库
CREATE TABLE public.aaf_user_job (
id varchar(36) NOT NULL,
create_time timestamp(6) NULL,
operate_user_id varchar(36) NULL,
state varchar(2) NULL,
update_time timestamp(6) NULL,
end_date timestamp(6) NULL,
isdefault_job varchar(2) NOT NULL,
job_id varchar(36) NOT NULL,
job_type varchar(2) NOT NULL,
start_date timestamp(6) NULL,
user_id varchar(36) NOT NULL,
create_user_id varchar(36) NULL,
update_user_id varchar(36) NULL,
deleted varchar(1) NOT NULL DEFAULT '0'::character varying,
row_state varchar(2) NOT NULL DEFAULT '01'::character varying,
description varchar(255) NULL,
CONSTRAINT sys_c007779 CHECK ((id IS NOT NULL)),
CONSTRAINT sys_c007780 CHECK ((isdefault_job IS NOT NULL)),
CONSTRAINT sys_c007781 CHECK ((job_id IS NOT NULL)),
CONSTRAINT sys_c007782 CHECK ((job_type IS NOT NULL)),
CONSTRAINT sys_c007783 CHECK ((user_id IS NOT NULL)),
CONSTRAINT sys_c007877 CHECK ((id IS NOT NULL)),
CONSTRAINT sys_c007878 CHECK ((isdefault_job IS NOT NULL)),
CONSTRAINT sys_c007879 CHECK ((job_id IS NOT NULL)),
CONSTRAINT sys_c007880 CHECK ((job_type IS NOT NULL)),
CONSTRAINT sys_c007881 CHECK ((user_id IS NOT NULL)),
CONSTRAINT sys_c007882 PRIMARY KEY (id),
CONSTRAINT sys_c007967 CHECK ((deleted IS NOT NULL)),
CONSTRAINT sys_c007968 CHECK ((row_state IS NOT NULL)),
CONSTRAINT unique_user_job_isdefault UNIQUE (user_id, job_id, isdefault_job)
);
CREATE UNIQUE INDEX aaf_user_job_pkey ON public.aaf_user_job USING btree (id);
CREATE INDEX idx_job_job_id ON public.aaf_user_job USING btree (job_id);
CREATE INDEX idx_job_user_id ON public.aaf_user_job USING btree (user_id);
--查看目标库aaf_user_job表结构 Kingbase库
CREATE TABLE public.aaf_user_job (
id varchar(36) NOT NULL,
create_time timestamp,
operate_user_id varchar(36),
state varchar(2),
update_time timestamp,
end_date timestamp,
isdefault_job varchar(2) NOT NULL,
job_id varchar(36) NOT NULL,
job_type varchar(2) NOT NULL,
start_date timestamp,
user_id varchar(36) NOT NULL,
create_user_id varchar(36),
update_user_id varchar(36),
deleted varchar(1) NOT NULL,
row_state varchar(2) NOT NULL,
description varchar(255),
CONSTRAINT "sys_c007882_DE31C607" PRIMARY KEY (id)
);
CREATE UNIQUE INDEX "unique_user_job_isdefault_8E532681" ON public.aaf_user_job (isdefault_job,job_id,user_id);
表记录数差异
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables
where schemaname='public'
ORDER BY relname;源库PG和目标库Kingbase的部分表记录数不一致，经过库中查询，发现一直，推测该语句只是大概记录数的一个统计，仅作参考
补充
查视图语句
如果不加viewowner条件，人大金仓会比PG多system下的视图
--目标库人大金仓
ksql -Uahtjtest01 -W ahtjtestnew01
select * from pg_views where schemaname='public' and viewowner='ahtjtest01';
--源库PG库
select * from pg_views where schemaname='public' and viewowner='ahtjtest';
查索引语句
对于复合索引，人大金仓不会对每个列再单独创建索引而PG会再创建复合索引的同时再对列单独创建索引，所以，如果迁移后索引数量不一致，可对比复合索引的表的索引数量。
select tablename,indexname,indexdef from pg_indexes where schemaname = 'public' order by 1;查表语句
和查表记录数通用
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables
where schemaname='public'
ORDER BY relname;查序列语句
如果不加sequenceowner条件，人大金仓会比PG多
select * from pg_sequences where schemaname='public' and sequenceowner='ahtjtest01';查函数语句
人大金仓会比PG多一些系统函数
SELECT n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
CASE p.prokind
WHEN 'a' THEN 'agg'
WHEN 'w' THEN 'window'
WHEN 'p' THEN 'proc'
ELSE 'func'
END as "Type"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'比如：
701
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
