F.33. pg_archive#

F.33. pg_archive
F.33. pg_archive
Назад НаверхПредметный указатель F. Дополнительные поставляемые модули и расширенияНачало Далее

F.33. pg_archive #

F.33.1. Установка
F.33.2. Обновление
F.33.3. Использование
F.33.4. Функции, Процедуры и Таблицы
F.33.5. Ограничения в использовании

pg_archive - это расширение для автоматического архивирования исторических данных из секционированных таблиц путем преобразования секций в методы хранения, которые более подходят для огромных объемов холодных, только для чтения или в основном для чтения данных по сравнению с обычными таблицами PostgreSQL. В настоящее время единственным поддерживаемым архивным хранилищем является колонный метод доступа, поддерживаемый проектом Hydra, но в будущем могут быть добавлены и другие методы.

Код в общих чертах основан на отличном проекте pg_partman.

Автоматическое обслуживание для архивирования разделов на основе предоставленных пользователем политик реализовано с помощью фонового рабочего процесса, который периодически выполняет хранимую процедуру pg_archive_run_maintenance_proc(). Процедура проверяет разделенные таблицы, управляемые pg_archive, и преобразует разделы, содержащие данные старше временного интервала, указанного в политике для каждой таблицы. pg_archive_run_maintenance_proc() также может быть вызвана вручную пользователем, когда требуется незапланированное обслуживание.

F.33.1. Установка #

Требования:

F.33.1.1. Сборка из исходников #

В корневом каталоге исходного кода выполните: 

make install

F.33.1.2. Настройка #

Фоновый рабочий процесс должен быть загружен при запуске базы данных путем добавления библиотеки в shared_preload_libraries в postgresql.conf 

shared_preload_libraries = 'pg_archive_bgw'       # (change requires a restart)

Другие GUC, которые должны быть настроены перед использованием расширения, это interval, который представляет собой временной интервал в секундах между запусками обслуживания фоновым процессом, dbname, который является CSV строкой базы(баз) данных для обслуживания, и role, которая является ролью, используемой рабочим процессом: Вот пример: 

pg_archive_bgw.interval = 3600
pg_archive_bgw.role = 'alex'
pg_archive_bgw.dbname = 'mydb'

Роль должна иметь разрешения на выполнение pg_archive_run_maintenance_proc().

Установка расширения после запуска сервера с необходимыми настроенными параметрами GUC: 

CREATE SCHEMA archive;
CREATE EXTENSION pg_archive SCHEMA archive;

F.33.2. Обновление #

ALTER EXTENSION pg_archive UPDATE TO '<latest version>';

F.33.3. Использование #

Разделенные таблицы могут быть настроены для управления с помощью pg_archive с функцией pg_archive_manage().

Этот и последующие примеры в этом разделе предполагают, что pg_archive установлен в схеме archive, а таблицы находятся в схеме monitoring: 

SELECT archive.pg_archive_manage('monitoring.metrics', 'metric_timestamp', '1 year');
 pg_archive_manage
-------------------
 t
(1 row)

Вышеуказанный вызов добавляет запись в таблицу archive.pg_archive_config, которая позже будет использована pg_archive_run_maintenance_proc() для архивирования и, при необходимости, отсоединения разделов с данными старше 1 года в таблице metrics в схеме monitoring на основе значения metrics_timestamp.

Когда таблица становится управляемой pg_archive, её разделы будут проверены и, возможно, архивированы при следующем запланированном вызове pg_archive_run_maintenance_proc() фоновым рабочим процессом, который по умолчанию настроен на запуск каждые 3600 секунд. В качестве альтернативы, вы можете вызвать pg_archive_run_maintenance_proc() вручную: 

CALL archive.pg_archive_run_maintenance_proc();

F.33.3.1. Примеры #

Сначала создайте расширение. Оно требует, чтобы pg_columnar также был установлен, поэтому используйте CASCADE. 

CREATE EXTENSION pg_archive CASCADE;

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

shared_preload_libraries = 'pg_archive_bgw'
pg_archive_bgw.dbname = 'postgres'
pg_archive_bgw.role = 'postgres'
pg_archive_bgw.interval = 3600

