Sdílet prostřednictvím

Povolení a používání pg_partman na flexibilním serveru Azure Database for PostgreSQL

PLATÍ PRO: Flexibilní server Azure Database for PostgreSQL

V tomto článku se dozvíte, jak optimalizovat flexibilní server Azure Database for PostgreSQL pomocí rozšíření PostgreSQL Partition Manager (pg_partman).

Když se tabulky v databázi stanou velkými, je obtížné spravovat, jak často se vysadí, kolik místa zabírají a jak zajistit efektivitu indexů. Tento problém může zpomalit dotazy a ovlivnit výkon. Dělení velkých tabulek je řešením pro tyto situace.

V tomto článku vytvoříte pg_partman na flexibilním serveru Azure Database for PostgreSQL oddíly založené na rozsahu tabulek.

Požadavky

Pokud chcete rozšíření povolit pg_partman , postupujte takto:

  1. Na webu Azure Portal vyberte pg_partman v seznamu parametrů serveru pro azure.extensions.

    Snímek obrazovky znázorňující výběr rozšíření pg_partman v seznamu rozšíření Azure 

    CREATE EXTENSION pg_partman;

  2. Zahrňte související pg_partman_bgw rozšíření do shared_preload_librariessouboru . Nabízí naplánovanou funkci run_maintenance(). Postará se o sady oddílů, které jsou automatic_maintenance nastavené na ON hodnotu in part_config.

    Snímek obrazovky znázorňující výběr rozšíření pg_partman_bgw

    Pomocí parametrů serveru na webu Azure Portal můžete změnit následující možnosti konfigurace, které ovlivňují proces BGW (Background Writer):

    • pg_partman_bgw.dbname:Požadovaný. Tento parametr by měl obsahovat jednu nebo více databází, kde run_maintenance() je potřeba spustit. Pokud existuje více než jedna databáze, použijte seznam oddělený čárkami. Pokud není nic nastavené, pg_partman_bgw nespustí proceduru.

    • pg_partman_bgw.interval: Počet sekund mezi voláními procedury run_maintenance() . Výchozí hodnota je 3600 (1 hodina). Tuto hodnotu můžete aktualizovat na základě požadavků projektu.

    • pg_partman_bgw.role: Role, která run_maintenance() procedura běží jako. Výchozí hodnota je postgres. Je povolen pouze jeden název role.

    • pg_partman_bgw.analyze: Stejný účel jako argument argumentu p_analyzerun_maintenance(). Ve výchozím nastavení je nastavená na OFFhodnotu .

    • pg_partman_bgw.jobmon: Stejný účel jako argument argumentu p_jobmonrun_maintenance(). Ve výchozím nastavení je nastavená na ONhodnotu .

Poznámka:

  • Když funkce identity používá sekvence, data z nadřazené tabulky získávají nové hodnoty sekvence. Při přímém přidání dat do podřízené tabulky nevygeneruje nové sekvenční hodnoty.

  • Rozšíření pg_partman používá šablonu k řízení, zda je UNLOGGEDtabulka . To znamená, ALTER TABLE že příkaz nemůže změnit tento stav sady oddílů. Změnou stavu šablony ji můžete použít pro všechny budoucí oddíly. U existujících podřízených tabulek ale musíte příkaz použít ALTER TABLE ručně. Tato chyba ukazuje, proč.  

Nastavení oprávnění

U role superuživatele se nevyžaduje pg_partman. Jediným požadavkem je, že role, která spouští pg_partman funkce, má vlastnictví nad všemi sadami oddílů a schématy, ve kterých se vytvoří nové objekty.

Doporučujeme vytvořit samostatnou roli pro pg_partman schéma a předat jí vlastnictví schématu a všem objektům, na kterých pg_partman bude fungovat:

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. 

Vytvoření oddílů

Rozšíření pg_partman podporuje pouze oddíly typu rozsahu, nikoli oddíly založené na triggerech. Následující kód ukazuje, jak pg_partman pomáhá s dělením tabulky:

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); 

Snímek obrazovky s výstupem tabulky pro pg_partman

Pomocí create_parent funkce můžete nastavit požadovaný počet oddílů v tabulce oddílů:

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ředchozí příkaz rozdělí p_parent_table na menší části na p_control základě sloupce pomocí nativního dělení. (Druhá možnost je dělení na základě triggerů, ale pg_partman aktuálně ji nepodporuje.) Oddíly se vytvářejí v denním intervalu.

