admin 发布时间:2020-09-10 分类:记事 阅读:11758次 2 条评论
因业务涉及到跨库查询,之前的方案为dblink进行查询,随着数据量变大,dblink的响应时间严重超时,因为dblink一次请求都会对远端数据库进行一次全表扫描,且所有数据都会传输回本地数据库内,导致性能降低并且严重浪费当前系统资源。本地系统每通过DBLINK链接远端系统一次,都会生成一个本地session,如本地session不退出或者手动释放,只有通过session超时才能自动释放,会浪费大量的系统资源。
在PGSQL上可使用postgres_fdw这个扩展来代替dblink。
-- 01 创建扩展参数
CREATE EXTENSION postgres_fdw;
-- 02 创建一个外部服务器,设置数据库的连接(删除 DROP SERVER foreign_server CASCADE)
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5432', dbname '外部的数据库名称'); -- 后续对此进行修改:ALTER SERVER foreign_server OPTIONS (SET port '1921', SET host 'localhost', SET dbname '数据库名称'); -- 如需删除某个配置项:ALTER SERVER foreign_server OPTIONS (DROP host, DROP port); -- 添加某个配置项 ALTER SERVER foreign_server OPTIONS (ADD host); -- 查询本数据库的连接端口 show port
如果有该报错信息:only connections to self instance are supported, please do not specify the host or hostaddr parameters。
则创建外部服务器的时候,不要填写port和host的信息,如:
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname '外部的数据库名称');
-- 03 定义的用户映射, 辨认将要在远程服务器上使用的角色
CREATE USER MAPPING FOR postgres SERVER foreign_server OPTIONS (user 'postgres', password '密码');
-- 04 创建schema,并创建所有的外部数据库表
CREATE SCHEMA ft; import foreign schema public from server foreign_server into ft; -- 单独增加外部表(如果后续有新的外部表可以用这个增加limit to/排除except) IMPORT FOREIGN SCHEMA public LIMIT TO (test_tablename) from server foreign_server into ft select * from ft.表名
如果区分权限不创建所有的表,只创建单独的外部表关联
-- drop FOREIGN TABLE f_order_info CREATE FOREIGN TABLE f_order_info ( id CHAR(24) NOT NULL, order_no VARCHAR(50), store_id VARCHAR(50), department_id VARCHAR(50), order_status VARCHAR(20), product_count int, product_total decimal(18,2) ) SERVER foreign_server OPTIONS (schema_name 'public', table_name '外部数据库表名'); select * from f_order_info
参考:https://help.aliyun.com/document_detail/142422.html
衍生问题:(赋予某子账户跨库查询权限)
-- 01 创建 子账户的映射
CREATE USER MAPPING FOR 子账户名称 SERVER foreign_server OPTIONS (user '子账户名称', password '子账户密码');
-- 02 赋予schema的权限给子账户
grant all on SCHEMA ft to 子账户名称;
-- 03 赋予schema的表权限给子账户
GRANT SELECT,INSERT,DELETE ON ALL TABLES IN SCHEMA ft TO 子账户名称; -- 赋予所有的表查询、插入、删除权限 GRANT SELECT ON TABLE ft.表名 TO 子账户名称; -- 赋予单独的表查询权限 REVOKE SELECT ON TABLE 表名 FROM 子账户名称; -- 撤销某表的查询权限
202304故障:
凌晨RDS数据库实例被阿里云强制升级了,早上各运营业务群反馈部分功能异常. 经过排查定位到是跨库查询失效了. 查看阿里云通知提示数据库被强制小版本升级了,怀疑是本次升级导致问题.
我们在主库中用的postgres_fdw插件创建foreign_server以实现数据库本实例的跨库查询.
我们连接的是本示例内的另外数据库,之前的连接有设置OPTIONS (host 'localhost', port '3002', dbname '数据库名').升级以后不能使用localhost和port了. 查询同一个实例只用设置数据库名dbname,其他的设置host和port设置的localhost/127.0.0.1和3002端口就不能使用了,使用了就会报错误提示 ERROR: only connections to self instance are supported, please do not specify the port parameter
本次处理删除掉OPTIONS 中的host和port就恢复正常.
下一篇:微信默认表情符号的代码对照表
发布于 2021-03-18 22:35:52 回复该评论
发布于 2021-02-13 01:00:27 回复该评论
发表评论:
◎欢迎您的参与讨论。