PostgreSQL 的 custom format 備份可用 pg_restore 選擇物件與平行還原，通常比純 SQL 更靈活。client 工具的大版本最好與 server 相容，正式作業前查看當版文件。
動手前：不要用 PGPASSWORD 直接留在共用 script、history 或程序環境；優先使用權限受控的 .pgpass、service file 或秘密管理。還原前先確認目標資料庫與角色。
psql 連線與查看
登入指定資料庫psql
psql -h db.example.com -p 5432 -U appreader -d appdb密碼由安全機制提供，不放在命令列。
顯示連線資訊psql
\conninfo在 psql 內確認 server、database、user 與 TLS。
列出資料庫psql
\l只顯示有權查看的資訊。
列出 schemapsql
\dn避免把 public 當成唯一 schema。
列出資料表psql
\dt *.*範圍很大時可改成 app.*。
查看資料表結構psql
\d+ app.orders包含欄位、索引、storage 與描述。
切換展開顯示psql
\x auto寬資料列較容易讀。
顯示查詢時間psql
\timing onclient 時間包含傳輸與呈現，不等同 server execution time。
執行單一查詢psql
psql -h db.example.com -U appreader -d appdb -c 'SELECT version();'適合健康檢查與自動化。
活動、容量與執行計畫
查看連線活動PostgreSQL SQL
SELECT pid, usename, application_name, client_addr, state, query_start FROM pg_stat_activity ORDER BY query_start;查詢文字可能含敏感值，分享前遮蔽。
找長時間執行查詢PostgreSQL SQL
SELECT pid, now()-query_start AS age, state, left(query,200) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC;不要未查交易與業務影響就 cancel/terminate。
查看資料庫大小PostgreSQL SQL
SELECT pg_size_pretty(pg_database_size(current_database()));還要看磁碟、WAL 與暫存空間。
查看最大資料表PostgreSQL SQL
SELECT schemaname, relname, 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 LIMIT 20;total 包含索引與 TOAST。
查看執行計畫PostgreSQL SQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM app.orders WHERE order_no = 'A001';ANALYZE 會真的執行查詢；寫入語句或重查詢先在測試環境。
只看預估計畫PostgreSQL SQL
EXPLAIN (COSTS, VERBOSE) SELECT * FROM app.orders WHERE order_no = 'A001';不執行查詢，適合先看。
查看目前設定值PostgreSQL SQL
SHOW work_mem;注意 session、database、role、system 多層設定來源。
pg_dump 與 pg_restore
custom format 備份pg_dump
pg_dump -h db.example.com -U backupuser -d appdb -Fc -f appdb.dump輸出可用 pg_restore 選擇性還原。
directory format 平行備份pg_dump
pg_dump -h db.example.com -U backupuser -d appdb -Fd -j 4 -f appdb-dir平行工作數依 server、網路與磁碟能力調整。
純 SQL 備份pg_dump
pg_dump -h db.example.com -U backupuser -d appdb -Fp -f appdb.sql易閱讀，但大型庫還原彈性與速度較低。
只備份 schemapg_dump
pg_dump -h db.example.com -U backupuser -d appdb --schema-only -f appdb-schema.sql不含資料。
只備份指定 schemapg_dump
pg_dump -h db.example.com -U backupuser -d appdb -Fc -n app -f app-schema.dump跨 schema 相依需另外檢查。
列出 dump 內容pg_restore
pg_restore -l appdb.dump | less還原前確認包含哪些 schema、table、function 與 ACL。
還原到測試資料庫pg_restore
pg_restore -h test-db.example.com -U restoreuser -d appdb_restore --no-owner --exit-on-error appdb.dump目標庫需先建立，並確認 extension 與 role。
平行還原pg_restore
pg_restore -h test-db.example.com -U restoreuser -d appdb_restore -j 4 --no-owner appdb.dump錯誤處理與資源使用要搭配 log。
檢查 server readinesspg_isready
pg_isready -h db.example.com -p 5432 -d appdbaccepting connections 不等於查詢與 replication 都健康。
計算備份 SHA-256shell
sha256sum appdb.dump > appdb.dump.sha256複製到異地後再驗一次。
怎麼確認有做對
- 記錄 server/client 版本、dump format、參數、大小與 SHA-256。
- 在隔離環境做完整還原，檢查 extension、role、ACL、sequence 與應用查詢。
- 用 pg_isready 加一筆唯讀 SQL 作健康檢查。
常見錯誤
- 把 pg_isready 成功當成資料完整。
- 使用比 server 舊太多的 pg_dump。
- 還原時忽略 owner、role 與 extension。
- 在正式寫入查詢上直接 EXPLAIN ANALYZE。
版本與官方文件
參數會隨工具版本與作業系統實作改變。正式環境先用 --help、-h 或系統內建說明確認，再以當版官方文件為準。
