Реализация архивирования старых данных базы данных

Реализация архивирования старых данных базы данных

Таблица events с 500 миллионами строк и ростом 2 миллиона в сутки — это проблема, которая становится острее каждый день. SELECT тормозит, VACUUM не успевает, индексы занимают гигабайты. Архивирование решает задачу: горячие данные остаются в основной базе, холодные переезжают в архив с другим режимом хранения.

Стратегии архивирования

Partition detach — если таблица партиционирована, старые партиции отсоединяются и переносятся в архивную базу или tablespace. Самый быстрый подход.

INSERT + DELETE батчами — для непартиционированных таблиц. Копируем строки в архивную таблицу батчами, удаляем из основной. Не создаёт длинных транзакций.

Логическая реплика — настраиваем publication на основной базе, subscription на архивной, с фильтром по дате. Архив обновляется в реальном времени.

Dump + truncate — экспорт в CSV/parquet, удаление из базы. Данные больше не в PostgreSQL/MySQL — только в файловом архиве.

PostgreSQL: батчевое перемещение

-- Архивная таблица (может быть в отдельной схеме или базе)
CREATE TABLE archive.events (
    LIKE public.events INCLUDING ALL
);

-- Функция архивирования с батчами
CREATE OR REPLACE FUNCTION archive_old_events(
    p_before_date  TIMESTAMPTZ,
    p_batch_size   INTEGER DEFAULT 10000
) RETURNS TABLE(batches_processed INTEGER, rows_archived BIGINT)
LANGUAGE plpgsql AS $$
DECLARE
    v_batches  INTEGER := 0;
    v_total    BIGINT  := 0;
    v_moved    INTEGER;
BEGIN
    LOOP
        -- Переносим один батч в архив
        WITH moved AS (
            DELETE FROM public.events
            WHERE id IN (
                SELECT id FROM public.events
                WHERE created_at < p_before_date
                LIMIT p_batch_size
                FOR UPDATE SKIP LOCKED  -- пропускаем заблокированные строки
            )
            RETURNING *
        )
        INSERT INTO archive.events SELECT * FROM moved;

        GET DIAGNOSTICS v_moved = ROW_COUNT;
        EXIT WHEN v_moved = 0;

        v_batches := v_batches + 1;
        v_total   := v_total + v_moved;

        -- Пауза между батчами — не перегружаем диск
        PERFORM pg_sleep(0.1);

        -- Прогресс
        RAISE NOTICE 'Batch %: % rows archived (total: %)', v_batches, v_moved, v_total;
    END LOOP;

    RETURN QUERY SELECT v_batches, v_total;
END $$;

Запуск:

SELECT * FROM archive_old_events('2024-01-01'::timestamptz, 10000);
-- Batch 1: 10000 rows archived (total: 10000)
-- Batch 2: 10000 rows archived (total: 20000)
-- ...

SKIP LOCKED позволяет запускать несколько параллельных воркеров без конфликтов.

Планировщик: Artisan команда

// app/Console/Commands/ArchiveOldData.php
class ArchiveOldData extends Command
{
    protected $signature   = 'db:archive {--days=365 : Архивировать данные старше N дней}';
    protected $description = 'Archive old records to archive tables';

    public function handle(): int
    {
        $beforeDate = now()->subDays($this->option('days'))->toDateTimeString();

        $this->info("Archiving events before {$beforeDate}...");

        $result = DB::selectOne(
            'SELECT * FROM archive_old_events(?::timestamptz, 5000)',
            [$beforeDate]
        );

        $this->info("Done: {$result->batches_processed} batches, {$result->rows_archived} rows");

        // VACUUM после массового удаления
        DB::statement('VACUUM ANALYZE events');

        return self::SUCCESS;
    }
}

// app/Console/Kernel.php
$schedule->command('db:archive --days=180')
    ->monthlyOn(1, '02:00')  // первое число каждого месяца в 2:00
    ->withoutOverlapping()
    ->onFailure(fn() => Notification::route('telegram', config('services.telegram.ops_chat'))
        ->notify(new ArchivingFailedNotification()));

