数据库

PostgreSQL 中的postgres_fdw扩展详解
数据库 2024/11/5 佚名
3 2

通过postgres_fdw 扩展，访问远程数据库表

一、环境准备

虚拟机(node107)：centos7、PostgreSQL10

远程服务器(百度云服务BBC)： centos7、PostgreSQL10

在本地虚拟机上访问远程服务器的数据表。

二、配置连接

（1）创建扩展: 在本地107这个节点上创建扩展。

[root@107 ~]# su postgre
su: user postgre does not exist
[root@107 ~]# su postgres
bash-4.2$ psql mydb postgres
could not change directory to "/root": 权限不够
psql (10.7)
Type "help" for help.

mydb=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

如果是普通用户使用 ·postgres_fdw 需要单独授权

grant usage on foreign data wrapper postgres_fdw to 用户名

（2） 创建 foreign server 外部服务器，外部服务是指连接外部数据源的连接信息

mydb=# create server fs_postgres_bbc 
foreign data wrapper postgres_fdw options(host '182.61.136.109',port '5432',dbname 'technology');
mydb=#

定义名称为 fs_postgres_bbc的外部服务，options 设置远程PostgreSQL数据源连接选项，通常设置主机名、端口、数据库名称。

（3）需要给外部服务创建映射用户

mydb=# create user mapping for postgres server 
fs_postgres_bbc options(user 'postgres',password 'password');
CREATE USER MAPPING
mydb=#

for 后面接的是 node107 的数据库用户，options 里接的是远程PostgreSQL数据库的用户和密码。password 注意修改成自己的

其实想访问远程数据库，无非就是知道连接信息。包括host、port、dbname、user、password

（4）BBC上准备数据。

technology=# select * from public.customers where id < 5;
 id | name 
----+-------
 1 | name1
 2 | name2
 3 | name3
 4 | name4
(4 rows)

technology=# 
-- schemaname = public

(5) 在node107上创建外部表：

mydb=# create foreign table ft_customers 
(
 id int4 primary key ,
 name varchar(200)
 ) server fs_postgres_bbc options (schema_name 'public',table_name 'customers');



错误: 外部表上不支持主键约束

第1行create foreign table ft_customers (id int4 primary key , nam...
            ^
mydb=#

可以看见，外部表不支持主键约束。想想也是合理

mydb=# create foreign table ft_customers (
 id int4 , 
 name varchar(200)
) server fs_postgres_bbc options (schema_name 'public',table_name 'customers');
CREATE FOREIGN TABLE
mydb=#

options 选项中： 需要指定外部表的schema和表名

（6）在node107上去访问远程BBC的数据

mydb=# select * from ft_customers where id < 5;
 id | name 
----+-------
 1 | name1
 2 | name2
 3 | name3
 4 | name4
(4 rows)

mydb=#

可以看见在mydb上能够访问远程数据库上 的数据了。

如果出现报错，如报pg_hba.conf 文件没有访问策略，在需要在对修改配置文件。

（7）本地数据库表与远程数据库表进行进行关联查询

create table orders (
 id int PRIMARY KEY,
 customerid int
);

INSERT INTO orders(id,customerid) VALUES(1,1),(2,2);

SELECT * FROM orders;

-- 和外部表关联查询。
mydb=# SELECT o.*,c.*
mydb-# FROM orders o
mydb-# INNER JOIN ft_customers c ON o.customerid = c.id
mydb-# WHERE c.id < 10000;
 id | customerid | id | name 
----+------------+----+-------
 1 |   1 | 1 | name1
 2 |   2 | 2 | name2
(2 rows)

mydb=#

PostgreSQL 中的postgres_fdw扩展详解

三、postgres_fdw 外部表支持写操作

postgres_fdw 外部表一开始只支持读，PostgreSQL9.3 版本开始支持可写。

写操作需要保证：1. 映射的用户对有写权限；2. 版本需要9.3 以上。

