Cоздание минимального жизнеспособного гибридного хранилища данных

Содержание:

1. Инструкция по шагу 0.1: Проектирование логической архитектуры Data Lakehouse (DLH)
2. Инструкция по шагу 0.2: Подготовка виртуальной инфраструктуры (с использованием gdisk)
3. Подробная инструкция по шагу 1.1: Установка PostgreSQL
4. Подробная инструкция по шагу 1.2: Создание слоя STG (таблица-песочница)
5. Следует ли в PostgreSQL сохранять формат названий таблиц и их полей из исходной БД MS SQL (например, Table1 вместо table_1)?
6. Подробная инструкция по шагу 1.3: Создание слоя RAW с партиционированием по кварталам
7. Подробная инструкция по шагу 1.4: Управление жизненным циклом партиций и автоматизация архивации

Инструкция по шагу 0.1: Проектирование логической архитектуры Data Lakehouse (DLH)¶

Шаг 0.1 итоговой инструкции — это проектирование логической архитектуры Data Lakehouse (DLH). Это фундаментальный этап, от которого зависят все последующие решения по инфраструктуре, выбору инструментов и настройке ETL/ELT-процессов. В закрытом контуре особенно важно сделать это правильно, чтобы избежать дорогостоящих переделок.

Ниже представлена подробная инструкция по данному шагу.

Технологический стек шага 0.1¶

Этот шаг — аналитический и не требует установки программного обеспечения.

Пошаговая инструкция¶

Шаг 0.1.1: Определение бизнес-целей и требований к данным¶

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

1. Определите потребителей данных:

   • Кто будет использовать хранилище? (Аналитики, руководители, Data Scientists, операционные системы?)
   • Каковы их ключевые вопросы и отчёты? (аналитика продаж, операционные дашборды, отчёты для руководства, обучение ML-моделей)

2. Определите источники и объёмы данных:

   • Основной источник: таблица table_1 (∼3 ТБ) в MS SQL Server, с ежегодным приростом ~2 ТБ.
   • Потенциальные будущие источники: другие таблицы из той же БД, внешние системы.
   • Требование к историчности: Хранение данных минимум за 5 лет.

3. Определите нефункциональные требования:

   • Время загрузки (окно 7:00–8:00).
   • Производительность запросов (аналитика и операционное использование).
   • Надёжность и возможность восстановления (бэкапы, WAL-архивация).

Шаг 0.1.2: Проектирование многослойной архитектуры (Medallion Architecture)¶

Лучшей мировой практикой для Lakehouse является многослойная архитектура (Medallion Architecture), где данные последовательно проходят через слои, повышая своё качество и ценность. Для нашего проекта предлагается следующая структура из пяти слоёв.

1. Слой STAGING (STG) — «Песочница»

• Назначение: Быстрая загрузка сырых данных из источника. Данные загружаются в исходном виде, без изменений.
• Особенности: Полностью перезаписывается при каждой загрузке. Хранит данные только за последние 30 суток.
• Технология: Таблица в PostgreSQL без индексов.

2. Слой RAW DATA LAKE (RAW) — «Озеро сырых данных»

• Назначение: Долгосрочное, неизменяемое хранилище сырых данных в том виде, в котором они были получены из STG.
• Особенности: Данные партиционируются по кварталам (3 месяца) в отдельные LVM-тома, которые можно архивировать и отключать. Начальное хранение — 3 месяца (2–4 партиции), в перспективе — до 1–3 лет (до 20 партиций). На этом слое выполняется проверка целостности.
• Технология: Партиционированная таблица PostgreSQL. Каждая партиция в отдельном табличном пространстве на своём LVM-томе.

3. Слой OPERATIONAL DATA STORE (ODS) — «Операционное хранилище»

• Назначение: Очистка и первичная обработка данных из RAW. Данные структурируются и приводятся к плоскому формату.
• Особенности: Слой для оперативной работы. Здесь происходит нормализация (разбор JSON-полей, например) и подготовка данных для дальнейших трансформаций.
• Технология: Таблицы PostgreSQL, близкие по структуре к исходным, но с очищенными данными.

4. Слой DETAIL DATA STORE (DDS) — «Хранилище детализированных данных»

• Назначение: Хранение детализированных, историчных и нормализованных данных.
• Особенности: Данные организованы по модели Data Vault 2.0 или 3-ей нормальной форме (3НФ), что обеспечивает гибкость, масштабируемость и полную историчность.
• Технология: Нормализованные таблицы PostgreSQL с SCD Type 2 (Slowly Changing Dimensions) для отслеживания истории изменений.

5. Слой COMMON DATA MARTS (CDM) — «Общие витрины данных»

• Назначение: Предоставление данных для конечного использования: аналитика, дашборды, отчёты.
• Особенности: Данные агрегированы и денормализованы для быстрых запросов. Используются схемы «звезда» или «снежинка».
• Технология: Материализованные представления и таблицы PostgreSQL, оптимизированные под конкретные бизнес-запросы.

Шаг 0.1.3: Определение потоков данных (ETL/ELT)¶

Опишите, как данные будут перемещаться между слоями.

1. Extract (Извлечение): Из MS SQL Server в слой STG.
2. Load (Загрузка): Из STG в RAW.
3. Transform (Трансформация): Последовательные преобразования: RAW → ODS → DDS → CDM.
   • ODS: Парсинг JSON, очистка, валидация.
   • DDS: Нормализация, применение SCD Type 2.
   • CDM: Агрегация, денормализация, создание витрин.

На этапе MVP реализуются только первые два слоя (STG → RAW). Остальные слои добавляются на этапах промышленного внедрения и развития.

Шаг 0.1.4: Проектирование модели данных¶

Разработайте структуру таблиц для каждого слоя.

• STG и RAW: Таблица table_1 с колонками (id, dt, json_data, col4, col5).
• ODS: Таблица, где поля из json_data вынесены в отдельные колонки.
• DDS: Таблицы, нормализованные по модели Data Vault 2.0 (Хабы, Линки, Сателлиты).
• CDM: Таблицы-витрины по схеме «звезда» (таблицы фактов и измерений).

Шаг 0.1.5: Планирование инфраструктуры¶

На основе спроектированной архитектуры рассчитайте требования к инфраструктуре.

1. Дисковая подсистема:
   • RAID-массив: Для надёжности и производительности рекомендуется использовать RAID 6 (как минимум 8 дисков).
   • Разделение дисков: Выделите отдельные тома для:
     • Данных PostgreSQL (горячие)
     • Бэкапов
     • Логов
2. Вычислительные ресурсы: 16 vCPU, 64 ГБ RAM (как указано в ТЗ).
3. Сетевое взаимодействие: Обеспечьте доступ к MS SQL Server в окно 7:00–8:00.

Шаг 0.1.6: Документирование архитектуры¶

Создайте документ «Архитектурная схема DLH», который будет включать:

• Общую схему со всеми слоями и потоками данных.
• Описание каждого слоя: назначение, технологии, структура данных.
• Описание ETL/ELT-процессов: как данные перемещаются и трансформируются.
• План инфраструктуры: требования к ВМ, дискам, сети.
• План развития: как архитектура будет эволюционировать на этапах промышленного внедрения и развития (добавление Airflow, мониторинга, новых слоёв и источников).

Итоговый чек-лист шага 0.1¶

