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 rotation of the 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.