Создание архивной базы данных¶

В случае ухудшения работы сервера Staffcop Enterprise из-за большого объёма накопленных данных, можно разделить текущую базу данных на две: актуальную и архивную. В актуальной можно оставить данные за текущий календарный год, а в архивную поместить данные за всё предыдущее время работы.

Примечание

Начиная с версии 5.6 скрипт поддерживает создание архивной БД для основной БД, расположенной на другом хосте. См. параметр [DATABASE HOST] в п. 6 инструкции.

Итогом выполнения всех операций данной инструкции станут:

две базы данных:
- архивная, в которой будут содержаться данные с года хххх до прошлого года включительно;
- текущая, в которой будут храниться данные за текущий год;
возможность выбора базы данных для просмотра.

Предупреждение

Перед созданием архивной базы данных сделайте бэкап БД. Резервное копирование гарантирует восстановление данных при потере.

Предупреждение

Перед архивированием БД закройте старые инциденты или отключите защиту инцидентов. Если защита от удаления включена и есть открытые инциденты за текущий год, связанные с этими инцидентами шарды из архивной БД не удалятся.

Создать архив данных можно двумя способами:

Скриптом.
Командами.

Примечание

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

Подготовка к архивации

Убедитесь, что на вашем диске есть около 150% свободного места от размера текущей базы данных.

Примечание

При недостатке доступного пространства воспользуйтесь скриптом compact из п.4 инструкции в соседней вкладке.

Если вы используете ClickHouse, создайте в нём базу данных ClickHouse по инструкции из раздела Создание архивной базы данных в соседней вкладке.
Создайте папку для архива с правами доступа для PostgreSQL. Желательно сделать это на отдельном носителе:

sudo mkdir /mnt/separate_disk/<archive_name>
sudo chmod 700 /mnt/separate_disk/<archive_name>
sudo chown postgres:postgres /mnt/separate_disk/<archive_name>
где <archive_name> – имя архивной базы данных.

Примечание

Используйте для папок и баз данных простые имена, основанные на датах архивов. Например, archive_2022.

Создайте папку archives, если она отсутствует:

sudo mkdir /var/lib/staffcop/archives

Создайте символическую ссылку на новую папку:

cd /var/lib/staffcop/archives
sudo ln -s /mnt/separate_disk/<archive_name> <archive_name>

Создайте новое табличное пространство и скопируйте в него базу данных:

bash /usr/share/staffcop/bin/db/experimental/copy_db_to_tablespace <ARCHIVE NAME> [DATABASE HOST] [--remote]
где:

<ARCHIVE NAME> — имя новой архивной базы данных;

[DATABASE HOST] — укажите этот параметр, только если база данных находится на другом хосте.

[--remote]— флаг, который указывает на необходимость удаленного подключения к БД.

Примечание

Скрипт экспериментальный и требует внимания оператора. По окончанию работы будет предложено создать архивную БД Clickhouse. Этот процесс выполняется долго, но уже не требует внимания. Копирование базы 13 ГБ занимает 39 минут, копирование базы 3,3 ТБ занимает 4 часа.

Удаление устаревших данных

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

bash /usr/share/staffcop/bin/db/experimental/drop_shards -d <MAIN DB NAME> [-n 100 (default 3)] [-o ASC (default)] -md true
где:

-d указывает на базу данных, из которой нужно удалить данные;

-n определяет количество шардов, значение по умолчанию — 3;

-o указывает порядок удаления шардов. ASC применяет порядок по возрастанию от самых старых данных до начала текущего года (значение по умолчанию);

-md при указании true отмечает указанную в параметре -d базу данных как основную, что влияет на получение списка шардов для удаления (по умолчанию false);

Пример: чтобы стереть данные за два предыдущих года, укажите -n 24 -md true. Порядок ASC применяется автоматически.

Удалите шарды текущего года из архивной базы данных:

bash /usr/share/staffcop/bin/db/experimental/drop_shards -d <ARCHIVE NAME> [-n 100 (default 3)] -o DESC
где -o DESC означает, что вы удаляете данные в порядке убывания с последнего по первый месяц текущего года. Например, чтобы удалить все шарды текущего года, укажите -n 12 -o DESC.

Удалите данные из PostgreSQL и ClickHouse архивной и основной БД:

./bin/db/experimental/cleanup_archive_data
Готово! При входе в Staffcop вы увидите новый архив в списке баз данных:

Подготовка к архивации

Убедитесь, что на диске (отдельном диске) есть достаточное количество свободного места — около 150% от текущей базы данных.

Примечание

Рекомендуем хранить архивную базу данных на отдельном диске. Это упростит работу с ней и ускорит работу с основной базой.
Остановите сервисы Staffcop:

sudo service staffcop stop
sudo service nginx stop

Проверьте, что сервисы остановились. Для этого выполните:

staffcop top
staffcop ps

Ни одна из этих команд не должна выводить список процессов.

Если есть сомнения в доступном количестве свободного места:

4.1 Откройте файл скрипта compact, являющегося частью Staffcop:
sudo nano /usr/share/staffcop/bin/compact
4.2 В открывшемся файле раскомментируйте строки echo "Vacuum database" и sudo -u postgres psql staffcop -c "vacuum full". Итоговый файл будет иметь следующий вид:
#!/bin/bash # USE compact # HELP Cleanup unreffered dimensions and vacuum database staffcop stop echo "Cleanup dimensions" cat sql/cleanup_dimensions.sql | bin/sql echo "Vacuum database" sudo -u postgres psql staffcop -c "vacuum full" staffcop start echo "Cleanup done" bin/info
4.3 Сохраните изменения.

4.4 После завершения очистки базы запустите:
staffcop compact
Примечание

Эта команда существенно уменьшит объём памяти, который занимает база данных.

Создание архивной базы данных

Примечание

Если вы не используете ClickHouse, то переходите к пункту Создание новой базы данных.

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

Зайдите в ClickHouse:

staffcop clickhouse

Переименуйте текущую базу данных:

RENAME DATABASE staffcop TO archive_2022;

Здесь *archive_2022* — имя архивной базы данных.

Создайте новую основную базу данных в ClickHouse:

staffcop clickhouse reinit

После этого переходите к созданию новой базы данных.

Создание новой базы данных

Примечание

Для удобства работы в качестве имени новой базы и TABLESPACE используйте простые имена, основанные на датах архивов. Например: archive_2022.

Создайте папку с правами доступа для PostgreSQL. Желательно сделать это на отдельном носителе:

sudo mkdir /mnt/separate_disk/archive_2022
sudo chmod 700 /mnt/separate_disk/archive_2022
sudo chown postgres:postgres /mnt/separate_disk/archive_2022

Создайте папку archives, если она отсутствует:

sudo mkdir /var/lib/staffcop/archives

Создайте символическую ссылку на новую папку:

cd /var/lib/staffcop/archives
sudo ln -s /mnt/separate_disk/archive_2022 archive_2022

Создайте TABLESPACE. Для этого войдите в консоль управления PostgreSQL:

sudo -u postgres psql

и выполните команду:

CREATE TABLESPACE archive_2022 LOCATION '/var/lib/staffcop/archives/archive_2022';

Скопируйте текущую базу в архивную:

CREATE DATABASE archive_2022 TEMPLATE staffcop TABLESPACE archive_2022;

Удалите шарды из таблицы до текущего года. Чтобы открыть список шардов, выйдите из PostgreSQL:

\q
Откройте список:
staffcop shard list
Вернитесь в PostgreSQL:
sudo -u postgres psql -d staffcop
или
staffcop sql
Удалите шарды, подставив название в <название таблицы>:
drop table <название таблицы>;

Теперь текущая база полностью скопирована в архивную. Удалите из текущей базы Staffcop всё, что было перенесено в архив:

delete from agent_event where local_time < '2023-01-01 00:00:00'; # в нашем случае оставляем только 2023-й год
# Очистите измерения
delete from agent_web where id not in (select distinct web_data_id from agent_event where web_data_id IS NOT NULL);
delete from agent_time where id not in (select distinct time_id from agent_event where time_id IS NOT NULL);
delete from agent_account where id not in (select distinct account_id from agent_event where account_id IS NOT NULL);
delete from agent_appinstallation where id not in (select distinct app_installation_id from agent_event where app_installation_id IS NOT NULL);
delete from agent_application where id not in (select distinct application_id from agent_event where application_id IS NOT NULL);
delete from agent_networkconnection where id not in (select distinct net_data_id from agent_event where net_data_id IS NOT NULL);
delete from agent_dialog where id not in (select distinct dialog_id from agent_event where dialog_id IS NOT NULL);
delete from agent_device where id not in (select distinct device_id from agent_event where device_id IS NOT NULL);
delete from agent_attachedfile where not exists (select 1 from agent_event where agent_event.attached_file_id=agent_attachedfile.id) and agent_attachedfile.category is null;
delete from agent_filepath where not exists (select 1 from agent_event where agent_event.file_path_id = agent_filepath.id);
delete from analytics_session where local_time < '2023-01-01 00:00:00';
delete from analytics_filter_events where not exists (select 1 from agent_event where agent_event.id=analytics_filter_events.event_id);