💡 Важное замечание: Учитывая, что проект ведётся в закрытом контуре, все инструменты и библиотеки, которые потребуются на следующих этапах (PostgreSQL, Airflow, Zabbix, Grafana, Python-пакеты), должны быть заранее загружены в локальное хранилище (например, на внутреннем сервере RPM-пакетов или в виде Docker-образов). Архитектура должна проектироваться с учётом этого ограничения.

Инструкция по шагу 0.2: Подготовка виртуальной инфраструктуры (с использованием gdisk)¶

Ниже представлена исправленная и дополненная инструкция по шагу 0.2 «Подготовка виртуальной инфраструктуры» с учётом использования gdisk вместо fdisk для работы с диском свыше 2 ТБ.

Инструкция полностью адаптирована для закрытого контура (без доступа в интернет), предполагает использование только локального хранилища и учитывает лучшие мировые практики виртуализации и работы с GPT-разделами.

Технологический стек шага 0.2¶

Пошаговая инструкция¶

Шаг 0.2.1: Подготовка установочных образов в локальном хранилище¶

Поскольку доступ в интернет отсутствует, заранее подготовьте все необходимые файлы в локальном хранилище (например, на внутреннем файловом сервере или USB-носителе):

1. ISO-образ РЕД ОС 8 (серверная редакция)

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

2. Пакет gdisk (RPM) — если он отсутствует в базовом репозитории РЕД ОС, его необходимо заранее загрузить в локальное хранилище. В РЕД ОС 8 пакет gdisk обычно доступен в репозитории baseos.

3. Разместите ISO-образы и RPM-пакеты в каталоге, доступном с хоста VMware (например, на общем сетевом хранилище или локальном диске).

Шаг 0.2.2: Создание виртуальной машины в VMware¶

0.2.2.1. Запуск мастера создания виртуальной машины¶

1. Подключитесь к VMware vSphere Client или VMware Workstation.
2. Выберите действие:
   • В vSphere Client: правой кнопкой мыши на кластере/хосте → New Virtual Machine.
   • В VMware Workstation: File → New Virtual Machine.
3. Выберите тип конфигурации:
   • В Workstation: выберите Custom (advanced) для полного контроля над настройками.
   • В vSphere: выберите Create a new virtual machine.

0.2.2.2. Выбор установочного носителя¶

На этапе выбора установочного диска укажите путь к ISO-образу РЕД ОС 8 из локального хранилища:

• Browse → укажите путь к ISO-образу (например, /mnt/storage/redos-8-server.iso).

0.2.2.3. Выбор гостевой операционной системы¶

1. Guest OS Family: Linux.
2. Guest OS Version: Red Hat Enterprise Linux 8 (64-bit).

   РЕД ОС 8 базируется на RHEL 8, поэтому эта опция обеспечивает максимальную совместимость.

0.2.2.4. Настройка имени и расположения ВМ¶

1. Virtual machine name: DLH-PG-SRV (или другое осмысленное имя).
2. Location: выберите каталог хранения файлов ВМ (желательно на быстром хранилище).

0.2.2.5. Настройка процессора и памяти¶

В соответствии с требованиями проекта:

Рекомендация: в vSphere включите опцию Enable CPU Hot Add и Enable Memory Hot Add для возможности расширения ресурсов без остановки ВМ.

0.2.2.6. Настройка сетевого адаптера¶

1. Network adapter type: VMXNET3 (паравиртуальный драйвер, обеспечивает лучшую производительность).
2. Network connection: выберите нужную VLAN/порт-группу для доступа к MS SQL Server и другим системам.
3. MAC address: оставьте автоматическую генерацию.

0.2.2.7. Настройка SCSI-контроллера¶

1. SCSI controller type: VMware Paravirtual (рекомендуется для дисков с высокой нагрузкой).
2. Disk type: Thick Provision Eager Zeroed (для лучшей производительности) или Thin Provision (для экономии места, если хранилище ограничено).

0.2.2.8. Создание виртуальных дисков¶

В соответствии с архитектурой DLH необходимо создать несколько виртуальных дисков для разделения данных, логов и бэкапов.

Важно: Общий объём дисков (100 ГБ + 3,5 ТБ + 1.4 ТБ + 300 ГБ) ≈ 5,3 ТБ, что соответствует требованиям ТЗ (5 ТБ HDD).

Рекомендации по размещению дисков:

• Разместите диски на разных LUN/датасторах для повышения производительности ввода-вывода.
• Для диска с данными используйте отдельный RAID-массив (RAID 10 или RAID 6).
• Для бэкапов можно использовать более медленное хранилище.

0.2.2.9. Настройка дополнительных параметров¶

1. CD/DVD drive: оставьте подключение к ISO-образу РЕД ОС.
2. USB controller: не требуется.
3. Video card: оставьте стандартные настройки (достаточно 4 МБ видеопамяти).
4. VMware Tools: отключите автоматическую установку (установим позже вручную через Open VM Tools).

Шаг 0.2.3: Установка РЕД ОС 8 на виртуальную машину¶

0.2.3.1. Запуск ВМ и начало установки¶

1. Включите виртуальную машину.
2. Загрузка с ISO: система автоматически загрузится с подключенного ISO-образа.
3. Выберите пункт меню: Install RED OS 8.

0.2.3.2. Настройка языка и раскладки¶

1. Выберите язык установки: Русский.
2. Раскладка клавиатуры: Russian (добавьте English (US) для удобства).

0.2.3.3. Настройка установочных параметров¶

Выбор типа установки:

• Выберите «Установка РЕД ОС» (полная установка, не обновление).

Настройка дисков (разметка). Для диска 0 (системный, 100 ГБ) рекомендуется следующая схема разметки:

Важно: Для дисков 1–4 (данные, WAL, бэкапы, логи) не создавайте разделов в Anaconda. Они будут подключены и отформатированы позже как отдельные тома.

Выбор программного обеспечения:

• Software Selection: выберите «Сервер» (Server).
• Дополнительные модули: отметьте:
  • Headless Management
  • Standard или Minimal (для снижения потребления ресурсов)

Настройка сети:

1. Hostname: dlh-pg-srv.local (или согласно корпоративной политике именования).
2. Network interfaces: настройте статический IP-адрес для гарантированной доступности:
   • IP-адрес: согласно плану сети
   • Маска подсети: согласно плану сети
   • Шлюз: согласно плану сети
   • DNS-серверы: согласно плану сети

Настройка времени и локали:

1. Time Zone: выберите свой часовой пояс (например, Europe/Moscow).
2. NTP: настройте синхронизацию с корпоративным NTP-сервером (в закрытом контуре).

Создание пользователей:

1. Пароль root: установите надёжный пароль для суперпользователя.
2. Создание пользователя: создайте учётную запись для администратора (например, admin).

Начало установки:

1. Нажмите «Начать установку».
2. Дождитесь завершения установки (обычно 15–30 минут).
3. После завершения перезагрузите систему.

Шаг 0.2.4: Первичная настройка системы¶

0.2.4.1. Вход в систему¶

1. Войдите под пользователем root.
2. Проверьте сетевое подключение:

ip addr show
   ping <gateway_ip>

0.2.4.2. Настройка менеджера пакетов для локального репозитория¶

Поскольку доступ в интернет отсутствует, настройте dnf на использование локального репозитория:

1. Подключите локальный репозиторий (если он не был подключён автоматически):

   dnf config-manager --add-repo=file:///mnt/local_repo
   dnf makecache
   