MySQL: архивирование с pt-archiver

Percona Toolkit предоставляет pt-archiver — инструмент для переноса строк между таблицами или в файл:

# Перенос в архивную таблицу
pt-archiver \
  --source  h=localhost,D=mydb,t=events,u=root,p=password \
  --dest    h=localhost,D=archive_db,t=events,u=root,p=password \
  --where   "created_at < '2024-01-01'" \
  --limit   5000 \
  --sleep   0.1 \
  --no-delete  # сначала только копируем; после проверки добавить --purge

# С удалением из источника
pt-archiver \
  --source  h=localhost,D=mydb,t=events \
  --where   "created_at < '2024-01-01'" \
  --limit   5000 \
  --sleep   0.1 \
  --purge  # удалять после копирования

# Только удаление без архива (если данные уже перенесены)
pt-archiver \
  --source  h=localhost,D=mydb,t=events \
  --where   "created_at < '2024-01-01'" \
  --limit   10000 \
  --purge \
  --no-safe-auto-increment

Архивирование через PostgreSQL COPY в файл

Для долгосрочного хранения без доступа через SQL:

#!/bin/bash
# /opt/scripts/archive_events.sh

YEAR_MONTH=$1  # например: 2024-01
DB=mydb
ARCHIVE_DIR=/mnt/archive/events

mkdir -p "${ARCHIVE_DIR}/${YEAR_MONTH}"

# Экспорт в gzip CSV
psql -d "$DB" -c \
  "COPY (SELECT * FROM events WHERE created_at >= '${YEAR_MONTH}-01' AND created_at < '$(date -d "${YEAR_MONTH}-01 +1 month" +%Y-%m-%d)') TO STDOUT CSV HEADER" \
  | gzip > "${ARCHIVE_DIR}/${YEAR_MONTH}/events_${YEAR_MONTH}.csv.gz"

echo "Exported: $(stat -c%s ${ARCHIVE_DIR}/${YEAR_MONTH}/events_${YEAR_MONTH}.csv.gz) bytes"

# Проверяем, что файл не пустой
if [ $(stat -c%s "${ARCHIVE_DIR}/${YEAR_MONTH}/events_${YEAR_MONTH}.csv.gz") -gt 1000 ]; then
    psql -d "$DB" -c \
      "DELETE FROM events WHERE created_at >= '${YEAR_MONTH}-01' AND created_at < '$(date -d "${YEAR_MONTH}-01 +1 month" +%Y-%m-%d)'"
    echo "Deleted from events table"
else
    echo "ERROR: archive file too small, skipping delete"
    exit 1
fi

Архивная база на отдельном хранилище

Архивные данные можно хранить в отдельной PostgreSQL с включённым сжатием:

-- На архивном сервере: включить сжатие страниц (PostgreSQL 14+ с pglz)
-- Или использовать TOAST компрессию для JSONB/TEXT

-- Tablespace на дешёвом HDD или NFS
CREATE TABLESPACE archive_ts LOCATION '/mnt/slow_storage/pgsql';
CREATE TABLE archive.events TABLESPACE archive_ts (LIKE public.events);

Политика хранения данных

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

Политика согласовывается с требованиями бизнеса и регулятора (GDPR, 152-ФЗ).

Восстановление из архива

# Восстановить данные из CSV-архива обратно в базу
gunzip -c /mnt/archive/events/2024-01/events_2024-01.csv.gz | \
  psql -d mydb -c "COPY events FROM STDIN CSV HEADER"

Из партиционированной архивной таблицы — ATTACH PARTITION к основной таблице без копирования данных.

Сроки реализации

Базовое архивирование батчами с Artisan-командой и расписанием: 1–2 дня. Полная система с мониторингом, политиками хранения, восстановлением из архива и обработкой ошибок: 3–5 дней.