How to maintain the csv log files in pg_log directory only for past 30 days
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.
--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425
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
Thanks for the prompt response.
On Fri 28 Sep, 2018, 10:55 AM Michael Paquier, <michael@paquier.xyz> wrote:
Show quoted text
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
--
Angular momentum makes the world go 'round.
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
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.
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> 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
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 RaoOn 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/>