Изменения:

  • shared_preload_libraries - фоновый рабочий процесс должен быть загружен при запуске сервера для регистрации

  • pg_archive_bgw.dbname - установлено в postgres как база данных по умолчанию

  • роль и интервал - это значения по умолчанию, но вы можете их изменить

    • запустите как postgres пользователь (в производственной среде создайте отдельную роль для фонового рабочего процесса, не суперпользователь)

    • запускать каждый час (3600 секунд)

Далее следуют дополнительные примеры. Все операции выполняются в postgres базе данных и public схеме. После каждого примера, данная таблица должна быть зарегистрирована в pg_archive для дальнейшего архивирования. Но если вы хотите, чтобы обслуживание выполнялось немедленно, выполните процедуру: 

CALL pg_archive_run_maintenance_proc();

Или, вы можете захотеть принудительно преобразовать раздел. Это можно сделать с помощью другой процедуры (заметьте: она принимает имя таблицы раздела - не родителя): 

CALL pg_archive_convert_partition_proc('public.partition_tbl_name');

Чтобы проверить, что таблицы архивированы, вы можете выполнить этот запрос: 

SELECT c.relname, am.amname
FROM pg_class c
JOIN pg_am am ON c.relam = am.oid
WHERE c.relname LIKE 'tbl_name%';

'tbl_name%' в запросе должен быть заменен на реальное имя таблицы. Предположим, что секционированные таблицы имеют имя как у родительской таблицы плюс суффикс в конце.

Не стесняйтесь изменять границы разделов или максимальный возраст (параметр для pg_archive_manage), чтобы проверить, что таблицы преобразуются.

Таблица для измерений температуры, разделенная по временной метке измерения. Используется в основном для аналитики, поэтому архивируйте её часто - установите максимальный возраст на 1 день. 

CREATE TABLE measurements(
    temp numeric NOT NULL,
    ts timestamp NOT NULL
) PARTITION BY RANGE(ts);
CREATE TABLE measurements_20240901
    PARTITION OF measurements
    FOR VALUES FROM ('2024-09-01') TO ('2024-09-02');
CREATE TABLE measurements_20240902
    PARTITION OF measurements
    FOR VALUES FROM ('2024-09-02') TO ('2024-09-03');
CREATE TABLE measurements_20240903
    PARTITION OF measurements
    FOR VALUES FROM ('2024-09-03') TO ('2024-09-04');
SELECT pg_archive_manage('public.measurements', 'ts', '1 day');

Храните бронирования клиентов в секционированной таблице. Бронирования могут быть изменены в любое время, поэтому секции разделены по месяцам. 

CREATE TABLE bookings(
    customer_id int NOT NULL,
    book_date date NOT NULL
) PARTITION BY RANGE(book_date);
CREATE TABLE bookings_20240901
    PARTITION OF bookings
    FOR VALUES FROM ('2024-09-01') TO ('2024-09-02');
CREATE TABLE bookings_20240902
    PARTITION OF bookings
    FOR VALUES FROM ('2024-09-02') TO ('2024-09-03');
CREATE TABLE bookings_20240903
    PARTITION OF bookings
    FOR VALUES FROM ('2024-09-03') TO ('2024-09-04');
SELECT pg_archive_manage('public.bookings', 'book_date', '1 year');

Используйте секционированную таблицу для хранения журналов микросервисов. Метки времени журналов предоставляются в виде UNIX-метки времени (в секундах). 

CREATE TABLE logs(
    message text NOT NULL,
    service int NOT NULL,
    ts numeric NOT NULL
) PARTITION BY RANGE(ts);
CREATE TABLE logs_202409
    PARTITION OF logs
    FOR VALUES FROM (1725148800) TO (1727740800);
CREATE TABLE logs_202410
    PARTITION OF logs
    FOR VALUES FROM (1727740800) TO (1730419200);
CREATE TABLE logs_202411
    PARTITION OF logs
    FOR VALUES FROM (1730419200) TO (1733011200);
SELECT pg_archive_manage('public.logs', 'ts', '1 month', p_epoch := 'seconds');

F.33.4. Функции, Процедуры и Таблицы #

Ниже приведено описание доступных функций, их аргументов и столбцов в таблице pg_archive_config:

