1.使用场景描述

在项目中不同的业务为了松耦合、可扩展、独立性等原因，适当的抽建了些单独的微服务库，而在统计分析的时候却需要进行跨库关联，不知不觉提高了技术实现成本，此时常用两种解决方案:

• 方案一
  通过采集工具将需要的数据抽到一个集中的库中再进行统计分析;
• 方案二
  通过数据库底层的关联特性进行处理,例如oracle的dblink或postgrep的fwd等特性.

因项目不大且业务简单，正好使用pg库的所有业务库都在同一个内网中，所以采用了方案二快速解决业务数据集成问题，没有上相关CDC数据同步组件了。

2.场景配置

具体场景是要在A库中通过外部表的方式引用B库的sys_depart表进行数据的增删改查操作,从以下几个步骤说明这个场景的配置

• 2.1 在A库中创建fdw扩展

create extension postgres_fdw;

pg外部表特性说明
PostgreSQL的fdw实现的功能是各个postgresql数据库及远程数据库之间的跨库操作，功能和Oracle的dblink一样

• 2.2 在A库中创建一个server引用B库

CREATE SERVER server_b
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.0.200', port '5432', dbname 'B')

• 2.3 在A库中创建user postgres的映射

CREATE USER MAPPING FOR postgres SERVER server_b
  OPTIONS ("user" 'postgres', password 'xxx@2021');

注: FOR postgres映射到当前库的帐号，OPTIONS是关联目标库的帐号

• 2.4.在A库中指定引用B库的sys_depart表全部字段

IMPORT FOREIGN SCHEMA public limit to(sys_depart) FROM SERVER server_b INTO public;

3.外部表批量引用配置

• 3.1 批量引用全部表

IMPORT FOREIGN SCHEMA public FROM SERVER server_b INTO public;

• 3.2 引用指定的表

IMPORT FOREIGN SCHEMA public limit to(sys_user,sys_role) FROM SERVER server_b INTO public;

• 3.3 引用指定表的指定字段

CREATE FOREIGN TABLE "public"."sys_depart" (
  "id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "parent_id" varchar(32) COLLATE "pg_catalog"."default",
  "depart_name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "depart_name_en" varchar(500) COLLATE "pg_catalog"."default",
  "depart_name_abbr" varchar(500) COLLATE "pg_catalog"."default",
  "depart_order" int4,
  "description" varchar(500) COLLATE "pg_catalog"."default",
  "org_category" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
  "org_code" varchar(64) COLLATE "pg_catalog"."default",
  "mobile" varchar(32) COLLATE "pg_catalog"."default",
  "fax" varchar(32) COLLATE "pg_catalog"."default",
  "address" varchar(100) COLLATE "pg_catalog"."default",
  "memo" varchar(500) COLLATE "pg_catalog"."default",
  "status" varchar(1) COLLATE "pg_catalog"."default",
  "del_flag" int4,
  "create_by" varchar(32) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6),
  "update_by" varchar(32) COLLATE "pg_catalog"."default",
  "update_time" timestamp(6),
  "tenant_id" varchar(100) COLLATE "pg_catalog"."default",
  "tree_path" varchar(1000) COLLATE "pg_catalog"."default"
)
SERVER server_b
OPTIONS ("schema_name" 'public', "table_name" 'sys_depart');

注: 主键语言不需要带

4.管理外部库和表

• 4.1 查询外部库服务

select * from pg_foreign_server

• 4.1 查询映射用户

select * from pg_user_mappings;

• 4.2 查询外部表

select * from pg_foreign_table;

• 4.3 修改外部库服务
  

  https://www.kancloud.cn/wizardforcel/postgresql-doc/104661

alter server server_sys_dev options (SET host '192.168.0.200',SET port '5432', SET dbname 'sys_dev');

• 4.4 删除相关外部引用

drop user mapping for postgres server server_remote ;
drop server server_remote;
drop extension postgres_fdw ;
drop foreign table test1;