file_fdw&postgres_fdw

数据库版本postgresql13.2

postgres_fdw方便了pg之间的访问，兼容性请参照官网：http://www.postgres.cn/docs/13/postgres-fdw.html

另外还有mysql_fdw等

file_fdw 是为了直接访问外部文件一种特性

postgres_fdw例子，这里用一台服务器测试，用户u1本地账户，test本地库；u2远程账户，test2远程库

管理员执行：

create database test;
create user u1 with password 'china'
grant all privileges on database test to u1;

create database test2;
create user u2 with password 'china'
grant all privileges on database test2 to u2;

-- 授权
alter user zeei superuser ;
grant usage on foreign data wrapper postgres_fdw to zeei;

创建远程库测试表，切换到u2用户，库test2执行：

drop table t1;
create table t1(
id serial primary key,
name varchar(50));
insert into t1(name) values('beijing');
commit;
select * from public.t1 t ;

切换用户u1和库test

create extension postgres_fdw;

CREATE SERVER pg_link FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5432', dbname 'test2');
-- drop server pg_link

CREATE USER MAPPING FOR zeei
        SERVER pg_link
        OPTIONS (user 'u2', password 'china');
-- drop user mapping for zeei server pg_link;
       
CREATE FOREIGN TABLE foreign_table_t1 (
        id integer NOT NULL,
        name varchar(50)
)
        SERVER pg_link
        OPTIONS (schema_name 'public', table_name 't1');
-- drop foreign table foreign_table_t1
       
select * from foreign_table_t1;
insert into foreign_table_t1(id,name) values(2,'shanghai');
select * from foreign_table_t1;
-- 查看扩展
test=> \dx
                                    已安装扩展列表
     名称     | 版本 |  架构模式  |                        描述

--------------+------+------------+-------------------------------------------
-------
 plpgsql      | 1.0  | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0  | public     | foreign-data wrapper for remote PostgreSQL
ervers
(2 行记录)

file_fdw例子：

test=> create extension file_fdw ;
CREATE EXTENSION
test=> create  table tb10(id integer,name character varying,passworld character
varying);
CREATE TABLE
test=> insert into tb10 select generate_series(1,50),'john',md5(random()::text);

INSERT 0 50
test=> copy tb10 to 'E:/test/tb10.csv';
ERROR:  could not open file "E:/test/tb10.csv" for writing: No such file or dire
ctory
提示:  COPY TO instructs the PostgreSQL server process to write a file. You may
want a client-side facility such as psql's \copy.

test=> copy tb10 to 'E:\soft\pg13\test\tb10.csv';
COPY 50
test=> create server server_file_fdw foreign data wrapper file_fdw;
CREATE SERVER
test=> create foreign table foreign_tb10 (id integer,name character varying,pas
word character varying)server server_file_fdw  options (filename 'E:\soft\pg13\
est\tb10.csv');
CREATE FOREIGN TABLE
test=> \d foreign_tb10
                引用的外部表 "public.foreign_tb10"
   栏位   |       类型        | 校对规则 | 可空的 | 预设 | FDW选项
----------+-------------------+----------+--------+------+---------
 id       | integer           |          |        |      |
 name     | character varying |          |        |      |
 password | character varying |          |        |      |
服务器 server_file_fdw
FDW选项: (filename E'E:\\soft\\pg13\\test\\tb10.csv')


test=> select * from foreign_tb10 order by id limit 10;
 id | name |             password
----+------+----------------------------------
  1 | john | 81295c8e64adf5066318b5c5fbd981b9
  2 | john | 5a61103ef2aadb800061d0f2305af0ea
  3 | john | 9511339cb9316a649cd02cd4a58d559b
  4 | john | 8413c3138760565d7618bc63fa09b2c3
  5 | john | f38c62b386abb1d478e0383199990e71
  6 | john | 11566ae5bea1fcd1955b53e95939a976
  7 | john | e4104ab9ae16983586fdad0130390a38
  8 | john | aa0d23cb19bf9c5266cffc1ba940e187
  9 | john | 5a4d1b2262eb98d5cf89bf67d7449265
 10 | john | 413c0e3ad9415901a789141bb0baa04b
(10 行记录)


test=> explain select * from foreign_tb10 order by id limit 10;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Limit  (cost=3.68..3.70 rows=10 width=68)
   ->  Sort  (cost=3.68..3.73 rows=22 width=68)
         Sort Key: id
         ->  Foreign Scan on foreign_tb10  (cost=0.00..3.20 rows=22 width=68)
               Foreign File: E:\soft\pg13\test\tb10.csv
               Foreign File Size: 2091 b
(6 行记录)

中间遇到写文件权限问题，如果是linux则设置文件夹权限

chown -R postgres:postgres    xxx_path;

如果是windows则设置文件权限为everyone所有权限即可