· PolloChang 工作筆記

📋 PostgreSQL 跨資料庫查詢設定 SOP
#

第一階段：環境初始化
#

在**發起查詢的資料庫（Local DB）**中，開啟 postgres_fdw 擴展功能。

安裝擴展：

1

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

確認安裝：使用 \dx 指令確認 postgres_fdw 已出現在清單中。

第二階段：建立連線通道 (SERVER)
#

定義遠端資料庫的連線資訊。

執行腳本：

1
2
3


CREATE SERVER remote_server_link
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '127.0.0.1', port '5432', dbname '目標資料庫名稱');

注意：如果是同一個 Instance，host 建議使用 127.0.0.1。

第三階段：身分對應 (USER MAPPING)
#

這是最容易出錯的一步。每一個需要存取遠端資料的本地帳號，都必須建立對應。

為超級用戶 (postgres) 建立：

1
2
3


CREATE USER MAPPING FOR postgres
SERVER remote_server_link
OPTIONS (user '遠端帳號', password '遠端密碼');

為普通開發帳號 (如 reportdba) 建立：

1
2
3


CREATE USER MAPPING FOR reportdba
SERVER remote_server_link
OPTIONS (user '遠端帳號', password '遠端密碼');

第四階段：匯入遠端資料表 (FOREIGN TABLE)
#

你有兩種方式可以把資料「拉」進來：

方法 A：精確建立單一資料表 (推薦，效能可控)

1
2
3
4
5
6
7


CREATE FOREIGN TABLE local_table_name (
    id int4,
    column_name varchar
    -- 欄位名稱與型態必須與遠端對應
)
SERVER remote_server_link
OPTIONS (schema_name 'public', table_name 'remote_table_name');

方法 B：批量匯入整個 Schema

1
2
3


IMPORT FOREIGN SCHEMA public 
FROM SERVER remote_server_link 
INTO local_schema_name;

第五階段：權限管理 (GRANT)
#

確保非超級用戶也能正常使用連線。

授權伺服器使用權：

1

GRANT USAGE ON FOREIGN SERVER remote_server_link TO reportdba;

授權資料表查詢權：

1

GRANT SELECT ON TABLE remote_table_name TO reportdba;

🛠 常見錯誤排除 (Troubleshooting)
#

錯誤訊息	可能原因	解決方法
`ERROR: permission denied for foreign table`	本地帳號沒有該表的 `SELECT` 權限	執行 `GRANT SELECT ON TABLE...`
`ERROR: user mapping not found`	當前登入的帳號沒有設定對應的遠端身分	執行 `CREATE USER MAPPING FOR...`
`ERROR: could not connect to server`	網路不通、遠端帳號密碼錯誤、或是 `pg_hba.conf` 擋住	檢查連線參數與遠端 DB 存取權限

💡 小撇步
#

效能優化：postgres_fdw 支援 Predicate Pushdown。當你在本地寫 WHERE 子句時，它會儘可能在遠端資料庫先過濾，減少傳輸量。
安全性：如果你不想在 USER MAPPING 中明文寫入密碼，可以考慮使用 PostgreSQL 的 passfile 或其他驗證機制，但這通常需要更複雜的伺服器端配置。

恭喜！現在你的跨資料庫查詢已經具備正式環境的穩定架構了。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63





```bash
postgres@manage-tools:~$ psql
psql (14.20 (Debian 14.20-1.pgdg13+1))
Type "help" for help.

postgres=# CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE EXTENSION
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+------------------------
 netbox    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres          +
           |          |          |             |             | postgres=CTc/postgres +
           |          |          |             |             | netbox=CTc/postgres   +
           |          |          |             |             | bot=c/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rmdb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres          +
           |          |          |             |             | postgres=CTc/postgres +
           |          |          |             |             | bot=c/postgres
 rptdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres          +
           |          |          |             |             | postgres=CTc/postgres +
           |          |          |             |             | reportdba=CTc/postgres+
           |          |          |             |             | bot=c/postgres        +
           |          |          |             |             | bot_import=c/postgres
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +
           |          |          |             |             | postgres=CTc/postgres
(6 rows)

postgres=# \c rptdb
You are now connected to database "rptdb" as user "postgres".
rptdb=# CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE EXTENSION
rptdb=# CREATE SERVER rmdb_link FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5432', dbname 'rmdb');
CREATE SERVER
rptdb=# CREATE USER MAPPING FOR CURRENT_USER SERVER rmdb_link OPTIONS (user 'bot', password 'fd831554@b0t');
CREATE USER MAPPING
rptdb=# CREATE FOREIGN TABLE rmdb_issue_categories (
    subject varchar,
    due_date date,
    tracker_id int4,
    category_id int4
)
SERVER rmdb_link OPTIONS (schema_name 'public', table_name 'issue_categories');
CREATE FOREIGN TABLE
rptdb=#
rptdb=# select count(*) from rmdb_issue_categories;
 count
-------
    15
(1 row)

rptdb=# GRANT USAGE ON FOREIGN SERVER rmdb_link to reportdba;
GRANT
rptdb=# GRANT SELECT ON TABLE public.rmdb_issue_categories TO reportdba;
GRANT
rptdb=# CREATE USER MAPPING FOR reportdba SERVER rmdb_link OPTIONS (user 'bot', password 'fd831554@b0t');
CREATE USER MAPPING
rptdb=#

📋 PostgreSQL 跨資料庫查詢設定 SOP #

第一階段：環境初始化 #

第二階段：建立連線通道 (SERVER) #

第三階段：身分對應 (USER MAPPING) #

第四階段：匯入遠端資料表 (FOREIGN TABLE) #

第五階段：權限管理 (GRANT) #

🛠 常見錯誤排除 (Troubleshooting) #

💡 小撇步 #