PostgreSQL创建外部表场景及使用

2024-03-05 647 0

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 'Dimine@2021');
  • 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.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;

相关文章

vim常用命令
navicat15 for linux桌面的破解激活
快速实现通用的办公文档在线预览方案
自建流媒体服务,快速打造自己的短视频点播平台
如何为项目构建高效的统一文件存储方案
老文件服务升级新存储方案及工作评估

发布评论