Расширение pgpro_ilm решает задачу, аналогичную управлению жизненным циклом информации (information lifecycle management, ILM) в Oracle, которое анализирует частоту использования данных в базе данных и выполняет определённые действия с редко используемыми таблицами, например переносит их в более дешёвое и медленное хранилище.
pgpro_ilm позволяет задавать правила для перемещения таблиц. Правило может быть типа NO_MODIFICATION или NO_ACCESS. Правило NO_MODIFICATION определяет действие, которое выполняется для таблицы, если она не изменялась (с помощью команд UPDATE, INSERT, DELETE или TRUNCATE), но могла читаться (с помощью команды SELECT) в течение определённого периода времени. Правило NO_ACCESS определяет действие, которое выполняется для таблицы, если ни одна из вышеуказанных команд не выполнялась для этой таблицы в течение определённого периода времени. pgpro_ilm предоставляет функции для управления правилами и их применения.
pgpro_ilm получает статистику времени последнего доступа к таблицам в разрезе пользователей из функций и представлений pgpro_usage. Для целей ILM сервисные операции с таблицами, такие как регулярное резервное копирование, архивация и т. д., не должны учитываться. Эти операции выполняются определёнными пользователями, например postgres, и такие пользователи должны быть исключены при проверке применимости правил pgpro_ilm. pgpro_ilm предоставляет функции для управления списком исключаемых пользователей.
Для текущей версии pgpro_ilm существуют следующие ограничения:
Поддерживается только перемещение отношения в другое табличное пространство.
Поддерживаются только обычные таблицы и секции секционированных таблиц.
Правила для индексов не поддерживаются, и индексы перемещаются вместе с соответствующими таблицами.
Расширение pgpro_ilm включено в состав Postgres Pro Enterprise как стандартное расширение. Чтобы задействовать pgpro_ilm, создайте расширение с помощью следующего запроса:
CREATE EXTENSION pgpro_ilm;
pgpro_ilm будет работать при условии, что установлено расширение pgpro_usage, как объяснено в Подразделе F.55.1.
Для настройки перемещения таблиц в более дешёвое хранилище pgpro_ilm предоставляет SQL-интерфейс, состоящий из нескольких функций.
Следующие функции управляют правилами pgpro_ilm:
add_rule( iv_object_name text, iv_rule_type text, iv_period interval, iv_action text, iv_parameter text ) returns void
Добавляет новое правило.
Аргументы:
iv_object_name — имя отношения. Может быть полным или неполным. Если имя неполное, при сохранении правила во внутреннюю таблицу схема будет определена автоматически с учётом пути поиска (как описано в Подразделе 5.10.4).
iv_rule_type — тип правила: NO_ACCESS или NO_MODIFICATION.
iv_period — период, по истечении которого правило начинает действовать.
iv_action — действие, которое требуется выполнить. Возможное значение: ALTER_TS — переместить отношение в другое табличное пространство.
iv_parameter — параметр действия. Возможное значение — табличное пространство, в которое нужно переместить таблицу.
remove_rule( iv_object_name text, iv_rule_type text, iv_period interval, iv_action text ) returns void
Удаляет правило или правила.
Аргументы:
iv_object_name — имя отношения. Может быть полным или неполным. Если имя неполное, при поиске правила, которое нужно удалить, схема будет определена автоматически с учётом пути поиска (как описано в Подразделе 5.10.4).
iv_rule_type — тип правила: NO_ACCESS или NO_MODIFICATION. Значение NULL удаляет правила любого типа.
iv_period — период, по истечении которого правило начинает действовать. Значение NULL удаляет правила с любым значением периода.
iv_action — действие, которое требуется выполнить. Возможное значение: ALTER_TS — переместить отношение в другое табличное пространство. Значение NULL удаляет правила с любым действием.
get_rules( [iv_object_name text] ) returns table
Возвращает список всех правил, если вызывается без параметра, или список правил, применимых к указанному отношению. Для таблиц, являющихся секциями секционированных таблиц, этот список также включает правила, которые применяются к ним косвенно, через правила, относящиеся к родительским таблицам разного уровня. Список возвращается в виде таблицы следующего формата:
Столбец Тип Описание |
|---|
Имя схемы, в которой находится отношение |
Имя отношения |
Значение, определённое в столбце |
Тип правила: |
Период, по истечении которого правило начинает действовать |
Действие, которое требуется выполнить |
Параметр действия |
Аргументы:
iv_object_name — имя отношения. Может быть полным или неполным. Если имя неполное, схема будет определена автоматически с учётом пути поиска (как объяснено в Подразделе 5.10.4).
Следующие функции управляют списком пользователей, которых следует исключать при проверке правил pgpro_ilm:
set_exclude_users( iv_exclude_access name[], iv_exclude_modification name[] ) returns void
Сбрасывает список исключаемых пользователей и создаёт новый.
Аргументы:
iv_exclude_access — массив имён пользователей, которых нужно исключать при проверке правил NO_ACCESS.
iv_exclude_modification — массив имён пользователей, которых нужно исключать при проверке правил NO_MODIFICATION.
get_exclude_users() returns table
Возвращает список исключаемых пользователей в таблице следующего формата:
Столбец Тип Описание |
|---|
Имя пользователя. |
Указывает, следует ли исключить этого пользователя из проверки правил |
Указывает, следует ли исключить этого пользователя из проверки правил |
Следующие функции проверяют, применимы ли правила pgpro_ilm к отношениям, и выполняют необходимые действия с отношениями, к которым эти правила применимы. Эти функции не применяют никаких правил для секционированных таблиц и индексов по таким таблицам. Для обычных таблиц функции проверяют правила в убывающем порядке периода, по истечении которого правило начинает действовать. Для секций секционированных таблиц правила проверяются для родительских секций, начиная с низшего уровня иерархии секционированных таблиц и до самого высокого, при этом на одном уровне правила проверяются в убывающем порядке периода. Как только найдено правило, применимое к таблице, функции выполняют соответствующее действие и завершают обработку этой таблицы.
process_rules( iv_object_name text ) returns void
Проверяет, применимо ли какое-либо правило к таблице, выполняет необходимое действие с таблицей, если правило применимо, и завершает выполнение. Когда эта функция вызывается для таблицы более одного раза, pgpro_ilm определяет, было ли уже применено определённое правило, и не применяет его снова.
Аргументы:
iv_object_name — имя таблицы. Может быть полным или неполным. Если имя неполное, при прохождении по правилам схема будет определена автоматически с учётом пути поиска (как описано в Подразделе 5.10.4).
process_all_rules() returns void
Для каждой таблицы в базе данных проверяет, применяется ли к ней какое-либо правило, выполняет необходимое действие с таблицей, если правило применимо, и переходит к следующей таблице. Функция фактически вызывает process_rules для всех таблиц в базе данных, для которых существует хотя бы одно правило.
Пример использования pgpro_ilm:
Создадим и посмотрим список исключаемых пользователей:
test=# SELECT pgpro_ilm.set_exclude_users(array['exclude_access_user'], array['Exclude Write User']);
set_exclude_users
-------------------
(1 row)
test=# SELECT user_name, exclude_access, exclude_modification FROM pgpro_ilm.get_exclude_users();
user_name | exclude_access | exclude_modification
---------------------+----------------+----------------------
exclude_access_user | t | f
Exclude Write User | f | t
(2 rows)Рассмотрим секционированную таблицу с одной секцией:
test=# \d+ measurement
Partitioned table "public.measurement"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
city_id | integer | | not null | | plain | | |
logdate | date | | not null | | plain | | |
peaktemp | integer | | not null | | plain | | |
unitsales | integer | | | | plain | | |
Partition key: RANGE (logdate)
Indexes:
"measurement_pkey" PRIMARY KEY, btree (city_id, logdate, peaktemp)
"measurement_city_id_idx" btree (city_id)
"measurement_logdate_idx" btree (logdate)
Partitions: measurement_y2006m03 FOR VALUES FROM ('2006-03-01') TO ('2006-04-01')
test=# \d+ measurement_y2006m03
Table "public.measurement_y2006m03"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
city_id | integer | | not null | | plain | | |
logdate | date | | not null | | plain | | |
peaktemp | integer | | not null | | plain | | |
unitsales | integer | | | | plain | | |
Partition of: measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01')
Partition constraint: ((logdate IS NOT NULL) AND (logdate >= '2006-03-01'::date) AND (logdate < '2006-04-01'::date))
Indexes:
"measurement_y2006m03_pkey" PRIMARY KEY, btree (city_id, logdate, peaktemp)
"measurement_y2006m03_city_id_idx" btree (city_id)
"measurement_y2006m03_logdate_idx" btree (logdate)
Access method: heapДобавим несколько правил:
test=# SELECT * from pgpro_ilm.add_rule('measurement', 'NO_ACCESS', interval '16 mons', 'ALTER_TS', 'slow_space');
add_rule
----------
(1 row)
test=# SELECT * from pgpro_ilm.add_rule('measurement', 'NO_MODIFICATION', interval '16 mons', 'ALTER_TS', 'slow_space');
add_rule
----------
(1 row)
test=# SELECT * from pgpro_ilm.add_rule('measurement_y2006m03', 'NO_ACCESS', interval '14 mons', 'ALTER_TS', 'slow_space');
add_rule
----------
(1 row)
test=# SELECT * from pgpro_ilm.add_rule('measurement_y2006m03', 'NO_ACCESS', interval '12 mons', 'ALTER_TS', 'slow_space');
add_rule
----------
(1 row)Правила для секционированной таблицы выглядят следующим образом:
test=# select * from pgpro_ilm.get_rules('measurement');
nspname | relname | rule_type | period | action | parameter
---------+-------------+-----------------+---------------+----------+------------
public | measurement | NO_ACCESS | 1 year 4 mons | ALTER_TS | slow_space
public | measurement | NO_MODIFICATION | 1 year 4 mons | ALTER_TS | slow_space
(2 rows)К секции применимы два явно заданных правила NO_ACCESS и правило NO_MODIFICATION, унаследованное от родительской таблицы:
test=# select * from pgpro_ilm.get_rules('measurement_y2006m03');
nspname | relname | rule_type | period | action | parameter
---------+----------------------+-----------------+---------------+----------+------------
public | measurement | NO_MODIFICATION | 1 year 4 mons | ALTER_TS | slow_space
public | measurement_y2006m03 | NO_ACCESS | 1 year 2 mons | ALTER_TS | slow_space
public | measurement_y2006m03 | NO_ACCESS | 1 year | ALTER_TS | slow_space
(3 rows)Добавим ещё одно явное правило для секции:
test=# SELECT * from pgpro_ilm.add_rule('measurement_y2006m03', 'NO_MODIFICATION', interval '12 mons', 'ALTER_TS', 'slow_space');
add_rule
----------
(1 row)Теперь к секции применимы только явно заданные правила:
test=# select * from pgpro_ilm.get_rules('measurement_y2006m03');
nspname | relname | rule_type | period | action | parameter
---------+----------------------+-----------------+---------------+----------+------------
public | measurement_y2006m03 | NO_ACCESS | 1 year 2 mons | ALTER_TS | slow_space
public | measurement_y2006m03 | NO_ACCESS | 1 year | ALTER_TS | slow_space
public | measurement_y2006m03 | NO_MODIFICATION | 1 year | ALTER_TS | slow_space
(3 rows)Обратите внимание, что правила не обрабатываются для секционированных таблиц:
test=# SELECT pgpro_ilm.process_rules('measurement');
NOTICE: ILM: no rules matched for 'public.measurement'
process_rules
---------------
(1 row)При обработке правил индексы перемещаются вместе с таблицей:
test=# SELECT pgpro_ilm.process_rules('measurement_y2006m03');
NOTICE: ILM: found rule 'public.measurement_y2006m03' with type 'NO_ACCESS' and period '1 year 2 mons'. Action 'ALTER_TS', parameter 'slow_space'
NOTICE: ILM: executing 'ALTER TABLE public.measurement_y2006m03 SET TABLESPACE slow_space;'
NOTICE: ILM: executing 'ALTER INDEX public.measurement_y2006m03_pkey SET TABLESPACE slow_space;'
NOTICE: ILM: executing 'ALTER INDEX public.measurement_y2006m03_city_id_idx SET TABLESPACE slow_space;'
NOTICE: ILM: executing 'ALTER INDEX public.measurement_y2006m03_logdate_idx SET TABLESPACE slow_space;'
process_rules
---------------
(1 row)Результат:
test=# \d+ measurement_y2006m03
Table "public.measurement_y2006m03"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
city_id | integer | | not null | | plain | | |
logdate | date | | not null | | plain | | |
peaktemp | integer | | not null | | plain | | |
unitsales | integer | | | | plain | | |
Partition of: measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01')
Partition constraint: ((logdate IS NOT NULL) AND (logdate >= '2006-03-01'::date) AND (logdate < '2006-04-01'::date))
Indexes:
"measurement_y2006m03_pkey" PRIMARY KEY, btree (city_id, logdate, peaktemp), tablespace "slow_space"
"measurement_y2006m03_city_id_idx" btree (city_id), tablespace "slow_space"
"measurement_y2006m03_logdate_idx" btree (logdate), tablespace "slow_space"
Tablespace: "slow_space"
Access method: heapПри повторном вызове функции pgpro_ilm определяет, была ли таблица уже перемещена, и не перемещает её снова:
test=# SELECT pgpro_ilm.process_rules('measurement_y2006m03');
NOTICE: ILM: found rule 'public.measurement_y2006m03' with type 'NO_ACCESS' and period '1 year 2 mons'. Action 'ALTER_TS', parameter 'slow_space'
NOTICE: ILM: 'public.measurement_y2006m03' is already in the 'slow_space' tablespace
process_rules
---------------
(1 row)Удаление всех типов правил для таблицы measurement:
test=# select pgpro_ilm.remove_rule('measurement', NULL, '16 month'::interval, 'ALTER_TS');
remove_rule
-------------
(1 row)
test=# select * from pgpro_ilm.get_rules('measurement');
nspname | relname | rule_type | period | action | parameter
---------+---------+-----------+--------+--------+-----------
(0 rows)Для обработки всех таблиц можно вызвать функцию pgpro_ilm.process_all_rules(). В этом примере обнаруживается одна уже перемещённая таблица:
test=# call pgpro_ilm.process_all_rules(); NOTICE: ILM: checking 'public.measurement_y2006m03' NOTICE: ILM: found rule 'public.measurement_y2006m03' with type 'NO_ACCESS' and period '1 year 2 mons'. Action 'ALTER_TS', parameter 'slow_space' NOTICE: ILM: 'public.measurement_y2006m03' is already in the 'slow_space' tablespace CALL