postgres_fdw and dblink

源端：192.168.1.100

目标端：192.168.1.101

postgres_fdw

先安装postgres_fdw 插件

[root@node1 contrib]# pwd

/upload/postgresql-9.4.4/contrib

到数据库解压缩路径下：

make

make install postgres_fdw

psql

postgres=# create extension postgres_fdw;

CREATE EXTENSION

postgres=# select * from pg_foreign_data_wrapper;

fdwname | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions

--------------+----------+------------+--------------+--------+------------

postgres_fdw | 10 | 25316 | 25317 | |

(1 row)

postgres=# select * from pg_available_extensions;

创建外部服务器：

postgres=# create SERVER postgres_fdw_server FOREIGN DATA WRAPPER postgres_fdw options (host '192.168.1.101',dbname 'postgres',port '5432');

CREATE SERVER

创建用户映射，指定连接远程数据库的用户名和密码，命令如下：

postgres=# create user mapping for current_user

postgres-# server postgres_fdw_server

postgres-# options(user 'postgres',password 'postgres');

CREATE USER MAPPING

创建外部表:

postgres=# create FOREIGN TABLE qqq(id int,note text) server postgres_fdw_server

postgres-# options (table_name 'test00');

CREATE FOREIGN TABLE

postgres=# create table test00 (id int ,note text);

CREATE TABLE

postgres=# insert into test00 values (1,'1''2''3''4''5');

INSERT 0 1

【这个表test00可以在创建外部表之前创建也可以之后创建】

查询外部表：

postgres=# select * from qqq;

id | note

----+-----------

1 | 1'2'3'4'5

(1 row)

查询外部表ＳＱＬ的执行计划：

postgres=# explain select * from qqq limit 2;

QUERY PLAN

---------------------------------------------------------------------

Limit (cost=100.00..100.07 rows=2 width=36)

-> Foreign Scan on qqq (cost=100.00..150.95 rows=1365 width=36)

(2 rows)

dblink

postgres=# create extension dblink ;

CREATE EXTENSION

查看一下是否安装上：

select * from pg_available_extensions;

在目标端（192.168.1.101）：

postgres=# create database test;

CREATE DATABASE

postgres=# create user xixi superuser password 'postgres';

CREATE ROLE

postgres=# alter database test owner to xixi;

ALTER DATABASE

test=# \c test xixi;

You are now connected to database "test" as user "xixi"

test=# create table test00 (id int ,note text);

CREATE TABLE

test=# insert into test00 select generate_series (1,5);

INSERT 0 5

test=# select * from test00;

id | note

----+------

1 |

2 |

3 |

4 |

5 |

(5 rows)

源端（192.168.1.100）：

建立一个到远程数据库的连接：

postgres=# select dblink_connect('postgres_dblink_test','dbname=test host=192.168.1.101 port=5432 user=xixi ');

dblink_connect

----------------

OK

(1 row)

查看已经创建的连接：

postgres=# select dblink_get_connections() ;

dblink_get_connections

------------------------

{postgres_dblink_test}

(1 row)

通过远程连接查看目标端数据库中的数据：

postgres=# select * from dblink ('postgres_dblink_test','select * from test00') as t(id int,note text);

id | note

----+------

1 |

2 |

3 |

4 |

5 |

(5 rows)

【这里as后面相当于把远程数据库中的字段在本地重新命名。这个一定要写，不然dblink会报错误。】

使用dblink最方便的方法是创建一个视图view。这样在查询远程数据表的时候就不用再一次输入以上信息了。比如：

postgres=# create view dblink_test00_view as select * from dblink ('postgres_dblink_test','select * from test00') as t(id int,note text);

CREATE VIEW

postgres=# select * from dblink_test00_view where id>2;

id | note

----+------

3 |

4 |

5 |

(3 rows)

dblink_exec：执行一个不返回行的远程命令，比如insert into语句 。

postgres=# select dblink_exec('postgres_dblink_test', 'insert into test00 values (6);');

dblink_exec

-------------

INSERT 0 1

(1 row)

postgres=# select dblink_exec('postgres_dblink_test', 'insert into test00 values (7,''aa'');');

dblink_exec

-------------

INSERT 0 1

(1 row)

【要注意此处text类型数据此处插入时要加'' ''】

postgres=# select * from dblink_test00_view;

id | note

----+------

1 |

2 |

3 |

4 |

5 |

6 |

7 | aa

(7 rows)

dblink_open：执行一个远程的命令，但是不显示，可以用之后的dblink_fetch()或者dblink_close()来操作

postgres=# select dblink_open('postgres_dblink_test','foo', 'select proname, prosrc from pg_proc where proname like ''bytea%''');

dblink_open

-------------

OK

(1 row）

dblink_fetch：返回之前用dblink_open执行命令的结果

postgres=# select * from dblink_fetch('postgres_dblink_test','foo', 5) AS (funcname name, source text);

funcname | source

--------------------------+--------------------------

bytea_string_agg_finalfn | bytea_string_agg_finalfn

bytea_string_agg_transfn | bytea_string_agg_transfn

byteacat | byteacat

byteacmp | byteacmp

byteaeq | byteaeq

(5 rows)

dblink_close：关闭之前用dblink_open执行命令的结果

postgres=# select * from dblink_close('postgres_dblink_test','foo') ;

dblink_close

--------------

OK

(1 row)

dblink_error_message：得到最后出现错误的信息提示

postgres=# select dblink_error_message('postgres_dblink_test');

dblink_error_message

----------------------

OK

(1 row)

dblink_send_query、dblink_is_busy、dblink_get_notify和dblink_get_result

单次发送和单次接收查询

postgres=# SELECT dblink_connect('dtest1', 'dbname=postgres');

dblink_connect

----------------

OK

(1 row)

postgres=# SELECT * FROM dblink_send_query('dtest1', 'select * from qqq where id < 3') AS t1;

t1

----

1

(1 row)

postgres=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text);

f1 | f2

----+-----------

1 | 1'2'3'4'5

1 | q'w'e'r't

(2 rows)

postgres=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text);

f1 | f2

----+----

(0 rows)

断开远程数据库连接：

postgres=# select dblink_disconnect('dtest1') ;

dblink_disconnect

-------------------

OK

(1 row)

postgres=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text);

ERROR: connection "dtest1" not available

官方参考文档： http://www.postgresql.org/docs/9.2/interactive/dblink.html

postgres_fdw用于访问服务于其他postgreSQL数据库的外部数据包装器，它提供了与原先已有的dblink模块相同的功能，但是使用postgres_fdw更符合SQL标准，在某些场景应下有比dblink更好的性能。