postgresql_fdw 跨数据库查询

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: --PostgreSQL的数据库逻辑上是相互独立的,和Oracle类似,如果要访问其他数据库,需要做跨库操作,--Postgres本身提供了一些扩展,比如dblink,pgsql_fdw等,高版本的建议使用postgres_fdw,也就是pgsql_fdw的升级版。
--PostgreSQL的数据库逻辑上是相互独立的,和Oracle类似,如果要访问其他数据库,需要做跨库操作,
--Postgres本身提供了一些扩展,比如dblink,pgsql_fdw等,高版本的建议使用postgres_fdw,也就是pgsql_fdw的升级版。 


远端数据准备
postgres=# show search_path;
 search_path 
-------------
 schema_fdw
(1 row)
postgres=# create table tbl_kenyon (id int,remark text);
CREATE TABLE
postgres=# insert into tbl_kenyon select generate_series(1,100),'Kenyon Go!';
INSERT 0 100

二、安装使用 
 安装分4步走 
1.本地安装extension 
--安装的扩展名是来自于share/extension/*.control中的文件名*,比如postgres_fdw.control
postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# select * from pg_extension ;
 extname     | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition   -------------------+----------+--------------+----------------+------------+-----------+
 plpgsql            |       10 |           11 | f              | 1.0        |           | 
 pg_stat_statements |       10 |         2200 | t              | 1.1        |           | 
 postgres_fdw       |       10 |         2200 | t              | 1.0        |           | 
(3 rows)
postgres=# select * from pg_foreign_data_wrapper;
  fdwname    | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions 
  ------------+----------+------------+--------------+--------+------------
 postgres_fdw |       10 |     154356 |       154357 |        | 
(1 row)

或
postgres=# \dx
                                     List of installed extensions
      Name        | Version |  Schema |                        Description                     -----------------+---------+------------+-----------------------------------------------------
 pg_stat_statements | 1.1     | public |track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw       | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(3 rows)

2.本地创建server并查看 
该server作用是在本地配置一个连接远程的信息,下面的配置是要连接到远程DB名称是postgres数据库
postgres=# create server server_remote_rudy_01 foreign data wrapper postgres_fdw options(host 'rudy_01',port '5432',dbname 'postgres');
CREATE SERVER
postgres=# select * from pg_foreign_server ;
     srvname      | srvowner | srvfdw | srvtype | srvversion | srvacl |   srvoptions  
--------------+----------+--------+---------+------------+--------+--------------------------
server_remote_rudy_01 |       10 | 154358 |         |     |{host=10.1.11.71,port=5432,dbname=postgres}
(1 row)
或者
postgres=# \des
              List of foreign servers
       Name       |  Owner   | Foreign-data wrapper 
------------------+----------+----------------------
 server_remote_rudy_01 | postgres | postgres_fdw
(1 row)

3.创建用户匹配信息并查看,在本地
--for后面的postgres是本地登录执行的用户名,option里存储的是远程的用户密码
postgres=# create user mapping for postgres server server_remote_rudy_01 options(user 'postgres',password '123456');
CREATE USER MAPPING
postgres=# select * from pg_user_mappings;
  umid  | srvid  |     srvname      | umuser | usename  |             umoptions             
--------+--------+------------------+--------+----------+-----------------------------------
 154360 | 154359 | server_remote_rudy_01 |     10 | postgres | {user=usr_pg_fdw,password=123456}
(1 row)
或
postgres=# \deu+
                          List of user mappings
      Server      | User name |               FDW Options                
------------------+-----------+------------------------------------------
 server_remote_rudy_01 | postgres  | ("user" 'usr_pg_fdw', password '123456')
(1 row)

4.本地创建外部表,指定server
postgres=#  CREATE FOREIGN TABLE test1(id int,remark text) server server_remote_rudy_01 options (schema_name 'public',table_name 'tbl_kenyon');
CREATE FOREIGN TABLE



--导入整个schem下面的表
create schema test;
--视用户不同授予不同的权限
grant all on schema test to rudy_02;
grant all on foreign data wrapper postgres_fdw to rudy_02;                     
grant all on foreign server server_remote_rudy_01 to rudy_02;    
--导入指定的表,也可以不导入指定的表,也可以导入整个schema下面的表        
IMPORT FOREIGN SCHEMA public FROM SERVER server_remote_rudy_01 INTO test;
IMPORT FOREIGN SCHEMA public limit to(t1) FROM SERVER server_remote_rudy_01 INTO test;

--通过使用\d查看表,注意不通过使用\dt查看表,而且如果远端的表有drop或者create,在本地会察觉不到
\d test.

postgres=> select * from test.t1 ;          
ERROR:  relation "test.t1" does not exist

--查看系统中有哪些外部表,可查看如下的sql
select * from pg_foreign_table;
--注意如果要删除外部表,千万千万不要直接删除pg_foreign_table表中的数据
--delete from pg_foreign_table ;
--否则不能再导入IMPORT FOREIGN 删除的表,也不能执行 select froeign table 查询删除的表,也不能drop server,都会报如下的错
ERROR:  cache lookup failed for foreign table 49251
--再插入删除的数据
postgres=# \d+ pg_foreign_table;
                  Table "pg_catalog.pg_foreign_table"
  Column   |  Type  | Modifiers | Storage  | Stats target | Description 
-----------+--------+-----------+----------+--------------+-------------
 ftrelid   | oid    | not null  | plain    |              |  在本地pg_class表中对应的 relfilenode
 ftserver  | oid    | not null  | plain    |              |  在本的pg_foreign_server表的oid
 ftoptions | text[] |           | extended |              |  对应foreign server的schema和table名字
postgres=> insert into pg_foreign_table values(49251,49247,array['schema_name=public','table_name=postgres']); 
--而后再删除外部表
postgres=> drop foreign table test.postgres_log ;
DROP FOREIGN TABLE


-- postgres_fdw 为了性能其会把where查询发送到远端
postgres_fdw attempts to optimize remote queries to reduce the amount of data transferred from foreign servers
The query that is actually sent to the remote server for execution can be examined using EXPLAIN VERBOSE


相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
4月前
|
人工智能 安全 机器人
无代码革命:10分钟打造企业专属数据库查询AI机器人
随着数字化转型加速,企业对高效智能交互解决方案的需求日益增长。阿里云AppFlow推出的AI助手产品,借助创新网页集成技术,助力企业打造专业数据库查询助手。本文详细介绍通过三步流程将AI助手转化为数据库交互工具的核心优势与操作指南,包括全场景适配、智能渲染引擎及零代码配置等三大技术突破。同时提供Web集成与企业微信集成方案,帮助企业实现便捷部署与安全管理,提升内外部用户体验。
450 12
无代码革命:10分钟打造企业专属数据库查询AI机器人
|
4月前
|
SQL 存储 关系型数据库
PostgreSQL窗口函数避坑指南:如何让复杂分析查询提速300%?
本文基于真实企业级案例,深入剖析PostgreSQL窗口函数的执行原理与性能陷阱,提供8大优化策略。通过定制索引、分区裁剪、内存调优及并行处理等手段,将分钟级查询压缩至秒级响应。结合CTE分阶段计算与物化视图技术,解决海量数据分析中的瓶颈问题。某金融客户实践表明,风险分析查询从47秒降至0.8秒,效率提升5800%。文章附带代码均在PostgreSQL 15中验证,助您高效优化SQL性能。
198 0
|
13天前
|
关系型数据库 MySQL 分布式数据库
阿里云PolarDB云原生数据库收费价格:MySQL和PostgreSQL详细介绍
阿里云PolarDB兼容MySQL、PostgreSQL及Oracle语法,支持集中式与分布式架构。标准版2核4G年费1116元起,企业版最高性能达4核16G,支持HTAP与多级高可用,广泛应用于金融、政务、互联网等领域,TCO成本降低50%。
|
14天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
14天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
6月前
|
Cloud Native 关系型数据库 分布式数据库
|
20天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
4月前
|
SQL 关系型数据库 MySQL
Go语言数据库编程:使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口,支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容,涵盖实际开发中常用的技巧与注意事项,适合快速掌握Go语言数据库编程基础。
296 62
|
6月前
|
并行计算 关系型数据库 MySQL
如何用 esProc 将数据库表转储提速查询
当数据库查询因数据量大或繁忙变慢时,可借助 esProc 将数据导出为文件进行计算,大幅提升性能。以 MySQL 的 3000 万行订单数据为例,两个典型查询分别耗时 17.69s 和 63.22s。使用 esProc 转储为二进制行存文件 (btx) 或列存文件 (ctx),结合游标过滤与并行计算,性能显著提升。例如,ctx 并行计算将原查询时间缩短至 0.566s,TopN 运算提速达 30 倍。esProc 的简洁语法和高效文件格式,特别适合历史数据的复杂分析场景。
|
2月前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL数据库的WAL日志与数据写入的过程
PostgreSQL中的WAL(预写日志)是保证数据完整性的关键技术。在数据修改前,系统会先将日志写入WAL,确保宕机时可通过日志恢复数据。它减少了磁盘I/O,提升了性能,并支持手动切换日志文件。WAL文件默认存储在pg_wal目录下,采用16进制命名规则。此外,PostgreSQL提供pg_waldump工具解析日志内容。
173 0

推荐镜像

更多