Поделиться через

Включение и использование pg_partman на гибком сервере База данных Azure для PostgreSQL

ОБЛАСТЬ ПРИМЕНЕНИЯ: База данных Azure для PostgreSQL — гибкий сервер

В этой статье вы узнаете, как оптимизировать База данных Azure для PostgreSQL гибкий сервер с помощью расширения PostgreSQL Partition Manager (pg_partman).

Когда таблицы в базе данных становятся большими, трудно управлять тем, как часто они вакуумируются, сколько пространства они занимают, и как обеспечить эффективность индексов. Это может замедлить запросы и повлиять на производительность. Секционирование больших таблиц — это решение для этих ситуаций.

В этой статье вы используете pg_partman для создания секций на основе диапазона таблиц на База данных Azure для PostgreSQL гибком сервере.

Необходимые компоненты

Чтобы включить pg_partman расширение, выполните следующие действия.

  1. В портал Azure выберите pg_partman в списке параметров сервера.azure.extensions

    Снимок экрана: выбор расширения pg_partman в списке расширений Azure. 

    CREATE EXTENSION pg_partman;

  2. Включите связанное pg_partman_bgw расширение в shared_preload_libraries. Она предлагает запланированную функцию run_maintenance(). Он заботится о наборах секций, для которых задано automatic_maintenanceONpart_configзначение .

    Снимок экрана: выбор расширения 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_parent_table на небольшие части на основе столбца с помощью собственного p_control секционирования. (Другой вариант — секционирование на основе триггеров, но pg_partman в настоящее время не поддерживает его.) Секции создаются по ежедневному интервалу.

В примере создается 20 будущих секций заранее, а не использование значения 4по умолчанию. Он также указывает p_start_partition, где вы упоминаете последнюю дату, с которой должны начинаться секции.

Функция create_parent() заполняет две таблицы: part_config и part_config_sub. Существует функция обслуживания. run_maintenance() Вы можете запланировать cron задание для этой процедуры периодически. Эта функция проверяет все родительские таблицы в part_config таблице и создает для них новые секции или запускает политику хранения наборов таблиц. Дополнительные сведения о функциях и таблицах см. в pg_partmanдокументации по расширению диспетчера секций PostgreSQL на сайте GitHub.

Чтобы создать новые секции при каждом run_maintenance() запуске в фоновом режиме pg_partman_bgw через расширение, выполните следующую UPDATE инструкцию:

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

Если премейк совпадает с run_maintenance() выполнением процедуры, новые секции не создаются в течение этого дня. На следующий день, так как предварительная версия определяется с текущего дня, создается новая секция в течение дня с выполнением функции 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 параметры сервера и cron.database_name к нейazure. extensionsshared_preload_libraries.

    Снимок экрана: добавление 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  
);

Связанный контент