一、创建DBlink
[postgres@db2 extension]$ psql -U postgres
psql (9.5.2)
Type "help" for help.
postgres=# \c warehouse_db
warehouse_db=# create extension dblink;
CREATE EXTENSION
warehouse_db=# SELECT dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
二、添加FOREIGN DATA WRAPPER
warehouse_db=#
warehouse_db=# CREATE SERVER jyoa246 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '108.88.3.246', dbname 'jyoa');
CREATE SERVER
三、添加本地用户映射
warehouse_db=# CREATE USER fdjyoa WITH PASSWORD 'fdjyao';
CREATE ROLE
warehouse_db=# CREATE USER MAPPING FOR fdjyoa SERVER jyoa246 OPTIONS (user 'jyoa', password 'jyoa');
CREATE USER MAPPING
warehouse_db=#
warehouse_db=# GRANT USAGE ON FOREIGN SERVER jyoa246 TO fdjyoa;
GRANT
四、访问外部表测试
warehouse_db=# \set ORIGINAL_USER :USER
warehouse_db=# \c - fdjyoa
You are now connected to database "warehouse_db" as user "fdjyoa".
warehouse_db=> select dblink_connect('myconn','jyoa246');
dblink_connect
----------------
OK
(1 row)
warehouse_db=>
warehouse_db=>
warehouse_db=> select * from dblink('myconn','select * from tab') as t1 (id int, adf char(30));
id | adf
----+--------------------------------
1 | aaa
2 | bbb
3 | ccc
1 | aaa
2 | bbb
3 | ccc
(6 rows)
warehouse_db=>
warehouse_db=> select dblink_connect('myodnn','jyoa246');
dblink_connect
----------------
OK
(1 row)
warehouse_db=> SELECT dblink_exec('myodnn','insert into tab values(21,''d'')');
dblink_exec
-------------
INSERT 0 1
(1 row)
warehouse_db=> select * from dblink('myonn','select * from tab') as t1 (id int, adf char(30));
id | adf
----+--------------------------------
1 | aaa
2 | bbb
3 | ccc
1 | aaa
2 | bbb
3 | ccc
21 | d
(7 rows)
warehouse_db=> SELECT dblink_exec('myodnn','insert into tab values(21,''ddd'')');
dblink_exec
-------------
INSERT 0 1
(1 row)
warehouse_db=> SELECT dblink_exec('myodnn','insert into tab values(21,''dwd'')');
dblink_exec
-------------
INSERT 0 1
(1 row)
warehouse_db=> select * from dblink('myonn','select * from tab') as t1 (id int, adf char(30));
id | adf
----+--------------------------------
1 | aaa
2 | bbb
3 | ccc
1 | aaa
2 | bbb
3 | ccc
21 | d
21 | ddd
21 | dwd
(9 rows)
五、添加本地查询视图
warehouse_db=> CREATE VIEW emp AS select * from dblink('myonn','select * from tab') as t1 (id int, adf char(30));
ERROR: relation "emp" already exists
warehouse_db=> select * from emp;
id | adf
----+--------------------------------
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=> select * from emp;
id | adf
----+--------------------------------
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=>
阅读(2766) | 评论(0) | 转发(0) |
