Postgres16数据库集成外部库dblink和postgres_fdw扩展的方式

环境信息：

Postgres 16 版本

方式1 - dblink

有关dblink函数如何引用外部库的方式，由于过于简单，这里只贴出官方直达dblink文档链接，自行查看吧。
有关connstr的参数值组成格式说明。

方式2 - postgres_fdw扩展

postgres-fdw扩展官方直达链接文档。

由于都是SQL，接下来就只写相关的SQL，其他的内容去上述的官方文档找即可。

-- 1. 查找当前PG是否支持**postgres_fdw**扩展
SELECT *
FROM pg_available_extensions
WHERE name = 'postgres_fdw';


-- 2. 创建外部服务器
CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');
-- -- 查询外部服务器
select * from pg_foreign_server;
-- -- 删除外部服务器
DROP SERVER IF EXISTS foreign_server;

-- 3. 创建用户映射到远程服务器的角色
CREATE USER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user 'foreign_user', password 'password');

-- -- 删除用户映射
DROP USER MAPPING IF EXISTS FOR local_user SERVER foreign_server;

-- 4. 导入远程服务器的指定schema的表到指定模式中
IMPORT FOREIGN SCHEMA foreign_films
    FROM SERVER foreign_server INTO srv_films;

其他辅助语句

-- 查看当前连接的登录用户
select CURRENT_USER;

-- 删除指定远程服务的`srv_behavior_monitor_dev`下的用户`postgres `的用户映射
DROP USER MAPPING IF EXISTS FOR postgres SERVER srv_behavior_monitor_dev;

-- 查询远程服务的条目信息
SELECT srv.srvname       AS server_name,
       fdw.fdwname       AS wrapper,
       srv.srvoptions    AS options
FROM pg_foreign_server srv
JOIN pg_foreign_data_wrapper fdw
  ON srv.srvfdw = fdw.oid;

-- 查询FDW的信息
select * from  pg_foreign_data_wrapper;
-- 查询远程服务的信息
select * from  pg_foreign_server;
-- 查询用户映射的信息
select * from  pg_user_mapping;

-- 查看授权情况
SELECT
  grantee,
  table_schema,
  table_name,
  privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'srv_films'
ORDER BY table_name, grantee, privilege_type;

挂载远程数据库Schema授权指定schema及表的权限

-- 根据需要进行授权，授权FDW扩展谁可以进行管理
GRANT ALL PRIVILEGES
  ON FOREIGN DATA WRAPPER postgres_fdw
  TO adminuser;

-- 同上反之，撤销
REVOKE ALL PRIVILEGES
  ON FOREIGN DATA WRAPPER postgres_fdw
  FROM adminuser;

-- 根据需要进行授权，授权谁可以使用`create server`创建外部服务器
GRANT ALL PRIVILEGES
  ON FOREIGN SERVER srv_films
  TO adminuser;

-- 同上反之，撤销
REVOKE ALL PRIVILEGES
  ON FOREIGN SERVER srv_behavior_monitor_dev
  FROM adminuser;

-- 授权模式的所有权限
GRANT USAGE
  ON SCHEMA srv_films
  TO adminuser;

GRANT ALL
  ON ALL TABLES IN SCHEMA srv_films
  TO adminuser;

完整的授权语句：

GRANT ALL PRIVILEGES ON DATABASE mydb TO postgres;

-- 如果还需改模式所有者
ALTER SCHEMA myschema OWNER TO postgres;  

-- 先给 schema 本身的 USAGE（让它能访问这个模式）
GRANT USAGE
  ON SCHEMA myschema
  TO target_user;

-- 给模式下所有表（包括视图）的所有操作权限
GRANT ALL PRIVILEGES
  ON ALL TABLES IN SCHEMA myschema
  TO target_user;

-- 给模式下所有序列的所有操作权限
GRANT ALL PRIVILEGES
  ON ALL SEQUENCES IN SCHEMA myschema
  TO target_user;

-- 给模式下所有函数和过程的执行权限
GRANT ALL PRIVILEGES
  ON ALL FUNCTIONS IN SCHEMA myschema
  TO target_user;

----------------------------------------------------------------------------
-- 为后续在该模式中创建的新对象设置默认权限
-- 新表/视图、新序列、新函数/过程等都自动授予给 target_user

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
  GRANT ALL PRIVILEGES ON TABLES
  TO target_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
  GRANT ALL PRIVILEGES ON SEQUENCES
  TO target_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
  GRANT ALL PRIVILEGES ON FUNCTIONS
  TO target_user;

常见问题

org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping

当你使用dblink_exec执行insert语句时，由于mybatis.xml的参数是使用#{}进行绑定参数，找不到参数?位置的时候报的异常内容，换成${}即可。
虽然${}不安全，但是也没有其他办法了，不然就要把SQL用程序来拼接，然后使用${}进行占位并替换了。

关键参数

• dblink_connect
• postgres_fdw
• search_path
• Server Configuration
• Dollar-Quoted String Constants($$)