2. Проверьте доступные репозитории:

   dnf repolist
   

0.2.4.3. Установка пакета gdisk (если отсутствует)¶

В РЕД ОС 8 пакет gdisk обычно входит в состав базового репозитория. Установите его:

dnf install -y gdisk

Если пакет отсутствует в репозитории, установите его из заранее подготовленного RPM-файла:

dnf install -y /path/to/gdisk-*.rpm

Шаг 0.2.5: Работа с диском данных (3,5 ТБ) с использованием gdisk¶

Важно: Для дисков размером более 2 ТБ необходимо использовать GPT-разметку. Стандартная MBR-разметка не поддерживает диски свыше 2 ТБ. gdisk является специализированным инструментом для работы с GPT.

0.2.5.1. Проверка обнаруженных дисков¶

lsblk

Вы должны увидеть устройства, соответствующие добавленным виртуальным дискам:

• /dev/sda — системный диск
• /dev/sdb — диск данных, бэкапов и логов (3.5 + 1.4 + 0.3 ТБ)

0.2.5.2. Создание GPT-раздела STG на диске данных с помощью gdisk¶

1. Запустите gdisk для диска /dev/sdb:

gdisk /dev/sdb

Последовательность действий в интерактивном режиме:

• Создание новой GPT-таблицы (если на диске есть старая MBR-разметка):
  • Введите команду: o
  • Подтвердите создание новой GPT-таблицы: Y
• Создание нового раздела:
  • Введите команду: n
  • Номер раздела (по умолчанию 1): нажмите Enter
  • Начальный сектор (по умолчанию): нажмите Enter
  • Конечный сектор (задать вручную): введите 1757812534 и нажмите Enter
  • Тип раздела (по умолчанию 8300 для Linux filesystem): нажмите Enter
• Проверка созданного раздела:
  • Введите команду: p — вы увидите информацию о новом разделе
• Запись изменений на диск:
  • Введите команду: w
  • Подтвердите запись: Y

Примечание: gdisk автоматически создаёт GPT-разметку, которая поддерживает диски объёмом более 2 ТБ.

2. Смонтируйте раздел:

mkfs.xfs -f /dev/sdb1
mkdir -p /pgdata
mount /dev/sdb1 /pgdata

0.2.5.3. Создание разделов и файловых систем для остальных дисков¶

Для дисков менее 2 ТБ (WAL, бэкапы, логи) можно использовать как gdisk, так и fdisk. Рекомендуется использовать gdisk для единообразия.

1. Создайте диск RAW (/dev/sdb2):

gdisk /dev/sdd
# o - n (конечный сектор 6835937534) - p - w
mkfs.xfs -f /dev/sdb2
mkdir -p /backup
mount /dev/sdb2 /backup

2. Создайте диск бэкапов (/dev/sdb3):

gdisk /dev/sdd
# o - n (конечный сектор 9765625034) - p - w
mkfs.xfs -f /dev/sdb3
mkdir -p /backup
mount /dev/sdb3 /backup

3. Создайте диск логов (/dev/sdb4):

gdisk /dev/sde
# o - n (конечный сектор 10485759966) - p - w
mkfs.xfs -f /dev/sdb4
mkdir -p /var/log/dlh
mount /dev/sdb4 /var/log/dlh

4. Проверьте созданные разделы диска sdb:

# Последовательно введите команды
sudo gdisk /dev/sdb/
p

# Результат
Number  Start (sector)    End (sector)  Size       Code  Name
   1            2048      1757812534   838.2 GiB   8300  Linux filesystem
   2      1757812736      6835937534   2.4 TiB     8300  Linux filesystem
   3      6835939328      9765625034   1.4 TiB     8300  Linux filesystem
   4      9765625856     10485759966   343.4 GiB   8300  Linux filesystem

Созданные тома:

0.2.5.4. Добавление в /etc/fstab для автоматического монтирования¶

1. Добавьте записи в /etc/fstab:

nano /etc/fstab

2. Добавьте строки:

/dev/sdb1  /pgdata       xfs  defaults  0 2
/dev/sdb3  /backup       xfs  defaults  0 2
/dev/sdb4  /var/log/dlh  xfs  defaults  0 2

3. Проверьте монтирование:

mount -a
df -h

Шаг 0.2.6: Настройка LVM для будущих партиций (на диске данных)¶

Для упрощения управления дополнительными томами (партициями RAW-слоя) создайте LVM-группу на диске данных:

# Проверка существующих томов
lvdisplay

# Создание физического тома
sudo pvcreate /dev/sdb2

# Создание группы томов
sudo vgcreate vg_data /dev/sdb2

# Создание логических томов для будущих партиций (пример для первых 4 кварталов)
sudo lvcreate -L 800G -n lv_raw_q2_2026 vg_data
sudo lvcreate -L 800G -n lv_raw_q3_2026 vg_data
sudo lvcreate -L 800G -n lv_raw_q4_2026 vg_data

# Форматирование
sudo mkfs.xfs /dev/vg_data/lv_raw_q2_2026
sudo mkfs.xfs /dev/vg_data/lv_raw_q3_2026
sudo mkfs.xfs /dev/vg_data/lv_raw_q4_2026

# Создание точек монтирования
sudo mkdir -p /mnt/raw_q2_2026 /mnt/raw_q3_2026 /mnt/raw_q4_2026

# Добавление записей в `/etc/fstab` для автоматического монтирования
echo '/dev/vg_data/lv_raw_q2_2026 /mnt/raw_q2_2026 xfs defaults 0 0' | sudo tee -a /etc/fstab
echo '/dev/vg_data/lv_raw_q3_2026 /mnt/raw_q3_2026 xfs defaults 0 0' | sudo tee -a /etc/fstab
echo '/dev/vg_data/lv_raw_q4_2026 /mnt/raw_q4_2026 xfs defaults 0 0' | sudo tee -a /etc/fstab

# Назначение прав на каталоги для пользователя `postgres`
sudo chown postgres:postgres /mnt/raw_q2_2026 /mnt/raw_q3_2026 /mnt/raw_q4_2026
sudo chmod 700 /mnt/raw_q*

# Проверка всех смонтированных томов:
df -h /mnt/raw_q*

# Проверка существующих томов
lvdisplay

Шаг 0.2.7: Установка Open VM Tools (VMware Tools)¶

VMware рекомендует использовать Open VM Tools из репозитория ОС.

1. Установка через локальный репозиторий:

dnf install -y open-vm-tools open-vm-tools-desktop

2. Запуск службы:

systemctl enable vmtoolsd.service --now
systemctl status vmtoolsd.service

3. Перезагрузка ВМ для применения изменений:

reboot

Шаг 0.2.8: Настройка безопасности (базовая)¶

1. Настройка файрвола (firewalld):

# Открыть порт для SSH (если ещё не открыт)
firewall-cmd --add-service=ssh --permanent

# Открыть порт для PostgreSQL (для будущих подключений)
firewall-cmd --add-port=5432/tcp --permanent

# Перезагрузить правила
firewall-cmd --reload

2. Отключение SELinux (опционально, для упрощения). В некоторых конфигурациях РЕД ОС может потребоваться отключение SELinux:

setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

Рекомендация: в продуктивных средах лучше настроить SELinux в режиме permissive для отладки, а затем переключить в enforcing.

3. Настройка SSH для удалённого управления из Windows:

