Kingbase_FDW 使用介紹

與postgresql_fdw功能類似，KINGBASE_FDW 是一種外部通路接口，它可以被用來通路存儲在外部的資料。想要使用fdw通路資料需要先確定：網絡通，資料庫通路配置(pg_hba,conf)正常，同時遠端資料庫的使用者必須有表的相關權限。

一、遠端資料庫

遠端資料庫 IP: 142 。建立使用者及測試資料：

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

\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 有如下一行：

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)

create server <server名稱> foreign data wrapper postgres_fdw options (host '<遠端資料庫主機名>’, port '<遠端資料庫端口>', dbname '<遠端資料庫名稱>');

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 會有如下一條：

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)

create user mapping for <本地資料庫使用者>

server <server名稱>

options (user '<遠端資料庫使用者>', password '<遠端資料庫使用者密碼>');

create

user

mapping

for

user_local server srv_42 options(

user

'user_remote'

password

'user_remote'

);

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}

create

foreign

table

ft_t1 (

id

integer

name

varchar

(9)

) server srv_42 options (schema_name

'user_remote'

,table_name

't1'

);

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

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