Příklad vytvoří 20 budoucích oddílů předem místo použití výchozí hodnoty 4. Určuje také , kde se p_start_partitionzmíníte o minulém datu, od kterého se mají oddíly začínat.

Funkce create_parent() naplní dvě tabulky: part_config a part_config_sub. K dispozici je funkce údržby. run_maintenance() Pro tento postup můžete naplánovat cron pravidelné spuštění úlohy. Tato funkce zkontroluje všechny nadřazené tabulky v part_config tabulce a vytvoří pro ně nové oddíly nebo spustí zásady uchovávání informací nastavené pro tabulky. Další informace o funkcích a tabulkách najdete v pg_partmandokumentaci k rozšíření Správce oddílů PostgreSQL na GitHubu.

Pokud chcete vytvořit nové oddíly při run_maintenance() každém spuštění na pozadí prostřednictvím pg_partman_bgw rozšíření, spusťte následující UPDATE příkaz:

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

Pokud je předmake stejný a váš run_maintenance() postup se spustí, pro tento den se nevytvořijí žádné nové oddíly. Pro další den, protože premake definuje z aktuálního dne, se vytvoří nový oddíl pro den se spuštěním vaší run_maintenance() funkce.

Pomocí následujících INSERT INTO příkazů vložte 100 000 řádků pro každý měsíc:

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') ; 

Spuštěním následujícího příkazu v PostgreSQL zobrazte vytvořené oddíly:

\d+ partman.partition_test;

Snímek obrazovky s výstupem tabulky s oddíly

Tady je výstup SELECT příkazu, který jste spustili:

Snímek obrazovky s výstupem plánu vysvětlení

Ruční spuštění postupu údržby

Příkaz můžete spustit partman.run_maintenance() ručně namísto použití pg_partman_bgw. Pomocí následujícího příkazu spusťte postup údržby ručně:

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

Upozorňující

Pokud před vytvořením oddílů vložíte data, data se přejdou do výchozího oddílu. Pokud výchozí oddíl obsahuje data, která patří do nového oddílu, který chcete vytvořit později, zobrazí se výchozí chyba porušení oddílu a postup nefunguje. Změňte hodnotu předmake doporučenou dříve a spusťte proceduru.

Naplánování postupu údržby

Spusťte postup údržby pomocí příkazu pg_cron:

  1. Nejprve povolte pg_cron na serveru. Na webu Azure Portal přidejte pg_cron parametry azure. extensions, shared_preload_librariesa cron.database_name serveru.

    Snímek obrazovky znázorňující přidání pg_cron do parametru serveru pro rozšíření Azure

    Snímek obrazovky znázorňující přidání pg_cron do parametru serveru pro sdílené knihovny předběžného načtení

    Snímek obrazovky znázorňující parametr serveru pro název databáze Cron

  2. Vyberte tlačítko Uložit a nechte nasazení dokončit.

    Po dokončení pg_cron nasazení se vytvoří automaticky. Pokud se ho pokusíte nainstalovat, zobrazí se následující zpráva:

    CREATE EXTENSION pg_cron;   

    ERROR: extension "pg_cron" already exists

  3. K naplánování cron úlohy použijte následující příkaz:

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

  4. Pokud chcete zobrazit všechny cron úlohy, použijte následující příkaz:

    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. Pokud chcete zkontrolovat historii spuštění úlohy, použijte následující příkaz:

    SELECT * FROM cron.job_run_details; 

    Výsledky zobrazují nulové záznamy, protože jste úlohu ještě nespustí.

  6. Pokud chcete zrušit plánování cron úlohy, použijte následující příkaz:

    SELECT cron.unschedule(1); 

Nejčastější dotazy

  • pg_partman_bgw Proč nespouštět postup údržby na základě zadaného intervalu?

    Zkontrolujte parametr pg_partman_bgw.dbname serveru a aktualizujte ho správným názvem databáze. Zkontrolujte také parametr pg_partman_bgw.role serveru a zadejte odpovídající roli. Měli byste také zajistit, abyste se připojili k serveru pomocí stejného uživatele a vytvořili rozšíření místo Postgres.

  • Při spuštění postupu údržby dochází k chybě pg_partman_bgw . Jaké můžou být důvody?

    Podívejte se na předchozí odpověď.

  • Návody nastavit oddíly tak, aby začínaly od předchozího dne?

    Funkce p_start_partition odkazuje na datum, ze kterého musí být oddíl vytvořen. Spusťte následující příkaz:

    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  
);

Související obsah