# Убедиться, что SSH-сервер запущен
systemctl enable sshd --now

# Разрешить вход по ключам (для PowerShell)
mkdir -p /root/.ssh
chmod 700 /root/.ssh

Итоговый чек-лист шага 0.2¶

Возможные проблемы и их решение¶

Почему gdisk, а не fdisk?¶

Использование gdisk — это стандартная мировая практика для работы с дисками объёмом более 2 ТБ.

Эта инструкция полностью учитывает работу в закрытом контуре без доступа в интернет, опирается на официальную документацию РЕД ОС и VMware, а также на лучшие мировые практики работы с большими дисками с использованием GPT-разметки.

Инструкция по шагу 0.3: Настройка сетевого взаимодействия¶

Ниже представлена детальная пошаговая инструкция по выполнению шага 0.3. Настройка сетевого взаимодействия из итоговой архитектуры Data Lakehouse (DLH). В рамках этого шага мы настроим статический IP-адрес, DNS-серверы, имя хоста, межсетевой экран (firewalld), службу синхронизации времени (Chrony) и SSH-доступ для удалённого управления.

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

Технологический стек шага 0.3¶

Пошаговая инструкция¶

Шаг 0.3.1: Вход в систему и проверка сетевых интерфейсов¶

1. Войдите в систему под пользователем root или пользователем с правами sudo.

2. Проверьте список сетевых интерфейсов и их текущие настройки:

   ip addr show
   

   или

   nmcli device status
   

   
   

3. Определите имя сетевого интерфейса, который будет использоваться для подключения к корпоративной сети (обычно eth0 или ens192). В дальнейшем в командах мы будем использовать eth0 как пример — замените его на актуальное имя вашего интерфейса.

Шаг 0.3.2: Настройка статического IP-адреса¶

В закрытом контуре сервер должен иметь постоянный IP-адрес для гарантированной доступности. Настройка выполняется через NetworkManager с использованием утилиты nmcli.

1. Проверьте текущее подключение:

   nmcli connection show
   

   
   

   Вы увидите список подключений. Обычно активное подключение называется eth0 или System eth0.

2. Настройте статический IP-адрес, маску подсети и шлюз. Замените значения на актуальные для вашей сети:

   nmcli connection modify eth0 ipv4.method manual
   nmcli connection modify eth0 ipv4.addresses 192.168.1.100/24
   nmcli connection modify eth0 ipv4.gateway 192.168.1.1
   

   
   

3. Отключите автоматическое получение DNS-адресов от DHCP и укажите статические DNS-серверы (в закрытом контуре это обычно внутренние DNS-серверы):

   nmcli connection modify eth0 ipv4.ignore-auto-dns true
   nmcli connection modify eth0 ipv4.dns "192.168.1.10 192.168.1.11"
   

   
   

4. Укажите поисковый домен (если требуется):

   nmcli con mod eth0 ipv4.dns-search "your-company.local"
   

   
   

5. Перезапустите сетевое подключение для применения настроек:

   nmcli con down eth0 && nmcli con up eth0
   

   
   

6. Проверьте новые настройки:

   ip addr show eth0
   ping 192.168.1.1   # проверка доступности шлюза
   

   
   

Шаг 0.3.3: Настройка имени хоста (hostname)¶

Установите осмысленное имя хоста, которое будет использоваться для идентификации сервера в сети.

1. Проверьте текущее имя хоста:

   hostname
   hostnamectl
   

   
   

2. Установите новое имя хоста (например, dlh-pg-srv):

   hostnamectl set-hostname dlh-pg-srv.your-company.local
   

   
   

3. Добавьте запись в файл /etc/hosts, чтобы имя хоста разрешалось в локальный IP-адрес:

   echo "192.168.1.100 dlh-pg-srv.your-company.local dlh-pg-srv" >> /etc/hosts
   

   
   

4. Проверьте разрешение имени:

   ping dlh-pg-srv
   

Шаг 0.3.4: Настройка DNS-разрешения (дополнительно)¶

В РЕД ОС управление DNS может осуществляться через systemd-resolved или напрямую через /etc/resolv.conf. Если вы уже указали DNS-серверы через nmcli (шаг 0.3.2), они должны автоматически появиться в /etc/resolv.conf.

1. Проверьте содержимое файла /etc/resolv.conf:

   cat /etc/resolv.conf
   

   
   

   В нём должны быть указаны ваши DNS-серверы и поисковый домен.

2. Если настройки не применились, можно отредактировать файл вручную (но учтите, что NetworkManager может перезаписывать его):

   echo "nameserver 192.168.1.10" > /etc/resolv.conf
   echo "nameserver 192.168.1.11" >> /etc/resolv.conf
   echo "search your-company.local" >> /etc/resolv.conf
   

   
   

3. Проверьте резолвинг внешних (внутри контура) имён:

   nslookup dlh-pg-srv
   

   
   

Шаг 0.3.5: Настройка межсетевого экрана (firewalld)¶

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

1. Убедитесь, что firewalld установлен и запущен:

   dnf install -y firewalld
   systemctl enable firewalld --now
   systemctl status firewalld
   

   
   

2. Проверьте текущую зону и активные правила:

   firewall-cmd --get-default-zone
   firewall-cmd --list-all
   

   
   

3. Откройте порт для SSH (порт 22), если он ещё не открыт:

   firewall-cmd --add-service=ssh --permanent
   

   
   

4. Откройте порт для PostgreSQL (порт 5432), который потребуется на следующих этапах для подключения извне (например, из Airflow):

   firewall-cmd --add-port=5432/tcp --permanent
   

   
   

5. Если планируется использование веб-интерфейсов (например, Grafana на порту 3000 или Airflow на порту 8080), откройте соответствующие порты:

   firewall-cmd --add-port=3000/tcp --permanent
   firewall-cmd --add-port=8080/tcp --permanent
   

   
   

6. Перезагрузите правила firewalld для применения постоянных изменений:

   firewall-cmd --reload
   

   
   

7. Проверьте список открытых портов:

   firewall-cmd --list-all
   

   
   

Шаг 0.3.6: Настройка синхронизации времени (Chrony)¶

Точное время критически важно для корректной работы базы данных, логирования и ETL-процессов. В РЕД ОС по умолчанию используется Chrony.

1. Проверьте статус службы Chrony:

   systemctl status chronyd.service
   

   
   

   Если служба не активна, запустите её:

   systemctl enable chronyd.service --now
   

   
   

2. Настройте NTP-серверы. В закрытом контуре это должен быть корпоративный NTP-сервер. Отредактируйте файл конфигурации:

   nano /etc/chrony.conf
   

   
   

   Найдите и замените или добавьте строки с NTP-серверами (пример для корпоративного сервера):

   server ntp.your-company.local iburst
   

   
   

   Закомментируйте или удалите публичные серверы, если они есть.

3. Перезапустите Chrony для применения изменений:

   systemctl restart chronyd.service
   

   
   

4. Проверьте синхронизацию времени:

   chronyc tracking
   

   
   

   В выводе должно отображаться, что система синхронизирована с указанным NTP-сервером.

5. Проверьте текущее системное время:

   date
   

   
   

Шаг 0.3.7: Настройка SSH-доступа для удалённого управления¶

SSH-сервер в РЕД ОС обычно установлен по умолчанию. Однако для повышения безопасности и удобства управления из Windows 11 через PowerShell рекомендуется выполнить дополнительные настройки.

