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

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

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

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

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

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

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

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

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. Удалите все шарды до текущего года.

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 id not in (select distinct attached_file_id from agent_event where attached_file_id IS NOT NULL) and guid = '';

delete from analytics_session where local_time < '2023-01-01 00:00:00';
delete from analytic_filters_events where not exists (select 1 from agent_event where agent_event.id=analytics_filter_events.event_id);

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

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 id not in (select distinct attached_file_id from agent_event where attached_file_id IS NOT NULL) and guid = '';

delete from analytics_session where local_time < '2023-01-01 00:00:00';
delete from analytic_filters_events where not exists (select 1 from agent_event where agent_event.id=analytics_filter_events.event_id);

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

ANALYZE agent_event

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

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

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

Итоги работы

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

• две базы данных (основная и архивная), доступные для выбора при входе в веб-интерфейс;

• возможность работы Clickhouse с обеими базами данных;

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