# Выйдите из PostgreSQL
\q

# Завершите сессии в Staffcop
staffcop reset_extended_sessions

Вернитесь в PostgreSQL с помощью одной из команд:

sudo -u postgres psql -d staffcop

или

staffcop sql

Оптимизируйте работу базы (процедура может занять много времени):

ANALYZE;

Добавьте архивную базу данных в конфигурационный файл etc/staffcop/config в секцию DATABASES:

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'staffcop',
'USER': 'staffcop',
'PASSWORD': 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
'HOST': '',
'PORT': '',
},
'archive_2022': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'archive_2022',
'USER': 'staffcop',
'PASSWORD': 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', # пароль такой же, как у базы данных default
'HOST': '',
'PORT': '',
   }
 }

Готово! Теперь можно проверять, что перенос прошёл успешно.

Для этого запустите сервер Staffcop:

staffcop start

Откройте веб-интерфейс сервера. Если всё прошло успешно, в форме входа можно будет выбрать базу данных.

Окончательное оформление архивной базы данных

После проверки доступности обеих баз, закончите оформление архивной базы данных. Для этого:

Остановите сервисы Staffcop:

sudo service staffcop stop
sudo service nginx stop

Проверьте, что сервисы остановились. Для этого выполните:

staffcop top
staffcop ps
Ни одна из этих команд не должна выводить список процессов.

Очистите архивную базу данных PostgreSQL от лишних данных:

staffcop sql
staffcop=>\c archive_2022

Вы подключены к базе данных "archive_2022" как пользователь "staffcop".
archive_2022=>
Предупреждение

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

Удалите все шарды текущего года с помощью команды:

drop table <название таблицы>;

Удалите данные:

delete from agent_event where local_time >= '2023-01-01 00:00:00'; # Убираем 2023-й год
# Очищаем измерения
delete from agent_web where id not in (select distinct web_data_id from agent_event where web_data_id IS NOT NULL);
delete from agent_time where id not in (select distinct time_id from agent_event where time_id IS NOT NULL);
delete from agent_account where id not in (select distinct account_id from agent_event where account_id IS NOT NULL);
delete from agent_appinstallation where id not in (select distinct app_installation_id from agent_event where app_installation_id IS NOT NULL);
delete from agent_application where id not in (select distinct application_id from agent_event where application_id IS NOT NULL);
delete from agent_networkconnection where id not in (select distinct net_data_id from agent_event where net_data_id IS NOT NULL);
delete from agent_dialog where id not in (select distinct dialog_id from agent_event where dialog_id IS NOT NULL);
delete from agent_device where id not in (select distinct device_id from agent_event where device_id IS NOT NULL);
delete from agent_attachedfile where not exists (select 1 from agent_event where agent_event.attached_file_id=agent_attachedfile.id) and agent_attachedfile.category is null;
delete from agent_filepath where not exists (select 1 from agent_event where agent_event.file_path_id = agent_filepath.id);
delete from analytics_session where local_time >= '2023-01-01 00:00:00';
delete from analytics_session_extended where local_time >= '2023-01-01 00:00:00';
delete from analytics_filter_events where not exists (select 1 from agent_event where agent_event.id=analytics_filter_events.event_id);

Оптимизируйте работу базы (процедура может занять много времени):

ANALYZE;

Заполните данными архив ClickHouse:

OVERRIDE_DBNAME=archive_2022 staffcop clickhouse reinit
OVERRIDE_DBNAME=archive_2022 staffcop clickhouse pump

Готово! Можно запускать сервис Staffcop и проверять работу архивной базы данных.

Итоги работы¶

Итогом правильного выполнения всех операций станут:

основная и архивная базы данных, доступные для выбора при входе в веб-интерфейс;
возможность работы ClickHouse со всеми базами данных;
в основной базе данных будут выполняться политики, в архивной — нет.

Дата изменения: 10.07.2024