Managing Log Files

Managing Log Files

ClassDB's connection logging facilities rely on the external Postgres server log files. This document explains how ClassDB configures Postgres' logging system and how to monitor log file usage. This information most relevant to ClassDB deployments using the connection logging facility.

ClassDB Log File Configuration

ClassDB makes several modifications to the Postgres instance's settings. These changes allow ClassDB to retrieve connection information from the logs. The following SQL statements from prepareUserLogging.sql are used to configure Postgres' log system:

ALTER SYSTEM SET log_connections TO 'on';
ALTER SYSTEM SET log_destination TO 'csvlog';
ALTER SYSTEM SET log_filename TO 'postgresql-%m.%d.log';
SELECT pg_reload_conf();

Each statement modifies a Postgres system setting. Each setting is described below:

Setting	Description
log_connections TO 'on'	Causes Postgres to write on line to the log file for each connection established to the DBMS
log_destination TO 'csvlog'	Causes Postgres to write logs in a csv format, instead of plain text
log_filename TO 'postgresql-%m.%d.log'	Sets the log file name. %m and %d are placeholders for the current month and day, respectively

The function pg_reload_conf() applies these settings without having to restart the DBMS.

The log file name we set cause there to be one log file for each day of the year. Postgres automatically rotates the log every day, writing to the appropriate file. For example, on a June 5th, Postgres will write to the log file postgresql-6.5.log. At midnight on June 6th, Postgres will stop writing to that file, and begin writing to postgresql-6.6.log. Existing files will be truncated and reused. With this system, a maximum of 366 logs will be stored at any given time, one for each possible day of the year.

Log Storage Space

When dealing with a large number of users, it is possible for the log files to take a not insignificant amount of space. From our testing so far, we have found that, with default log setting plus connection logging, the logs consume about 40KB per user per day with light to moderate usage.

These numbers were not obtained rigorously, since we did not control for user activity. Additionally, Postgres logs a large amount of information related to automatic processes that are unrelated to users. However, we would expect this to be a reasonable estimate, since the amount of connections logged is more related to number of users, rather than activity. Additionally, we can place an upper limit on log usage using this estimate, since a maximum of 366 log files are stored at any given time. The following table shows various numbers of daily users, and the estimated maximum log size:

Daily Users (#)	Maximum Log Size (MB)
10	143
25	357
50	715
100	1,430

Free Log Storage Space

If you find the logs are taking too much space on your server, there are a few possible remedies. First, you may manually delete old log files that have already been imported to the server. To do this, execute the query as a superuser:

SHOW log_directory;

This will display the directory your logs are stored in. Next, execute the query:

SELECT MAX(lastConnection)
FROM classdb.student;

This will show the latest connection timestamp imported from the logs. This date in this timestamp also corresponds to the last log file that was imported. Log files matching dates earlier than the one returned may be safely deleted.

Another method to reduce log size is to reduce the amount of information stored. For example, setting log_error_verbosity to TERSE will disable the logging of full statements on errors. This can greatly reduce the amount of data logged. For more information about logging configuration, see the Postgres error reporting and logging documentation.

Managing-Log-Files.md - ClassDB Documentation

Steven Rollo Data Science & Systems Lab (DASSL), Western Connecticut State University (WCSU)

(C) 2017- DASSL. ALL RIGHTS RESERVED.
Licensed to others under CC 4.0 BY-SA-NC:
https://creativecommons.org/licenses/by-nc-sa/4.0/

PROVIDED AS IS. NO WARRANTIES EXPRESSED OR IMPLIED. USE AT YOUR OWN RISK.