Create archive database

In case you notice that performance of the StaffCop Server reduces as data accumulates, it is recommended to make an archive of the data as a separate database. At the same time, the archived database may still be available via the GUI for viewing and analyzing data. At the present moment, automatizing the archiving process does not seem reasonable, and the process itself consists of several simple steps, described below. This article gives the guide for creating an archive at the level of a single Postgres instance by organizing an additional TABLESPACE, which can be located on a separate drive, therefor minimizing the effect on the core server performance. It is also possible to migrate the archive to a separate (external) Postgres instance, in this case you need to use the pg_dump / pg_restore utilities for the database migration. The ending result of the actions described below is the ability to select a database when entering StaffCop.

Resources preparation

Before creating a backup, you must make sure that there is enough free space. It is recommended to have a separate disk to store the archive, which will improve performance and simplify further administration and adding new archives. If the future main database and archive database are located on the same disk, then it is desirable to have free space in the amount of at least 200% of the size of the current database.

Before performing this operation, it is necessary to transfer all agents to the configuration with modules completely disabled and wait until the agents apply these configurations. Then you need to stop the server:

sudo service staffcop stop
sudo service nginx stop

After that you can perform vacuum full, if you have doubts about the availability of sufficient disk space (the size of the base can be reduced several times).

Creating TABLESPACE and DATABASE

First we create TABLESPACE, as already mentioned, preferably on a separate drive. To do this, create a folder with access rights for postgres and a symlink to it from the working folder postgresql:

sudo mkdir /mnt/separate_disk/archive_2017_05
sudo chmod 700 /mnt/separate_disk/archive_2017_05
sudo chown postgres:postgres /mnt/separate_disk/archive_2017_05
cd /var/lib/posgtresql/9.6
sudo ln -s /mnt/separate_disk/archive_2017_05 archive_2017_05

When choosing name for the folder, TABLESPACE and database, I recommend using a friendly name based on a date, such as archive_2017_05. Next, create a TABLESPACE:

CREATE TABLESPACE archive_2017_05 LOCATION '/var/lib/postgresql/9.6/archive_2017_05';

Now copy the current database to the archive. Here another way can be chosen, for example, migrating the current database with further renaming, but we will consider the following:

CREATE DATABASE archive_2017_05 TEMPLATE staffcop TABLESPACE archive_2017_05;

Here we have created a new database by copying the old one. Now we can remove all unnecessary information until a certain date from the current staffcop database. First the facts:

delete from agent_event where local_time <'2017-06-01';

Or, if you want to delete all the events:

truncate table agent_event cascade;

Then clear the dimensions:

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 id not in (select distinct app_icon_id from agent_application where app_icon_id is not null);

In case events haven not been completely removed, rebuild the session table:

sudo staffcop reset_sessions

Create a configuration file with database settings.

In the /usr/share/staffcop folder there is a file (of symlink) local_settings.py (in some cases there is database_config.py, which has priority over local_settings.py), something like the following:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'staffcop',
        'USER': 'staffcop',
        'PASSWORD': 'xxxxxxxxxxxxxxxxxxxxxxxxxxx',
        'HOST': '',
        'PORT': '',
    }
}

We need to make it look like:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'staffcop',
        'USER': 'staffcop',
        'PASSWORD': 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
        'HOST': '',
        'PORT': '',
    },
    'archive_2017_05': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'archive_2017_05',
        'USER': 'staffcop',
        'PASSWORD': 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
        'HOST': '',
        'PORT': '',
    }
}

Add a section, change the name of the database which will be displayed at the entrance (default on archive_2017_05), as well as the real name (staffcop on archive_2017_05). In addition, the remaining parameters - HOST, PORT if different from the current server, if there was a migration to another instance, password, if the user password of the database was changed (if everything was done in the steps of this article, it did not change).

Then we start the server (staffcop start and nginx start) and open the web interface. If everything have been done correctly, then we will see the choice of the database in the login form , default on archive_2017_05.

Note

It should be understood that no changes, recalculations of reports, filters, etc. are possible in the archive database created this way. All staffcop administrative scripts will ignore it. The data itself (filters, configs) can be changed, but the changes will have no effect, so recalculations of sessions and filters, if any, are needed before archiving.

You should also to keep in mind that the “Backup” filter is also not applicable to the archive database.