1. Убедитесь, что пакет openssh-server установлен:

   dnf install -y openssh-server
   

   
   

2. Запустите SSH-сервер и добавьте его в автозагрузку:

   systemctl enable sshd --now
   systemctl status sshd
   

   
   

3. Настройте SSH-сервер для повышения безопасности. Отредактируйте файл /etc/ssh/sshd_config:

   nano /etc/ssh/sshd_config
   

   
   

   Внесите следующие изменения (раскомментируйте или добавьте строки):

   Port 22
   PermitRootLogin no                # Запрет входа под root (рекомендуется)
   PasswordAuthentication yes        # Оставьте для первоначальной настройки, затем отключите
   PubkeyAuthentication yes          # Включите аутентификацию по ключам
   MaxAuthTries 3                    # Ограничьте число попыток входа
   

   
   

4. Перезапустите SSH-сервер для применения изменений:

   systemctl restart sshd
   

   
   

5. Настройте аутентификацию по SSH-ключам для беспарольного входа из Windows 11:

   • На машине с Windows 11 откройте PowerShell и сгенерируйте ключи (если ещё не сделали):

     ssh-keygen -t rsa -b 4096
     

     
     

   • Скопируйте публичный ключ на сервер РЕД ОС:

     type C:\Users\<Ваше_имя>\.ssh\id_rsa.pub | ssh admin@192.168.1.100 "cat >> ~/.ssh/authorized_keys"
     

     
     

   • На сервере установите правильные права доступа:

     chmod 700 ~/.ssh
     chmod 600 ~/.ssh/authorized_keys
     

     
     

6. Проверьте подключение по SSH с Windows 11:

   ssh admin@192.168.1.100
   

   
   

Шаг 0.3.8: Проверка сетевой связанности и доступности сервисов¶

1. Проверьте доступность сервера из сети:

   ping <ip-адрес_сервера>
   

   
   

2. Проверьте доступность порта SSH (22) извне:

   telnet <ip-адрес_сервера> 22
   

   
   

3. Проверьте разрешение DNS-имён:

   nslookup dlh-pg-srv
   

   
   

4. Проверьте синхронизацию времени:

   timedatectl
   

   
   

5. Проверьте, что firewalld не блокирует необходимые порты:

   firewall-cmd --list-all
   

   
   

Итоговый чек-лист шага 0.3¶

Возможные проблемы и их решение¶

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

Подробная инструкция по шагу 1.1: Установка PostgreSQL¶

Ниже представлена детальная пошаговая инструкция по выполнению шага 1.1 «Установка PostgreSQL» из итоговой архитектуры Data Lakehouse (DLH).

Инструкция полностью адаптирована для закрытого контура (без доступа в интернет), предполагает установку только из локального репозитория и учитывает, что на предыдущем шаге вы уже подготовили диск /dev/sdb для хранения данных.

1. Технологический стек шага 1.1¶

Важно: пакет postgresql17-contrib отсутствует в локальном репозитории. Это ограничение учтено — на данном этапе мы устанавливаем только серверную часть, которая уже содержит всё необходимое для базовой работы (включая поддержку типа JSONB).

2. Цель шага¶

Установить PostgreSQL 17 на сервер РЕД ОС, разместив кластер баз данных на отдельном диске /dev/sdb (смонтирован в /pgdata), и настроить автоматический запуск службы. Это создаст основу для всех последующих слоёв хранилища (STG, RAW и т.д.).

3. Пошаговое выполнение¶

Шаг 1.1.1: Вход в систему и проверка дисков¶

1. Подключитесь к серверу по SSH или через локальную консоль.
2. Убедитесь, что диск /dev/sdb1 размечен и смонтирован в /pgdata:

lsblk
   df -h /pgdata

Вы должны увидеть, что /dev/sdb1 (или аналогичный раздел) смонтирован в /pgdata.

Шаг 1.1.2: Установка PostgreSQL 17 из локального репозитория¶

Поскольку доступ в интернет отсутствует, установка выполняется через dnf из локального репозитория.

1. Установите сервер PostgreSQL 17:

   sudo dnf install -y postgresql17-server
   

Обратите внимание: пакет postgresql17-contrib не устанавливаем, так как его нет в репозитории. Это не критично — все основные функции (включая JSONB) уже входят в серверный пакет.

2. Проверьте, что установка прошла успешно:

   rpm -qa | grep postgresql17
   

   
   

   В выводе должна быть строка с postgresql17-server.

Шаг 1.1.3: Инициализация кластера баз данных на отдельном диске¶

По умолчанию кластер создаётся в /var/lib/pgsql/. Нам нужно разместить его на /pgdata.

1. Создайте каталог для данных и назначьте владельца:

   sudo mkdir -p /pgdata/17/data
   sudo chown postgres:postgres /pgdata/17/data
   sudo chmod 700 /pgdata/17/data
   

   
   

2. Выполните инициализацию кластера с указанием нового каталога:

   sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
   

Если команда не сработает, можно использовать ручную инициализацию:

sudo -u postgres /usr/pgsql-17/bin/initdb -D /pgdata/17/data

3. Проверьте, что каталог данных создан:

   ls -la /pgdata/17/data
   

   
   

   Вы должны увидеть файлы postgresql.conf, pg_hba.conf и другие.

Шаг 1.1.4: Настройка конфигурационных файлов¶

Чтобы PostgreSQL использовал новый каталог данных, укажем путь в конфигурации службы.

1. Отредактируйте файл окружения для службы (если он существует):

   sudo nano /etc/sysconfig/postgresql-17
   

   
   

   Добавьте или раскомментируйте строку:

   PGDATA=/pgdata/17/data
   

   
   

2. Если файла нет, создайте символическую ссылку (альтернативный способ):

   sudo systemctl stop postgresql-17
   sudo mv /var/lib/pgsql/17/data /var/lib/pgsql/17/data.bak
   sudo ln -s /pgdata/17/data /var/lib/pgsql/17/data
   sudo chown -h postgres:postgres /var/lib/pgsql/17/data
   

   
   

3. Настройте производительность (под 64 ГБ RAM). Откройте postgresql.conf:

   sudo nano /pgdata/17/data/postgresql.conf
   

   
   

   Найдите и измените параметры:

   shared_buffers = 16GB
   work_mem = 128MB
   maintenance_work_mem = 2GB
   effective_cache_size = 48GB
   max_connections = 200
   

Это стандартная рекомендация для выделенных серверов с 64 ГБ RAM.

Шаг 1.1.5: Запуск службы и добавление в автозагрузку¶

1. Запустите PostgreSQL и настройте автоматический старт:

   sudo systemctl enable postgresql-17 --now
   

   
   

2. Проверьте статус службы:

   sudo systemctl status postgresql-17
   

   
   

   В выводе должно быть active (running).

Шаг 1.1.6: Настройка удалённого доступа (опционально)¶

Для подключения извне (например, из Airflow на следующих этапах) разрешите удалённые подключения.

1. В файле postgresql.conf раскомментируйте и измените:

   listen_addresses = '*'
   

   
   

2. В файле pg_hba.conf добавьте правило для удалённых подключений:

   sudo nano /pgdata/17/data/pg_hba.conf
   

   
   

   Добавьте строку (замените подсеть на вашу):

   host    all    all    192.168.1.0/24    scram-sha-256
   

   
   

