PJCHENder 未整理筆記

[PSQL] PostgreSQL CLI

2018-07-14

@(Database)[PostgreSQL]

[PSQL] PostgreSQL CLI

keywords: psql, PostgreSQL, cli

TL;DR;

在 Terminal bash 中:

1
2
3
4
5
6
7
$ 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 中:

1
2
3
4
5
6
7
8
9
10
11
12
13
# help
\? # 顯示和 psql IRB 有關的指令說明
\h # 顯示和 sql 有關的指令說明

# 資料庫與資料表資訊
\du # 列出所有 roles
\l # 列出所有 databases
\z # 列出所有 tables

# 連線資訊
\c [db_name] # 連線到另一個資料庫
\conninfo # 連線資訊
\password [username] # 修改某 username 的密碼

psql 指令

psql 完整指令 @ postgresql documentation

登入 psql

1
2
3
4
5
6
$ 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

可以使用的參數

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 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,唯有收到 ; 時才會開始執行。

1
2
3
$ 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):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 資料庫相關
$ \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 ]

取得變數名稱:

1
2
# 變數名稱 DBNAME, ENCODING, HOST, PORT, USER
$ \echo :DBNAME

createdb, dropdb 指令

1
2
3
4
5
6
7
8
9
# 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:

1
2
3
4
$ createdb demo
$ createdb minicare_test -O postgres -E utf8

$ dropdb demo

其他

Dump Database

1
2
3
4
5
6
7
8
# 將資料庫的內容 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 連線

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

停止卡住的 Postgres Session 的 SQL 指令

錯誤處理

檢視錯誤訊息 log

1
2
# Mac
$ cat /usr/local/var/log/postgres.log

終止 process

如果電腦操作到一半當機,可能會導致 PID 卡住,可以到該資料夾把檔案刪除即可:

1
2
$ cd /usr/local/var/postgres
$ rm postmaster.pid

安裝 Setup

MAC: 透過 homebrew 進行安裝

記得先安裝 homebrew 和 homebred-services:

postgreSQL @ Homebrew

1
2
3
$ brew install postgresql
$ brew services start postgresql
$ psql postgres

建立使用者和 db

Creating a Database @ psql

直接在 Terminal 使用關鍵字 createuser 建立使用者:

1
2
# createuser -- define a new PostgreSQL user account
$ createuser pjchender

使用關鍵字 createdb 建立 database

1
2
3
4
5
6
7
8
###
# 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 的集合。

1
$ initdb /usr/local/var/postgres -E utf8

接著就可以透過 Postico 連接到 PostgreSQL。

從 terminal 進入 db

透過 psql 指令可以進入 db 中測試 sql 指令(會出現 =>),透過 \q 可以離開

1
2
3
4
5
$ psql <database_name>

…=>...

\q

Ubuntu

1
2
# 安裝 psql cli
$ sudo apt-get install postgresql-client

參考

掃描二維條碼,分享此文章