PostgreSQL 進階技巧掌握JSON轉型與許可權控管

– 11 分鐘閱讀

身為一個技術工作者，我經常需要處理各式各樣的資料，其中 JSON 格式資料在現今應用程式開發中日益普及。PostgreSQL 提供了強大的 JSON 支援，讓我能有效率地儲存和查詢這些半結構化資料。以下我將分享一些使用 PostgreSQL 處理 JSON 資料、CAST 運算元以及許可權管理的技巧。

JSON 資料處理技巧

PostgreSQL 提供了 JSON 和 JSONB 兩種型別，我通常偏好使用 JSONB，因為它在儲存和查詢效能上更勝一籌。以下是一個建立包含 JSONB 欄位的表格的例子：

CREATE TABLE customer_info (
    id SERIAL PRIMARY KEY,
    data JSONB
);

這個 customer_info 表格包含一個 id 欄位作為主鍵，以及一個 data 欄位用於儲存 JSON 資料。接著，我們可以插入一些客戶資訊：

INSERT INTO customer_info (data) VALUES
('{"name": "John Doe", "email": "john@example.com", "age": 30, "address": {"street": "123 Main St", "city": "New York", "state": "NY", "zip": "10001"}}'),
('{"name": "Jane Doe", "email": "jane@example.com", "age": 28, "address": {"street": "456 Main St", "city": "New York", "state": "NY", "zip": "10002"}}');

這段程式碼插入了兩筆記錄，每筆記錄都包含一個帶有巢狀結構的 JSON 物件。

查詢 JSON 資料

PostgreSQL 提供了許多 JSON 運算元和函式，讓我們能有效率地查詢 JSON 資料。例如，使用 ->> 運算元可以提取特定的欄位值：

SELECT data->>'name' AS name FROM customer_info;

這個查詢會從 customer_info 表格中提取 data 欄位裡的 name 屬性值，並將其命名為 name 顯示出來。->> 運算元會回傳 JSON 物件中指定鍵的值作為文字字串。

我們也可以使用 JSON 欄位值來篩選資料：

SELECT * FROM customer_info WHERE (data->>'age')::integer >= 30;

這個查詢會篩選 customer_info 表格中，data 欄位裡的 age 屬性值大於或等於 30 的所有記錄。 (data->>'age')::integer 將 age 的文字字串值轉換為整數型別，以便進行數值比較。

修改 JSON 資料

使用 jsonb_set 函式可以修改表格中的 JSON 資料：

UPDATE customer_info
SET data = jsonb_set(data, '{address,zip}', '"10003"')
WHERE data->>'email' = 'john@example.com';

這段程式碼會更新 email 為 ‘john@example.com’ 的記錄，將 data 欄位中，address 物件內的 zip 欄位值修改為 “10003”。jsonb_set 函式用於修改 JSONB 資料，第一個引數是目標 JSONB 物件，第二個引數是 JSON 路徑，第三個引數是新的值。

檢查欄位是否存在

使用 ? 運算元可以檢查 JSON 資料中是否存在特定的欄位：

SELECT * FROM customer_info WHERE data ? 'email';

這段程式碼會檢查 customer_info 表格中，data 欄位是否存在 email 鍵。? 運算元會回傳一個布林值，表示指定的鍵是否存在於 JSON 物件中。

建立索引

為了提高查詢效能，特別是針對經常查詢的欄位，我建議在 JSONB 欄位上建立 GIN 索引：

CREATE INDEX idx_customer_info_data ON customer_info USING gin (data);

這段程式碼會在 customer_info 表格的 data 欄位上建立一個 GIN 索引，名為 idx_customer_info_data。GIN 索引適用於 JSONB 資料，可以有效提升包含 JSONB 欄位查詢的效能。

  graph LR
    B[B]
    D[D]
    F[F]
    A[建立表格] --> B{插入 JSON 資料};
    B --> C[查詢 JSON 資料];
    C --> D{修改 JSON 資料};
    D --> E[檢查欄位是否存在];
    E --> F{建立索引};

