How to Configure PostgreSQL Log Files Rotation by Size

The log rotation by size on PostgreSQL keeps all the log files, and then the disk space could become full.

To configure the rotation of PostgreSQL log files by size:

1. Locate and open the PostgreSQL configuration file using a text editor:

   • For embedded PostgreSQL: <UCMDB_Server_Home>\PostgreSQL\ucmdbpg.conf

   • For external PostgreSQL: <UCMDB_Server_Home>\PostgreSQL\data\postgresql.conf

2. Copy and paste the following configuration into the file, under the CUSTOMIZED OPTIONS section:

   # These are only used if logging_collector is on:
   log_directory = 'pg_log'           # directory where log files are written,
                                      # can be absolute or relative to PGDATA
   log_filename = 'postgresql-%a.log' # log file name pattern,
                                      # can include strftime() escapes
   log_file_mode = 0600               # creation mode for log files,
                                      # begin with 0 to use octal notation
   log_truncate_on_rotation = on      # If on, an existing log file with the
                                      # same name as the new log file will be
                                      # truncated rather than appended to.
                                      # But such truncation only occurs on
                                      # time-driven rotation, not on restarts
                                      # or size-driven rotation.  Default is
                                      # off, meaning append to existing files
                                      # in all cases.
   log_rotation_age = 1440            # Automatic rotation of logfiles will
                                      # happen after that time.  0 disables.
   log_rotation_size = 0              # Automatic rotation of logfiles will
                                      # happen after that much log output.
                                      # 0 disables

3. Save the file.

4. Restart PostgreSQL for the changes to take effect.

Recommended usage of the options for the backup period:

• For backup period of only 24 hours, just use %H: log_filename = 'postgresql-%H.log', set log_rotation_age = 60.

  One day could be too short for future investigation and troubleshooting, because the log files will be overwritten after one day.

• For backup period of one week, use %a-%w: log_filename = 'postgresql-%a.log', and set log_rotation_age = 1440.

  The log files will be overwritten after one week. %a is the day of the week and %w is the week of the month.

• For backup period of one month, use %d: log_filename = 'postgresql-%d.log', and set log_rotation_age = 1440.

  The log file will be overwritten after a month. %d is the day of the month.

  For old log files like “31”, you can right click order by modified date on Windows, or ls -t on Linux, then you can know this “31” is this month’s or last month’s.

  Note that this configuration could increase the space used by the PostgreSQL log files.

Configure PostgreSQL Log Files Rotation by Size on Data Flow Probe

If you want to configure the rotation of PostgreSQL log files by size on Data Flow Probe only, do the following:

1. Locate and open the following file using a text editor:

   <DataFlowProbe_Home>\conf\log\probeGwLog4j.properties

2. Locate and configure the following variables in the postgresql.log rotation section:

   log4j.appender.PGSQL_LOG_HELPER.MaxFileSize=20MB

   log4j.appender.PGSQL_LOG_HELPER.MaxBackupIndex=10

   • MaxFileSize. The maximum size for each PostgreSQL log file. By default, the variable is set to 20MB.
   • MaxBackupIndex.The maximum number of PostgreSQL log files permitted before log rotation begins. By default, the variable is set to 10 files.

3. If you want to disable the log rotation functionality, change the following

   log4j.category.postgresql.loghelper=DEBUG, PGSQL_LOG_HELPER

   to

   log4j.category.postgresql.loghelper=ERROR, PGSQL_LOG_HELPER

4. Save the file.
5. Restart the UCMDB_Probe and UCMDB_Probe_DB services.