對於資料庫，除了設計精巧，內建於各大語言、平台的 SQLite 外，一直以來我都是用 MariaDB / MySQL，原因很簡單，最多人用，從眾罷了，但最近注意到一些資料庫雲端服務廠商都是以 PostgreSQL 為基礎，並且各路大佬也都一致褒揚 PostgreSQL，且考量到 PostgreSQL 生態也足夠大，手邊有剛好要開個嚴肅的新專案，就毅然跳槽到 PostgreSQL 了（相對而言要轉到 Firebird 我就不敢），這邊是有 MariaDB / MySQL 經驗的用戶轉學到 PostgreSQL 幼幼班的小筆記。

以容器運行

由 Docker 維護的容器映像為 postgres，跑起來很簡單：

podman run \
--detach \
--env POSTGRES_PASSWORD=mysecretpassword \
--name pg \
--publish 5432:5432 \
docker.io/library/postgres:16-alpine

這裡我用的是 Podman，如果是您是用 Docker（注意，您可能是盜版軟體的受害者），把 podman 換成 docker、--publish 換成 --port 就可以了。

指令的涵義大多可以從參數的名稱猜出來，例如 --publish 5432:5432 用於映射 Docker host 與容器的 port，前面的是 Docker host port、後面的是容器 port，依照 PostgreSQL 的慣例開在 5432。

另外，上面的命令也指定了 PostgreSQL 的密碼為 myscrectpassword，並且我選用了以輕薄短小的 Alpine Linux 為基礎的 PostgreSQL 16 映像檔。

典型的連線字串為 postgresql://postgres:myscrectpassword@localhost:5432/postgres，其中 postgres 是預設的 PostgreSQL 管理員帳戶名稱，最後面的 postgres 則是預設的資料庫名稱。

作為開發環境，以容器運行 PostgreSQL 是滿好的選擇，對於生產環境，在還沒導入潮潮的 K8s 之前，只能一步一步裝囉！

安裝

在 PostgreSQL 的生態裡有很多第三方機構有做安裝包，通常會賦予一些額外的工具、套件、特性，有些是收費的，還有一些是以 PostgreSQL 為基礎，在上面搭建出自己的資料庫產品的，例如官網的 EDB，其他還有 bit.io、Timescale、Neon、Supabase、Citus、Greenplum 等等，可以參見〈PostgreSQL derived databases〉。

PostgreSQL 經歷多年發展有許多版本還在流通，要用哪個版本首先要看還有哪些版本有支援，也就是還會有更新，以 PostgreSQL 11 為例，它在 2023 年 11 月結束支援，而 PostgreSQL 16 則要到 2028 年才會結束支援。

另外也可以參考功能矩陣表來決定適合的版本。

在 Windows 安裝，沒什麼懸念，到官網下載 EDB 打包的安裝包，下一步下一步就好。

如果是 Linux，Linux 預帶套件庫中的版本可能不是最新的，以 Ubuntu 22.04 為例，預帶套件庫的 PostgreSQL 版本是 14，如果要裝 PostgreSQL 16，那就要添加 PostgreSQL 維護的 APT 庫，對 PostgreSQL 小白來說，理所當然選擇最新的版本。

一氣呵成的安裝步驟如下：

# Create the file repository configuration:
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
$ sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
$ sudo apt-get install postgresql-16

其中：

  • 第二步加金鑰系統會回報「Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).」，只是警示，可以無視，欲知詳情，參見《Handling “apt-key is deprecated. Manage keyring files in trusted.gpg.d instead” in Ubuntu Linux》。
  • 第三步系統會回報「W: http://apt.postgresql.org/pub/repos/apt/dists/jammy-pgdg/InRelease: Key is stored in legacy trusted.gpg keyring (/etc/apt/trusted.gpg), see the DEPRECATION section in apt-key(8) for details.」，原因同上，可以無視。
  • 如果想固定在 PostgreSQL 16.X，不想承受跳主版本更新的風險，請安裝 postgresql-16 套件，如果想讓跟著主板本更新，請安裝 postgresql 套件，這裡本人選用 postgresql-16。

裝完之後：

  • 多了一個系統帳號 postgres，這也是 PostgreSQL 預設的管理帳號。
  • 多了兩個配置資料夾 /etc/postgresql/ 和 /etc/postgresql-common。
  • 多了一個系統服務 postgresql.service 在 /etc/systemd/system/multi-user.target.wants/postgresql.service，它會監聽 5432 埠。
  • PostgreSQL 預設 locale 為 en_US.UTF-8、encoding 為 UTF8、text search 為 english。
  • 資料庫的真身在 /var/lib/postgresql/16/，知道就好不要亂動。

配置

主要的配置文件在 /etc/postgresql/16/main/postgresql.conf，裡面參數眾多，可以參考 PostgreSQL 手冊的《服務組態設定》來了解，或者參考網站 POSTGRESQLCO.NF，大部份保留原樣即可，下面是我個人異動的項目。