圖表説明: 此流程圖展示了 PostgreSQL JSON 資料處理的典型步驟，從建立表格、插入資料、查詢資料、修改資料，到最後建立索引以提升效能。

CAST 運算元妙用

CAST 運算元可以將資料從一種型別轉換為另一種型別，這在資料操作和確保跨操作的相容性方面提供了很大的彈性。以下是一些使用 CAST 運算元的例子：

轉換資料型別

SELECT CAST(total_due AS INTEGER) FROM orders;

或者使用 :: 語法：

SELECT total_due::INTEGER FROM orders;

這兩段程式碼都將 orders 表格中 total_due 欄位的值從原來的資料型別（例如：numeric）轉換為整數型別。CAST() 函式和 :: 運算元都可以用於資料型別轉換。

在插入和更新時轉換型別

INSERT INTO prices (price) VALUES (CAST('10.50' AS NUMERIC(10, 2)));

使用 :: 語法：

INSERT INTO prices (price) VALUES ('10.50'::NUMERIC(10, 2));

這兩段程式碼都將字串值 ‘10.50’ 轉換為 NUMERIC(10, 2) 型別，然後插入到 prices 表格的 price 欄位中。

UPDATE prices SET price = CAST('15.75' AS NUMERIC(10, 2)) WHERE id = 1;

使用 :: 語法：

UPDATE prices SET price = '15.75'::NUMERIC(10, 2) WHERE id = 1;

這兩段程式碼都將字串值 ‘15.75’ 轉換為 NUMERIC(10, 2) 型別，然後更新 prices 表格中 id 為 1 的記錄的 price 欄位。

在 WHERE 子句中轉換型別

SELECT * FROM orders WHERE CAST(created_at AS DATE) = '2023-10-01';

使用 :: 語法：

SELECT * FROM orders WHERE created_at::DATE = '2023-10-01';

這兩段程式碼都將 orders 表格中 created_at 欄位的值（通常是 timestamp 型別）轉換為日期型別，然後與 ‘2023-10-01’ 進行比較，篩選出在指定日期建立的訂單記錄。

PostgreSQL 角色、成員屬性、驗證和授權管理

在 PostgreSQL 中，角色是管理資料庫存取許可權的關鍵。角色可以代表使用者、群組或應用程式。以下是如何建立角色、管理成員資格以及設定驗證和授權的説明：

管理角色和屬性

使用 CREATE ROLE 陳述式定義新角色：

CREATE ROLE sales;
CREATE ROLE marketing;

這兩段程式碼建立了兩個新的角色：sales 和 marketing。這些角色可以用於組織和管理資料函式庫使用者的許可權。

管理成員資格

使用 GRANT 陳述式將使用者新增至角色：

CREATE USER alice WITH PASSWORD 'alicepassword';
GRANT sales TO alice;

這段程式碼首先建立了一個名為 alice 的新使用者，並設定了密碼為 ‘alicepassword’。接著，將 sales 角色授予 alice 使用者，讓 alice 繼承 sales 角色的所有許可權。

設定屬性

使用 ALTER ROLE 陳述式定義屬性，例如登入、密碼和到期時間：

ALTER ROLE sales LOGIN PASSWORD 'securepassword' VALID UNTIL '2025-12-31';

這段程式碼修改了 sales 角色的屬性，允許該角色登入資料函式庫，設定密碼為 ‘securepassword’，並設定有效期限至 2025 年 12 月 31 日。

  graph LR
    B[B]
    D[D]
    A[建立角色] --> B{新增使用者};
    B --> C[授予許可權];
    C --> D{設定屬性};

圖表説明: 此流程圖展示了 PostgreSQL 角色管理的基本步驟，包含建立角色、新增使用者到角色、授予許可權以及設定角色屬性。

透過以上技巧，我們可以更有效率地管理和運用 PostgreSQL 資料函式庫，處理各種資料型別，並確保資料函式庫的安全性。

總結：這篇文章涵蓋了 PostgreSQL 的 JSON 處理、CAST 運算元以及許可權管理等重要主題，提供實用的程式碼範例和詳細的「內容解密」説明，希望能幫助讀者更深入地理解和應用 PostgreSQL 資料函式庫。

