在postgresql数据库中如何实现Oracle中dblink功能

目录

引言

检查已安装得拓展

postgresql_fdw

 file_fdw

---

引言

在Oracle中常常有dblink功能，可以通过dblinks实现不同地址下得oracle数据库得数据交互。

dblink是Oracle独有得功能，其他数据库有吗？

当然，在postgresql也可以通过fdw实现与其他数据库进行数据交互，并且相较于Oracle得dblinks，postgresql得FDW更为强大，可以实现异构数据库得数据交互。Oracle、mysql、file(csv文件)、 MongoDB、SQL Server 等，

缺点是其只能做单表的创建，无法做全库的表操作。

本文列举postgresql_fdw、file_fdw两个示例进行操作说明。

检查已安装得拓展

进入到安装目录的share/extension/路径中，

 grep -lir "postgres_fdw" .

 如何没有，那是我们在安装的时候编译构建的问题，但是在postgresql-15的源码中   源码包/contrib 下时自带有此拓展的程序

可以进入到源码包中再次make&&make insatll 进行安装

 执行make&&make insatll进行构建

完成后

 拓展程序会根据当前设置的环境变量，自行安装在 数据库安装路劲/share/extension 路径下

 如何你和我一样使用的root用户执行make && make insall 此时你需要把文件授权给postgres用户或者你数据库指定的操作用户。

授权（本文指定的postgres用户作为数据库目录操作用户）

chown -R postgres:postgres /home/postgres/

备注：如果解压包已经删掉，重新下载源码包，进行编译，然后        进入contrib进行构建便可。 

postgresql_fdw

	ip	端口	dbname	用户
远端库	10.0.0.199	25432	pg02	postgres
操作端	10.0.0.200	15432	postgres	postgres

首先创建拓展

CREATE EXTENSION postgres_fdw;

注意创建拓展的用户必须超级用户，或者具有创建拓展权限的用户。否则会有以下报错！！

 postgres=> create extension  postgres_fdw ;
ERROR:  permission denied to create extension "postgres_fdw"
HINT:  Must be superuser to create this extension.

创建成功后，可以通过以下命令和系统表查看创建成功的拓展

sect *  from pg_extension; --系统表查看创建成功的拓展

\dx --元命令查看创建成功所有拓展

为postgresql_fdw拓展创建一个服务

create server pg_fdw_server foreign data wrapper postgres_fdw OPTIONS (host '10.0.0.199', dbname 'postgres', port '25432');

可以通过系统表 pg_catalog.pg_foreign_server查看我们创建外部服务的定义和相关信息

 为这个外部服务创建一个用户映射关系

create user mapping for postgres server pg_fdw_server OPTIONS  ( user 'pg02',password 'pg02');

options 是我们操作端访问远端数据库时，远端数据库的用户名和密码，由于两个数据库内部的用户名可能会有较大区别，以及远端可访问表的用户会有权限控制，所以我们在生产搭建外部表时，可以根据权限需要配置一个用户关系映射。

此时在操作端创建一个外部表

CREATE FOREIGN TABLE public.text_fdw (
	id varchar(50) NULL,
	remarke varchar(20) NULL,
	load_time varchar(50) NULL
)
SERVER pg_fdw_server
OPTIONS (table_name 'text_fdw');

查看以下创建外部表的语法，和创建普通表属性差不多，也可以作为分区表，

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
  PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
{ FOR VALUES partition_bound_spec | DEFAULT }
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED }

and table_constraint is:

[ CONSTRAINT constraint_name ]
CHECK ( expression ) [ NO INHERIT ]

and partition_bound_spec is:

IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
  TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

注意：操作端创建的外部表，必须schema_name，table_name,column_name和远端表的一致，并且列只能是远端含有的列名，数据类型可以不一样，但是需要二进制兼容；

此时在操作端创建一个同名表并插入数据。

CREATE TABLE public.text_fdw (
	id serial NOT NULL,
	remarke int4 NULL,
	load_time timestamp NULL DEFAULT now()
);

 insert into public.text_fdw (id ) select n from generate_series(1,100) as n;

此时操作端便可以看到对应的数据变化。 

 file_fdw

file_fdw扩展支持多种文件格式（如CSV、文本文件等），并提供了一些配置选项来指定文件的分隔符、文件编码、行结束符等信息。它还提供了一些可选功能，例如跳过文件的标题行或注释行，设置文件的定界符类型等。

我们数据库在使用过程中常常会配置一些日志输出为CSV文件，列如postgresql抓取数据库的DML DDL语句，存放在磁盘之中，此时可以通过FDW在数据库操作端，进行访问外部的CSV文件,本段就以此csv文件例子进行阐述.

在创建拓展和服务

create extension file_fdw ;
create server file_fdw_server foreign data wrapper file_fdw ;

 创建一个外部表,此处对日志各个字段的含义进行了说明，基于postgresql-15.3

此处对表各个字段的注释也适用于postgresql抓取数据库的DML DDL语句的blog的说明

create foreign table postgres_log
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text,
  backend_type text,
  leader_pid integer,
  query_id bigint 
) server file_fdw_server options ( format 'csv' ,filename '/home/postgres/log/postgres.csv' ,delimiter ',',null ''); 
comment on column postgres_log.log_time is'带毫秒的时间戳';                             
comment on column postgres_log.user_name is'用户名';                                                  
comment on column postgres_log.database_name is'数据库名';                                   
comment on column postgres_log.process_id is'进程 ID';
comment on column postgres_log.connection_from is'客户端主机:端口号';
comment on column postgres_log.session_id is'会话 ID';
comment on column postgres_log.session_line_num is'每个会话的行号';
comment on column postgres_log.command_tag is'命令标签';
comment on column postgres_log.session_start_time is'会话开始时间';
comment on column postgres_log.virtual_transaction_id is'虚拟事务 ID';
comment on column postgres_log.transaction_id is'普通事务 ID';
comment on column postgres_log.error_severity is'错误严重性'; 
comment on column postgres_log.sql_state_code is'SQLSTATE 代码';
comment on column postgres_log.message is'错误消息';
comment on column postgres_log.detail is'错误消息详情';
comment on column postgres_log.internal_query is'提示';
comment on column postgres_log.internal_query_pos is'导致错误的内部查询（如果有）';
comment on column postgres_log.context is'错误位置所在的字符计数';
comment on column postgres_log.query is'错误上下文';
comment on column postgres_log.query_pos  is'导致错误的用户查询（如果有且被log_min_error_statement启用）';
comment on column postgres_log.location  is'错误位置所在的字符计数';
comment on column postgres_log.application_name  is'在 PostgreSQL 源代码中错误的位置（如果log_error_verbosity被设置为verbose）以及应用名';
comment on column postgres_log.backend_type  is'后端类型';
comment on column postgres_log.leader_pid  is'并行组leader的进程ID';
comment on column postgres_log.query_id  is'和查询ID';

 此时就可以查看到相应的数据信息了。