-
PG14合并库
需求
开发要求将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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
发现的重名对象如下：
和开发沟通，保留门户/资料库中重名对象，业务库重名表/视图不保留。分析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
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
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笨方法，如果有读者有办法请留言，谢谢- 1
- 2
- 3
- 4
- 5
- 6
- 7
业务库（目标库）：数据库：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
- 2
- 3
- 4
- 5
- 6
- 7
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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
1.4.备份文件传输到目标服务器
如果是1个服务器上进行合并，跳过该步骤。本案例中跳过该步骤
scp ahtjj_20231009.sql ahtjyw20231009.sql postgres@192.168.3.132:/pgdb/bak 注：目标服务器192.168.3.132需存在该目录/pgdb/bak且postgres用户具备读写权限，不然报错。- 1
- 2
- 3
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 #结束日志输出- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
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;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
-
相关阅读:
R3LIVE论文学习（二）：VIO子系统
Window 10下SQL Server的安装配置以及C++使用案例
Monorepo 解决方案 — 基于 Bazel 的 Xcode 性能优化实践
Linux 基本语句_7_C语言_文件I/O文件复制操作
【MindSpore易点通】数据处理之Numpy数组的使用
用Java包com.sun.net.httpserver下面的类实现一个简单的http服务器demo
web前端框架设计第七课-事件处理
nginx的性能调优
LVS负载均衡群集
计蒜客T1375 百钱买百鸡(四)
- 原文地址：https://blog.csdn.net/qq961573863/article/details/133711645
