postgresql 通过dblink实现 跨库查询

好的，以下是安装和使用 PostgreSQL dblink 扩展的详细步骤。

核心概念

dblink 是 PostgreSQL 的一个contrib 扩展，它允许一个数据库会话连接到其他 PostgreSQL 数据库（可以是同一集群，也可以是远程服务器）并执行查询。它通常不默认安装，需要手动启用。

安装步骤

安装过程分为两个层面：服务器层面（确保扩展文件存在）和数据库层面（在特定数据库中创建扩展）。

第 1 步：服务器层面 - 安装 PostgreSQL Contrib 包

dblink 扩展包含在 postgresql-contrib 软件包中。首先你需要确保这个包已经安装在你的 PostgreSQL 服务器上。

基于 RPM 的系统 (CentOS, RHEL, Fedora):

bash

# 首先检查你的PostgreSQL版本，例如 15
sudo yum install postgresql15-contrib
# 或者使用 dnf (新版本的Fedora/RHEL/CentOS)
sudo dnf install postgresql15-contrib

基于 Debian 的系统 (Ubuntu, Debian):

bash

# 同样，先检查版本，例如 15
sudo apt-get install postgresql-contrib-15
# 或者安装所有版本的contrib包
sudo apt-get install postgresql-contrib

通过源码编译安装：
如果你是用源码编译安装的 PostgreSQL，你需要单独编译 contrib 模块。

bash

# 进入 PostgreSQL 源码的 contrib 目录
cd /usr/local/src/postgresql-15.7/contrib
# 编译和安装所有 contrib 模块（包括 dblink）
make && sudo make install
# 或者只编译 dblink
cd /usr/local/src/postgresql-15.7/contrib/dblink
make && sudo make install

验证扩展文件是否存在：
安装完成后，扩展控制文件 (dblink.control) 和 SQL 文件应该出现在 PostgreSQL 的扩展目录中。

bash

# 找到你的扩展目录
find /usr -name "dblink.control" 2>/dev/null
# 通常路径类似于
# /usr/pgsql-15/share/extension/dblink.control
# /usr/share/postgresql/15/extension/dblink.control

第 2 步：数据库层面 - 在特定数据库中创建扩展

安装好软件包后，你需要在每个想要使用 dblink 功能的数据库中执行 CREATE EXTENSION 命令。通常需要超级用户权限。

1. 使用 psql 连接到目标数据库（例如，你想从哪个数据库发起跨库查询，就连接到哪个库）。

   bash

   psql -h localhost -U postgres -d your_database_name

2. 在数据库中创建 dblink 扩展：

   sql

   CREATE EXTENSION dblink;

   如果成功，你会看到提示 CREATE EXTENSION。

验证扩展是否创建成功：

sql

-- 查看当前数据库已安装的所有扩展
\dx

-- 或者使用SQL查询
SELECT * FROM pg_extension WHERE extname = 'dblink';

-- 查看dblink提供的所有函数
\df dblink.*

你应该能在结果列表中看到 dblink。

基本使用方法示例

假设你有两个数据库：

• source_db：你在这里执行 dblink 查询。

• target_db：你想要连接并查询的远程数据库。

示例 1：建立连接并执行查询

在 source_db 中执行以下 SQL：

sql

-- 最简单的用法：执行查询并获取所有结果
SELECT *
FROM dblink(
    -- 连接字符串：指定主机、数据库名、用户名、密码
    'dbname=target_db user=your_user password=your_password host=localhost',
    -- 要在目标数据库中执行的SQL查询
    'SELECT id, name, email FROM users'
) AS t(id INT, name VARCHAR(100), email VARCHAR(255)); -- 必须定义返回的列结构！

-- 如果你需要频繁连接到同一个外部数据库，可以创建一个命名连接（会在会话期间保持）
SELECT dblink_connect('my_conn', 'dbname=target_db user=postgres password=secret');

-- 使用命名连接进行查询
SELECT *
FROM dblink(
    'my_conn',
    'SELECT id, name FROM products'
) AS t(product_id INT, product_name TEXT);

-- 查询完成后关闭命名连接
SELECT dblink_disconnect('my_conn');

示例 2：将远程查询结果与本地表进行 JOIN

这是 dblink 最强大的功能之一。

sql

-- 将本地 orders 表与远程的 users 表进行关联
SELECT o.order_id, o.amount, u.remote_user_name
FROM local_orders o
JOIN dblink(
    'dbname=target_db user=postgres',
    'SELECT id, name AS remote_user_name FROM users'
) AS u(user_id INT, remote_user_name TEXT)
ON o.user_id = u.user_id;

示例 3：执行 UPDATE 或 INSERT（不返回结果集）

sql

-- 在远程数据库上执行UPDATE操作
SELECT dblink_exec(
    'dbname=target_db user=postgres',
    'UPDATE logs SET status = ''processed'' WHERE id = 123;'
);

重要注意事项和安全建议

1. 权限问题：CREATE EXTENSION 通常需要超级用户权限。如果你不是超级用户，可能需要管理员帮你安装。

2. 连接安全：

   • 明文密码：最上面的例子中，密码以明文形式写在 SQL 语句中，这非常不安全，不仅容易泄露，还会被记录在数据库日志和历史文件中。

   • 推荐做法：使用连接服务文件 (~/.pgpass) 或密码URI。

     • 方法A：使用 ~/.pgpass：在 PostgreSQL 服务器上的当前用户目录下创建 .pgpass 文件，存储密码。

       text

       # hostname:port:database:username:password
       localhost:5432:target_db:your_user:your_password

       然后，连接字符串可以省略密码：

       sql

       SELECT * FROM dblink('dbname=target_db user=your_user host=localhost', 'SELECT ...') AS t(...);

     • 方法B：使用外部表：对于长期需求，考虑使用更现代的 postgres_fdw（外部数据包装器）来代替 dblink，它提供了更优雅和安全的管理方式。

3. 性能：dblink 是通过网络调用另一个数据库，性能不如本地查询。对于大批量数据操作，可能不是最佳选择。

4. 事务控制：通过 dblink 执行的语句会在远程数据库的一个独立事务中执行，需要注意事务的一致性。

总结

1. 安装软件包：通过包管理器安装 postgresql-contrib 或从源码编译。

2. 创建扩展：在需要使用 dblink 的每个数据库中执行 CREATE EXTENSION dblink;。

3. 使用：在 SQL 查询中使用 dblink() 函数，提供目标数据库的连接信息和要执行的 SQL 语句。

4. 安全：切勿将明文密码写入 SQL 语句，使用 .pgpass 文件或考虑升级到 postgres_fdw。