PostgreSQL CLI（psql）
速查
Shell：
psql -l # 列出所有 database
psql -U myuser mydb # 以指定 role 登入資料庫
createuser myuser # 建立使用者
createdb mydb -O myuser -E utf8 # 建立 database（指定 owner、編碼）
dropdb mydb # 刪除 database
進入 psql 互動介面後：
\? # psql 本身指令說明
\h # SQL 語法說明（可接指令名，如 \h CREATE TABLE）
\du # 列出 roles
\l # 列出 databases
\dt # 列出目前資料庫的 tables
\z # 列出 tables 與權限
\c dbname # 切換連線到另一個 database
\conninfo # 顯示目前連線資訊
\password user # 修改密碼
\q # 離開
登入 psql
psql [dbname]
psql -U [role] [dbname]
psql -U postgres # 以 postgres role 登入預設 postgres DB
psql -U myuser mydb
psql -U postgres -d mydb # -d 指定 database
常用參數
| 參數 | 說明 |
|---|---|
-l / --list | 列出所有 database（不必進互動介面） |
-d / --dbname | 指定 database |
-h / --host | 主機 |
-p / --port | port |
-U / --username | 使用者（role） |
-w / --no-password | 不提示密碼 |
-W / --password | 強制提示密碼 |
-V / --version | 版本 |
-e / --echo-queries | 把送出的查詢印出來 |
-? / --help | 說明 |
完整參數見 psql 官方文件。
進入介面後：SQL 與 Meta-Commands
登入後可直接 寫 SQL；可換行，遇到 ; 才執行。
CREATE DATABASE my_new_db;
確認後切換：
\l
\c my_new_db
Meta-Commands（\ 指令）
以 \ 開頭的指令由 psql 本身處理，不是送進 SQL engine。形式：\ + verb + 參數。
| 類別 | 指令 | 說明 |
|---|---|---|
| 連線 | \c [dbname [username] [host] [port]] | 切換／重連 |
| 連線 | \conninfo | 目前連線資訊 |
| 角色 | \du | 列出 roles |
| 資料庫 | \l | 列出 databases |
| 資料表 | \dt | 目前 DB 的 tables |
| 資料表 | \z [pattern] | tables 與權限 |
| 變數 | \set foo bar / \echo :foo | 設定／讀取變數 |
| 內建變數 | \echo :DBNAME 等 | DBNAME、ENCODING、HOST、PORT、USER |
| 說明 | \h create table | SQL 用法 |
| Shell | \! pwd | 執行本機 shell 指令 |
| 帳號 | \password [username] | 改密碼 |
| 離開 | \q | 離開 psql |
createdb / dropdb / createuser
不必進 psql，可在 Shell 直接管理 DB：
createdb demo
createdb minicare_test -O postgres -E utf8
dropdb demo
createuser myuser
createdb 常用參數：
| 參數 | 說明 |
|---|---|
-O / --owner | 指定 owner |
-E / --encoding | 編碼（常見 utf8 ） |
-h / -p / -U | host、port、username |
-w / -W | 不提示／強制提示密碼 |
-e | echo |
備份與還原（Dump）
# 備份：導出到檔案
pg_dump \
-U myuser \
-h example.rds.amazonaws.com \
-p 5432 mydb > mydb.dump
# 還原：灌入另一個 database
psql target_db < mydb.dump
更多細節可參考 Postgres Guide：Backup and Restore。
終止卡住的連線
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle';
依實際狀況加上條件（例如排除自己的 pid、或依 datname 過濾），避免誤殺還在用的連線。
錯誤處理
查看 log（Homebrew / macOS 常見路徑）
cat /usr/local/var/log/postgres.log
路徑會依安裝方式與版本而異（Apple Silicon 的 Homebrew 可能在 /opt/homebrew/...）。
異常關機後 PID 卡住
若 PostgreSQL 異常中斷，可能留下殘留的 postmaster.pid，導致無法再啟動。確認服務已停、沒有真正在跑的 postgres process 後，再處理該檔：
cd /usr/local/var/postgres # 或你的 data directory
rm postmaster.pid
務必先確認沒有存活的 postgres process，否則刪錯可能造成資料損壞。
安裝與初次設定
macOS（Homebrew）
brew install postgresql
brew services start postgresql
psql postgres
建立 cluster
initdb 會建立一個 database cluster（一組 databases 的集合）：
initdb /usr/local/var/postgres -E utf8
Ubuntu（僅 client）
sudo apt-get install postgresql-client
進入 DB 測指令
psql <database_name>
# 提示字元 =>
\q