連線與認證（connectoins and authentication）

  • listen_addresses 從 'localhost' 改為機台自身的 IP 或 '*'，讓 PostgreSQL 接受外部連線。注意這是設定 PostgreSQL 要監聽的 IP 位址，不是客戶端的白名單位址。
  • unix_socket_directories = '/var/run/postgresql' ，此項不動，只是讓我自己知道 PostgreSQL 也有接受 Unix socket 連線。

錯誤回報與日誌記錄（reporting and logging）

  • log_timezone = 'Asia/Taipei'，此項不動，知道而已。

用戶端連線預設參數（client connection defaults）

  • datestyle = 'iso, mdy'，此項不動，知道而已。
  • timezone = 'Asia/Taipei'，此項不動，知道而已。

改完重啟服務：

$ sudo systemctl restart postgresql

進入 PostgreSQL

假設剛灌完什麼都沒配置，只能用 postgres 帳號進去：

$ sudo -u postgres psql

進去就會看到 PostgreSQL 的文字交互介面了。

帳號與角色

PostgreSQL 的身分認證配置文件在 /etc/postgres/16/main/pg_hba.conf，它的配置方式類似表格，最基本的像這樣：

# type  database  user      auth-method  [auth-options]
local   all       postgres  peer

逐項說明：

  • Type 為 local，表示走本機 Unix socket 連線。
  • Database 為 all，表示允許讀取所有資料庫。
  • User 為 postgres，表示 PostgreSQL 的帳號。
  • Auth-method 為 peer，表示走 OS 的帳號認證機制。

以上綜合起來表示「PostgreSQL 帳號 postgres 有權力走 Unix socket 連線至 PostgresSQL，並且以 Linux 帳號 postgres 做認證，且能操作任何資料庫。」

來看第二個例子：

# type  database  user auth-method  [auth-options]
local   all       all  peer

這表示「所有的 PostgreSQL 帳號都能透過 Unix socket 連線至 PostgreSQL，並且以當下登入之 Linux 帳號做認證，且能操作任何資料庫。」

這裡所謂「所有 PostgreSQL 帳號」可以透過 SQL 查詢取得：

SELECT rolname FROM pg_roles;

結果如下：

rolname          
---------------------------
 postgres
 pg_database_owner
 pg_read_all_data
 pg_write_all_data
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 pg_checkpoint
(13 rows)

可以看到除了安裝時添加的 postgres 之外，剩下的 pg_xxx 都是系統預建的角色，不作為登入帳號之用。

回到前面的問題「所有 PostgreSQL 帳號」究竟有哪些？目前只有 postgres 這位而已，所以我今天用 Linux 帳號 leon 是進不去 PostgreSQL 的，即便身份認證那邊設了 all。

看起來 PostgreSQL 推薦的實踐原則也是只以 postgres 帳號當 DBA，所以在本機端，還是乖乖的用 sudo -u postgres psql 進入 PostgreSQL 吧，其他應用再單獨開立權限較小的帳號給應用使用。

在遠端連線方面，看下面的例子，因為要走 TCP/IP，就有多了與 IP 相關的欄位：

# type  database  user      address       auth-method    [auth-options]
host    all       all       127.0.0.1/32  scram-sha-256
host    all       all       ::1/128       scram-sha-256

這兩個位址都是 localhost 的意思，也就是本機，第一行是 IPv4，第二行是 IPv6，這兩行的整體意思為「所有的 PostgreSQL 帳號只要它是位於 localhost，都能透過 TCP/IP 連線至 PostgreSQL，並且以 SHA-256 加密之密碼做認證，且能操作任何資料庫。」

前面提到在 Linux shell 層切換到 postgres 帳號進入 PostgreSQL：

$ sudo -u postgres psql

但一些本機的資料庫應用還是走密碼認證比較方便，要替 PostgreSQL 的 postgres 帳號添加密碼的話，可以如此這般：

ALTER USER postgres PASSWORD 'myPassword';

設好密碼之後，也要改一下 pg_hba.conf 讓它接受走 Unix socket + 密碼認證，在 pg_hba.conf 加入設置：

# type  database  user                    auth-method  [auth-options]
local   all       all                     scram-sha-256
local   all       postgres                peer
local   all       all                     peer

# type  database  user      address       auth-method    [auth-options]
host    all       all       127.0.0.1/32  scram-sha-256
host    all       all       ::1/128       scram-sha-256

上文中第二行就是讓 PostgreSQL 接受 Unix soket 且以密碼認證的設定。

改完記得重啟 PostgreSQL 服務。

$ sudo systemctl restart postgresql

依照上面的設定，在本機可以走 TCP 連線，以帳密認證，連線字串會長這樣：

postgresql://postgres:myPassword@localhost:5432/mydb

也可以走 Unix socket 連線，以帳密認證，連線字串長這樣：

postgresql://postgres:myPassword@localhost/mydb?host=/var/run/postgresql/

但要注意的是這裡的連線字串可能並非一體適用，每款應用的配置格式可能會有差異。

補充資料

-〈PG中的本地化排序规则〉。