pg数据库 dblink插件

场景

• 同一个 pg 实例下，创建了多个数据库，通过不同的 DB 来区分开发环境、测试环境。现想要将 A 库的数据表定时导入 B 库。
• 想在 A 库中查询 B 库的表。

数据复制解决方案

1. 方法一，使用 copy 命令，将数据从A库导出到 csv 文件，再用 copy 命令将 csv 文件导入B库。如果想要自动化每天同步，可以写一些shell脚本，通过cron定时执行（适合中等量数据）
2. 方法二，使用 pg_dump和pg_restore，类似 copy 命令的操作方式，这个命令适合大量数据，中间不用 csv 文件而是改用 sql 文件
3. 方法三，使用Foreign Data Wrapper（FDW）扩展，可以在 B 库里面创建一个postgres_fdw扩展，再创建外部服务器和用户映射，这样就能在 B 库访问本地表一样访问 A 库的表。好处是实时的访问，不需要做数据复制。这有点类似 Oracle 中的 dblink 功能。FDW 很强大，支持多种数据源、权限控制、将查询下推到远程数据源等等
4. 方法四，专属 pg 数据库的 dblink 扩展，pg 中的 dblink 功能是一个官方扩展，并不是原生支持的。好处是可以实现复杂的数据操作逻辑
5. 方法五，类似 datax 的方式，jdbc 连接读取再写入

这里用了 dblink 的方式，操作简单，具体如下

--1. 创建 pg dblink扩展
CREATE EXTENSION IF NOT EXISTS dblink;
SELECT * FROM pg_extension WHERE extname = 'dblink'; --如果已经有可以在 pg 扩展表查到

--2. 建立远程连接
SELECT dblink_connect('local_connect','hostaddr=127.0.0.1 port=5432 dbname=xxxx user=xxxx password=xxxx') as dev;
--解释：'local_connect'是我自定义的连接的名称,hostaddr=127.0.0.1表示是本机地址,port=5432表示使用5432端口,自行设置,dbname:表示要访问的数据库的名称,user，password分别表示用户名和密码,根据自己配置的用户名密码更改

--3. 跨库查询写入
insert into dim.test_table_o
(hos_code, hos_name, hos_project_code, hos_project_name, dop_code, type, rate)
SELECT hos_code, hos_name, hos_project_code, hos_project_name, dop_code, type, rate
FROM dblink('local_connect','SELECT hos_code, hos_name, hos_project_code, hos_project_name, dop_code, type, rate FROM dim.test_table where hos_code = ''xxx''')  --使用 dblink 函数从远程数据库获取数据。 local_connect是预先配置好的远程数据库连接名
--dblink 中查询语句被引号括起来，如果查询语句本身有引号，需要多写一个引号做转义
AS t(hos_code varchar, hos_name varchar, hos_project_code varchar, hos_project_name varchar, dop_code varchar, type varchar, rate numeric); --AS t()表示dblink返回的结果集定义了一个别名't'，并指定了每个列的数据类型

-- 4.关闭远程连接
SELECT dblink_disconnect('local_connect');

上面写法比较繁琐，要先创建 dblink连接才能使用，也可以写成下面这种方式，在一个语句中完成

CREATE EXTENSION IF NOT EXISTS dblink;

INSERT INTO insert into dim.test_table_o
    (hos_code, hos_name, hos_project_code, hos_project_name, dop_code, type, rate)
    SELECT hos_code, hos_name, hos_project_code, hos_project_name, dop_code, type, rate
    FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=xxxx user=xxxx password=xxxx',--直接写 dblink 方式，预先配置好的到远程数据库的连接名
                'SELECT hos_code, hos_name, hos_project_code, hos_project_name, dop_code, type, rate
                 FROM dim.test_table
                 WHERE hos_code = ''xxxx''')
    AS t(hos_code varchar, hos_name varchar, hos_project_code varchar, hos_project_name varchar, dop_code varchar, type varchar, rate numeric);

注意点

• 当dblink连接的是同一个PG实例下的不同数据库时，hostaddr就写 127.0.0.1，不用写实际的实例地址
• 当是不同实例时，需要写正确的实例。且这两个实例地址间网络是通的。