確保資料函式庫安全是任何系統管理員的首要任務。PostgreSQL 提供了多層次的防禦機制，有效保護資料安全。這篇文章將探討如何運用這些機制，開發更堅固的 PostgreSQL 資料函式庫環境。

精細的許可權控管：資料保護的根本

PostgreSQL 的許可權系統非常靈活，允許針對不同使用者設定精細的資料庫存取許可權。以下是如何設定許可權的步驟：

  1. 建立角色和使用者： 使用 CREATE ROLE 和 CREATE USER 指令建立角色和使用者，並設定密碼。
  2. 設定屬性： 使用 ALTER ROLE 指令設定角色的屬性，例如 LOGIN、SUPERUSER 和 CREATEDB 等。
  3. 設定許可權： 使用 GRANT 和 REVOKE 指令授予和復原角色在特定資料表、檢視、函式等物件上的許可權。
-- 建立一個名為 "data_analyst" 的角色，並授予其在 "sales" 資料表上的唯讀許可權
CREATE ROLE data_analyst WITH LOGIN PASSWORD 'secure_password';
GRANT SELECT ON TABLE sales TO data_analyst;

以上程式碼片段首先建立了一個名為 data_analyst 的角色，並設定了登入密碼。接著，它授予了該角色在 sales 資料表上的 SELECT 許可權，允許其讀取資料，但不能修改或刪除資料。

SSL 加密：確保資料傳輸安全

SSL 加密能確保使用者端和伺服器之間的資料傳輸安全，防止資料被竊取或竄改。以下是如何設定 SSL 的步驟：

  1. 產生 SSL 憑證： 使用 OpenSSL 等工具產生伺服器和使用者端的 SSL 憑證。
  2. 設定 PostgreSQL： 修改 postgresql.conf 檔案，設定 SSL 相關引數，例如 ssl = on、ssl_cert_file 和 ssl_key_file 等。
  3. 驗證安全連線： 使用 psql 等使用者端工具連線到資料函式庫，並確認連線已加密。
  graph LR
    Client -->|SSL加密連線| PostgreSQL Server

上圖展示了使用者端透過 SSL 加密連線到 PostgreSQL 伺服器的過程。SSL 確保了資料在傳輸過程中不被竊取或篡改。

OpenSSL 資料加密：靜態資料的守護者

OpenSSL 可以加密 PostgreSQL 的資料檔案，保護靜態資料安全。

  1. 安裝 OpenSSL:
sudo apt-get update
sudo apt-get install openssl
  1. 產生加密金鑰:
openssl rand -out mykey.bin 32

這個指令產生一個 32 位元組（256 位元）的隨機金鑰，並儲存到 mykey.bin 檔案中。

  1. 加密資料函式庫檔案:
openssl enc -aes-256-cbc -salt -in /var/lib/postgresql/data/mydb -out /var/lib/postgresql/data/mydb.enc -pass file:mykey.bin

這段程式碼使用 OpenSSL 的 enc 命令，以 AES-256-CBC 模式加密資料函式庫檔案 mydb。-salt 引數增加了安全性，-in 指定輸入檔案，-out 指定輸出檔案，-pass file:mykey.bin 指定加密金鑰檔案。

  1. 解密資料函式庫檔案:
openssl enc -d -aes-256-cbc -in /var/lib/postgresql/data/mydb.enc -out /var/lib/postgresql/data/mydb -pass file:mykey.bin

這段程式碼使用 OpenSSL 的 enc 命令和 -d 引數進行解密，其他引數與加密操作相同。

pgAudit 與觸發器：開發稽核日誌

pgAudit 擴充功能和觸發器可以記錄資料函式庫活動，方便追蹤和稽核。

  1. 安裝 pgAudit：
sudo apt-get install postgresql-16-pgaudit
  1. 啟用 pgAudit 擴充功能：
CREATE EXTENSION pgaudit;
  1. 設定 pgAudit： 在 postgresql.conf 檔案中設定 pgAudit：
pgaudit.log = 'ddl, write'

