⚠️ 安全宣告

本技能需要以下許可權： - 讀取資料庫憑證（.env 或 ~/.pgpass） - 寫入備份檔案到本地磁碟 - 執行使用者提供的 SQL 語句

請確保： - 僅在受信任的環境中使用 - 備份檔案妥善儲存 - 使用最小許可權資料庫賬戶

name: postgresql-db description: description: | PostgreSQL 資料庫操作技能（需要資料庫憑證）。 支援連線管理、表結構查詢、CRUD 操作、備份恢復、pgvector 向量查詢。

需要環境變數：DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD 或使用~/.pgpass 檔案儲存憑證。

使用 psql 命令列工具執行操作，適合生產環境資料庫運維和開發查詢。

PostgreSQL 資料庫操作

何時使用

✅ 使用此技能當: - 查詢資料庫表結構、欄位、索引 - 執行 SELECT/INSERT/UPDATE/DELETE 操作 - 建立/修改/刪除表結構 - 資料庫備份與恢復 - pgvector 向量相似度搜索 - 檢視連線狀態、鎖、效能指標 - 匯出/匯入資料（CSV/SQL）

❌ 不使用此技能當: - 需要圖形介面操作 → 推薦 DBeaver/pgAdmin - 複雜 ORM 操作 → 使用 SQLAlchemy/Prisma - 資料庫叢集管理 → 使用 Patroni/pgBouncer

資料庫配置模板

連線資訊儲存在 TOOLS.md 或環境變數，不要硬編碼密碼。

### PostgreSQL 資料庫

| 專案 | 值 |
|------|-----|
| 主機 | your-db-host.example.com |
| 埠 | 5432 |
| 資料庫 | your_database |
| 使用者 | your_user |
| 密碼 | $DB_PASSWORD (環境變數) |

基礎命令

連線資料庫

# 方式 1: 命令列引數
PGPASSWORD='密碼' psql -h 主機 -p 埠 -U 使用者 -d 資料庫

# 方式 2: 環境變數（推薦）
export PGHOST=主機
export PGPORT=5432
export PGDATABASE=資料庫
export PGUSER=使用者
export PGPASSWORD=密碼
psql

# 方式 3: .pgpass 檔案（最安全）
echo "主機：埠：資料庫：使用者：密碼" >> ~/.pgpass
chmod 600 ~/.pgpass
psql -h 主機 -U 使用者 -d 資料庫

查詢表結構

# 列出所有表
\dt

# 列出所有表（含 schema）
\dt+

# 查看錶結構
\d tablename

# 查看錶詳細結構（含索引、約束）
\d+ tablename

# 檢視所有欄位型別
SELECT column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE table_name = 'tablename';

基礎 CRUD

# 查詢
SELECT * FROM 表名 WHERE 條件 LIMIT 10;

# 插入
INSERT INTO 表名 (欄位 1, 欄位 2) VALUES (值 1, 值 2);

# 更新
UPDATE 表名 SET 欄位=新值 WHERE 條件;

# 刪除
DELETE FROM 表名 WHERE 條件;

# 計數
SELECT COUNT(*) FROM 表名;

高階操作

匯出/匯入

# 匯出為 CSV
psql -h 主機 -U 使用者 -d 資料庫 -c "COPY (SELECT * FROM 表名) TO STDOUT WITH CSV HEADER" > 輸出.csv

# 匯出整個表
pg_dump -h 主機 -U 使用者 -t 表名 資料庫 > 表名.sql

# 匯入 SQL
psql -h 主機 -U 使用者 -d 資料庫 < 輸入.sql

# 匯入 CSV
\copy 表名 FROM '輸入.csv' WITH CSV HEADER;

備份/恢復

# 完整備份
pg_dump -h 主機 -U 使用者 資料庫 > 備份.sql

# 壓縮備份
pg_dump -h 主機 -U 使用者 資料庫 | gzip > 備份.sql.gz

# 恢復
psql -h 主機 -U 使用者 -d 資料庫 < 備份.sql

# 恢復壓縮
gunzip -c 備份.sql.gz | psql -h 主機 -U 使用者 -d 資料庫

pgvector 向量查詢

# 向量相似度搜索（餘弦距離）
SELECT *, embedding <-> '[0.1, 0.2, ...]'::vector AS distance
FROM your_table
ORDER BY distance
LIMIT 10;

# 餘弦相似度
SELECT *, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM your_table
WHERE 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) > 0.8
ORDER BY similarity DESC;

# 查看向量維度
SELECT vector_dims(embedding) FROM your_table LIMIT 1;
小蔥技能站7w4.net每天更新，海量AI技能等你發現。

效能監控

# 當前連線
SELECT pid, usename, client_addr, query, state, query_start 
FROM pg_stat_activity WHERE datname = current_database();

# 鎖資訊
SELECT * FROM pg_locks WHERE NOT granted;

# 慢查詢（需要 pg_stat_statements）
SELECT query, calls, total_exec_time, mean_exec_time 
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

# 表大小
SELECT relname AS table, 
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

安全實踐

密碼管理

• ✅ 推薦: 使用 ~/.pgpass 檔案儲存密碼
• ✅ 推薦: 使用環境變數 $PGPASSWORD
• ✅ 推薦: 使用 .env 檔案 + dotenv 載入
• ❌ 避免: 在指令碼中硬編碼密碼
• ❌ 避免: 在日誌中暴露密碼

許可權控制

# 檢視使用者許可權
\du

# 建立只讀使用者
CREATE USER reader WITH PASSWORD '密碼';
GRANT CONNECT ON DATABASE 資料庫 TO reader;
GRANT USAGE ON SCHEMA public TO reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;

# 撤銷許可權
REVOKE ALL ON TABLE 敏感表 FROM reader;

審計日誌

# 開啟查詢日誌（postgresql.conf）
log_statement = 'all'  # 或 'mod' / 'ddl'
log_duration = on
log_min_duration_statement = 1000  # 記錄>1s 的查詢

指令碼示例

批次查詢指令碼

#!/bin/bash
# scripts/query.sh
source .env
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "$1"

自動備份指令碼

#!/bin/bash
# scripts/backup.sh
source .env
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump -h $DB_HOST -U $DB_USER $DB_NAME | gzip > backups/${DB_NAME}_${DATE}.sql.gz
find backups/ -mtime +7 -delete  # 保留 7 天

故障排查

連線失敗

# 檢查網路
telnet 主機 5432

# 檢查 pg_hba.conf
# 確保允許你的 IP 連線

# 檢查防火牆
sudo ufw status | grep 5432

許可權錯誤

# 檢視當前使用者
SELECT current_user;

# 查看錶所有者
SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'public';

效能問題

# 分析表（更新統計資訊）
ANALYZE 表名;

# 重建索引
REINDEX TABLE 表名;

# 清理死元組
VACUUM 表名;
VACUUM FULL 表名;  # 鎖表，謹慎使用

參考資料

• PostgreSQL 官方文件
• pgvector 文件
• psql 命令速查

這個 Skill 內容豐富、文件詳盡，涵蓋了資料庫操作的常用場景和安全管理建議，配套指令碼也相當實用。但其備份功能實現不完整，使用 CSV 匯出的方式只能備份資料，無法儲存表結構和索引等關鍵資訊，因此無法用於真正的資料恢復，安全性存疑。整體質量中等偏上，文件優秀而指令碼功能有缺陷。