借助jdbc_fdw插件可以使用JDBC协议访问相应的数据库,比如也可以连接访问大数据hive。本文在java1.8和PostgreSQL 14环境进行测试。
https://github.com/pgspider/jdbc_fdw
1. 准备java环境
测试环境使用java 1.8
$ java -version
java version "1.8.0_202"
Java(TM) SE Runtime Environment (build 1.8.0_202-b08)
Java HotSpot(TM) 64-Bit Server VM (build 25.202-b08, mixed mode)
配置java动态库,编译jdbc_fdw需要使用
sudo ln -s /opt/jdk1.8.0/jre/lib/aarch64/server/libjvm.so /usr/lib64/libjvm.so
2. 编译jdbc_fdw
https://github.com/pgspider/jdbc_fdw
从上面的链接下载压缩包,并解压到PostgreSQL源码目录的contrib子目录下
cp -r jdbc_fdw-0.3.0/ /opt/postgresql-14.6/contrib/
编译jdbc_fdw
cd /opt/postgresql-14.6/contrib/jdbc_fdw-0.3.0/
make clean
make install
3. 使用超级用户创建jdbc_fdw扩展
创建扩展命令:
create extension jdbc_fdw with schema public;
查看已安装的jdbc_fdw版本
postgres=# select jdbc_fdw_version();
┌──────────────────┐
│ jdbc_fdw_version │
├──────────────────┤
│ 300 │
└──────────────────┘
(1 row)
4. 超级用户授权普通用户admin使用jdbc_fdw扩展的权限
授权命令:
grant USAGE on FOREIGN data wrapper jdbc_fdw to admin;
不过目前普通用户使用受限,只能使用超级用户。
5.超级用户创建server,配置jdbc连接参数
创建jdbc server命令:
CREATE SERVER jdbc_server FOREIGN DATA WRAPPER jdbc_fdw OPTIONS(
drivername 'org.postgresql.Driver',
url 'jdbc:postgresql://192.168.20.100:1406/postgres',
querytimeout '5s',
jarfile '/home/postgres/postgresql-42.2.24.jar',
maxheapsize '128MB'
);
查看server使用元命令\des或\des+
postgres=# \des
List of foreign servers
┌─────────────┬──────────┬──────────────────────┐
│ Name │ Owner │ Foreign-data wrapper │
├─────────────┼──────────┼──────────────────────┤
│ jdbc_server │ postgres │ jdbc_fdw │
└─────────────┴──────────┴──────────────────────┘
(1 row)
6.超级用户创建与jdbc server之间的用户映射
创建用户映射的命令:
CREATE USER MAPPING FOR postgres SERVER jdbc_server OPTIONS (username 'postgres',password 'admin');
查看用户映射使用元命令\deu或\deu+
postgres=# \deu
List of user mappings
┌─────────────┬───────────┐
│ Server │ User name │
├─────────────┼───────────┤
│ jdbc_server │ postgres │
└─────────────┴───────────┘
(1 row)
7.创建外部表
创建外部表的命令:
CREATE FOREIGN TABLE f_t(id int OPTIONS(key 'true')) SERVER jdbc_server OPTIONS (
schema_name 'public',
table_name 't',
updatable 'true'
);
8.外部表测试
postgres=# insert into f_t values(1),(2),(3);
INSERT 0 3
postgres=# update f_t set id = 101 where id =1;
UPDATE 1
postgres=# delete from f_t where id=2;
DELETE 1
postgres=# select * from f_t;
┌─────┐
│ id │
├─────┤
│ 3 │
│ 101 │
└─────┘
(2 rows)
9.注意问题
1)需要使用超级用户,普通用户会报错
ERROR: password is required
DETAIL: Non-superuser cannot connect if the server does not request a password.
HINT: Target server's authentication method must be changed.
2)创建server的选项配置需要检查前后是否有空格,比如jarfile文件尾部有空格时,最后使用时会提示如下错误:
ERROR: remote server returned an error
3)外部表映射时,不能打开use_remote_estimate,测试报错如下:
ERROR: Unknown Error!
CONTEXT: Remote SQL command: EXPLAIN SELECT id FROM public.t
4)IMPORT FOREIGN SCHEMA功能也会报错如下:
ERROR: "pg_aggregate_fnoid_index" is not a foreign table
HINT: Use DROP INDEX to remove an index.
CONTEXT: SQL statement "DROP FOREIGN TABLE IF EXISTS pg_aggregate_fnoid_index"
保持联系
本人组建了一个技术群:PG乐知乐享交流群。欢迎关注文章的小伙伴随缘加入,进群请加微信并备注PG乐知乐享。
最后修改时间:2023-04-10 16:17:14
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。