這段設定啟用了 pgAudit 並設定記錄 DDL 和寫入操作。pgaudit.log = 'ddl, write' 指定要記錄的操作型別。

  1. 重新啟動 PostgreSQL：
sudo systemctl restart postgresql

LDAP 驗證整合：簡化使用者管理

LDAP 驗證允許 PostgreSQL 使用集中式目錄服務進行使用者驗證。

  1. 安裝 LDAP 客戶端套件：
sudo apt-get install ldap-utils
  1. 設定 LDAP 客戶端： 在 /etc/ldap/ldap.conf 中設定：
BASE dc=example,dc=com
URI ldap://ldap.example.com

這段設定安裝了 LDAP 工具並組態了 LDAP 客戶端，使其能夠連線到 LDAP 伺服器。BASE 指定了 LDAP 搜尋的基礎 DN，URI 指定了 LDAP 伺服器的地址。請將 example.com 替換為您的網域名稱。

透過以上五個技巧，您可以大幅提升 PostgreSQL 資料函式庫的安全性，有效防禦各種潛在威脅。

  graph LR
    A[PostgreSQL] --> B(許可權控管)
    A --> C(SSL加密)
    A --> D(資料加密)
    A --> E(稽核日誌)
    A --> F(LDAP整合)

此圖表總結了我們討論的 PostgreSQL 安全策略，包含許可權控管、SSL 加密、資料加密、稽核日誌和 LDAP 整合，這些策略共同構成了多層次的資料函式庫安全防禦體系。

這篇文章提供了一些強化 PostgreSQL 資料函式庫安全的技巧。從許可權管理、SSL 加密到進階的日誌和驗證整合，這些方法可以幫助您建立一個更安全可靠的資料函式庫環境。

身為一位資料函式庫技術工作者，我經常面對各種資料函式庫還原的挑戰。從基本的完整還原到進階的 Schema 級還原，每個情境都有其獨特的解決方案。在這篇文章中，我將分享 PostgreSQL 資料函式庫還原的實用技巧，並著重介紹 Barman 這個工具如何簡化和最佳化還原流程。

完整還原與時間點還原 (PITR)

資料遺失是資料倉管理員的夢魘。完整還原和 PITR 是應對此類別災難的兩種主要方法。

完整還原

完整還原將資料函式庫還原到備份時的狀態。首先，使用 pg_dump 建立完整備份：

pg_dump -U your_user -W -F t adventureworks > adventureworks_backup.tar

將 your_user 替換為您的 PostgreSQL 使用者名稱。

接著，移除現有資料函式庫並重建：

dropdb -U your_user adventureworks
createdb -U your_user adventureworks

最後，使用 pg_restore 還原備份：

pg_restore -U your_user -d adventureworks -F t adventureworks_backup.tar

pg_dump 命令將資料函式庫 adventureworks 備份到一個 tar 檔案。 dropdb 和 createdb 命令則移除並重建同名資料函式庫。 pg_restore 命令將備份檔案還原到新建立的資料函式庫。

時間點還原 (PITR)

PITR 允許您將資料函式庫還原到過去的特定時間點。首先，在 postgresql.conf 中啟用持續歸檔：

wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

調整 archive_command 根據您的環境設定。

重新啟動伺服器：

sudo systemctl restart postgresql

使用 pg_basebackup 建立資料函式庫快照：

pg_basebackup -U your_user -D /path/to/backup -Ft -Xs -P

要還原到特定時間點，在資料目錄中建立 recovery.conf：

standby_mode = on
primary_conninfo = 'host=localhost port=5432 user=your_user'
restore_command = 'cp /path/to/archive/%f "%p"'
recovery_target_time = 'YYYY-MM-DD HH:MI:SS'  # 設定目標時間

以還原模式啟動 PostgreSQL：

pg_ctl start -D /path/to/data -w -t 600

設定 wal_level 為 replica 並啟用 archive_mode 允許 PostgreSQL 記錄所有變更。 pg_basebackup 建立一個基礎備份。 recovery.conf 中的 recovery_target_time 設定了還原的目標時間點。

