DBLink 扩展

=# alter role user_local with password ‘ user_local ‘ ; ALTER ROLE test = # create schema user_local authorization user_local; CREATE SCHEMA

2、远程数据库创建用户与测试表

test= # create role user_remote with login;
CREATE ROLE
test =# alter role user_remote with password  ‘  user_remote  ‘  ;
ALTER ROLE
test = # create schema user_remote authorization user_remote;
CREATE SCHEMA

test = # \c test user_remote
You are now connected to database   "  test  "   as  user  "  user_remote  "  .
test => create table t1(id integer,name  char ( 9  ));
CREATE TABLE
test => insert into t1 values( 1 , ‘  a  ‘  );
INSERT   0   1  
test => insert into t1 values( 2 , ‘  b  ‘  );
INSERT   0   1 

二、创建扩展插件

1、创建扩展

test = #  create   extension dblink;
  CREATE   EXTENSION

创建后，pg_foreign_data_wrapper 会新增一条记录。
test  = #  select   *   from  pg_foreign_data_wrapper  where  fdwname =  ‘  dblink_fdw  ‘  ;
  oid    |   fdwname    |  fdwowner  |  fdwhandler  |  fdwvalidator  |  fdwacl  |   fdwoptions 
  --  -----+------------+----------+------------+--------------+--------+------------ 
  16466   |  dblink_fdw  |         10   |            0   |          16465   |          |  

2、创建Server

 create  server dblink_to43  foreign  data wrapper dblink_fdw options (hostaddr  ‘  192.168.237.43  ‘  , dbname  ‘  test  ‘  );

test  = #  select   *   from  pg_foreign_server  where  srvname =  ‘  dblink_to43  ‘  ;
  oid    |    srvname    |  srvowner  |  srvfdw  |  srvtype  |  srvversion  |  srvacl  |                srvoptions               
  --  -----+-------------+----------+--------+---------+------------+--------+--------------------------------------- 
  24693   |  dblink_to43  |         10   |    24692   |           |              |          |  {hostaddr =  192.168 . 237.43 ,dbname =  test}
(  1   row)

  grant  usage  on   foreign  server dblink_to43  to  user_local;

3、创建用户映射

 create   user  mapping  for  user_local server dblink_to43 options ( user   ‘  user_remote  ‘  , password  ‘  user_remote  ‘  );

test  = #  select   *   from   pg_user_mappings;
 umid    |  srvid  |    srvname    |  umuser  |   usename    |                  umoptions                
  --  -----+-------+-------------+--------+------------+----------------------------------------- 
  24694   |   24693   |  dblink_to43  |    24645   |  user_local  |  { user  = user_remote,password =  user_remote}
(  1  row)

三、测试数据库访问

1、dblink 连接

test =>   \c test user_local
You are now connected   to   database  "test"  as   user   "user_local".
test  =>   select  dblink_connect( ‘  conn01  ‘ , ‘  dblink_to43  ‘  ) ;
 dblink_connect 
  --  -------------- 
  OK
(  1   row)

test  =>   select   *   from   sys_database_link;
 oid   |  lnkname  |  lnknamespace  |  lnkuser  |  lnkowner  |  lnkserver  |  dbtype  |  lnkoptions  |   lnkcreated 
  --  ---+---------+--------------+---------+----------+-----------+--------+------------+------------ 
( 0   rows)

test  =>   select   *   from   all_db_links;
 owner   |  db_link  |  username  |  host  |   created 
  --  -----+---------+----------+------+--------- 
( 0   rows)
 

以dblink_connect 方式连接，采用的是长连接，直到显示关闭，或者数据库会话结束。

Note：dblink 信息在 sys_database_link 是看不到的，sys_database_link 是配合 kdb_database_link 插件使用。

2、数据访问

test =>   select   *   from  dblink( ‘  conn01  ‘ , ‘  select * from t1  ‘  );
 id   |     name    
  --  --+----------- 
   1   |   a        
    2   |   b        
(  2   rows)

test  =>   select  dblink_exec( ‘  conn01  ‘ , ‘  insert into t1 values(1,  ‘‘  a  ‘‘  )  ‘  );
 dblink_exec 
  --  ----------- 
  INSERT   0   1  
(  1   row)

test  =>    select   *   from  dblink_exec( ‘  conn01  ‘ , ‘  insert into t1 values(1,  ‘‘  a  ‘‘  )  ‘  );
 dblink_exec 
  --  ----------- 
  INSERT   0   1  
(  1  row)

3、事务操作

test =>   SELECT  dblink_exec( ‘  conn01  ‘ ,  ‘  BEGIN  ‘  );
 dblink_exec 
  --  ----------- 
  BEGIN  
(  1   row)

test  =>   select  dblink_exec( ‘  conn01  ‘ , ‘  insert into t1 values(1,  ‘‘  a  ‘‘  )  ‘  );
 dblink_exec 
  --  ----------- 
  INSERT   0   1  
(  1   row)

test  =>   SELECT  dblink_exec( ‘  conn01  ‘ ,  ‘  COMMIT  ‘  );
 dblink_exec 
  --  ----------- 
  COMMIT  
(  1  row)

4、断开连接

test =>   SELECT  dblink_disconnect( ‘  conn01  ‘  );
 dblink_disconnect 
  --  ----------------- 
  OK
(  1  row)

四、其他注意点

以上例子的是用dblink_connect 创建连接，是长连接。也可以在执行SQL的同时创建连接：

test =>   select   *   from  dblink( ‘  dbname=test host=192.168.237.43 port=54321 user=user_remote password=user_remote  ‘ , ‘  select * from t1  ‘ );

还有，dblink 实际是数据库的连接，前提是目标端的 sys_hba.conf 必须支持连接。

DBLink 扩展

标签：lte   class   tor   link   mes   use   dbn   row   插件   

查看更多关于DBLink 扩展的详细内容...