[PSQL] PostgreSQL CLI
- postgreSQL @ official documentation
- PostgreSQL Tutorial
TL;DR
在 Terminal bash 中：
psql -l # 列出所有 database
psql -U aaronchen minicare_demo # 以 aaronchen 的 role 登入 minicare_demo 這個 database
createuser pjchender # createuser 建立使用者
createdb <database-name> -O pjchender -E utf8 # createdb 建立 database
dropdb aaronchen
在 PSQL IRB 中：
# help
\? # 顯示和 psql IRB 有關的指令說明
\h # 顯示和 sql 有關的指令說明
# 資料庫與資料表資訊
\du # 列出所有 roles
\l # 列出所有 databases
\z # 列出所有 tables
# 連線資訊
\c [db_name] # 連線到另一個資料庫
\conninfo # 連線資訊
\password [username] # 修改某 username 的密碼
psql 指令
psql 完整指令 @ postgresql documentation
登入 psql
psql [dbname]
psql -U [role] [dbname] # 以 postgres 的 role 登入 postgres 的 database
psql -U postgres # 使用 postgres 的 role 登入 的 postgres 這個 database
psql -U aaronchen minicare_demo # 以 aaronchen 的 role 登入 minicare_demo 這個 database
psql -U postgres -d minicare_demo # 以 postgres 的 role 登入 minicare_demo 這個 database
可以使用的參數
# options for psql
-l # --list，列出所有 database
# 登入相關
-d dbname # --dbname=dbname
-h host # --host=host
-p # --port=port
-U username # --username=username
-w # --no-password
-W # --password
# 其他
-V # --version，顯示 psql 的版本
-a # --echo-all
-e # --echo-queries
# 設定環境變數
-v assignment
--set=assignment
--variable=assignment
# 幫助
-?
--help[=topic]
登入 psql 介面後
SQL
登入 psql 後可以直接撰寫 SQL，可以換行輸入 SQL，唯有收到 ; 時才會開始執行。
psql [dbname]
CREATE DATABASE [new_dbname] # 建立 minicare_demo 這個 database
\c [new_dbname]
Meta-Commands（slash, backslash commands）
登入 psql 後，任何以 \ 開頭的指令都被視為是 meta-command，這些指令將會被 psql 本身所處理。meta-commands 的形式都是在 \ 後接 command verb，再接參數（arguments）：
# 資料庫相關
$ \c [ dbname [ username ] [ host ] [ port ] | conninfo ] # \connect [dbname] 與該 database 連線
$ \encoding [ encoding ]
$ \du # 列出所有 roles
$ \l # 列出所有 database
# 資料表相關
$ \z [pattern] # 列出所有 table
# 設定環境變數
$ \set foo bar # 將 foo 設為 bar
$ \echo :foo # 取得 foo 的變數值
# 其他
$ \h create table # 查詢用法
$ \! pwd # 取得當前資料夾名稱
$ \q # \quit，離開
# 帳號相關
$ \password [ username ]
取得變數名稱：
# 變數名稱 DBNAME, ENCODING, HOST, PORT, USER
$ \echo :DBNAME
createdb, dropdb 指令
# options for createdb
-p port # --port=port
-h host # --host=host
-U username # --username=username
-O owner # --owner=owner
-w # --no-password
-W # --password
-e # --echo
-E encoding # --encoding=encoding
可以直接在 Shell 中使用指令新增或刪除 database：
# create <db_name> -O <db_owner> -E utf8
createdb demo
createdb minicare_test -O postgres -E utf8
dropdb demo
其他
Dump Database
# 將資料庫的內容 dump 下來（備份）
pg_dump \
-U pjchender \
-h pjchender.us-west-2.rds.amazonaws.com \
-p 5432 pjchender-prod > pjchender-prod-dump
# 將 dump 下來的檔案灌入資料庫（還原）
psql test < pjchender-prod-dump
終止卡住的 PSQL 連線
SELECT pg_terminate_backend(pid)
FROM from pg_stat_activity
WHERE state = 'idle';
錯誤處理
檢視錯誤訊息 log
# Mac
$ cat /usr/local/var/log/postgres.log
終止 process
如果電腦操作到一半當機，可能會導致 PID 卡住，可以到該資料夾把檔案刪除即可：
cd /usr/local/var/postgres
rm postmaster.pid
安裝 Setup
MAC: 透過 homebrew 進行安裝
記得先安裝 homebrew 和 homebred-services：
postgreSQL @ Homebrew
brew install postgresql
brew services start postgresql
psql postgres
建立使用者和 db
Creating a Database @ psql
直接在 Terminal 使用關鍵字 createuser 建立使用者：
# createuser -- define a new PostgreSQL user account
$ createuser pjchender
使用關鍵字 createdb 建立 database
###
# createdb -- create a new PostgreSQL database
# -O: 指定 db 的使用者
# -E: 指定代碼
###
$ createdb <database-name> -O pjchender -E utf8
$ dropdb <database-name>
建立 database cluster
透過關鍵字 initdb 可以建立新的 PostgreSQL database cluster。
database cluster 是 databases 的集合。
initdb /usr/local/var/postgres -E utf8
接著就可以透過 Postico 連接到 PostgreSQL。
從 terminal 進入 db
透過 psql 指令可以進入 db 中測試 sql 指令（會出現 =>），透過 \q 可以離開
$ psql <database_name>
…=>...
\q
Ubuntu
# 安裝 psql cli
$ sudo apt-get install postgresql-client
參考
- Postgres Guide
- 備份與還原資料庫 @ Postgres Guide