共用方式為

在 適用於 PostgreSQL 的 Azure 資料庫 上啟用和使用pg_partman - 彈性伺服器

適用於： 適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器

在本文中，您將瞭解如何使用 PostgreSQL 數據分割管理員 （pg_partman） 擴充功能，將 適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器優化。

當資料庫中的數據表變得很大時，很難管理其清理頻率、佔用多少空間，以及如何讓索引保持有效率。 這種困難會使查詢變慢並影響效能。 大型數據表的數據分割是這些情況的解決方案。

在本文中，您會使用 pg_partman 在 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器中建立數據表的範圍型數據分割。

必要條件

若要啟用擴充功能 pg_partman ，請遵循下列步驟：

  1. 在 Azure 入口網站 中，選取 pg_partman 的伺服器參數清單中。azure.extensions

    顯示 Azure 擴充功能清單中選取pg_partman延伸模組的螢幕快照。 

    CREATE EXTENSION pg_partman;

  2. 在中包含shared_preload_libraries相關的pg_partman_bgw延伸模組。 它提供排程的函式 run_maintenance()。 它會處理已在 automatic_maintenance 中part_config設定為 ON 的數據分割集。

    顯示選取pg_partman_bgw延伸模組的螢幕快照。

    您可以使用 Azure 入口網站 中的伺服器參數來變更下列會影響背景寫入器 （BGW） 程式的組態選項：

    • pg_partman_bgw.dbname：必填。 此參數應該包含一或多個需要執行的資料庫 run_maintenance() 。 如果有一個以上的資料庫，請使用逗號分隔清單。 如果未設定任何專案， pg_partman_bgw 則不會執行程式。

    • pg_partman_bgw.interval：呼叫 run_maintenance() 程式之間的秒數。 預設值為 3600 （1 小時）。 您可以根據專案的需求來更新此值。

    • pg_partman_bgw.role：程序執行時的角色 run_maintenance() 。 預設值為 postgres。 僅允許單一角色名稱。

    • pg_partman_bgw.analyze：與 自 p_analyze 變數相同的 run_maintenance()用途。 這預設為 OFF。

    • pg_partman_bgw.jobmon：與 自 p_jobmon 變數相同的 run_maintenance()用途。 這預設為 ON。

注意

  • 當識別功能使用序列時，來自父數據表的數據會取得新的時序值。 當數據直接加入子數據表時，它不會產生新的時序值。

  • 延伸 pg_partman 模組會使用樣本來控制資料表是否為 UNLOGGED。 這表示 ALTER TABLE 命令無法變更數據分割集的這個狀態。 藉由變更範本上的狀態，您可以將它套用至所有未來的分割區。 但對於現有的子數據表，您必須手動使用 ALTER TABLE 命令。 此錯誤會顯示原因。  

設定權限

不需要超級使用者角色 pg_partman。 唯一的需求是執行 pg_partman 函式的角色具有建立新物件的所有分割集和架構的擁有權。

建議您為 pg_partman 建立個別的角色，併為其授與架構的擁有權，以及將作的所有物件 pg_partman ：

CREATE ROLE partman_role WITH LOGIN; 
CREATE SCHEMA partman; 
GRANT ALL ON SCHEMA partman TO partman_role; 
GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman_role; 
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman_role; 
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman_role; 
GRANT ALL ON SCHEMA <partition_schema> TO partman_role; 
GRANT TEMPORARY ON DATABASE <databasename> to partman_role; --  This allows temporary table creation to move data. 

建立資料分割

延伸 pg_partman 模組僅支援範圍類型分割區，而不支援以觸發程式為基礎的分割區。 下列程式代碼示範如何 pg_partman 協助分割資料表：

CREATE SCHEMA partman; 
CREATE TABLE partman.partition_test 
(a_int INT, b_text TEXT,c_text TEXT,d_date TIMESTAMP DEFAULT now()) 
PARTITION BY RANGE(d_date); 
CREATE INDEX idx_partition_date ON partman.partition_test(d_date); 

pg_partman數據表輸出的螢幕快照。

藉由使用 函 create_parent 式，您可以在資料分割資料表上設定您想要的數據分割數目：

SELECT public.create_parent( 
p_parent_table := 'partman.partition_test', 
p_control := 'd_date', 
p_type := 'native', 
p_interval := 'daily', 
p_premake :=20, 
p_start_partition := (now() - interval '10 days')::date::text  
);

UPDATE public.part_config   
SET infinite_time_partitions = true,  
    retention = '1 hour',   
    retention_keep_table=true   
        WHERE parent_table = 'partman.partition_test';  

上述命令會使用原生數據分割，根據p_control數據行分割分成p_parent_table較小的部分。 （另一個選項是以觸發程式為基礎的數據分割，但 pg_partman 目前不支援它。分割區會以每日間隔建立。

此範例會事先建立 20 個未來的分割區，而不是使用的預設值 4。 它也會指定 p_start_partition，其中您提到數據分割應該從中開始的過去日期。