3. Перезапустите службу:

   sudo systemctl restart postgresql-17
   

   
   

Шаг 1.1.7: Установка пароля для пользователя postgres¶

Для безопасности и последующей работы задайте пароль суперпользователю:

sudo -u postgres psql -c "ALTER USER postgres WITH ENCRYPTED PASSWORD 'your_strong_password';"

Важно: замените 'your_strong_password' на надёжный пароль и сохраните его.

Шаг 1.1.8: Проверка работоспособности¶

1. Подключитесь локально:

   sudo -u postgres psql
   

2. Выполните команду для проверки версии:

   SELECT version();
   

   Ожидаемый вывод: PostgreSQL 17.x on x86_64-redhat-linux-gnu...

3. Проверьте список баз данных:

   \l
   

   Должны отображаться служебные базы template0, template1, postgres.

4. Выход из psql:

   \q
   

Шаг 1.1.9: Настройка файрвола (firewalld)¶

Откройте порт PostgreSQL (5432) для внешних подключений (если планируется):

sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

4. Полный скрипт для автоматизации шага 1.1¶

Вы можете сохранить этот скрипт в файл install_postgres.sh и выполнить его от имени root:

#!/bin/bash
# Шаг 1.1: Установка PostgreSQL 17 на РЕД ОС (закрытый контур)

set -e

echo "=== 1. Проверка диска /pgdata ==="
df -h /pgdata || { echo "Ошибка: /pgdata не смонтирован!"; exit 1; }

echo "=== 2. Установка PostgreSQL 17 ==="
dnf install -y postgresql17-server

echo "=== 3. Создание каталога данных ==="
mkdir -p /pgdata/17/data
chown postgres:postgres /pgdata/17/data
chmod 700 /pgdata/17/data

echo "=== 4. Инициализация кластера ==="
/usr/pgsql-17/bin/postgresql-17-setup initdb

echo "=== 5. Настройка PGDATA ==="
echo "PGDATA=/pgdata/17/data" > /etc/sysconfig/postgresql-17

echo "=== 6. Настройка производительности ==="
cat >> /pgdata/17/data/postgresql.conf << EOF
shared_buffers = 16GB
work_mem = 128MB
maintenance_work_mem = 2GB
effective_cache_size = 48GB
max_connections = 200
EOF

echo "=== 7. Запуск службы ==="
systemctl enable postgresql-17 --now
systemctl status postgresql-17 --no-pager

echo "=== 8. Установка пароля для postgres ==="
sudo -u postgres psql -c "ALTER USER postgres WITH ENCRYPTED PASSWORD 'your_strong_password';"

echo "=== 9. Проверка ==="
sudo -u postgres psql -c "SELECT version();"

echo "=== Установка PostgreSQL 17 завершена ==="

5. Учёт ограничений проекта¶

6. Лучшие мировые практики, применённые в шаге¶

• Размещение данных на отдельном диске — повышает производительность и упрощает бэкапирование.
• Настройка параметров памяти — оптимизация под 64 ГБ RAM для эффективной работы с большими объёмами данных.
• Использование systemd — стандартный механизм управления службами в РЕД ОС.
• Установка пароля для postgres — базовая мера безопасности.
• Открытие только необходимых портов — минимизация поверхности атаки.

7. Связь с последующими шагами¶

• Шаг 1.2 — на основе установленного PostgreSQL будет создан слой STG (таблица-песочница).
• Шаг 1.3 — будет создан слой RAW с партиционированием по кварталам.
• Шаг 1.5 — ETL-скрипт будет использовать установленный PostgreSQL для загрузки и трансформации данных.

8. Итоговый чек-лист шага 1.1¶

Если на каком-либо шаге возникнут трудности (например, с правами доступа или инициализацией), обратитесь к логам службы: sudo journalctl -u postgresql-17.

Следует ли в PostgreSQL сохранять формат названий таблиц и их полей из исходной БД MS SQL (например, Table1 вместо table_1)?¶

В PostgreSQL не следует сохранять формат названий таблиц и их полей из исходной БД MS SQL. Настоятельно рекомендуем использовать единый стиль именования snake_case (нижний регистр с подчёркиваниями) во всех слоях PostgreSQL, а оригинальные имена из MS SQL хранить в комментариях и документации.

Почему snake_case, а не оригинальный PascalCase или camelCase?¶

Рекомендация по реализации¶

1. На слоях STAGING и RAW – используйте snake_case, но добавьте комментарии к таблицам и колонкам с указанием оригинальных имён из MS SQL.
   Например:
   

   COMMENT ON TABLE stg.table_1 IS 'Оригинальная таблица: Table1 из MS SQL';
   COMMENT ON COLUMN stg.table_1.id IS 'Оригинальное поле: RowID';
   

   Это позволит разработчикам и аналитикам легко сопоставлять имена, не теряя связи с источником.

2. На слоях ODS, DDS, CDM – также используйте snake_case, но здесь вы можете давать более осмысленные имена, отражающие бизнес-смысл (например, customer_id, order_date), уже не привязываясь строго к исходным именам.

3. В ETL-скриптах – при выгрузке из MS SQL вы можете использовать алиасы для приведения имён к snake_case:

   SELECT RowID AS id, CreatedDate AS dt, ...
   

   Это позволит сразу загружать данные в столбцы с нужными именами.

Что делать, если аналитикам важно видеть оригинальные имена?¶

• Создайте словарь данных (документацию или представления), где будет таблица соответствия: оригинальное имя → имя в DLH.
• В BI-инструментах (например, Power BI) можно переименовывать поля на этапе визуализации, не меняя структуру хранилища.

Итог¶

Используйте snake_case во всех слоях PostgreSQL, а оригинальные имена из MS SQL храните в комментариях и документации. Это сэкономит вам множество часов отладки и упростит написание запросов. Сохранение оригинального стиля приведёт к постоянным проблемам с регистром и кавычками, что не стоит того ради «визуальной близости» к источнику.

Подробная инструкция по шагу 1.2: Создание слоя STG (таблица-песочница)¶

Технологический стек шага¶

Цель шага¶

Создать слой STG – временное хранилище для сырых данных, загружаемых из MS SQL Server. Таблица будет:

• Полностью перезаписываться при каждой загрузке (через TRUNCATE + INSERT).
• Не иметь индексов и ограничений для максимальной скорости записи.
• Хранить данные только за последние 30 суток (ограничение будет реализовано на этапе ETL-скрипта, шаг 1.5).
• Использовать схему stg для логической изоляции.

Предварительные условия¶

• Виртуальная машина создана и настроена (шаг 0.2).
• РЕД ОС установлена, сеть настроена (шаг 0.3).
• Диск /dev/sdb размечен с помощью gdisk, отформатирован и смонтирован в /pgdata (шаг 0.2).
• PostgreSQL 17 Server установлен, инициализирован и запущен (шаг 1.1). Служба работает, пароль для пользователя postgres задан.

Пошаговое выполнение¶

Шаг 1.2.1: Подключение к PostgreSQL¶

Войдите на сервер под пользователем root или через sudo, затем переключитесь на пользователя postgres:

sudo -i -u postgres

Выполните проверку, что кластер доступен:

psql -c "SELECT version();"

Ожидаемый вывод: PostgreSQL 17.x on x86_64-redhat-linux-gnu...

Шаг 1.2.2: Создание базы данных dlh_db¶

Если база данных ещё не создана, выполните:

CREATE DATABASE dlh_db
  OWNER postgres
  ENCODING 'UTF8'
  LC_COLLATE 'ru_RU.UTF-8'
  LC_CTYPE 'ru_RU.UTF-8'
  TEMPLATE template0;

Примечание: База будет физически размещена в каталоге PGDATA (мы настроили его на /pgdata на шаге 1.1). Это гарантирует, что все данные хранятся на выделенном диске.

Шаг 1.2.3: Создание пользователя для ETL-процессов¶

Для безопасности создадим отдельного пользователя с ограниченными правами (без права создавать объекты):

CREATE USER dlh_user WITH ENCRYPTED PASSWORD 'your_strong_password';

Важно: замените 'your_strong_password' на сложный пароль и сохраните его – он потребуется в скрипте загрузки (шаг 1.5).

Шаг 1.2.4: Создание схемы stg¶

Подключитесь к базе dlh_db и создайте схему:

\c dlh_db
CREATE SCHEMA IF NOT EXISTS stg AUTHORIZATION postgres;

Шаг 1.2.5: Создание таблицы stg.table_1¶

Структура таблицы полностью соответствует исходной таблице table_1 из MS SQL Server: | Колонка | Тип | Описание | |—|—|—| | id | VARCHAR(16) | 16-значный уникальный идентификатор (не NULL) | | dt | TIMESTAMP | дата и время создания записи в источнике (не NULL) | | json_data | JSONB | неструктурированные данные в формате JSON | | col4 | TEXT | дополнительное поле | | col5 | TEXT | дополнительное поле |

Для максимальной скорости вставки таблица создаётся без индексов, без первичного ключа и без ограничений (кроме NOT NULL для обязательных полей).

CREATE TABLE stg.table_1 (
    id          VARCHAR(16)   NOT NULL,
    dt          TIMESTAMP     NOT NULL,
    json_data   JSONB,
    col4        TEXT,
    col5        TEXT
);

Важно: тип JSONB поддерживается «из коробки» в PostgreSQL и не требует установки дополнительных расширений. Это особенно важно, так как пакет postgresql17-contrib отсутствует в локальном репозитории.

Шаг 1.2.6: Настройка прав доступа¶

Предоставьте пользователю dlh_user минимально необходимые права для загрузки данных:

-- Доступ к схеме
GRANT USAGE ON SCHEMA stg TO dlh_user;

-- Права на таблицу (вставка, очистка, чтение)
GRANT INSERT, TRUNCATE, SELECT ON stg.table_1 TO dlh_user;

Лучшая практика: Не давайте права на изменение структуры таблицы (ALTER, DROP) – это снижает риск случайных изменений.

Шаг 1.2.7: Дополнительные оптимизации для ускорения загрузки¶

Учитывая, что STG перезаписывается при каждой загрузке, рекомендуется:

• Отключить автовакуум для таблицы (чтобы избежать лишней нагрузки):

  ALTER TABLE stg.table_1 SET (autovacuum_enabled = false);
  

  
  

• Перевести таблицу в режим UNLOGGED – это ускоряет вставку за счёт отказа от записи в WAL. Поскольку данные в STG временные (последние 30 суток), потеря при сбое допустима. На этапе MVP (без WAL-архивации) это безопасно:

  ALTER TABLE stg.table_1 SET UNLOGGED;
  

Внимание: Если в будущем вы включите репликацию или WAL-архивацию, UNLOGGED-таблицы не будут реплицироваться. На этапе MVP это приемлемо.

Шаг 1.2.8: Проверка создания таблицы¶

Подключитесь к базе и выполните:

\c dlh_db
\d stg.table_1

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

Шаг 1.2.9: Тестовая вставка¶

Вставьте одну строку для проверки работоспособности:

INSERT INTO stg.table_1 (id, dt, json_data, col4, col5)
VALUES ('TEST000000000001', NOW(), '{"test": true}', 'sample', 'data');

Выполните выборку:

SELECT * FROM stg.table_1;

Убедитесь, что данные возвращаются корректно.

Полный SQL-скрипт для выполнения шага 1.2¶

Сохраните содержимое в файл create_staging.sql и выполните его через psql -U postgres -f create_staging.sql (или скопируйте и выполните в интерактивном режиме).

-- Создание базы данных (если не существует)
CREATE DATABASE dlh_db
  OWNER postgres
  ENCODING 'UTF8'
  LC_COLLATE 'ru_RU.UTF-8'
  LC_CTYPE 'ru_RU.UTF-8'
  TEMPLATE template0;

-- Подключение к базе
\c dlh_db

-- Создание пользователя для ETL
CREATE USER dlh_user WITH ENCRYPTED PASSWORD 'your_strong_password';

-- Создание схемы
CREATE SCHEMA IF NOT EXISTS stg AUTHORIZATION postgres;

-- Создание таблицы STG
CREATE TABLE stg.table_1 (
    id          VARCHAR(16)   NOT NULL,
    dt          TIMESTAMP     NOT NULL,
    json_data   JSONB,
    col4        TEXT,
    col5        TEXT
);

-- Отключение автовакуума (ускоряет загрузку)
ALTER TABLE stg.table_1 SET (autovacuum_enabled = false);

-- (Опционально) Сделать таблицу UNLOGGED для повышения скорости вставки
ALTER TABLE stg.table_1 SET UNLOGGED;

-- Настройка прав доступа
GRANT USAGE ON SCHEMA stg TO dlh_user;
GRANT INSERT, TRUNCATE, SELECT ON stg.table_1 TO dlh_user;

-- Проверка
INSERT INTO stg.table_1 (id, dt, json_data, col4, col5)
VALUES ('TEST000000000001', NOW(), '{"test": true}', 'sample', 'data');

SELECT * FROM stg.table_1;

Учёт ограничений проекта¶

Лучшие мировые практики, применённые в шаге¶

• Разделение прав – создан отдельный пользователь для ETL, что повышает безопасность.
• Отказ от индексов и ограничений – таблица STG служит только для временного хранения, индексы замедляют вставку.
• Использование JSONB – эффективное хранение и возможность индексирования в будущем (если потребуется).
• Настройка параметров хранения – отключение автовакуума и режим UNLOGGED снижают накладные расходы на этапе MVP.
• Проверка работоспособности – тестовая вставка гарантирует, что таблица создана корректно.

Связь с последующими шагами¶

• Шаг 1.3 – будет создан слой RAW с партиционированием по кварталам, используя ту же структуру, но с табличным пространством.
• Шаг 1.4 – создание табличных пространств и подготовка LVM-томов для партиций (на этом шаге мы будем использовать gdisk для выделения томов, но диск уже размечен, поэтому мы создадим табличные пространства внутри /pgdata).
• Шаг 1.5 – ETL-скрипт будет загружать данные в STG, затем переносить их в RAW.

Итоговый чек-лист шага 1.2¶

Если на каком-либо шаге возникнут трудности (например, с правами доступа или паролями), обратитесь к логам PostgreSQL (/pgdata/17/data/log/ или через journalctl -u postgresql-17).

Подробная инструкция по шагу 1.3: Создание слоя RAW с партиционированием по кварталам¶

Технологический стек шага¶

Цель шага¶

