Обсуждение: Limiting the no. of logs file generated by postgreSQL
Hi All,
I am currently using PostgreSQL 9.4.5.
In one scenario with heavy traffic, log file size increased to 100GB in a day, and due to which system ran out of disk space. The configuration in postgresql.conf for log file was:
log_filename = 'postgresql-%a.log'
So, I want the log files to rotate such that the,
1. Total size of the Postgres logs is always less than 700MB.
2. I want to maintain recent 5 log files of 20MB each in a day .i.e. Total 100MB/day.
So I have tried only log_rotation_size option as below in configuration file, but it didn't work for me.
log_rotation_size = '10kB' /* The file is not rotated after size increases over 10KB. */
When I tried logging with the following configuration (just to test, the values are pretty low):
log_rotation_size = '10kB'
log_filename = 'postgresql-%Y-%m-%d_%M.log'
log_truncate_on_rotation = 'on'
log_rotation_age = '10'
What I've observed is,
1. I get a new file after every 10 Mins or when I restart the service or logs size is over 10KB.
But, it doesn't solve my problem to reduce the size of postgres logs, as the file is not rotated, but a new is created every time.
Is there any way I can rotate the files or restrict number to, say max 5 files
like,
postgresql-Mon-1.log
postgresql-Mon-2.log
postgresql-Mon-3.log
postgresql-Mon-4.log
postgresql-Mon-5.log
and then re-write to "postgresql-Mon-1.log" file.
Regards,
Merina
Hello Merina, Firstly, did you observe the log files like what is getting logged? In case check whether it is logging all the statements ran on the server..! This will be caused because of the parameter "log_statements". What is the value of 'log_statements ' set in your configuration file 'postgresql.conf'? I think because of this parameters this is increasing the size of the log files. You can cross verify over that area. For further analysis provide the snap of Error Reporting and Logging parameters.! Regards, Pavan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
Hi Pavan,
At the time of the outage, the logs were off. I am assuming, the logs increased because I am using replication.
Below is the postgresql.conf,
log_destination = 'stderr'
logging_collector = on
log_filename = 'postgresql-%a.log'
log_statement = 'none'
All other parameters are default values.
On Thu, Feb 15, 2018 at 6:09 PM, pavan95 <pavan.postgresdba@gmail.com> wrote:
Hello Merina,
Firstly, did you observe the log files like what is getting logged? In case
check whether it is logging all the statements ran on the server..!
This will be caused because of the parameter "log_statements".
What is the value of 'log_statements ' set in your configuration file
'postgresql.conf'?
I think because of this parameters this is increasing the size of the log
files. You can cross verify over that area.
For further analysis provide the snap of Error Reporting and Logging
parameters.!
Regards,
Pavan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596. html
Hi Merina, Could you provide a sample snapshot of your log files. It helps in understanding better about the type of messages getting logged(whether they are ERROR/WARNING etc)..! Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
Hi All,I am currently using PostgreSQL 9.4.5.
Not that it changes the answer here but you are considerably out-of-date and should upgrade to 9.4.16 as the earliest opportunity.
What I've observed is,1. I get a new file after every 10 Mins or when I restart the service or logs size is over 10KB.But, it doesn't solve my problem to reduce the size of postgres logs, as the file is not rotated, but a new is created every time.Is there any way I can rotate the files or restrict number to, say max 5 fileslike,
The docs cover what can and cannot be done. My reading indicates that what you described cannot be done - since you want to key off of size instead of time.
My conclusion is that you need to turn over log file management to an external utility - syslog, logrotate, etc..., that provides the feature you desire.
David J.
Thanks, all.
--
I will then explore other utilities to achieve this.
On Thu, Feb 15, 2018 at 8:28 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Hi All,I am currently using PostgreSQL 9.4.5.Not that it changes the answer here but you are considerably out-of-date and should upgrade to 9.4.16 as the earliest opportunity.What I've observed is,1. I get a new file after every 10 Mins or when I restart the service or logs size is over 10KB.But, it doesn't solve my problem to reduce the size of postgres logs, as the file is not rotated, but a new is created every time.Is there any way I can rotate the files or restrict number to, say max 5 fileslike,The docs cover what can and cannot be done. My reading indicates that what you described cannot be done - since you want to key off of size instead of time.My conclusion is that you need to turn over log file management to an external utility - syslog, logrotate, etc..., that provides the feature you desire.David J.
Regards,
Merina Ghoghwani