Database maintenance

Daily automatic maintenance

By default one of the cronjobs installed for the user eranger is responsible for database housekeeping:

$ crontab -l | grep expire
0 4 * * * /opt/eranger/bin/eranger-server-expire

It reorganizes data daily by running the PostgreSLQ autovacuum function so data can be accessed faster.

The configuration is done in the SKOOR server configuration:

/etc/opt/eranger/eranger-server.cfg

Parameters with default values:

# Expire script config values
expire_days_long = 366
expire_days_short = 31
expire_row_limit=

Parameter name

Description

expire_days_long

Sets the number of days the following database entries are kept:

  • State history

  • Value history

  • Alarm messages

  • Alarm messages sent

  • Alarms

  • Change log

  • Maintenance (end time)

  • Users (last access)

  • Threshod prediction data

expire_days_short

Sets the number of days that job error messages are kept in the database. If omitted, it will be set to days long

expire_row_limit

Maximum rows to delete at once from the database for the following entries. If omitted, rows will be deleted with no limit:

  • Alarm messages

  • Alarm messages sent

  • Alarms

  • Change log

  • Maintenance

  • Users

Manual full table rewrite

However, autovacuum does not yet actualy delete table data that has been deleted by the users. To delete unused data and rewrite all tables, the vacuum full function needs to be used. A vacuum full can only be run when the eranger-server service is stopped.

To run a vacuum full, run the following script:

/opt/eranger/bin/eranger-db-housekeeping.sh
 *** ATTENTION! This script will block eranger-server for a very long time!
 *** Do you really want to execute this script on database "ng" ? Yy/[Nn]: n

Answer y to the above question. The operation will run for many hours, depending on the amount of data stored in the database.

It is recommended to run this at least yearly.