Thread: How to maintain the csv log files in pg_log directory only for past30 days

On Fri, Sep 28, 2018 at 10:33:30AM +0530, Raghavendra Rao J S V wrote:
> Log file will be generated in *csv* format at *pg_log* directory in our
> PostgreSQL. Every day we are getting one log file. We would like to
> maintain only max 30 days. Which setting need to modify by us in
> “postgresql.conf” in order to recycle the log files after 30 days.

If you use for example log_filename = 'postgresql-%d.log', then the
server uses one new file every day.  This truncates the contents from
the last month automatically.
--
Michael

On 9/27/18 10:25 PM, Michael Paquier wrote:
> On Fri, Sep 28, 2018 at 10:33:30AM +0530, Raghavendra Rao J S V wrote:
>> Log file will be generated in *csv* format at *pg_log* directory in our
>> PostgreSQL. Every day we are getting one log file. We would like to
>> maintain only max 30 days. Which setting need to modify by us in
>> “postgresql.conf” in order to recycle the log files after 30 days.
> 
> If you use for example log_filename = 'postgresql-%d.log', then the
> server uses one new file every day.  This truncates the contents from
> the last month automatically.

If log_truncate_on_rotation = 'on', correct?

> --
> Michael
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, Sep 28, 2018 at 7:03 AM, Raghavendra Rao J S V
<raghavendrajsv@gmail.com> wrote:
> Log file will be generated in csv format at pg_log directory in our
> PostgreSQL. Every day we are getting one log file. We would like to maintain
> only max 30 days. Which setting need to modify by us in “postgresql.conf” in
> order to recycle the log files after 30 days.

I have similar problems in a lot of things and normally use "find
<directory> -name <whatever> -mtime +<age> -delete" in the daemons
cron ( gnu find, on linux, I assume other os have a similar command ).

For postgres-only solutions you've been given some advice previously,
and like those I only know ways to do it daily/weekly/monthly/yearly.

Francisco Olarte.

On 09/28/2018 12:03 AM, Raghavendra Rao J S V wrote:

Hi All,

Log file will be generated in csv format at pg_log directory in our PostgreSQL. Every day we are getting one log file. We would like to maintain only max 30 days. Which setting need to modify by us in “postgresql.conf” in order to recycle the log files after 30 days.

Does it have to be in postgresql.conf?  A cron job which runs a few minutes after midnight works just fine.

Compresses yesterday's log file and deletes files older than 30 days:

#!/bin/bash

DIR=/var/lib/pgsql/data/pg_log

cd $DIR

PREVDT=$(date -d "-1 day" +"%F")

bzip2 -9 postgresql-${PREVDT}.log

OLDFILES=$(find $DIR/postgresql-*log* -mtime +30)

rm -v $OLDFILES

On Fri, Sep 28, 2018 at 06:19:16AM -0700, Adrian Klaver wrote:
> If log_truncate_on_rotation = 'on', correct?

Yup, thanks for precising.
--
Michael

On 9/28/18 7:05 PM, Raghavendra Rao J S V wrote:
> Hi All,
> 
> Hope you all are recommending below settings to maintain only max 30 
> days logs in *pg_log* directory. Please correct me if I am wrong.

Well it would actually be 31 days as:

http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html

%d
     Replaced by the day of the month as a decimal number [01,31]. [ 
tm_mday]

Not sure if that matters or not.

> 
> log_filename = 'postgresql-%d.log'
> log_truncate_on_rotation = 'on',
> 
> Regards,
> Raghavendra Rao
> 
> On Sat, 29 Sep 2018 at 04:24, Michael Paquier <michael@paquier.xyz 
> <mailto:michael@paquier.xyz>> wrote:
> 
>     On Fri, Sep 28, 2018 at 06:19:16AM -0700, Adrian Klaver wrote:
>      > If log_truncate_on_rotation = 'on', correct?
> 
>     Yup, thanks for precising.
>     --
>     Michael
> 
> 
> 
> -- 
> Regards,
> Raghavendra Rao J S V
> Mobile- 8861161425


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2018-09-29 07:12:32 -0700, Adrian Klaver wrote:
> On 9/28/18 7:05 PM, Raghavendra Rao J S V wrote:
> > Hope you all are recommending below settings to maintain only max 30
> > days logs in *pg_log* directory. Please correct me if I am wrong.
>
> Well it would actually be 31 days as:
>
> http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html
>
> %d
>     Replaced by the day of the month as a decimal number [01,31]. [ tm_mday]

Additionally, the log file for the 31st of a month is usually only
overwritten after 61 days.

> Not sure if that matters or not.

It may or may not. Personally I prefer to use find -mtime (or logrotate,
or cleandir, or keepfree, ...) to avoid the irregularities of the
Gregorian calendar.

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>