Skip to content
Database

postgres_fdw

这个扩展可以让 Postgres 查询远程 Postgres 服务器上的表和视图。

🌐 The extension enables Postgres to query tables and views on a remote Postgres server.

启用扩展 #

🌐 Enable the extension

  1. 在仪表板中转到数据库页面。
  2. 点击侧边栏的 扩展。
  3. 搜索“postgres_fdw”并启用该扩展。

创建到另一个数据库的连接 #

🌐 Create a connection to another database

1
Create a foreign server

定义远程数据库地址

1
create server "<foreign_server_name>"
2
    foreign data wrapper postgres_fdw
3
    options (
4
        host '<host>',
5
        port '<port>',
6
        dbname '<dbname>'
7
    );
2
Create a server mapping

为远程服务器设置用户凭据

1
create user mapping for "<dbname>"
2
server "<foreign_server_name>"
3
options (
4
    user '<db_user>',
5
    password '<password>'
6
);
3
Import tables

从外部数据库导入表

示例：从一个模式中导入所有表

1
import foreign schema "<foreign_schema>"
2
from server "<foreign_server>"
3
into "<host_schema>";

示例：导入特定表

1
import foreign schema "<foreign_schema>"
2
limit to (
3
    "<table_name1>",
4
    "<table_name2>"
5
)
6
from server "<foreign_server>"
7
into "<host_schema>";
4
Query foreign table
1
select * from "<foreign_table>"

配置执行选项 #

🌐 Configuring execution options

Fetch_size#

每次操作获取的最大行数。例如，如果 fetch_size 设置为 100，要获取 200 行就需要 2 次请求。

🌐 Maximum rows fetched per operation. For example, fetching 200 rows with fetch_size set to 100 requires 2 requests.

1
alter server "<foreign_server_name>"
2
options (fetch_size '10000');

Batch_size#

每个周期插入的最大行数。例如，如果将 batch_size 设置为 100，插入 200 行就需要 2 次请求。

🌐 Maximum rows inserted per cycle. For example, inserting 200 rows with batch_size set to 100 requires 2 requests.

1
alter server "<foreign_server_name>"
2
options (batch_size '1000');

扩展 #

🌐 Extensions

列出共享扩展。如果没有它们，涉及未列出扩展函数或操作符的查询可能会失败或遗漏引用。

🌐 Lists shared extensions. Without them, queries involving unlisted extension functions or operators may fail or omit references.

1
alter server "<foreign_server_name>"
2
options (extensions 'vector, postgis');

想要更多服务器选项的话，可以看看这个扩展的官方文档

🌐 For more server options, check the extension's official documentation

资源 #

🌐 Resources