PostgreSQL 快速參考指南

環境配置#

安裝客戶端及服務端工具#

詳情參考 https://www.postgresql.org/download/ 鏈接中所指示的步驟

(僅) 安裝客戶端連接工具#

# Debian 操作系統
sudo apt update
sudo apt install postgresql-client

# MacOS 操作系統
brew install libpq

服務管理#

Linux 操作系統下使用 systemctl 進行服務管理，服務名稱為 postgresql，可以使用的命令例如：

# 啟動服務
systemctl status postgresql

# 重啟服務
systemctl restart postgresql

# 重載配置文件
systemctl reload postgresql

# 停止服務
systemctl stop postgresql

使用者管理#

-- 創建使用者
CREATE USER <username> WITH PASSWORD '<password>';

-- 賦予權限 (單個資料庫)
GRANT ALL PRIVILEGES ON DATABASE <database> TO <username>;

-- 賦予權限 (單個表)
ALTER TABLE <table_name> OWNER TO <username>;

-- 修改密碼
ALTER USER <username> WITH PASSWORD <new_password>;

PostgreSQL 內建語句#

-- 列出伺服器所有資料庫
\l
\list

-- 使用特定的資料庫
\c <db_name>
\connect <db_name>

-- 列出資料庫中所有表格
\dt

-- 列出資料庫中所有表格及視圖
\d
\dtvmsE

-- 獲取指定表格的結構
\d <table_name>

-- 導入 SQL 檔案
\i <filename>
\include <filename>

DDL (資料定義語言)#

-- 創建資料庫
CREATE DATABASE mydb;

-- 刪除資料庫
DROP DATABASE mydb;

psql (PostgreSQL 互動式終端)#

在命令行執行 SQL 語句#

psql -h <host> -d <db_name> -U <username> -d <database> -c "<sql>"

在命令行免密執行 SQL 語句#

方式一：環境變數#

export PGPASSWORD='<password>'

配置完成後正常執行命令即可，例如：

方式二：.pgpass 配置檔案#

在使用者根目錄 (例如 ~/) 創建 .pgpass 檔案，檔案內容如下所示 (替換尖括號內的內容為實際資訊)：

<host>:<port>:<db_name>:<username>:<password>

接下來需要設置 .pgpass 檔案的權限，使只有使用者自己可以讀取和寫入該檔案：

chmod 600 "$HOME/.pgpass"

之後嘗試免密執行命令：

psql -h <host> -d <db_name> -U <username> -c "<sql>"

此外，可以通過配置 PGPASSFILE 環境變數指定自定義路徑的 .pgpass:

export PGPASSFILE="/path/to/.pgpass"

方式三： pg_service.conf 配置檔案#

在使用者主目錄創建 .pg_service.conf 配置檔案，並填入以下內容 (替換尖括號內的內容為實際資訊)：

[myservice]
host=<host>
port=<port>
dbname=<db_name>
user=<username>
password=<password>

之後配置 PGSERVICE 環境變數為配置檔案中的指定名稱：

export PGSERVICE="myservice"

最後嘗試免密執行命令：

psql -c "<sql>"

注意，使用 .pg_service.conf 可以存儲多個服務連接配置，切換不同的配置只需要將 PGSERVICE 環境變數修改特定的名稱。

此外，可以通過配置 PGSERVICEFILE 環境變數指定自定義路徑的 .pg_service.conf:

export PGPASSFILE="/path/to/.pg_service.conf"