PostgreSQL 跨库查询配置

步骤

• 开启扩展插件

-- 当前数据库为 sentry

-- 开启扩展插件
CREATE EXTENSION postgres_fdw;
-- SELECT * FROM pg_extension;
-- 或 \dx
-- SELECT * FROM pg_foreign_data_wrapper;
1
2
3
4
5
6
7

• 创建外部服务器 映射远程数据库 IP 端口 数据库名

-- 创建外部服务器 映射远程数据库 IP 端口 数据库名
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '127.0.0.1', port '5432', dbname 'sentry2');
-- SELECT * FROM pg_foreign_server;
-- 或 \des
-- ALTER SERVER foreign_server OPTIONS (SET port '1921', SET host 'localhost', SET dbname '数据库名称');
-- ALTER SERVER foreign_server OPTIONS (DROP host, DROP port);
-- DROP SERVER foreign_server CASCADE;
1
2
3
4
5
6
7
8
9

• 创建本地登录远端的账号映射

-- for 后面的 postgres 是本地登录执行的用户名 
-- option 里存储的是远程的用户密码
CREATE USER MAPPING
FOR PUBLIC
SERVER foreign_server
OPTIONS (user 'sentry', password '123456');

-- \deu+
1
2
3
4
5
6
7
8

• 创建模式 将外部服务器的所有模式对象导入到本地模式 ft 中

CREATE SCHEMA sentry2;
import foreign schema public from server foreign_server into sentry2;
1
2

• 测试效果

-- 授权
ALTER SCHEMA sentry2 TO grafana;
-- GRANT SELECT ON ALL TABLES IN SCHEMA sentry2 TO grafana;

SELECT id, name FROM sentry_project
UNION ALL
SELECT id, name FROM sentry2.sentry_project;
1
2
3
4
5
6
7

参考

• https://blog.csdn.net/quan278905570/article/details/121101928
• https://blog.csdn.net/sunny_day_day/article/details/118364315
• https://www.postgresql.org/docs/current/sql-createusermapping.html
• https://stackoverflow.com/questions/47574943/postgres-user-mapping-not-found-for-postgres
• https://www.jb51.cc/postgresql/195808.html
• https://www.yisu.com/zixun/221752.html
• http://blog.itpub.net/4606/viewspace-2835167/