F.33.4.1. pg_archive_manage() #

CREATE FUNCTION @extschema@.pg_archive_manage(
    p_parent_table text
    , p_control text
    , p_max_age text
    , p_type text DEFAULT 'range'
    , p_epoch text DEFAULT 'none'
    , p_automatic_maintenance boolean DEFAULT true
)

Функция регистрирует таблицу разделов для управления pg_archive.

  • p_parent_table

    • существующая секционированная таблица. ДОЛЖНА быть с указанием схемы, даже если находится в публичной схеме

  • p_control

    • name of a column that the partitioning will be based on. Must be of either a time- or integer-based type

  • p_max_age

    • либо временной интервал в формате типа PostgreSQL INTERVAL, либо целое значение, в зависимости от типа разбиения. Независимо от используемого типа разбиения, это значение должно быть указано как текст.

  • p_type

    • тип разбиения, используемый таблицей. В настоящее время поддерживаются только range и list. См. параметр p_max_age для особых условий, связанных с типом.

  • p_epoch

    • сообщает pg_archive, что управляющий столбец является целочисленным типом, но фактически представляет значение времени эпохи. Допустимые значения для этой опции: 'seconds', 'milliseconds', 'nanoseconds' и 'none'. Значение по умолчанию — 'none'. В дополнение к обычному индексу на управляющем столбце, обязательно создайте функциональный, основанный на времени индекс на управляющем столбце (to_timestamp(controlcolumn)), чтобы это работало эффективно.

  • p_automatic_maintenance

    • указывает, управляется ли обслуживание автоматически, когда вызывается pg_archive_run_maintenance_proc() без параметра таблицы или фоновым рабочим процессом. По умолчанию true. Когда установлено в false, pg_archive_run_maintenance_proc() все еще может быть вызвана для отдельного набора разделов, передавая его в качестве параметра функции.

F.33.4.2. pg_archive_convert_partition() #

CREATE PROCEDURE @extschema@.pg_archive_convert_partition_proc(
    p_partition text
    , p_access_method text DEFAULT 'columnar'
    , p_keep_indexes boolean DEFAULT true
    , p_reattach boolean DEFAULT false
)

Функция может быть полезна, когда отдельный раздел необходимо архивировать вручную. Обратите внимание, что вам нужно вызвать pg_archive_manage() для соответствующей родительской таблицы, прежде чем ее разделы могут быть переданы в pg_archive_convert_partition().

  • p_partition

    • спецификация раздела в формате schema.name.

  • p_access_method

    • метод доступа для использования архивированной таблицы. В настоящее время columnar (по умолчанию) является единственным поддерживаемым методом.

  • p_keep_indexes

    • логическое значение, указывающее, следует ли сохранять индексы в архивированной таблице

    • по умолчанию true

  • p_reattach

    • логическое значение, указывающее, следует ли присоединить архивированную таблицу обратно к родительской таблице

    • по умолчанию true

F.33.4.3. pg_archive_config #