Создать слой RAW DATA LAKE – долгосрочное, неизменяемое хранилище сырых данных. Данные будут партиционироваться по кварталам (3 месяца) на основе поля dt (время создания записи в источнике). Каждая партиция физически размещается в отдельном табличном пространстве, которое соответствует отдельному LVM-тому (или каталогу на основном томе, если отдельный том не создан). В рамках данного шага мы:

• Убедимся, что для каждого квартала существует отдельный том.
• Создадим табличные пространства для всех кварталов.
• Создадим схему raw и партиционированную таблицу-родитель.
• Создадим партиции для Q2–Q4 2026 года.
• Настроим права доступа для пользователя dlh_user.

Примечание: на предыдущем шаге (0.2) были созданы LVM-тома для Q2, Q3, Q4 в группе томов vg_data.

Предварительные условия¶

• Диск /dev/sdb разделён на разделы, создана группа томов vg_data и логические тома для Q2, Q3, Q4 (см. шаг 0.2).
• Каталоги /mnt/raw_q2_2026, /mnt/raw_q3_2026, /mnt/raw_q4_2026 созданы и смонтированы.
• PostgreSQL 17 установлен, служба запущена, база dlh_db и слой stg созданы (шаги 1.1–1.2).
• Пользователь dlh_user существует и имеет права на схему stg.

Пошаговое выполнение¶

Шаг 1.3.1: Создание слоя RAW¶

Для автоматизации создания слоя RAW «с нуля» рекомендуется использовать файлы create_raw.sh. Также рекомендуется использовать файл drop_raw.sh для автоматизации удаления слоя RAW в случае, например, потребности его пересоздания. Это полезно при первичном создании слоя и отладки его функционирования.

Шаг 1.3.2: Тестирование вставки (проверка маршрутизации)¶

Вставьте тестовую запись с датой, попадающей в Q2, и убедитесь, что она попала в нужную партицию:

INSERT INTO raw.table_1 (id, dt, json_data, col4, col5)
VALUES ('TEST_Q2_001', '2026-02-15 10:00:00', '{"test": "q2"}', 'test', 'data');

SELECT tableoid::regclass AS partition, * FROM raw.table_1 WHERE id = 'TEST_Q2_001';

Ожидаемый результат: partition = raw.table_1_q2_2026.

Учёт ограничений проекта¶

Лучшие мировые практики, применённые в шаге¶

• Партиционирование по бизнес-времени – естественный способ управления большими объёмами данных, ускоряет запросы и упрощает архивацию.
• Размещение партиций на отдельных томах – позволяет независимо управлять каждым томом (архивировать, отключать, переносить).
• Отказ от индексов на этапе MVP – снижает нагрузку при загрузке; индексы можно добавить позже при необходимости.
• Использование табличных пространств – обеспечивает гибкость и соответствие требованиям ТЗ.
• Комментирование объектов – упрощает поддержку и трассировку данных.

Связь с последующими шагами¶

• Шаг 1.4 (подготовка томов и табличных пространств) – в нашей реализации уже выполнен на шаге 0.2 и дополнен сейчас. При добавлении новых кварталов потребуется повторить создание томов и табличных пространств.
• Шаг 1.5 (ETL-скрипт) – будет загружать данные из MS SQL в stg.table_1, затем выполнять проверку целостности и вставлять данные в raw.table_1 (автоматическое распределение по партициям).
• Шаг 2.2 (переход на Airflow) – DAG будет использовать те же таблицы и партиции.

Итоговый чек-лист шага 1.3¶

Если на каком-либо шаге возникнут проблемы (например, с правами доступа к каталогам или недостатком места в группе томов), проверьте логи и состояние LVM.

Подробная инструкция по шагу 1.4: Управление жизненным циклом партиций (архивация и восстановление)¶

Технологический стек шага¶

Цель шага¶

Разработать скрипты для полного удаления квартальной партиции (включая объект БД, табличное пространство, LVM-том и точку монтирования) и последующего восстановления из архива. Это необходимо для:

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

Ключевое требование: после выполнения архивации с флагом --remove-lv в системе не должно оставаться следов партиции, которые могли бы вызвать ошибки при запросах к raw.table_1.

Предварительные условия¶

• Шаг 1.3 выполнен: созданы табличные пространства ts_raw_q2_2026, ts_raw_q3_2026, ts_raw_q4_2026 и соответствующие партиции.
• LVM-тома lv_raw_q2_2026, lv_raw_q3_2026, lv_raw_q4_2026 смонтированы в /mnt/raw_q2_2026, /mnt/raw_q3_2026, /mnt/raw_q4_2026.
• PostgreSQL работает, база dlh_db и слой raw существуют.
• Каталоги /backup и /var/log/dlh смонтированы.

Пошаговое выполнение¶

Шаг 1.4.1: Полный жизненный цикл квартальных партиций от архивации и разархивирования до создания новых партиций¶

В целях автоматизации полного жизненного цикла партиций слоя RAW рекомендуется использовать файл archive_raw_partition.sh для архивации партиций слоя RAW, файл restore_raw_partition.sh для восстановления партиции слоя RAW из архива, а также файл create_new_partition.sh для создания новых партиций слоя RAW.

Шаг 1.4.2: Тестирование полного цикла¶

1. Архивация с удалением тома (например, для Q2 2026):

   sudo /opt/dlh/scripts/archive_raw_partition.sh q2_2026 --remove-lv
   

   Проверьте:

   • Партиция raw.table_1_q2_2026 отсутствует (запрос \d raw.table_1_q2_2026 в psql выдаст ошибку).
   • Табличное пространство ts_raw_q2_2026 удалено.
   • Том /dev/vg_data/lv_raw_q2_2026 удалён (команда lvdisplay не показывает его).
   • Каталог /mnt/raw_q2_2026 удалён.
   • Запись из /etc/fstab удалена.
   • Архив создан в /backup/raw_archives/.

2. Запрос к raw.table_1 не должен вызывать ошибок, так как партиция отключена и удалена:

   sudo -u postgres psql -d dlh_db -c "SELECT * FROM raw.table_1 LIMIT 1;"
   

3. Восстановление:

   sudo /opt/dlh/scripts/restore_raw_partition.sh q2_2026 /backup/raw_archives/raw_q2_2026_*.tar.gz
   

   Проверьте:

   • Том создан, смонтирован, данные распакованы.
   • Табличное пространство создано.
   • Партиция создана и присоединена.
   • Запрос SELECT * FROM raw.table_1 WHERE dt BETWEEN '2026-04-01' AND '2026-06-30' возвращает данные.

Шаг 1.4.3: Настройка логов и ротации¶

Для всех скриптов логи пишутся в /var/log/dlh/scripts/. Настройте ротацию логов, чтобы избежать переполнения диска:

sudo nano /etc/logrotate.d/dlh-scripts

Добавьте:

/var/log/dlh/scripts/*.log {
    daily
    rotate 30
    compress
    missingok
    notifempty
    create 0640 root root
}

Учёт ограничений проекта¶

Лучшие мировые практики¶

• Полное удаление объектов БД перед удалением табличного пространства и тома – исключает ошибки «не удалось отправить файл».
• Архивация в режиме read-only – гарантирует целостность архива.
• Восстановление через создание заново – воссоздаёт все объекты в актуальном состоянии.
• Логирование – упрощает отладку и аудит.
• Параметризация скриптов – универсальность для любых кварталов.

Итоговый чек-лист шага 1.4¶

Если на каком-либо шаге возникнут проблемы, проверьте логи в /var/log/dlh/scripts/.