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

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

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

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

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

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

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

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

Примечание

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

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

Убедитесь, что на вашем диске есть около 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 <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 <кол-во шардов> -о DESC

DESC значит, что вы удаляете данные в порядке возрастания с первого месяца текущего года. Например, если вы создаете архив в середине года, укажите -n 6 -o DESC.

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

./bin/db/experimental/cleanup_archive_data

Применение изменений

Перезапустите Staffcop:

sudo staffcop reinit

Перенесите данные в ClickHouse из PostgreSQL:

OVERRIDE_DBNAME=<архивная бд> staffcop clickhouse reinit
OVERRIDE_DBNAME=<архивная бд> staffcop clickhouse pump

Готово! При входе в 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

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

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