通过DTS实现PG14迁移到人大金仓V8R6

发布时间:2024年01月15日

迁移需求

xxx项目适配人大金仓,测试环境195pgsql数据库需要进行迁移至192.168.3.29 人大金仓数据库;

数据库信息

ip

os登录账号密码

数据库类型

数据库端口

数据库

数据库用户密码

源库

192.168.3.15

root/123456

PG14.2

5432

ahtjtestnew

ahtjtest/123456

目标库

192.168.3.75

root/123456

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的部分表记录数不一致,经过库中查询,发现一直,推测该语句只是大概记录数的一个统计,仅作参考

文章来源:https://blog.csdn.net/qq961573863/article/details/135608250
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。