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

 grep -lir "postgres_fdw" .

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 --元命令查看创建成功所有拓展

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

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

 
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;

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

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';