一、创建postgres_fdw
warehouse_db=#CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
warehouse_db=#
warehouse_db=#
二、添加FOREIGN DATA WRAPPER
warehouse_db=# CREATE SERVER foreign_server
warehouse_db-# FOREIGN DATA WRAPPER postgres_fdw
warehouse_db-# OPTIONS (host '108.88.3.246', port '5432', dbname 'jyoa');
CREATE SERVER
三、添加本地用户映射
warehouse_db=# CREATE USER MAPPING FOR ghan SERVER foreign_server OPTIONS (user 'jyoa', password 'jyoa');
CREATE USER MAPPING
warehouse_db=#
四、添加外部表
warehouse_db=# CREATE FOREIGN TABLE ghan_table ( a integer NOT NULL, b character varying(3) ) SERVER foreign_server OPTIONS (schema_name 'public', table_name 'tab');
CREATE FOREIGN TABLE
warehouse_db=# GRANT SELECT ON TABLE ghan_table TO ghan;
GRANT
五、测试外表部
warehouse_db=# \c - ghan
You are now connected to database "warehouse_db" as user "ghan".
warehouse_db=# select * from ghan_table;
a | b
----+-----
1 | aaa
2 | bbb
3 | ccc
1 | aaa
2 | bbb
3 | ccc
21 | d
21 | ddd
21 | dwd
45 | asd
45 | asd
(11 rows)
warehouse_db=#
阅读(2860) | 评论(0) | 转发(0) |