在node107结点上线删除数据，后再插入数据、最后更新。并查看远程BBC数据库表情况

mydb=# select count(*) from ft_customers;
 count 
----------
 10000000
(1 row)

mydb=# delete from ft_customers where id = 9999999;
DELETE 1
mydb=# select count(*) from ft_customers;
 count 
---------
 9999999
(1 row)

mydb=# insert into ft_customers values(9999999,'name1');
INSERT 0 1
mydb=# select count(*) from ft_customers;
 count 
----------
 10000000
(1 row)

mydb=# select * from ft_customers where id = 9999999;
 id | name 
---------+-------
 9999999 | name1
(1 row)

mydb=# update ft_customers set name = 'name999' where id = 9999999;
UPDATE 1
mydb=# select * from ft_customers where id = 9999999;
 id | name 
---------+---------
 9999999 | name999
(1 row)

mydb=#

可以看见对ft_customers 进行增删改查。

四、postgres_fdw支持聚合函数下推

PostgreSQL10 增强了postgres_fdw 扩展模块的特性，可以将聚合、关联操作下推到远程PostgreSQL数据库进行，而之前的版本是将外部表相应的远程数据全部取到本地再做聚合，10版本这个心特性大幅度减少了从远程传输到本地库的数据量。提升了postgres_fdw外部表上聚合查询的性能。

mydb=# EXPLAIN(ANALYZE on,VERBOSE on) select id,count(*) from ft_customers where id < 100 group by id;
            QUERY PLAN            
----------------------------------------------------------------------------------------------------
 Foreign Scan (cost=104.88..157.41 rows=199 width=12) (actual time=16.725..16.735 rows=99 loops=1)
 Output: id, (count(*))
 Relations: Aggregate on (public.ft_customers)
 Remote SQL: SELECT id, count(*) FROM public.customers WHERE ((id < 100)) GROUP BY 1
 Planning time: 0.247 ms
 Execution time: 249.410 ms
(6 rows)

mydb=#

PostgreSQL 中的postgres_fdw扩展详解

remote sql: 远程库上执行的SQL，此SQL为聚合查询的SQL。聚合是在远程上执行的。

如果在PostgreSQL9.6 测试，则需要从远程传输到本地才可以。

小结

物理表和外部表不能同名，因为pg_class的对象名称唯一键的缘故

外部表不会存储数据。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

标签：
PostgreSQL,postgres_fdw扩展
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
白云城资源网 Copyright www.dyhadc.com

评论“PostgreSQL 中的postgres_fdw扩展详解”

暂无“PostgreSQL 中的postgres_fdw扩展详解”评论...
www.dyhadc.com 白云城资源网
129,905影音资源
244,626技术资源
111,817软件资源
578,645站长资源
一句话新闻
一口气升级7个大模型SaaS应用，百度智能云：突出一个“开箱即用” - 2024/11/5

这一波大模型产业落地浪潮里，不少企业其实处在 “干瞪眼“的状态。

一种情况是，很多大模型产品看得见却摸不着，在台上一个个遥遥领先——今天Sora技精四座，明天英伟达的机器人又赢得满堂彩，可是到了台下一问：啥时候能用上啊？答曰：遥遥无期。

另一种情况是，企业想用上大模型，却又难免瞻前顾后——既要考虑场景融合，又得兼顾安全性，还要考虑打通现有系统，再加上各种部署成本和繁琐的采购流程……最后只能拂袖：罢了，再等等吧。

稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！

昨天有一位朋友在大神群里分享，自己亚服账号被封号之后居然弹出了国服的封号信息对话框。

这里面让他访问的是一个国服的战网网址，com.cn和后面的zh都非常明白地表明这就是国服战网。

而他在复制这个网址并且进行登录之后，确实是网易的网址，也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情，因为以前都没有出现这样的情况，现在突然提示跳转到国服战网的网址，是不是说明了简体中文客户端已经开始进行更新了呢？

更新日志

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网 更多链接
白云城资源网 Copyright www.dyhadc.com