使用 Barman 進行增量/差異還原

Barman 是一個強大的 PostgreSQL 備份和還原管理工具。

  graph LR
    B[B]
    E[E]
    delta[delta]
    recover[recover]
    A[列出可用備份 (barman list-backup)] --> B{標記完整備份 (barman mark-restore)};
    B --> C[建立臨時目錄];
    C --> D[還原完整備份 (barman recover)];
    D --> E{標記增量/差異備份 (barman mark-restore)};
    E --> F[套用增量/差異備份 (barman recover --delta)];

此流程圖展示了使用 Barman 進行增量/差異還原的步驟。首先列出可用備份，標記要還原的完整備份，然後建立一個臨時目錄。接著使用 Barman 還原完整備份，再標記並套用增量/差異備份。

首先，列出可用備份：

barman list-backup adventureworks

標記要還原的完整備份：

barman mark-restore adventureworks <full_backup_id>

建立臨時目錄：

mkdir /path/to/restore

還原完整備份：

barman recover --remote-ssh-command "ssh user@your_server" --target-directory /path/to/restore adventureworks <full_backup_id>

如果有增量/差異備份：

barman mark-restore adventureworks <incremental_backup_id>
barman recover --remote-ssh-command "ssh user@your_server" --target-directory /path/to/restore --delta adventureworks <incremental_backup_id>

barman list-backup 命令列出所有可用的備份。 barman mark-restore 命令標記要使用的備份。 barman recover 命令執行還原操作，--delta 選項用於套用增量/差異備份。

表空間、資料表和 Schema 級還原

Barman 也支援表空間、資料表和 Schema 級還原，提供更精細的還原控制。這些進階還原技巧可以參考 PostgreSQL 官方檔案和 Barman 檔案，其中包含更詳細的説明和示例。

Barman 監控最佳實務

我使用 Barman 監控 PostgreSQL 還原操作的最佳實務如下：

  • 使用 barman list-restore 檢視所有還原操作。
  • 使用 barman show-restore 監控特定還原操作的進度。
  • 使用 barman show-restore-logs 檢視詳細日誌。
  graph LR
    A[barman list-restore] --> B(所有還原操作概覽);
    C[barman show-restore] --> D(特定還原操作進度);
    E[barman show-restore-logs] --> F(詳細日誌訊息);

這個流程圖展示了使用 Barman 監控還原操作的三個關鍵命令。 barman list-restore 提供所有還原操作的概覽，barman show-restore 顯示特定還原操作的進度，而 barman show-restore-logs 則提供詳細的日誌訊息。

善用 Barman 和這些監控技巧，可以有效地管理 PostgreSQL 資料函式庫的備份和還原，確保資料安全和業務連續性。

透過這篇文章，我希望能夠幫助您更深入地理解 PostgreSQL 資料函式庫還原的藝術，並有效運用 Barman 這個強大的工具。記住，定期測試備份和還原流程至關重要，才能在真正需要的時候派上用場。

SELECT COUNT(*) FROM my_table;
SELECT * FROM my_table WHERE id = 123;

這段程式碼展示了兩種 SQL 查詢指令，用於驗證資料函式庫還原後的資料完整性。第一個指令 SELECT COUNT(*) FROM my_table; 計算 my_table 表中的總資料列數，這有助於快速確認還原後的資料量是否與預期相符。第二個指令 SELECT * FROM my_table WHERE id = 123; 則檢索特定 ID (此處為 123) 的資料，用於驗證資料內容的正確性。在實際操作中，我會選擇一些具有代表性的資料進行抽樣檢查，確保資料在還原過程中沒有損壞或遺失。

透過這些查詢指令，可以有效地監控 PostgreSQL 資料函式庫的還原操作，確保資料的完整性和一致性。結合 Barman 等備份和還原工具，更能提升資料倉管理的效率，並在發生意外狀況時迅速還原資料函式庫運作。我個人經驗是，建立一套自動化的監控和還原流程至關重要，這能大幅降低人工操作的風險，並確保業務的持續運作。

玄貓

玄貓

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。