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

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

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

• две базы данных:

  • архивная, в которой будут содержаться данные с года хххх до 2022 включительно;

  • текущая, в которой будут храниться данные за 2023 год;

• возможность выбора базы данных для просмотра.

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

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

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

1. Командами. Требует ввода команд.

2. Скриптом. Автоматизирует процесс.

Примечание

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

Чтобы воспользоваться скриптом, перейдите в раздел Организация архива с помощью скрипта. Если вы предпочитаете команды, переходите к следующему разделу.

Организация архива с помощью команд

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

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

   Примечание

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

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

   sudo service staffcop stop
   sudo service nginx stop
   

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

   staffcop top
   staffcop ps
   

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

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

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 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/info

4.3 Сохраните изменения.

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

staffcop compact

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

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

Примечание

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

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

1. Зайдите в Сlickhouse

staffcop clickhouse

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

RENAME DATABASE staffcop TO archive_2022;

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

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

staffcop clickhouse reinit

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

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

Примечание

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

1. Создайте папку с правами доступа для 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

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

cd /var/lib/postgresql/11
sudo ln -s /mnt/separate_disk/archive_2022 archive_2022

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

sudo -u postgres psql

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

CREATE TABLESPACE archive_2022 LOCATION '/var/lib/postgresql/11/archive_2022';

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

CREATE DATABASE archive_2022 TEMPLATE staffcop TABLESPACE archive_2022;

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

\q

Откройте список:

staffcop shard list

Вернитесь в PostgreSQL:

sudo -u postgres psql -d staffcop

или

staffcop sql

Удалите шарды, подставив название в <название таблицы>:

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

6. Теперь текущая база полностью скопирована в архивную. Удалите из текущей базы 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

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

ANALYZE;

8. Добавьте архивную базу данных в конфигурационный файл 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

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

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

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

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

   sudo service staffcop stop
   sudo service nginx stop
   

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

   staffcop top
   staffcop ps
   

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

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

staffcop sql
staffcop=>\c archive_2022

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

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

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

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

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

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

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);

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

ANALYZE;

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

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

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

Организация архива с помощью скрипта

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

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

2. Если вы используете Clickhouse, создайте в нём базу данных.

3. Создайте папку для архива с правами доступа для 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.

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

cd /var/lib/postgresql/11
sudo ln -s /mnt/separate_disk/<archive_name> <archive_name>

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

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 со всеми базами данных;

• в основной базе данных будут выполняться политики, в архивной — нет.