跳至主要内容

基本操作

https://www.postgresguide.com/ https://docs.postgresql.tw/reference/sql-commands/drop-database

登入數據庫​

PostgreSQL 提供了在命令行底下運行的數據庫工具 psql，我們可以通過 psql 命令執行內部管理數據庫，也可以執行 SQL，做用戶管理增刪查改等操作。

# 指定用戶連接 PostgreSQL
psql -U postgres

# 指定數據庫連接 PostgreSQL
psql -d postgres

# 參數參考
psql -h 127.0.0.1 -p 5432 -U ken -d postgres

常用 psql 命令​

# 資料表相關 列出所有 table
\z [pattern]

# 查看所有用户 roles
\du

# 查看所有數據庫
\l

# 切換當前數據庫
\c {dbname}

# 查看當前庫下所有表
\dt

# 查看指定表
\d {tablename}

# 查看數據目錄
SHOW data_directory;

# 查詢用法
\h create table

# 取得當前資料夾名稱
\! pwd

# 退出 psql
\q

# 帳號相關
\password [ username ]

# 設定環境變數
\set foo bar # 將 foo 設為 bar
\echo :foo   # 取得 foo 的變數值

常用 SQL 命令​

# 創建數據庫
CREATE DATABASE test;

# 創建表(記得使用 \c 命令切換數據庫)
CREATE TABLE t1(id int,body varchar(100));

# 創建用戶
CREATE USER test WITH PASSWORD 'Test#1357';

# 修改密碼
ALTER USER test WITH PASSWORD 'Test#2468';

# 指定用戶添加指定角色
ALTER USER test createdb;

# 賦予指定帳戶指定數據庫所有權限
GRANT ALL PRIVILEGES ON DATABASE test TO test;

# 移除指定帳戶指定數據庫所有權限
REVOKE ALL PRIVILEGES ON DATABASE test TO test;

遠程訪問 PostgreSQL​

帳戶與數據目錄​

# 切換帳戶（圖形化介面安裝需要此步驟）
su postgres

# 進入PostgreSQL數據目錄（brew）
cd /opt/homebrew/var/postgresql@15/

# 進入PostgreSQL數據目錄（dmg）
cd /Library/PostgreSQL/15/data/

修改監聽地址​

# 修改 postgresql.conf
vi postgresql.conf 

# 修改監聽地址
listen_addresses= '*'

# 查看配置情況
cat postgresql.conf | grep "listen_addresses"

放開客戶端限制​

# 修改 pg_hba.conf
vi pg_hba.conf  

# 追加配置（md5指定加密方式，也可以選擇 scram-sha-256 等）
host  all  all 0.0.0.0/0 md5

# 查看配置情況
cat  pg_hba.conf | grep "host"

重啟 PostgreSQL 服務​

# 重啟服務（brew安裝）
brew services restart postgresql@15

# 重啟服務（dmg安裝，通過-D指定數據目錄）
pg_ctl restart -D /Library/PostgreSQL/15/data

備份還原數據庫​

https://axiomq.com/blog/backup-and-restore-a-postgresql-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 連線​

https://ravenonhill.blogspot.com/2017/04/postgres-hanging-query.html

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

建立使用者和 db​

直接在 Terminal 使用關鍵字 createuser 建立使用者；使用關鍵字 createdb 建立 database。

# createuser -- define a new PostgreSQL user account
createuser apollo
###
# createdb -- create a new PostgreSQL database
# -O: 指定 db 的使用者
# -E: 指定編碼
###

createdb <database-name> -O apollo -E utf8
dropdb <database-name>

建立 database cluster​

透過關鍵字 initdb 可以建立新的 PostgreSQL database cluster。 database cluster 是 databases 的集合。 接著就可以透過 Postico 連接到 PostgreSQL。

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