PostgreSQL获取数据库外部数据fwd

参考 贱一白的博客，网址：https://blog.csdn.net/luojinbai/article/details/45673113

 

fdw是foreign data wrappers是PostgreSql获取外部数据的扩展功能。

1 添加file_fdw扩展

postgres=# create extension file_fdw ;

CREATE EXTENSION

3 创建表

create table tb10(id integer,name character varying,passworld character varying);

添加数据

insert into tb10(xxxxxxxxxxxxxx); 

 

通过copy拷贝成文件

copy tb10 to '/home/postgres/tb10.csv';

创建server(外部服务器)

create server server_file_fdw foreign data wrapper file_fdw;

添加server:https://www.postgresql.org/docs/8.4/sql-createserver.html

CREATE SERVER servername [ TYPE 'servertype' ] [ VERSION 'serverversion' ]
    FOREIGN DATA WRAPPER fdwname
    [ OPTIONS ( option 'value' [, ... ] ) ]

修改server:https://www.postgresql.org/docs/8.4/sql-alterserver.html

ALTER SERVER servername [ VERSION 'newversion' ]
    [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ]
ALTER SERVER servername OWNER TO new_owner

 ALTER SERVER mysql_server  OPTIONS (SET  host 'dbxx');

 

修改Host:  vim /etc/hosts

 

 

创建外部表 

create foreign table foreign_tb10 (id integer,name character varying,password character varying)server server_file_fdw  options (filename '/home/postgres/tb10.csv');

执行计划：

explain select * from foreign_tb10 order by id limit 10;