以下清單聚焦企業常用 PostgreSQL 運維與開發的「基本操作命令」，覆蓋連接、角色/權限、庫表、事務、索引、備份恢復與觀測。關鍵術語以 <span style="color:red">紅色</span> 標註，所有命令均附解釋，便於一線落地執行。🚀

一、連接與基礎元信息

psql -h 127.0.0.1 -p 5432 -U appuser -d appdb

解釋：連接到實例；-h/-p/-U/-d 分別為主機、端口、用户、數據庫。首次建議在 pg_hba.conf 配置 <span style="color:red">最小權限網段</span> 並啓用 <span style="color:red">SCRAM-SHA-256</span> 認證。

-- psql 元命令（在 psql 中執行）
\l         -- 列出數據庫
\c appdb   -- 切換數據庫
\dn        -- 列出 schema
\dt        -- 列出表
\du        -- 列出角色

解釋：\ 開頭為 psql 客户端內置命令，用於快速盤點實例資源清單。

二、角色與權限（RBAC）

CREATE ROLE app_rw LOGIN PASSWORD 'StrongP@ss_2025';
GRANT CONNECT ON DATABASE appdb TO app_rw;
GRANT USAGE ON SCHEMA public TO app_rw;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO app_rw;

解釋：創建可登錄角色並授予庫、schema、表權限；通過 ALTER DEFAULT PRIVILEGES 保障新建表繼承權限，符合 <span style="color:red">最小權限</span> 與 <span style="color:red">可持續授權</span> 原則。🔐

三、庫表與數據定義（DDL）

CREATE DATABASE appdb TEMPLATE template1;
CREATE SCHEMA biz;
CREATE TABLE biz.orders(
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  amount NUMERIC(12,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

解釋：新建庫/Schema/表；BIGSERIAL 自動遞增主鍵；TIMESTAMPTZ 記錄含時區時間，利於跨區審計（<span style="color:red">推薦</span>）。

四、數據操作（CRUD）

INSERT INTO biz.orders(user_id,amount) VALUES (1001,199.99) RETURNING id;
UPDATE biz.orders SET amount=209.99 WHERE id=1;
DELETE FROM biz.orders WHERE id=1;
SELECT * FROM biz.orders ORDER BY id DESC LIMIT 10;

解釋：典型寫、改、刪、查；RETURNING 直接回傳主鍵，減少往返。

五、事務與一致性

BEGIN;
UPDATE biz.orders SET amount=amount+10 WHERE id=2;
SAVEPOINT s1;
UPDATE biz.orders SET amount=amount+99999 WHERE id=2; -- 發現異常
ROLLBACK TO s1;  -- 局部回滾
COMMIT;

解釋：BEGIN/COMMIT 管理事務；SAVEPOINT 用於細粒度回滾，確保 <span style="color:red">ACID</span> 語義同時提升修復效率。🧩

六、索引與性能基線

CREATE INDEX idx_orders_user_id ON biz.orders(user_id);
CREATE INDEX idx_orders_created_at ON biz.orders(created_at DESC);
EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM biz.orders WHERE user_id=1001;

解釋：為高頻過濾字段建索引；DESC 有助於時間倒序分頁；EXPLAIN 觀察執行計劃與緩衝命中，定位 <span style="color:red">慢查詢</span> 根因。

七、備份與恢復（邏輯備份）

pg_dump -h 127.0.0.1 -U postgres -d appdb -F c -j 4 -f appdb_$(date +%F).dump
pg_restore -h 127.0.0.1 -U postgres -d newdb -j 4 --clean appdb_2025-10-03.dump

解釋：-F c 自定義格式支持並行恢復；-j 並行度提升速度；--clean 恢復前清理對象。生產落地建議結合 <span style="color:red">WAL 歸檔</span> 做時間點恢復（PITR）滿足 RPO/RTO。🛡️

八、日常維護與觀測

VACUUM (VERBOSE, ANALYZE);
SELECT schemaname,relname,n_live_tup,n_dead_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

解釋：VACUUM/ANALYZE 維持統計信息與存儲回收；查詢 pg_stat_* 系列視圖感知 <span style="color:red">膨脹與死元組</span>。

九、連接管理與會話排障

SELECT pid,usename,datname,client_addr,query,state
FROM pg_stat_activity ORDER BY state_change DESC LIMIT 20;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname='appdb' AND state='idle in transaction' AND now()-state_change> interval '10 min';

解釋：定位熱點連接與阻塞源；針對 <span style="color:red">長時間閒置事務</span> 做温和清理，避免 VACUUM 被阻塞。⚠️

「任務-命令-要點」對照表（vditor/Markdown）

最小閉環流程（Mermaid）

flowchart LR
A[建表/授權] --> B[寫入/查詢]
B --> C[EXPLAIN 優化]
C --> D[VACUUM/ANALYZE 維護]
D --> E[pg_dump 備份]
E --> F[pg_restore 恢復/演練]
F --> B

運營級小結

將日常操作標準化為：<span style="color:red">連接規範</span>→<span style="color:red">角色最小權限</span>→<span style="color:red">索引基線</span>→<span style="color:red">慢查詢觀測</span>→<span style="color:red">備份與演練</span>→<span style="color:red">會話治理</span>。這套閉環能穩定支撐中高併發業務迭代，降低變更與擴容風險。📈