需求
开发要求将1个服务器上的2个系统库合并到同服务器上的1个新库中，要求用命令行实现，不让用工具。具体信息如下：
| 源端库 | 目标库 | |
|---|---|---|
| 192.168.3.195:5432 | 门户库/资料库 ahtjyw 账号密码：ahtjyw/123456 | ahtjtest 账号密码：ahtjtest/123456 |
| 业务库 ahtjj 账号密码：ahtjtest/123456 |
1.合库前准备工作
1.1.统计源库信息
1.1.1.门户/资料库ahtjyw源库信息
门户库/资料库（源端库）：数据库：192.168.3.195:5432/ahtjyw；用户名密码：ahtjyw/123456
\c ahtjyw ahtjyw
#查数据库版本
select version();
#查数据库大小
\l+ ahtjyw
或
select pg_size_pretty(pg_database_size('ahtjyw'));
#查数据库函数
\df
#查数据库扩展，若有安装的扩展目标库需提前安装好，不然报错
\dx
#查看用户权限
SELECT * FROM pg_roles WHERE rolname='ahtjtest';
#源库表记录数
select relname as TABLE_NAME, reltuples as rowCounts from pg_class
where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public')
order by relname;
#源库对象类型和个数统计
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;
--输出信息如下：
ahtjyw=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 行记录)
ahtjyw=> \l+ ahtjyw
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限 | 大小 | 表空间 | 描述
--------+--------+----------+-------------+-------------+-------------------+-------+------------+------
ahtjyw | ahtjyw | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjyw +| 85 MB | pg_default |
| | | | | ahtjyw=CTc/ahtjyw | | |
(1 行记录)
ahtjyw=> \df
函数列表
架构模式 | 名称 | 结果数据类型 | 参数数据类型 | 类型
----------+------+--------------+--------------+------
(0 行记录)
ahtjyw=> \dx
已安装扩展列表
名称 | 版本 | 架构模式 | 描述
---------+------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 行记录)
ahtjj=> SELECT * FROM pg_roles WHERE rolname='ahtjyw';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+---------
ahtjyw | f | t | f | f | t | f | -1 | ******** | | f | | 1039243
(1 行记录)
table_name | rowcounts
---------------------------------------------------------+-----------
a_organ_jc | -1
aaf_job | -1
aaf_job_role | 62
aaf_menu | 61
aaf_organ | -1
aaf_resource | 146
aaf_role | -1
aaf_role_resource | 944
aaf_user | 67
aaf_user_job | -1
dim_xzqh | 152
dm_material_code | 93
dm_xzqh | 20117
gov_annex | -1
gov_baseinfo | -1
gov_basic_ind_filter_condition | -1
gov_basic_ind_filter_group | -1
gov_cfg_omit_field | -1
gov_conf_pendinginfo | -1
gov_custom_report | -1
gov_data_entry | -1
gov_data_entry_indicator_bind | -1
gov_dataset_alter_record | -1
gov_enterprise_directory_library | -1
gov_final_stat_report | -1
gov_group_field_relation | -1
gov_group_main | -1
gov_help | -1
gov_importdata_log | -1
gov_ind_derived_basic_relation | -1
gov_indicator | -1
gov_indicator_class | -1
gov_indicator_data | -1
gov_indicator_data_import | -1
gov_indicator_data_open | -1
gov_indicator_entry_data_bind | -1
gov_indicator_group | -1
gov_institution | -1
gov_key_enterprises_info | -1
gov_key_project_info | -1
gov_log_data_processing | -1
gov_materials_field_check_result | -1
gov_materials_field_conf | -1
gov_mid_data_agri_animal_pro_situation | -1
gov_mid_data_agri_economic_crop_area | -1
gov_mid_data_agri_fishery_pro_situation | -1
gov_mid_data_agri_fruits_fore_pro_situation | -1
gov_mid_data_agri_output_value | -1
gov_mid_data_agri_vege_fruits_pro_situation | -1
gov_mid_data_comp_construction_busi_conditions | -1
gov_mid_data_comp_construction_finance_season | -1
gov_mid_data_comp_construction_finance_year | -1
gov_mid_data_ent_baseinfo | -1
gov_mid_data_ent_develop_and_research | -1
gov_mid_data_enterprise_tables_s204_1 | -1
gov_mid_data_four_down_sampling_211 | -1
gov_mid_data_four_down_sampling_e224 | -1
gov_mid_data_industry_economic_benefit | -1
gov_mid_data_industry_energy_consumption | -1
gov_mid_data_industry_energy_output | -1
gov_mid_data_industry_product_output | -1
gov_mid_data_industry_product_value | -1
gov_mid_data_invest_estate_develop_sale | -1
gov_mid_data_invest_estate_funds_land | -1
gov_mid_data_invest_fixed_asset | 0
gov_mid_data_labor_employee_and_salary | -1
gov_mid_data_labor_wages_i201_2 | -1
gov_mid_data_labor_wages_i202_2 | -1
gov_mid_data_restricted_individual_e107 | -1
gov_mid_data_restricted_individual_e204_3 | -1
gov_mid_data_restricted_individual_s107 | -1
gov_mid_data_restricted_individual_s204_3 | -1
gov_mid_data_service_finance_month | -1
gov_mid_data_service_finance_year | -1
gov_mid_data_trade_sales_and_stock | -1
gov_monitoring_data | -1
gov_monitoring_data_detail | -1
gov_monitoring_indicator_data | -1
gov_monitoring_info | -1
gov_monitoring_info_audit_log | -1
gov_monitoring_info_relation | -1
gov_monitoring_prediction_model_conf | -1
gov_monitoring_proj_ent_data | -1
gov_monitoring_proj_ent_detail | -1
gov_monitoring_rule | -1
gov_monitoring_task | -1
gov_monitoring_task_execute | -1
gov_monitoring_template | -1
gov_monitoring_templatet_indicator_relation | -1
gov_msg_read | -1
gov_msg_unread | -1
gov_ocr_bank_receipt | -1
gov_ocr_central_unified | -1
gov_ocr_contract | -1
gov_ocr_financial_voucher | -1
gov_ocr_invoice | -1
gov_ocr_progress_confirmation | -1
gov_ocr_record_certificate | -1
gov_ocr_seal | -1
gov_online_apply | -1
gov_online_apply_reply | -1
gov_original_data_2017 | -1
gov_original_data_2018 | -1
gov_original_data_2019 | -1
gov_original_data_2020 | -1
gov_original_data_2021 | -1
gov_original_data_2022 | -1
gov_original_data_temp | -1
gov_original_mid_col_relation | -1
gov_original_mid_tab_relation | -1
gov_pendinginfo | -1
gov_project_application_audit | -1
gov_project_application_form_investment | -1
gov_project_application_form_investment_hx | -1
gov_project_application_info_check_detail | -1
gov_project_directory_library | -1
gov_project_invest_fixed_asset | -1
gov_project_month_check | -1
gov_project_month_check_hx | -1
gov_projectmanage | -1
gov_report | -1
gov_report_catalogue | -1
gov_report_catalogue_detail | -1
gov_report_data | -1
gov_report_explain | -1
gov_report_indicator_bind | -1
gov_report_indicator_relation | -1
gov_report_load | -1
gov_report_period_bind | -1
gov_reportmanage | -1
gov_reportmanage_read_record | -1
gov_resource_category | -1
gov_resource_content | -1
gov_resource_content_audit | -1
gov_role_group_relation | -1
gov_snapshot_mid_data_agri_animal_pro_situation | -1
gov_snapshot_mid_data_agri_economic_crop_area | -1
gov_snapshot_mid_data_agri_fishery_pro_situation | -1
gov_snapshot_mid_data_agri_fruits_fore_pro_situation | -1
gov_snapshot_mid_data_agri_output_value | -1
gov_snapshot_mid_data_agri_vege_fruits_pro_situation | -1
gov_snapshot_mid_data_comp_construction_busi_conditions | -1
gov_snapshot_mid_data_comp_construction_finance_season | -1
gov_snapshot_mid_data_comp_construction_finance_year | -1
gov_snapshot_mid_data_ent_baseinfo | -1
gov_snapshot_mid_data_ent_develop_and_research | -1
gov_snapshot_mid_data_enterprise_tables_s204_1 | -1
gov_snapshot_mid_data_four_down_sampling_211 | -1
gov_snapshot_mid_data_four_down_sampling_e224 | -1
gov_snapshot_mid_data_industry_economic_benefit | -1
gov_snapshot_mid_data_industry_energy_consumption | -1
gov_snapshot_mid_data_industry_energy_output | -1
gov_snapshot_mid_data_industry_product_output | -1
gov_snapshot_mid_data_industry_product_value | -1
gov_snapshot_mid_data_invest_estate_develop_sale | -1
gov_snapshot_mid_data_invest_estate_funds_land | -1
gov_snapshot_mid_data_invest_fixed_asset | -1
gov_snapshot_mid_data_labor_employee_and_salary | -1
gov_snapshot_mid_data_labor_wages_i201_2 | -1
gov_snapshot_mid_data_labor_wages_i202_2 | -1
gov_snapshot_mid_data_restricted_individual_e107 | -1
gov_snapshot_mid_data_restricted_individual_e204_3 | -1
gov_snapshot_mid_data_restricted_individual_s107 | -1
gov_snapshot_mid_data_restricted_individual_s204_3 | -1
gov_snapshot_mid_data_service_finance_month | -1
gov_snapshot_mid_data_service_finance_year | -1
gov_snapshot_mid_data_trade_sales_and_stock | -1
gov_submitted_materials | -1
gov_table_conf | -1
gov_table_field_conf | -1
gov_topbi_dataset | -1
imagecodetable | -1
inc_kettle_etl_sjdz | 0
inc_kettle_etl_sjdz_hist | 2
oauth_client_details | -1
portal_bill_board | -1
sis_sysparam | -1
sis_verhis | -1
sso_client_scope | -1
sso_open_api | -1
sso_scope | -1
sso_sms_record | -1
stat_audit_priority_conf | -1
stat_audit_process_node | -1
stat_audit_rule | -1
stat_check_project_list | 46
stat_check_report_data | 27
stat_check_report_materials | 36
stat_check_report_ocr_detail | 1333
stat_check_report_ocr_summary | 40
stat_check_report_total_info | 0
stat_dict_item | 3038
stat_dict_main | 81
stat_import_excel_header | 461
stat_material_verify_score_conf | 317
stat_materials | 0
stat_materials_audit_record | 26
stat_materials_field_check_result | 2082
stat_materials_field_conf | 317
stat_materials_ocr_detail | 109
stat_ocr_accounts_chart | 0
stat_ocr_bank_receipt | 133
stat_ocr_business_license | 0
stat_ocr_central_unified | 0
stat_ocr_composition | -1
stat_ocr_construction_contract | 43
stat_ocr_construction_land_use_permit | 3
stat_ocr_construction_permit_for_construction_project | 2
stat_ocr_document_filing | 0
stat_ocr_equip_buy_contract | 29
stat_ocr_equipment_in_place_photo | 25
stat_ocr_invoice | 219
stat_ocr_main_construction_contents | 8
stat_ocr_organization_code_certificate | -1
stat_ocr_progress_confirmation | 95
stat_ocr_project_approval_documents | 1
stat_ocr_project_site_verification_form | 0
stat_ocr_public_institution_legal_person_certificate | 0
stat_ocr_quantities_valuation | 0
stat_ocr_real_estate_sales | 0
stat_ocr_sale_contract | 0
stat_ocr_site_construction_photos | 0
stat_ocr_state_owned_land_use_right_certificate | 5
stat_ocr_total_schedule | 0
stat_origon_report_data_construction | 18
stat_origon_report_data_x2041 | 0
stat_picket_mark | 95
stat_project_apply_report | 1292
stat_project_apply_report_h202 | 10338
stat_project_apply_report_verify_summary | 25
stat_project_apply_report_x202 | 5
stat_project_audit_record | 51
stat_sys_param | 16
topdp_api_app_client | 1
topdp_api_open_api | 1
topdp_api_scope | 1
topdp_api_scope_app | 1
topdp_dict | -1
topdp_org_job | 1
topdp_org_job_role | 3
topdp_org_menu | 7
topdp_org_organ | 1
topdp_org_resource | 10
topdp_org_role | 4
topdp_org_role_resource | 11
topdp_org_sys_param | 4
topdp_org_user | 1
topdp_org_user_job | 1
topdp_user | -1
topdp_user_info | -1
topre_calculation_variable | -1
topre_dept | -1
topre_person | -1
topre_rule_engine | -1
topre_rule_engine_item | -1
topre_rule_engine_lable | -1
topre_rule_engine_list | -1
topre_sys_menu | -1
topre_sys_name | -1
topre_sys_param | -1
(260 行记录)
schemaname | objecttype | cnt
------------+------------+-----
public | VIEW | 7
public | INDEX | 279
public | TABLE | 260
public | SEQUENCE | 2
(4 行记录)
1.1.2.业务库ahtjj源库信息
业务库（源端库）：数据库：192.168.3.195:5432/ahtjj；用户名密码：ahtjtest/123456
\c ahtjj ahtjtest
#查数据库版本
select version();
#查数据库大小
\l+ ahtjj
或
select pg_size_pretty(pg_database_size('ahtjj'));
#查数据库函数
\df
#查数据库扩展，若有安装的扩展目标库需提前安装好，不然报错
\dx
#查看用户权限
SELECT * FROM pg_roles WHERE rolname='ahtjtest';
#源库表记录数
select relname as TABLE_NAME, reltuples as rowCounts from pg_class
where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public')
order by relname;
#源库对象类型和个数统计
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;
--输出信息如下：
ahtjj=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 行记录)
ahtjj=> \l+ ahtjj
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限 | 大小 | 表空间 | 描述
-------+----------+----------+-------------+-------------+-----------------------+--------+------------+------
ahtjj | ahtjtest | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjtest +| 132 MB | pg_default |
| | | | | ahtjtest=CTc/ahtjtest+| | |
| | | | | ahtjyw=CTc/ahtjtest +| | |
| | | | | ahtjywss=CTc/ahtjtest | | |
(1 行记录)
ahtjj=> \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_ind
ex integer | 函数
public | partitiontableprocedure | void |
| 函数
public | sys_guid | character varying |
| 函数
(8 行记录)
ahtjj=> \dx
已安装扩展列表
名称 | 版本 | 架构模式 | 描述
---------+------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 行记录)
ahtjj=> 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 行记录)
table_name | rowcounts
-------------------------------+-----------
aaf_change_his | -1
aaf_dh_account_mapping | -1
aaf_digitization | -1
aaf_job | 198
aaf_job_20230704 | 61
aaf_job_role | 568
aaf_menu | 94
aaf_menu0628 | 66
aaf_menu_20230704 | 88
aaf_organ | 314
aaf_organ_20230704 | 51
aaf_organ_copy1 | 313
aaf_organ_mapping | -1
aaf_organ_merge | -1
aaf_organ_sub | -1
aaf_organ_sync | 39
aaf_resource | 123
aaf_resource0628 | 112
aaf_resource_20230704 | 123
aaf_role | 41
aaf_role_20230704 | 58
aaf_role_resource | 467
aaf_role_resource0628 | 394
aaf_role_resource_20230704 | 437
aaf_sync_mapping | -1
aaf_sync_version | -1
aaf_sys_param | 93
aaf_sys_param_his | -1
aaf_user | 200
aaf_user_20230704 | 85
aaf_user_info | -1
aaf_user_job | 198
aaf_user_job_20230704 | 61
aaf_user_sub | -1
aaf_user_sync | 81
aaf_visible_permission | -1
app_carousel | -1
app_login_config | -1
app_navigation | -1
app_version | -1
archive_dir | 58
code_priority | -1
code_source | -1
code_sys_param | 60
common_file | -1
dict_item | 174
dict_main | -1
file | 3539
file_classification | 71
file_content | 15
file_extend | 69
file_info | 15
file_permission | -1
file_type | -1
image | 75221
log_email_record | -1
log_full_record | -1
log_interface_record | -1
log_sms_record | -1
msg_read | -1
msg_read_hist | -1
msg_setting | -1
msg_sys_param | -1
msg_unread | -1
music | -1
notice | -1
oauth_client_details | -1
operation_log | 145886
pending_history | -1
pending_item | -1
pending_msg_compensate | -1
pending_param | -1
pending_sms_record | -1
pending_status | -1
pending_task | -1
pending_temporary_msg | -1
permission | -1
persistent_logins | -1
picture_file | -1
portal_bill_board | -1
portal_browser_package_manage | -1
portal_calendar_mark | -1
portal_common_service | -1
portal_header_menu | -1
portal_notepad | -1
portal_notepad_item | -1
portal_other_website | -1
portal_quick_login | -1
portal_regist_holiday | -1
portal_release_explain | -1
portal_topic | -1
portal_topic_category | -1
portal_topic_skin | -1
portal_topic_user | -1
recovery_file | -1
repo_archive_organ | 1589
repo_business_attribute | 135866
repo_business_file | 4183
role | -1
role_permission | -1
share | -1
share_file | -1
sis_verhis | -1
sso_client_scope | -1
sso_open_api | -1
sso_scope | -1
sso_sms_record | -1
storage | 102
sys_param | 13
t_sys_param | 62
upload_task | 1019
upload_task_detail | 54
user_file | 0
user_login_info | -1
user_role | -1
(115 行记录)
schemaname | objecttype | cnt
------------+------------+-----
public | VIEW | 1
public | INDEX | 183
public | TABLE | 115
public | SEQUENCE | 2
public | func | 8
(5 行记录)
1.1.3.对象名重复值筛选
将2个库对象名字放入excel表进行排序、重复值筛选。若有重复和开发沟通，无重复后再合并。如果视图引用的有重复表需和开发沟通修改视图或者视图中引用的表。
--查询所有对象的名字
select
nsp.nspname as SchemaName
,cls.relname as ObjectName
,rol.rolname as ObjectOwner
,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
from pg_class cls
join pg_roles rol
on rol.oid = cls.relowner
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%'
and rol.rolname = 'ahtjtest'
order by nsp.nspname, cls.relname
发现的重名对象如下：
和开发沟通，保留门户/资料库中重名对象，业务库重名表/视图不保留。
分析2个库发现有16个同名表1个同名视图，如下：
1)同名表：
aaf_job
aaf_job_role
aaf_menu
aaf_organ
aaf_resource
aaf_role
aaf_role_resource
aaf_user
aaf_user_job
oauth_client_details
portal_bill_board
sis_verhis
sso_client_scope
sso_open_api
sso_scope
sso_sms_record
2)同名视图:
v_sz_dm_jg
1.2.备份源库
门户库/资料库（源端库）：数据库：192.168.3.195:5432/ahtjyw；用户名密码：ahtjyw/123456
全库导出，由于源库和目标库用户名不同故采用文本方式导出
--备份门户/资料库ahtjyw源库
su - postgres
cd /var/lib/pgsql/backuptmp
nohup pg_dump -Uahtjyw -w -p 5432 -f ahtjyw20231009.sql ahtjyw -v #-v输出日志详细模式 -w永远不提示输入口令
mv nohup.out ahtjyw_exp20231009.log
--暂未实现后台运行自定义日志故采用了mv笨方法，如果有读者有办法请留言，谢谢
业务库（目标库）：数据库：192.168.3.195:5432/ahtjj；用户名密码：ahtjtest/123456
排除重名表和视图，然后导出，由于源库和目标库用户名不同故采用文本方式导出
--备份业务库ahtjj源库
su - postgres
cd /var/lib/pgsql/backuptmp
nohup pg_dump -Uahtjtest -w -p 5432 -f ahtjj_20231009.sql ahtjj -T ahtjtest.aaf_job -T ahtjtest.aaf_job_role -T ahtjtest.aaf_menu -T ahtjtest.aaf_organ -T ahtjtest.aaf_resource -T ahtjtest.aaf_role -T ahtjtest.aaf_role_resource -T ahtjtest.aaf_user -T ahtjtest.aaf_user_job -T ahtjtest.oauth_client_details -T ahtjtest.portal_bill_board -T ahtjtest.sis_verhis -T ahtjtest.sso_client_scope -T ahtjtest.sso_open_api -T ahtjtest.sso_scope -T ahtjtest.sso_sms_record -v #-v输出日志详细模式 -w永远不提示输入口令
mv nohup.out ahtjj_exp20231009.log
--暂未实现后台运行自定义日志故采用了mv笨方法，如果有读者有办法请留言，谢谢
1.3.目标库中创建新库/用户
用户密码ahtjtest/123456，在该用户下创建新数据库ahtjtest。由于在同一台服务器同一个数据库里进行合库操作，创建库前需先查下是否有和新创建数据库同名，若有换其他名字。
--登录数据库
psql
--按条件模糊查询现有数据库
postgres=# \l ahtj*
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
--------------------+---------------+----------+-------------+-------------+---------------------------------
ahtjj | ahtjtest | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjtest +
| | | | | ahtjtest=CTc/ahtjtest +
| | | | | ahtjyw=CTc/ahtjtest +
| | | | | ahtjywss=CTc/ahtjtest
ahtjss | ahtjss | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjss +
| | | | | ahtjss=CTc/ahtjss
ahtjyw | ahtjyw | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjyw +
| | | | | ahtjyw=CTc/ahtjyw
ahtjywss | ahtjywss | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjywss +
或
select datname from pg_database where datname like 'ahtjtest%';
select datname from pg_database where datname in ('ahtjtest','AHTJTEST');--无输出，确定无要新创建的数据库
--创建数据库
postgres=# create database ahtjtest owner ahtjtest;
postgres=# grant all privileges on database ahtjtest to ahtjtest;
postgres=# grant all privileges on all tables in schema public to ahtjtest;
--按条件模糊查询现有数据库
postgres=# \l ahtj*
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
--------------------+---------------+----------+-------------+-------------+---------------------------------
ahtjj | ahtjtest | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjtest +
| | | | | ahtjtest=CTc/ahtjtest +
| | | | | ahtjyw=CTc/ahtjtest +
| | | | | ahtjywss=CTc/ahtjtest
ahtjss | ahtjss | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjss +
| | | | | ahtjss=CTc/ahtjss
ahtjtest | ahtjtest | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjtest +
| | | | | ahtjtest=CTc/ahtjtest
ahtjyw | ahtjyw | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjyw +
| | | | | ahtjyw=CTc/ahtjyw
ahtjywss | ahtjywss | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/ahtjywss +
| | | | | ahtjywss=CTc/ahtjywss
补充：
select datname from pg_database where datname not in('template2','template1','template0','samples','tempdb','global');
select datname from pg_database where datname like 'topisp%';
select datname from pg_database where datname in ('topisp_pt','TOPISP_PT');--无输出，确定无要新创建的数据库
#查询用户权限，确定无要新创建的用户
SELECT * FROM pg_roles WHERE rolname='topispys'; --rolsuper=t
SELECT * FROM pg_roles WHERE rolname='topisp_pt'; --无输出，确定无要新创建的用户
--创建目标数据库和用户
create user ahtjtest with encrypted password '123456';
create database ahtjtest owner ahtjtest;
grant all privileges on database ahtjtest to ahtjtest;
grant all privileges on all tables in schema public to ahtjtest;
补充：
alter database ahtjtest owner to ahtjtest;
1.4.备份文件传输到目标服务器
如果是1个服务器上进行合并，跳过该步骤。本案例中跳过该步骤
scp ahtjj_20231009.sql ahtjyw20231009.sql postgres@192.168.3.132:/pgdb/bak
注：目标服务器192.168.3.132需存在该目录/pgdb/bak且postgres用户具备读写权限，不然报错。
2.将源库导入目标库
--恢复门户库/资料库
su - postgres
1.格式化备份文件 ahtjyw20231009.sql owner由ahtjyw全局替换为ahtjtest
:%s#ahtjyw#ahtjtest#g
2.导入备份文件
psql -Uahtjtest -W ahtjtest -p 5432
\o /pgdb/bak/ahtjyw_imp_20231009.log #输出到日志文件
\i /pgdb/bak/ahtjyw20231009.sql #导入sql文件
\o #结束日志输出
--恢复业务库
su - postgres
1.格式化备份文件 ahtjj_20231009.sql owner ahtjtest源库和目标库一致跳过该步骤
:%s#ahtjyw#ahtjtest#g
2.删除同名视图
vi /pgdb/bak/ahtjj_20231009.sql
删除v_sz_dm_jg内容
2.导入备份文件
psql -Uahtjtest -W ahtjtest -p 5432
\o /pgdb/bak/ahtjj_imp_20231009.log #输出到日志文件
\i /pgdb/bak/ahtjj_20231009.sql #导入sql文件
\o #结束日志输出
3.数据校验
新库中查询对象信息，和源库中进行对比
#源库表记录数
select relname as TABLE_NAME, reltuples as rowCounts from pg_class
where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public')
order by relname;
#源库对象类型和个数统计
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;
4421
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
