Skip to main content

[PSQL] PostgreSQL CLI

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

Backup and Restore a PostgreSQL Database

終止卡住的 PSQL 連線#

SELECT pg_terminate_backend(pid)
FROM from pg_stat_activity
WHERE state = 'idle';

停止卡住的 Postgres Session 的 SQL 指令

錯誤處理#

檢視錯誤訊息 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

參考#

Last updated on