Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Nov 11 '13 at 8:41

This question came from our site for professional and enthusiast programmers.

    
Probably a candidate for migration to dba.SE. –  Erwin Brandstetter Nov 11 '13 at 1:51
1  
Wondering if postgresql.org/docs/current/static/… is relevant –  Craig Ringer Nov 11 '13 at 2:27
1  
That configuration looks fine. I'd look for any error message at the time or offset in the log when it fails to switch. Also check that postgres has sufficient permissions to create a new file into your log_directory. –  Daniel Vérité Nov 11 '13 at 13:25

2 Answers 2

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.

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.