函 create_parent() 式會填入兩個資料表： part_config 和 part_config_sub。 有維護功能， run_maintenance()。 您可以排程 cron 此程式定期執行的作業。 此函式會檢查數據表中的所有 part_config 父數據表，併為其建立新的分割區，或執行數據表的設定保留原則。 若要深入瞭解 中的 pg_partman函式和數據表，請參閱 GitHub 上的 PostgreSQL 資料分割管理員擴充 功能檔。

若要在每次 run_maintenance() 透過 pg_partman_bgw 延伸模組在背景中執行 時建立新的分割區，請執行下列 UPDATE 語句：

UPDATE partman.part_config SET premake = premake+1 WHERE parent_table = 'partman.partition_test'; 

如果預先建立相同且您的 run_maintenance() 程式正在執行，則當天不會建立任何新的分割區。 第二天，因為 premake 定義自目前一天起，因此會使用函 run_maintenance() 式的執行來建立一天的新分割區。

使用下列 INSERT INTO 命令，在每個月插入 100,000 個資料列：

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(1,100000),GENERATE_SERIES(1, 100000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(1, 100000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-03-01',timestamp '2024-03-30', interval '1 day ') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(100000,200000),GENERATE_SERIES(100000,200000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(100000,200000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-04-01',timestamp '2024-04-30', interval '1 day') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(200000,300000),GENERATE_SERIES(200000,300000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(200000,300000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-05-01',timestamp '2024-05-30', interval '1 day') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(300000,400000),GENERATE_SERIES(300000,400000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(300000,400000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-06-01',timestamp '2024-06-30', interval '1 day') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(400000,500000),GENERATE_SERIES(400000,500000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(400000,500000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-07-01',timestamp '2024-07-30', interval '1 day') ; 

在 PostgreSQL 上執行下列命令，以查看已建立的數據分割：

\d+ partman.partition_test;

包含數據分割之數據表輸出的螢幕快照。

以下是您執行之語句的 SELECT 輸出：

說明計劃輸出的螢幕快照。

手動執行維護程式

您可以手動執行 partman.run_maintenance() 命令，而不是使用 pg_partman_bgw。 使用下列命令手動執行維護程式：

SELECT partman.run_maintenance(p_parent_table:='partman.partition_test');

警告

如果您在建立數據分割之前插入數據，則數據會移至預設數據分割。 如果預設分割區的數據屬於您想要稍後建立的新分割區，您會收到預設的數據分割違規錯誤，而且程式無法運作。 變更稍早建議的預先建立值，然後執行程式。

排程維護程式

使用 pg_cron執行維護程式：

  1. 首先，在您的伺服器上啟用 pg_cron 。 在 Azure 入口網站 中，將 新增pg_cron至 azure. extensions、 shared_preload_libraries和 cron.database_name 伺服器參數。

    顯示將pg_cron新增至 Azure 擴充功能伺服器參數的螢幕快照。

    顯示將pg_cron新增至共用預先載入連結庫之伺服器參數的螢幕快照。

    顯示cron資料庫名稱之伺服器參數的螢幕快照。

  2. 選取 [ 儲存] 按鈕，讓部署完成。

    部署完成之後， pg_cron 會自動建立 。 如果您嘗試安裝它，您會收到下列訊息：

    CREATE EXTENSION pg_cron;   

    ERROR: extension "pg_cron" already exists

  3. 若要排程 cron 作業，請使用下列命令：

    SELECT cron.schedule_in_database('sample_job','@hourly', $$SELECT partman.run_maintenance(p_parent_table:= 'partman.partition_test')$$,'postgres'); 

  4. 若要檢視所有 cron 作業，請使用下列命令：

    SELECT * FROM cron.job; 

    -[ RECORD 1 ]----------------------------------------------------------------------- 

jobid    | 1 
schedule | @hourly 
command  | SELECT partman.run_maintenance(p_parent_table:= 'partman.partition_test') 
nodename | /tmp 
nodeport | 5432 
database | postgres 
username | postgres 
active   | t 
jobname  | sample_job 

  5. 若要檢查作業的執行歷程記錄，請使用下列命令：

    SELECT * FROM cron.job_run_details; 

    結果會顯示零筆記錄，因為您尚未執行作業。

  6. 若要取消排程 cron 作業，請使用下列命令：

    SELECT cron.unschedule(1); 

常見問題集

  • pg_partman_bgw為什麼不是根據我提供的間隔來執行維護程式？

    請檢查伺服器參數 pg_partman_bgw.dbname ，並使用適當的資料庫名稱加以更新。 此外，請檢查伺服器參數 pg_partman_bgw.role 並提供適當的角色。 您也應該使用相同使用者來建立擴充功能，而不是 Postgres，以確保連線到伺服器。

  • 我在執行維護程式時 pg_partman_bgw 遇到錯誤。 原因是什麼？

    請參閱先前的答案。

  • 如何? 將分割區設定為從前一天開始？

    函 p_start_partition 式是指必須從中建立數據分割的日期。 執行以下命令：

    SELECT public.create_parent( 
p_parent_table := 'partman.partition_test', 
p_control := 'd_date', 
p_type := 'native', 
p_interval := 'daily', 
p_premake :=20, 
p_start_partition := (now() - interval '10 days')::date::text  
);

相關內容