Database maintenance

Rudder uses two backends to store information as of now: LDAP and SQL

To achieve this, OpenLDAP and PostgreSQL are installed with Rudder.

However, like every database, they require a small amount of maintenance to keep operating well. Thus, this chapter will introduce you to the basic maintenance procedure you might want to know about these particular database implementations.

Automatic PostgreSQL table maintenance

Rudder uses an automatic mechanism to automate the archival and pruning of the reports database.

By default, this system will:

  • Archive reports older that 3 days (30 in Rudder 2.6)
  • Remove reports older than 90 days

It thus reduces the work overhead by only making Rudder handle relevant reports (fresh enough) and putting aside old ones.

This is obviously configurable in /opt/rudder/etc/rudder-web.properties, by altering the following configuration elements:

  • rudder.batch.reportscleaner.archive.TTL: Set the maximum report age before archival
  • rudder.batch.reportscleaner.delete.TTL: Set the maximum report age before deletion

The default values are OK for systems under moderate load, and should be adjusted in case of excessive database bloating.

The estimated disk space consumption, with a 5 minute agent run frequency, is 150 to 400 kB per Directive, per day and per node, which is roughly 5 to 10 MB per Directive per month and per node.

Thus, 25 directives on 100 nodes, with a 7 day log retention policy, would take 2.5 to 10 GB, and 25 directives on 1000 nodes with a 1 hour agent execution period and a 30 day log retention policy would take 9 to 35 GB.

PostgreSQL database vacuum

In some cases, like a large report archiving or deletion, the Rudder interface will still display the old database size. This is because even if the database has been cleaned as requested, the physical storage backend did not reclaim space on the hard drive, resulting in a "fragmented" database. This is not an issue, as PostgreSQL handles this automatically, and new reports sent by the nodes to Rudder will fill the blanks in the database, resulting in a steady growth of the database. This task is handled by the autovacuum process, which periodically cleans the storage regularly to prevent database bloating.

However, to force this operation to free storage immediately, you can trigger a "vacuum full" operation by yourself, however keep in mind that this operation is very disk and memory intensive, and will lock both the Rudder interface and the reporting system for quite a long time with a big database.

Manual vacuuming using the psql binary. 

# You can either use sudo to change owner to the postgres user, or use the rudder connection credentials.

# With sudo:
sudo -u postgres psql -d rudder

# With rudder credentials, it will ask the password in this case:
psql -u rudder -d rudder -W

# And then, when you are connected to the rudder database in the psql shell, trigger a vacuum:
rudder=# VACUUM FULL;

# And take a coffee.

LDAP database reindexing

In some very rare case, you will encounter some LDAP database entries that are not indexed and used during searches. In that case, OpenLDAP will output warnings to notify you that they should be.

LDAP database reindexing. 

# Stop OpenLDAP
service rudder-slapd stop

# Reindex the databases
service rudder-slapd reindex

# Restart OpenLDAP
service rudder-slapd restart