Инструменты для создания, обслуживания таблиц с использованием декларативного партиционирования.
Установка Можно воспользоваться pgcodekeeper для получения diff и кода из репозитория
git clone https://github.com/iriswind/part_tools.git
/PATH_TO_pgcodekeeper/pgcodekeeper-cli.sh -t "jdbc:postgresql://host:port/database?user=username&password=PASS" public -s /PATH_TO_part_tools/part_tools &> diff.sql
psql -h host -p port -d database -U username -f diff.sql
Актуальный sql-скрипт можно взять из релиза и также его запустить в удобном PostgreSQL-клиенте(psql, pgAdmin3, pgAdmin4, DBaeaver...)
Использование
Для объявления новой таблицы сверьтесь с синтаксисом https://postgrespro.ru/docs/postgresql/15/ddl-partitioning?ysclid=lzuxhcd8d7212539523
Также рекомендуется создать в схеме partitions default-секцию. Стоит отметить, что все объекты(триггеры, индексы, внешние ключи, первичные ключи, check-условия) будут унаследованы секциями автоматически.
Создание секций
В зависимости от типа разбиения(библиотека пока поддерживает секционирование по диапазону дат или int) два варианта создания секций:
- Для секционирования по полю даты:
select part_tools.create_range_partitions(_parent_table_name, _part_schema, _date_start, _interval, _count, _format);
_parent_table_name - родительская таблица
_part_schema text - желательно указывать схему 'partitions' как контейнер для секций
_date_start - дата, которая попадает в диапазон желаемого интервала(можно указать текущую дату)
_interval text - желаемый интервал для секций, поддерживаются различные варианты - 'year','month','week','day','hour'
_count - сколько секций от _date_start с интервалом _interval нужно создать
_format - этот параметр зависит от _interval и может принимать следующие варианты
| _interval | _format |
| --------- | ------------ |
| 'year' | 'yyyy' |
| 'month' | 'yyyymm' |
| 'week' | 'yyyyww' |
| 'day' | 'yyyymmdd' |
| 'hour' | 'yyyymmddhh' |
- Для секционирования по полю int:
select part_tools.create_range_partitions_int(_parent_table_name, _part_schema, _start_val, _interval, _count);
_parent_table_name - родительская таблица
_part_schema text - желательно указывать схему 'partitions' как контейнер для секций
_start_val - определяем от какого значения будем далее с интервалом _interval создавать секции
_interval text - желаемый интервал для секций, поддерживаются различные варианты - 'NК','NМ','NB', где N-натуральное число, а K, M, B - соответственно 1000, 1000000, 1000000000
Т.е. мы можем создавать к примеру секции по 30 млн записей каждая, тогда _interval будет '300M'
_count - сколько секций от _date_start с интервалом _interval нужно создать
Удаление, отсоединение секций
Конкретную секцию можно отсоединить без удаления:
select part_tools.detach_range_partition(_parent_name, _part_name);
Удалить секцию(в этом случае будет неявно выполнен detach, наложена блокировка, не рекомендуется):
select part_tools.drop_range_partition(_part_name);
Также можем выполнить групповое удаление секций за период, ранее указанного интервала, хранимка сначала выполнит detach, лишь затем удаление
select part_tools.delete_range_tables(_parent_table_name, _interval)
Миграция данных из существующей regular или таблицы, объявленной через inherit-секционирование****
Мы можем создать секционированную таблицу на основе существующей(ее имя и объекты будут иметь суффикс _parted), залить в нее из основной таблицы данные, а потом подменить.
- Создание Нужно учитывать то, что если у нас есть contraint в виде pk, uniq не по полю секционирования, то в этом случае их мы сможем наложить только на секции, т.к. декларативное секционирование допускает сквозные коснтрейнты только если они содержат поле секционирования.
select part_tools.create_range_table(_orig_table_name, _column, _part_schema, _pk_uniq)
_orig_table_name - имя оригинальной таблицы с полным указанием схемы(nspname.relname)
_column - поле, по которому разбиваем таблицу
_part_schema - схема для секций
_pk_uniq - если по _column есть pk, uniq - то указываем true и эти констрейнты будут созданы на головной таблице
- Миграция данных В этом случае возможны варианты:
- засинхронить репликатором
- залить данные вручную, после подмены досинхронить что не залилось
- через логическую репликацию. В этом варианте создаем новую ВМ, на ней поднимаем структуру БД, объявляем нужную нам таблицу через декларативное партиционирование, создаем секции и запускаем логическую репликацию данных. После того, как данные будут засинхронены, мы можем бесшовно переключиться на новую БД.
- Замена, удаление таблиц Может оказаться, что на нашу таблицу наложены внешние ключи от другие таблиц, либо есть view, содержащая в своем определении оригинальную таблицу. В таком случае запросами генерируем запросы на удаление и создание соответс 5CC5 твующих fk,view
select part_tools.generate_query_prepare_constraints(_table_name);
select part_tools.generate_query_prepare_views(_table_name);
Соответственно, сначала удаляем все зависимые view, внешние ключи, а затем удаляем оригинальную таблицу(либо мы ее можем переименовать - см. далее). После этого выполняем переименование _parted-таблицы.
Переименование(таблицы и всех ее объектов) выполняется запросом:
select part_tools.rename_table_and_objects(_table_name_parted, _suff, _old_suff)
_table_name_parted - имя таблицы с полным указанием схемы(nspname.relname), которую мы объявили через декларативное партиционирование
_suff - новый суффикс, если он необходим
_old_suff - старый суффикс, если он необходим
В нашем случае, чтобы переименовать таблицу, которую мы объявили через декларативное партиционирование, запрос будет таким:
select part_tools.rename_table_and_objects(_table_name_parted, '', '_parted');
Если мы хотим переименовать оригинальную таблицу, то запрос будет такой:
select part_tools.rename_table_and_objects(_table_name, '_old', '');