7

I have a postgres system with a hot standby server. They use the same config file. I've setup the log rotation to use log_rotation_size and log_rotation_age so that the master server is rotated every 10MB and the standby logs are rotated every day. My parameters are as follows:

postgres=# select name,setting,unit from pg_settings where name like '%log%';
            name             |            setting             | unit
-----------------------------+--------------------------------+------
 log_autovacuum_min_duration | -1                             | ms
 log_checkpoints             | on                             |
 log_connections             | on                             |
 log_destination             | stderr                         |
 log_directory               | /apps/postgres/pg_log          |
 log_disconnections          | on                             |
 log_duration                | off                            |
 log_error_verbosity         | default                        |
 log_executor_stats          | off                            |
 log_file_mode               | 0600                           |
 log_filename                | postgresql-%Y-%m-%d_%H%M%S.log |
 log_hostname                | on                             |
 log_line_prefix             | %u %h %p                       |
 log_lock_waits              | on                             |
 log_min_duration_statement  | 10000                          | ms
 log_min_error_statement     | error                          |
 log_min_messages            | warning                        |
 log_parser_stats            | off                            |
 log_planner_stats           | off                            |
 log_rotation_age            | 1440                           | min
 log_rotation_size           | 10240                          | kB
 log_statement               | ddl                            |
 log_statement_stats         | off                            |
 log_temp_files              | -1                             | kB
 log_timezone                | Australia/NSW                  |
 log_truncate_on_rotation    | off                            |
 logging_collector           | on                             |
 syslog_facility             | local0                         |
 syslog_ident                | postgres                       |

My problem is that the logfiles in the production database aren't being rotated at all, while the standby logs are being rotated as expected every day. This is a high availability application so I'm keeping it running at present by copying the logfile to a new file then echoing "" to the current logfile. Is there anyway I can fix this problem without an outage to the system?

0
3

Try the following log parameters. After modify them, remember to reload the configuration file ($PGDATA/postgresql.conf).

# These are only used if logging_collector is on:
log_directory = '/var/applog/pg_log/1921'               # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.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 = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 10MB               # Automatic rotation of logfiles will
0

Try and open the init script and see if the pg_ctl command is directing output to the log file directly.

I had the same problem whereas the init script (taken from the PostgreSQL source) was doing something like

/usr/bin/postmaster -D /datadir & >> /datadir/serverlog 2&>1

Even though I set up the correct values in postgresql.conf this was overriding them. Removing the output part and setting up the postgresql.conf parameters properly solved the problem.

0

My VirtualMachine is normally off at midnight and uses the unrotated log. You can create your own cronjob:

0 0 * * * psql -Xc"select pg_rotate_logfile()"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy