Real天下::码农频道

PostgreSQL的跨库操作，很早之前看了一下别人写的文章。

大家主要用两个内置的扩展dblink或postgres_fdw：

前者比较简单，直接执行SQL语句，返回查询结果；
后者比较规范，还能映射成本数据表，增删改查操作，跟本地表体验接近。另外，同款的扩展插件，还有支持ORACLE和MYSQL的。感觉可以玩出很多花样来。

以上就是昨天之前，我对PostgreSQL数据库的跨库操作所知道的全部。因为没有实际需求，故所知甚浅。庄子说：“吾生有涯而知无涯，以有涯随无涯，殆矣！”翻译成，大白话就是知识无穷，生命有限，不要瞎JB研究，要讲求实用主义。

不过，因为昨天的一个突发奇想，居然让我找到了postgres_fdw跨库表的应用场景。于是，我最终还是掌握了这个技能。

缘起

需要从Nocobase说起，大家都知道，我的Blog V9版之后，后台是用了Nocobase去搭建的。可能大家不知道Nocobase是什么万一，下面我简单介绍一下吧。

NocoBase 是一个极易扩展的开源无代码开发平台。不必投入几年时间、数百万资金研发，花几分钟时间部署 NocoBase，马上拥有一个私有、可控、极易扩展的无代码开发平台。

中文官网：https://www.nocobase.com/cn/

一句话，就是个基于Node.JS的开源的低代码应用开发平台。用它搭建一个博客CMS站点，只需要半天时间，不写一行代码就能完成。是的，我现在已经懒成这个样子了！

我的博客实践成功之后，有打算推广到公司的项目上。标准的CRUD用它来完成，超级爽！如果人手不够，还能抓提需求的人来自己搭功能模块。因为数据表逻辑建好之后，剩下基本就是界面上的拖拉拽的操作。即便不直接用在生产环境上，用来搭建项目原型，也是很友好的。

好，言归正传，为什么Nocobase会驱动我折腾跨库表呢？

Nocobase虽然好，但是这玩意的开源版本有些功能不太够意思，比如一个应用只能有一个主数据库。不能添加外部数据库，这样就大大限制了我们开发大中型应用的可能性。当然官方并不是没有这样的插件，只是要升级到商业版才可以支持，或者自己写插件进行扩展。

思来想去，不如试试用跨库表来解决？

尝试一下

1、安装postgres_fdw扩展

CREATE EXTENSION postgres_fdw;

2、创建外部服务器

create server external_server
    foreign data wrapper postgres_fdw options (host 'localhost'
    , port '5432'
    , dbname 'db_name'
    )
;

3、创建用户映射

create user mapping for postgres
    server servername_fdw options (user  '远程服务器用户'
    , password '远程服务器用户密码'
    );

4、创建外部表

create foreign table table_name (
        id bigint options (column_name 'id') not null,
        name integer options (column_name 'name') not null,
        value varchar(100) options (column_name 'value'),
        remark varchar(100) options (column_name 'remark')
 )
server external_server 
options (schema_name 'public', table_name '外部表名');

5、删除外部表及外部服务器

-- 删除外部表
drop foreign table table_name;
-- 删除用户映射
drop user mapping for username server external_server;
-- 删除外部服务器
drop server external_server;

6、在Nocobase上创建数据表同名的数据表以及字段

其他字段都能正常创建成功，但是如果让id字段作为主键（Primary Key）是没办法在外部表进行设置的。

解决方法：暂时将它创建为普通字段，完了之后手动改数据表dataSourcesFields对应记录的属性，将其强行改成主键。

然后，创建界面表格、表单界面。结果让人喜忧参半，表格能够把远程数据表的数据加载过来，删除和修改记录都没问题，唯独新增数据失败了！还是那个主键自增id的问题，远程数据库id不能传null过去。映射到本地的的数表，又不能将id处理成Auto-Increment，因为压根没有这个属性。

于是，我的研究陷入了两难的境地，postgres_fdw并不能完美解决我的问题。翻了几遍官方的文档，完全没有讲到这个问题，或许人家给你这个扩展，只让你Select，没让你Insert一般。

解决方法

不得以，今天架上梯子看看外面的世界，能不能找到解决办法。

嗯，还真有一篇文章讲到中问题，而且人家还很详细地给出了解决方案。这么好的一份作业，不抄过来有点太浪费这位小哥的心血了！正所谓“盗亦有道”，抄袭之前先给出原文链接：

https://www.linkedin.com/pulse/cross-database-integrations-postgresfdw-solution-benvenutti-qwmgf

有需要的小伙伴，可以打开看看别人的完整思路。我这里只摘要搬运了我所需要的其中一部分。

这位PostgreSQL专家给出的方案是这样的：

1、用利用dblink查询序列信息，在insert之前获取表的自增id；

-- 创建dblink扩展
create extension dblink;

-- 配置dblink外部库连接字符串
select dblink_connect('external_server', 'host=127.0.0.1 port=5432 user=dbuser password=dbpassword dbname=db_name'); 

-- 测试一下能不能获取远程数据表的自增id
SELECT next_id FROM dblink('external_server', 'select nextval(''table_name_id_seq'') as next_id') as T(next_id int);

2、将上面的脚本封装成一个函数（MSSQL叫存储过程）；

-- 封装一个获取远程表自增ID的函数
CREATE OR REPLACE FUNCTION public.fdw_nextval(seq text)
RETURNS integer LANGUAGE 'sql' AS $BODY$
SELECT seq_id FROM dblink('external_server', 'SELECT nextval(' || quote_literal($1) || ') AS seq_id') x (seq_id integer);
$BODY$;

3、创建远程表时候id字段设它的默认值为上面的函数调用。

-- 注意fdw_nextval的参数是对应远程表的序列名称
create foreign table table_name (
        id bigint options (column_name 'id') not null DEFAULT fdw_nextval('table_name_id_seq'),
        name integer options (column_name 'name') not null,
        value varchar(100) options (column_name 'value'),
        remark varchar(100) options (column_name 'remark')
 )
server external_server 
options (schema_name 'public', table_name '外部表名');

验证

确实有了以上函数的加持，外部表卡在自增ID无法Insert的问题，非常完美地解决了。

感谢LinkIn那位老外兄弟！

接下来，又可以继续我的Nocobase的探索了。

PostgreSQL的跨库操作（dblink + postgres_fdw）

缘起

尝试一下

解决方法

验证

ChenReal♥GZ