Создание архивной базы данных¶
В случае ухудшения работы сервера Staffcop Enterprise из-за большого объёма накопленных данных, можно разделить текущую базу данных на две: актуальную и архивную. В актуальной можно оставить данные за текущий календарный год, а в архивную поместить данные за всё предыдущее время работы.
Итогом выполнения всех операций данной инструкции станут:
две базы данных:
архивная, в которой будут содержаться данные с года хххх до 2022 включительно;
текущая, в которой будут храниться данные за 2023 год;
возможность выбора базы данных для просмотра.
Предупреждение
Перед созданием архивной базы данных сделайте бэкап БД. Резервное копирование гарантирует восстановление данных при потере.
Создать архив данных можно двумя способами:
Командами. Требует ввода команд.
Скриптом. Автоматизирует процесс.
Примечание
Скрипт ускоряет работу, но находится на стадии тестирования и доработки. По всем вопросам обращайтесь в поддержку за последней информацией.
Чтобы воспользоваться скриптом, перейдите в раздел Организация архива с помощью скрипта. Если вы предпочитаете команды, переходите к следующему разделу.
Организация архива с помощью команд¶
Подготовка к архивации¶
Убедитесь, что на диске (отдельном диске) есть достаточное количество свободного места — около 150% от текущей базы данных.
Примечание
Рекомендуем хранить архивную базу данных на отдельном диске. Это упростит работу с ней и ускорит работу с основной базой.
Остановите сервисы Staffcop:
sudo service staffcop stop sudo service nginx stop
Проверьте, что сервисы остановились. Для этого выполните:
staffcop top staffcop ps
Ни одна из этих команд не должна выводить список процессов.
Если есть сомнения в доступном количестве свободного места:
4.1 Откройте файл скрипта compact, являющегося частью Staffcop:
sudo nano /usr/share/staffcop/bin/compact4.2 В открывшемся файле раскомментируйте строки echo «Vacuum database» и sudo -u postgres psql staffcop -c «vacuum full». Итоговый файл будет иметь следующий вид:
#!/bin/bash # USE compact # HELP Cleanup unreffered dimensions and vaccum 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/info4.3 Сохраните изменения.
4.4 После завершения очистки базы запустите:
staffcop compactЭта команда существенно уменьшит объём памяти, занимаемый базой данных.
Создание архивной базы данных¶
Примечание
Если вы не используете Clickhouse, то переходите к пункту Создание новой базы данных.
Чтобы не потерять данные в Clickhouse, выполните следующие действия:
Зайдите в Сlickhouse
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
Создайте символическую ссылку на новую папку из рабочей папки PostgreSQL:
cd /var/lib/postgresql/11 sudo ln -s /mnt/separate_disk/archive_2022 archive_2022
Создайте TABLESPACE. Для этого войдите в консоль управления PostgreSQL:
sudo -u postgres psqlи выполните команду:
CREATE TABLESPACE archive_2022 LOCATION '/var/lib/postgresql/11/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); staffcop reset_extended_sessions
Оптимизируйте работу базы (процедура может занять много времени):
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 и проверять работу архивной базы данных.
Организация архива с помощью скрипта¶
Подготовка к архивации¶
Убедитесь, что на вашем диске есть около 150% свободного места от размера текущей базы данных. Если есть сомнения относительно доступного пространства, воспользуйтесь скриптом compact.
Если вы используете 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.
Создайте символическую ссылку на новую папку из рабочей папки PostgreSQL:
cd /var/lib/postgresql/11
sudo ln -s /mnt/separate_disk/<archive_name> <archive_name>
Создайте новое табличное пространство и скопируйте в него базу данных:
bash /usr/share/staffcop/bin/db/experimental/copy_db_to_tablespace <tablespace_name>
где <tablespace_name> – имя базы данных.
Примечание
Копирование базы 13 ГБ занимает 39 минут, копирование базы 3,3 ТБ занимает 4 часа.
Удаление устаревших данных¶
Запустите скрипт, который удаляет из основной базы данных шарды (месяцы), предшествующие текущему году:
bash /usr/share/staffcop/bin/db/experimental/drop_shards -d <основная бд> -n <кол-во шардов> -o ASC
где:
-d указывает на базу данных, из которой нужно удалить данные;
-n определяет количество шардов;
-o ASC удаляет шарды в порядке убывания с начала текущего года. Например, для удаления данных двух предыдущих годов укажите -n 24 -o ASC.
В результате основная база данных будет содержать только те данные, которые вы хотите оставить.
Создание архива¶
Удалите шарды текущего года из архивной базы данных:
bash /usr/share/staffcop/bin/db/experimental/drop_shards -d <архивная бд> -n <кол-во шардов> -o DESC
DESC значит, что вы удаляете данные в порядке возрастания с первого месяца текущего года. Например, если вы создаете архив в середине года, укажите -n 6 -o ASC.
Применение изменений¶
Перезапустите Staffcop:
sudo staffcop reinit
Перенесите данные в Clickhouse из PostgreSQL:
OVERRIDE_DBNAME=<архивная бд> staffcop clickhouse reinit
OVERRIDE_DBNAME=<архивная бд> staffcop clickhouse pump
Готово! При входе в Staffcop вы увидите новый архив в списке баз данных:
Итоги работы¶
Итогом правильного выполнения всех операций станут:
основная и архивная базы данных, доступные для выбора при входе в веб-интерфейс;
возможность работы Clickhouse со всеми базами данных;
в основной базе данных будут выполняться политики, в архивной — нет.