pgsql 跨库查询

1. 引言

在数据库开发和管理过程中，经常需要在不同的数据库之间进行查询，以获取更全面的数据或进行数据处理。在 PostgreSQL（简称 pgsql）中，可以通过使用外部表和分布式查询等机制来实现跨库查询。本文将详细介绍如何在 pgsql 中进行跨库查询。

2. 外部表

外部表是一种特殊的表，它并不存储数据，而是引用了其他数据库中的数据。通过定义外部表，我们可以在 pgsql 中使用跨库查询。

2.1 连接外部数据库

在 pgsql 中，通过使用postgres_fdw扩展来连接外部数据库。首先，需要在 pgsql 中安装postgres_fdw扩展。可以使用以下命令来安装：

CREATE EXTENSION postgres_fdw;

然后，可以使用以下命令来创建外部服务器，并连接外部数据库：

CREATE SERVER remote_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (dbname 'remote_db', host 'remote_host', port 'remote_port');

其中，remote_db为外部数据库的名称，remote_host为外部数据库的主机地址，remote_port为外部数据库的端口号。

2.2 创建外部表

连接外部数据库后，可以使用以下命令来创建外部表：

CREATE FOREIGN TABLE remote_table (
  id INT,
  name TEXT
)
SERVER remote_server
OPTIONS (table_name 'remote_table');

其中，remote_table为外部数据库中的表名称，id和name为外部表的列。

2.3 跨库查询

创建外部表后，就可以在 pgsql 中进行跨库查询了。例如，可以使用以下命令来查询外部表中的数据：

SELECT * FROM remote_table;

3. 分布式查询

除了使用外部表，另一种实现跨库查询的方法是使用分布式查询。在 pgsql 中，可以通过使用dblink扩展来进行分布式查询。

3.1 安装 dblink 扩展

首先，需要在 pgsql 中安装dblink扩展。可以使用以下命令来安装：

CREATE EXTENSION dblink;

3.2 创建连接

安装dblink扩展后，可以使用以下命令来创建连接：

SELECT dblink_connect('dbname=remote_db host=remote_host port=remote_port user=remote_user password=remote_password');

其中，remote_db为外部数据库的名称，remote_host为外部数据库的主机地址，remote_port为外部数据库的端口号，remote_user为外部数据库的用户名，remote_password为外部数据库的密码。

3.3 进行查询

创建连接后，可以使用以下命令来进行查询：

SELECT *
FROM dblink('SELECT id, name FROM remote_table') AS t(id INT, name TEXT);

其中，remote_table为外部数据库中的表名称。

4. 示例代码

下面是一个完整的示例代码，演示如何在 pgsql 中进行跨库查询：

-- 连接外部数据库
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (dbname 'remote_db', host 'remote_host', port 'remote_port');

-- 创建外部表
CREATE FOREIGN TABLE remote_table (
  id INT,
  name TEXT
)
SERVER remote_server
OPTIONS (table_name 'remote_table');

-- 跨库查询
SELECT * FROM remote_table;

-- 安装 dblink 扩展
CREATE EXTENSION dblink;

-- 创建连接
SELECT dblink_connect('dbname=remote_db host=remote_host port=remote_port user=remote_user password=remote_password');

-- 分布式查询
SELECT *
FROM dblink('SELECT id, name FROM remote_table') AS t(id INT, name TEXT);