Kingbase_FDW 使用介绍

 与postgresql_fdw功能类似，KINGBASE_FDW  是一种外部访问接口，它可以被用来访问存储在外部的数据。想要使用fdw访问数据需要先确保：网络通，数据库访问配置(pg_hba,conf)正常，同时远端数据库的用户必须有表的相关权限。

一、远程数据库

远程数据库 IP: 142 。创建用户及测试数据：

1 2 3 4 5 6

create role user_remote with login; alter role user_remote with password 'user_remote'; create schema user_remote authorization user_remote; \c test user_remote create table t1(id integer,name varchar(9)); insert into t1 values(1,'a'),(2,'b');

二、本地数据库

本地数据库 IP: 143 

1、创建扩展 kingbase_fdw

1 2 3 4 5

\c test system create role user_local with login; alter role user_local with password 'user_local'; create schema user_local authorization user_local; create extension kingbase_fdw;

扩展创建完成后，pg_foreign_data_wrapper 有如下一行：

1 2 3 4 5

test=# select * from pg_foreign_data_wrapper where fdwname='kingbase_fdw';   oid  |   fdwname    | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions -------+--------------+----------+------------+--------------+--------+------------  16495 | kingbase_fdw |       10 |      16493 |        16494 |        | (1 row)

2、create server

create server <server名称>  foreign data wrapper postgres_fdw  options (host '<远程数据库主机名>’, port '<远程数据库端口>', dbname '<远程数据库名称>');

1 2	create server srv_42 foreign data wrapper kingbase_fdw options(host '192.168.237.42',port '54321',dbname 'test'); grant usage on foreign server srv_42 to user_local;

注意：必须将 Server 授权给用户，否则用户在创建外部表时，会报“ERROR:  permission denied for foreign server srv_42” 错误。创建完server后，在sys_foreign_server 会有如下一条：

1 2 3 4 5

test=> select * from sys_foreign_server where srvname='srv_42';   oid  | srvname | srvowner | srvfdw | srvtype | srvversion |                srvacl                 |                  srvoptions                  -------+---------+----------+--------+---------+------------+---------------------------------------+----------------------------------------------  16502 | srv_42  |       10 |  16495 |         |            | {system=U/system,user_local=U/system} | {host=192.168.237.42,port=54321,dbname=test} (1 row)

3、创建user mapping

create user mapping for <本地数据库用户>
server <server名称>
options (user '<远程数据库用户>', password '<远程数据库用户密码>');

1	create user mapping for user_local server srv_42 options(user 'user_remote',password 'user_remote');

创建后，在pg_user_mappings 会有一条记录：

1 2 3 4

test=> select * from pg_user_mappings where srvname='srv_42';  umid  | srvid | srvname | umuser |  usename   |                umoptions                -------+-------+---------+--------+------------+-----------------------------------------  16503 | 16502 | srv_42  |  16500 | user_local | {user=user_remote,password=user_remote}

4、创建外部表

1 2 3 4

create foreign table ft_t1 (     id integer ,     name varchar(9) ) server srv_42 options (schema_name 'user_remote',table_name 't1');

5、访问测试

1 2 3 4 5 6 7 8 9 10 11 12 13

test=> \d                 List of relations    Schema   | Name  |     Type      |   Owner    ------------+-------+---------------+------------  user_local | ft_t1 | foreign table | user_local (1 row)   test=> select * from ft_t1;     id |   name    ----+-----------   1 | a          2 | b        (2 rows)

6、选择导入远程的整个schema

可以将远程schema下的所有表及视图通过import 方式导入，避免逐表创建。

import foreign schema <远程数据库schema名称>
        from <server名称> 
        into <本地数据库schema名称>;

1 2 3 4 5 6 7 8

test=> import foreign schema user_remote from server srv_42 into user_local; IMPORT FOREIGN SCHEMA test=> \d                List of relations    Schema   | Name |     Type      |   Owner    ------------+------+---------------+------------  user_local | t1   | foreign table | user_local  user_local | t2   | foreign table | user_local