Kingbase_FDW 使用介绍
2024-09-03 21:56:35
与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
1、创建扩展 kingbase_fdw
\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)
2、create server
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)
3、创建user mapping
create user mapping for user_local server srv_42 options(user 'user_remote',password 'user_remote');
创建后,在pg_user_mappings 会有一条记录:
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、创建外部表
create foreign table ft_t1 (
id integer ,
name varchar(9)
) server srv_42 options (schema_name 'user_remote',table_name 't1');
5、访问测试
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 方式导入,避免逐表创建。
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
最新文章
- [原创]MvvmLight中用IDialogService替代DialogMessage的用法
- 如何转换SQL Server 2008数据库到SQL Server 2005
- UVALive 4329 Ping pong
- EditText显示明文与密码
- java14-9 Doteformat的练习
- SqlServer2008R2 如何插入多条数据
- Maven——Maven核心概念
- Careercup - Google面试题 - 5424071030341632
- 最简单的视音频播放示例3:Direct3D播放YUV,RGB(通过Surface)
- IDEA新建spring boot项目没有Spring Initializr选项
- 一个实时收集MySql变更记录的组件CanalSharp.AspNetCore
- const命令
- win10优化
- Python解析HDF文件 分类: Python 2015-06-25 00:16 743人阅读 评论(0) 收藏
- rm与管道使用
- c# 日期函数[string.Format----GetDateTimeFormats]格式
- ASP.NET MVC3默认提供了11种ActionResult的实现
- layui的分页
- 20145335郝昊《java程序设计》第8周学习总结
- 【Web学习笔记】浅析CGI概念及用法