CREATE TABLE @extschema@.pg_archive_config (
    parent_table text NOT NULL
    , control text NOT NULL
    , partition_type text NOT NULL
    , automatic_maintenance boolean NOT NULL DEFAULT true
    , max_age text NOT NULL
    , new_schema text
    , keep_indexes boolean NOT NULL DEFAULT true
    , reattach boolean NOT NULL DEFAULT true
    , access_method text NOT NULL DEFAULT 'columnar'
    , epoch text NOT NULL DEFAULT 'none'
    , maintenance_order int
    , keep_publication boolean NOT NULL DEFAULT false
    , maintenance_last_run timestamptz
    , CONSTRAINT pg_archive_config_parent_table_pkey PRIMARY KEY (parent_table)
);

  • parent_table

    • родительская таблица набора разделов для управления

  • control

    • столбец, используемый в качестве контроля для ограничений разделения. Должен быть столбцом на основе времени или целым числом.

  • partition_type

    • тип разбиения. Должен быть одним из типов, упомянутых выше в документации pg_archive_manage().

  • automatic_maintenance

    • флаг, указывающий, управляется ли обслуживание автоматически, когда pg_archive_run_maintenance_proc() вызывается без параметра таблицы или фоновым рабочим процессом.

    • По умолчанию true.

    • Когда установлено значение false, pg_archive_run_maintenance_proc() все еще может быть вызвана для отдельного раздела, передавая его в качестве параметра функции.

  • max_age

    • text type value, который определяет, насколько старыми могут быть данные в дочернем разделе, прежде чем раздел будет архивирован.

  • new_schema

    • схема для перемещения архивированных разделов в рамках процесса архивирования. NULL (по умолчанию) означает, что разделы не изменяют свою схему после архивирования.

  • keep_indexes

    • логическое значение, указывающее, следует ли удалять индексы для архивированных таблиц.

    • По умолчанию true. Установка в false означает, что pg_archive не будет пытаться воссоздать какие-либо индексы на архивированной таблице. Обратите внимание, что если таблица настроена на повторное присоединение архивированных разделов (см. reattach ниже), PostgreSQL может автоматически воссоздать индексы, унаследованные от родительской таблицы. См. документацию PostgreSQL для ALTER TABLE ATTACH PARTITION для получения дополнительной информации.

  • reattach

    • логическое значение, указывающее, должны ли разделы оставаться прикрепленными к родительской таблице после преобразования. Установка его в «false» означает, что разделы будут продолжать существовать как отдельные таблицы после архивации, т.е. будут отсоединены от родительской таблицы.

    • по умолчанию true

  • access_method

    • метод доступа для использования при преобразовании разделов во время архивирования

    • значение по умолчанию и единственное в настоящее время поддерживаемое значение - columnar

  • epoch

    • указывает, что таблица разделена по времени с использованием целочисленного значения эпохи вместо метки времени. См. описание соответствующего аргумента в pg_archive_manage() функции для получения дополнительной информации.

    • по умолчанию 'none'.

  • maintenance_order

    • целочисленное значение, определяющее порядок выполнения обслуживания наборов разделов. Наборы будут выполняться в порядке возрастания числового значения.

    • значение по умолчанию NULL. Все наборы разделов, установленные в NULL, будут выполняться после наборов разделов с определенным значением. Наборы разделов NULL выполняются в неопределенном порядке.

    • для наборов с подчастичными разделами дочерние таблицы по умолчанию наследуют порядок своих родительских таблиц. Дочерние родительские таблицы будут выполняться в логическом порядке, когда будет выполняться обслуживание их родительской таблицы, если оставить значение по умолчанию.

  • keep_publication

    • определяет, следует ли удалять архивированные разделы из любых публикаций, членами которых они могут быть.

    • по умолчанию false, что означает, что архивированные разделы удаляются из их публикаций.

  • maintenance_last_run

    • временная метка последнего успешного запуска обслуживания для этого набора разделов. Может быть полезно в качестве метрики мониторинга для обеспечения правильной работы обслуживания разделов.

F.33.5. Ограничения в использовании #

В рамках процедуры архивирования pg_archive должен скопировать данные раздела в новую таблицу, а затем обменять существующий раздел на новую архивированную версию (если только reattach не настроен на false для этой таблицы). Поскольку ALTER TABLE ... EXCHANGE PARTITION в настоящее время не поддерживается PostgreSQL, это сводится либо к DROP TABLE, либо к ALTER TABLE DETACH PARTITION, за которым следует ALTER TABLE ATTACH PARTITION.

Оба DROP TABLE для раздела и DETACH PARTITION берут ACCESS EXCLUSIVE блокировку на родительской таблице, что проблематично для загруженных таблиц. Хотя в PostgreSQL 14 была введена команда DETACH PARTITION CONCURRENTLY для решения этой конкретной проблемы, эта функциональность недоступна для расширений, потому что ALTER TABLE DETACH PARTITION CONCURRENTLY должна быть командой верхнего уровня и не может использоваться в хранимых процедурах. См. это обсуждение на pgsql-general для получения дополнительной информации.

В результате pg_archive в настоящее время использует DROP TABLE для отсоединения раздела как единственный жизнеспособный вариант. Мы осознаем проблемы, которые это может вызвать для загруженных, часто изменяющихся таблиц, и ищем возможные обходные пути.

Назад Наверх Далее
F.32. passwordcheck — проверка надежности пароля Начало F.34. pgAudit