当前位置: 代码迷 >> 综合 >> pgsql:跨库访问其他数据库
  详细解决方案

pgsql:跨库访问其他数据库

热度:53   发布时间:2023-12-08 17:54:11.0

在mssql或mysql中,跨库访问其他数据库是很简单的。用 [数据库名].[表名]即可访问。

但在pgsql中跨库访问就稍微麻烦一些,需要做一些前期准备。

以下使用了自带的扩展插件postgres_fdw来创建跨库链接。

-- 开启扩展插件
CREATE EXTENSION postgres_fdw;

-- 创建外部服务器 映射远程数据库IP 端口 数据库名
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.0.23', port '5432', dbname 'db_fee_mhzls');

-- 创建本地登录远端的账号映射 options里面为远程数据库的账号密码
CREATE USER MAPPING FOR postgres
SERVER foreign_server
OPTIONS (user 'postgres', password '123456');

-- 创建模式 将外部服务器的所有模式对象导入到本地模式ft中
CREATE SCHEMA ft;
import foreign schema public from server foreign_server into ft;

--或者可以只导入指定表

import foreign schema public limit to(mytabA,mytabB) from server foreign_server into ft;

-- 使用本地模式ft实例 来执行sql
select * from ft.bk;

说明:

除了语句执行,也可以通过“其他”-》“外部服务器”菜单进行添加或修改连接属性。

创建模式语句执行后可以看到左边菜单出现了ft的节点。

你也可以通过navicat的选项菜单创建服务器。

效果:

上面示例执行查询bk表的sql语句后,可以看到获取到正常数据了。

可能出现的问题:

如果多个数据库都需要创建ft的情况 ,执行上述命令可能会出现报错

错误描述:function *** already exists with same argument types 

错误原因:同名函数已经存在,且要创建的函数和原函数参数相同。就是说已经创建了扩展,你无法再创建新的扩展。

解决方案:

在执行语句CREATE EXTENSION 做一些修改。

CREATE EXTENSION [ IF NOT EXISTS ] extension_name[ WITH ] [ SCHEMA schema_name ][ VERSION version ][ FROM old_version ]

1.用 DROP  EXTENSION postgres_fdw;删除原扩展插件

2.将扩展安装到指定模式下

CREATE EXTENSION postgres_fdw SCHEMA ftnew;

//扩展的实例postgres_fdw 被安装在名为ftnew的模式下,这样就不会与其他扩展实例冲突。