Thread: Log retention query
Good morning everyone,
Before I get on with today's problem, I would like to say how much I appreciate this community and everything that you do for end users.
In today's problem I would like to understand if the following lines in our config handle the log rotation for our clusters?
log_checkpoints: on
logging_collector: on
log_truncate_on_rotation: on
log_rotation_age: 1d
log_rotation_size: 1GB
logging_collector: on
log_truncate_on_rotation: on
log_rotation_age: 1d
log_rotation_size: 1GB
log_error_verbosity: verbose
I have been deleting the logs manually for the last month, since I am confused how the log collector rotates them.
Am looking to delete logs older than 180 days. What are we doing wrong in the config?
On Tue, Jan 28, 2025 at 5:57 PM Paul Brindusa <paulbrindusa88@gmail.com> wrote:
>
> Good morning everyone,
>
> Before I get on with today's problem, I would like to say how much I appreciate this community and everything that
youdo for end users.
>
> In today's problem I would like to understand if the following lines in our config handle the log rotation for our
clusters?
>
> log_checkpoints: on
> logging_collector: on
> log_truncate_on_rotation: on
> log_rotation_age: 1d
> log_rotation_size: 1GB
> log_error_verbosity: verbose
>
> I have been deleting the logs manually for the last month, since I am confused how the log collector rotates them.
>
> Am looking to delete logs older than 180 days. What are we doing wrong in the config?
I doubt Postgres can do this, but you can do this by adding a crontab
entry. e.g.
0 2 * * * find /path/to/logs -type f -mtime +180 -name "*.log" -exec rm {} \;
>
> --
> Kind Regards,
> Paul Brindusa
> paulbrindusa88@gmail.com
>
--
Regards
Junwang Zhao
On Tue, 2025-01-28 at 09:57 +0000, Paul Brindusa wrote: > Good morning everyone, > > Before I get on with today's problem, I would like to say how much I appreciate this community and everything that youdo for end users. > > In today's problem I would like to understand if the following lines in our config handle the log rotation for our clusters? > > log_checkpoints: on > logging_collector: on > log_truncate_on_rotation: on > log_rotation_age: 1d > log_rotation_size: 1GB > log_error_verbosity: verbose > > I have been deleting the logs manually for the last month, since I am confused how the log collector rotates them. > > Am looking to delete logs older than 180 days. What are we doing wrong in the config? It all depends on how you configured "log_filename". If the setting is "postgresql-%a.log" or "postgresql-%d.log", PostgreSQL will recycle the old log files once a week or once a month. If the setting is the default "postgresql-%Y-%m-%d_%H%M%S.log", the same log file name will never be reused, and there will be no log rotation. PostgreSQL doesn't actively delete old log files. Yours, Laurenz Albe
@Junwang apologies, I should have mentioned that we've tried setting up a crontab and it has not worked. Have you got something similar working?
@Laurenz: log_filename: postgresql-%Y-%m-%d.log -- if we redo the syntax can we make it trigger garbage collection on 180 days?
On Tue, Jan 28, 2025 at 1:28 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Tue, 2025-01-28 at 09:57 +0000, Paul Brindusa wrote:
> Good morning everyone,
>
> Before I get on with today's problem, I would like to say how much I appreciate this community and everything that you do for end users.
>
> In today's problem I would like to understand if the following lines in our config handle the log rotation for our clusters?
>
> log_checkpoints: on
> logging_collector: on
> log_truncate_on_rotation: on
> log_rotation_age: 1d
> log_rotation_size: 1GB
> log_error_verbosity: verbose
>
> I have been deleting the logs manually for the last month, since I am confused how the log collector rotates them.
>
> Am looking to delete logs older than 180 days. What are we doing wrong in the config?
It all depends on how you configured "log_filename".
If the setting is "postgresql-%a.log" or "postgresql-%d.log", PostgreSQL
will recycle the old log files once a week or once a month.
If the setting is the default "postgresql-%Y-%m-%d_%H%M%S.log", the same
log file name will never be reused, and there will be no log rotation.
PostgreSQL doesn't actively delete old log files.
Yours,
Laurenz Albe
Deleting old log files is _NOT_ a Postgresql problem. Bash and crontab work perfectly for me.
(Could I have done a one-liner like Junwang? Sure, but I like the obvious and explicit.)
declare -i Weeks=8
declare -i Days=$Weeks*7
declare Dir=/var/log/postgresql
declare OldFiles=$(find $Dir/p*-*log* -mtime +$Days | sort)
if [ -z "$OldFiles" ];
then
echo "No old files to delete in ${Dir}."
else
rm -v $OldFiles
fi
if [ -z "$OldFiles" ];
then
echo "No old files to delete in ${Dir}."
else
rm -v $OldFiles
fi
On Tue, Jan 28, 2025 at 8:41 AM Paul Brindusa <paulbrindusa88@gmail.com> wrote:
@Junwang apologies, I should have mentioned that we've tried setting up a crontab and it has not worked. Have you got something similar working?@Laurenz: log_filename: postgresql-%Y-%m-%d.log -- if we redo the syntax can we make it trigger garbage collection on 180 days?On Tue, Jan 28, 2025 at 1:28 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:On Tue, 2025-01-28 at 09:57 +0000, Paul Brindusa wrote:
> Good morning everyone,
>
> Before I get on with today's problem, I would like to say how much I appreciate this community and everything that you do for end users.
>
> In today's problem I would like to understand if the following lines in our config handle the log rotation for our clusters?
>
> log_checkpoints: on
> logging_collector: on
> log_truncate_on_rotation: on
> log_rotation_age: 1d
> log_rotation_size: 1GB
> log_error_verbosity: verbose
>
> I have been deleting the logs manually for the last month, since I am confused how the log collector rotates them.
>
> Am looking to delete logs older than 180 days. What are we doing wrong in the config?
It all depends on how you configured "log_filename".
If the setting is "postgresql-%a.log" or "postgresql-%d.log", PostgreSQL
will recycle the old log files once a week or once a month.
If the setting is the default "postgresql-%Y-%m-%d_%H%M%S.log", the same
log file name will never be reused, and there will be no log rotation.
PostgreSQL doesn't actively delete old log files.
Yours,
Laurenz Albe--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
logrotate will, of course, also work.
On Tue, Jan 28, 2025 at 9:20 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
Deleting old log files is _NOT_ a Postgresql problem. Bash and crontab work perfectly for me.(Could I have done a one-liner like Junwang? Sure, but I like the obvious and explicit.)declare -i Weeks=8declare -i Days=$Weeks*7declare Dir=/var/log/postgresqldeclare OldFiles=$(find $Dir/p*-*log* -mtime +$Days | sort)
if [ -z "$OldFiles" ];
then
echo "No old files to delete in ${Dir}."
else
rm -v $OldFiles
fiOn Tue, Jan 28, 2025 at 8:41 AM Paul Brindusa <paulbrindusa88@gmail.com> wrote:@Junwang apologies, I should have mentioned that we've tried setting up a crontab and it has not worked. Have you got something similar working?@Laurenz: log_filename: postgresql-%Y-%m-%d.log -- if we redo the syntax can we make it trigger garbage collection on 180 days?On Tue, Jan 28, 2025 at 1:28 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:On Tue, 2025-01-28 at 09:57 +0000, Paul Brindusa wrote:
> Good morning everyone,
>
> Before I get on with today's problem, I would like to say how much I appreciate this community and everything that you do for end users.
>
> In today's problem I would like to understand if the following lines in our config handle the log rotation for our clusters?
>
> log_checkpoints: on
> logging_collector: on
> log_truncate_on_rotation: on
> log_rotation_age: 1d
> log_rotation_size: 1GB
> log_error_verbosity: verbose
>
> I have been deleting the logs manually for the last month, since I am confused how the log collector rotates them.
>
> Am looking to delete logs older than 180 days. What are we doing wrong in the config?
It all depends on how you configured "log_filename".
If the setting is "postgresql-%a.log" or "postgresql-%d.log", PostgreSQL
will recycle the old log files once a week or once a month.
If the setting is the default "postgresql-%Y-%m-%d_%H%M%S.log", the same
log file name will never be reused, and there will be no log rotation.
PostgreSQL doesn't actively delete old log files.
Yours,
Laurenz Albe----Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On 1/28/25 05:40, Paul Brindusa wrote: > @Junwang apologies, I should have mentioned that we've tried setting up > a crontab and it has not worked. Have you got something similar working? > > @Laurenz: log_filename: postgresql-%Y-%m-%d.log -- if we redo the > syntax can we make it trigger garbage collection on 180 days? If by garbage collection you mean 'log_truncate_on_rotation: on' then you would need some pattern that would repeat after 180 days. Off the top of my head I cannot come up with one that would do that. How did you install Postgres and on what platform? > -- > Kind Regards, > Paul Brindusa > paulbrindusa88@gmail.com <mailto:paulbrindusa88@gmail.com> > -- Adrian Klaver adrian.klaver@aklaver.com
Hi everyone,
@Ron: I've tried all the classic log tools, for some reason that I do not remember logrotate does not rotate the logs.
@Adrian: three node cluster with patroni again.
On Tue, Jan 28, 2025 at 3:02 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
logrotate will, of course, also work.On Tue, Jan 28, 2025 at 9:20 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:Deleting old log files is _NOT_ a Postgresql problem. Bash and crontab work perfectly for me.(Could I have done a one-liner like Junwang? Sure, but I like the obvious and explicit.)declare -i Weeks=8declare -i Days=$Weeks*7declare Dir=/var/log/postgresqldeclare OldFiles=$(find $Dir/p*-*log* -mtime +$Days | sort)
if [ -z "$OldFiles" ];
then
echo "No old files to delete in ${Dir}."
else
rm -v $OldFiles
fiOn Tue, Jan 28, 2025 at 8:41 AM Paul Brindusa <paulbrindusa88@gmail.com> wrote:@Junwang apologies, I should have mentioned that we've tried setting up a crontab and it has not worked. Have you got something similar working?@Laurenz: log_filename: postgresql-%Y-%m-%d.log -- if we redo the syntax can we make it trigger garbage collection on 180 days?On Tue, Jan 28, 2025 at 1:28 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:On Tue, 2025-01-28 at 09:57 +0000, Paul Brindusa wrote:
> Good morning everyone,
>
> Before I get on with today's problem, I would like to say how much I appreciate this community and everything that you do for end users.
>
> In today's problem I would like to understand if the following lines in our config handle the log rotation for our clusters?
>
> log_checkpoints: on
> logging_collector: on
> log_truncate_on_rotation: on
> log_rotation_age: 1d
> log_rotation_size: 1GB
> log_error_verbosity: verbose
>
> I have been deleting the logs manually for the last month, since I am confused how the log collector rotates them.
>
> Am looking to delete logs older than 180 days. What are we doing wrong in the config?
It all depends on how you configured "log_filename".
If the setting is "postgresql-%a.log" or "postgresql-%d.log", PostgreSQL
will recycle the old log files once a week or once a month.
If the setting is the default "postgresql-%Y-%m-%d_%H%M%S.log", the same
log file name will never be reused, and there will be no log rotation.
PostgreSQL doesn't actively delete old log files.
Yours,
Laurenz Albe----Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
It's impossible to rotate logs with the date embedded in the name. All you can do is delete the oldest files. Why we call that "rotation" is a mystery.
Junwang's find command does that for you, as does the bash script I presented.
On Tue, Jan 28, 2025 at 11:22 AM Paul Brindusa <paulbrindusa88@gmail.com> wrote:
Hi everyone,@Ron: I've tried all the classic log tools, for some reason that I do not remember logrotate does not rotate the logs.@Adrian: three node cluster with patroni again.On Tue, Jan 28, 2025 at 3:02 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:logrotate will, of course, also work.On Tue, Jan 28, 2025 at 9:20 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:Deleting old log files is _NOT_ a Postgresql problem. Bash and crontab work perfectly for me.(Could I have done a one-liner like Junwang? Sure, but I like the obvious and explicit.)declare -i Weeks=8declare -i Days=$Weeks*7declare Dir=/var/log/postgresqldeclare OldFiles=$(find $Dir/p*-*log* -mtime +$Days | sort)
if [ -z "$OldFiles" ];
then
echo "No old files to delete in ${Dir}."
else
rm -v $OldFiles
fiOn Tue, Jan 28, 2025 at 8:41 AM Paul Brindusa <paulbrindusa88@gmail.com> wrote:@Junwang apologies, I should have mentioned that we've tried setting up a crontab and it has not worked. Have you got something similar working?@Laurenz: log_filename: postgresql-%Y-%m-%d.log -- if we redo the syntax can we make it trigger garbage collection on 180 days?On Tue, Jan 28, 2025 at 1:28 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:On Tue, 2025-01-28 at 09:57 +0000, Paul Brindusa wrote:
> Good morning everyone,
>
> Before I get on with today's problem, I would like to say how much I appreciate this community and everything that you do for end users.
>
> In today's problem I would like to understand if the following lines in our config handle the log rotation for our clusters?
>
> log_checkpoints: on
> logging_collector: on
> log_truncate_on_rotation: on
> log_rotation_age: 1d
> log_rotation_size: 1GB
> log_error_verbosity: verbose
>
> I have been deleting the logs manually for the last month, since I am confused how the log collector rotates them.
>
> Am looking to delete logs older than 180 days. What are we doing wrong in the config?
It all depends on how you configured "log_filename".
If the setting is "postgresql-%a.log" or "postgresql-%d.log", PostgreSQL
will recycle the old log files once a week or once a month.
If the setting is the default "postgresql-%Y-%m-%d_%H%M%S.log", the same
log file name will never be reused, and there will be no log rotation.
PostgreSQL doesn't actively delete old log files.
Yours,
Laurenz Albe----Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Good afternoon Peter, I had the exact same query as Junwang proposed. Was mega upset that I could not get the cronjobs to work, and from what I can tell from @Laurenz's response above we have the names of the logs customised to posgtres-%d-%m-%y. Removing the logs after a week or month does not since our retention policy is 6 months. The cron job was set from root and it did not remove the logs/ On 02/02/2025 08:26, Peter J. Holzer wrote: > On 2025-01-28 13:40:42 +0000, Paul Brindusa wrote: >> @Junwang apologies, I should have mentioned that we've tried setting up a >> crontab and it has not worked. > Then you have a cron problem and not a postgresql problem. > > What that problem is is impossible to say with the information you have > given us. What was the exact crontab entry, and what did it do? (When > describing a problem, always use positives, not negatives. "it did not > work" is particularly useless, since there are a gazillion ways in which > something could not do what you expected.) > >> Have you got something similar working? > Yes. Cleaning up stuff is probably one of the most frequent uses of > cron. > > hp >
I am defo sure that my syntax was fine.
I have tried the same syntax to remove the logs manually from the
folder and it worked perfectly.
The cronjob was set from root, so I am assuming it has the right
privileges over the folder in cause.
@ Adrian the cluster runs on Rocky9
There is no error from cron that is the weird bit as well.
I do not have MAILTO set up on cron.
Regards,
Paul
On 02/02/2025 19:57, Peter J. Holzer wrote:
> On 2025-02-02 12:12:07 +0000, Paul Brindusa wrote:
>> I had the exact same query as Junwang proposed.
> Assuming that by "query" you mean the crontab entry:
>
> Well, if if was *exactly* the same it's unlikely to work since you
> probably don't have a directory literally called "/path/to/logs". If you
> made the obvious substitution, it should work provided it runs with
> appropriate privileges.
>
> What is the output if you remove the «-exec rm {} \;» bit? What happens
> if you reduce the mtime?
>
>
>> Was mega upset that I could not get the cronjobs to work, and from what I
>> can tell from @Laurenz's response above we have the names of the logs
>> customised to posgtres-%d-%m-%y.
> Earlier you wrote that the pattern was actually
> «postgresql-%Y-%m-%d.log». «find ... -name "*.log"» would find that but
> of course not «posgtres-%d-%m-%y».
>
> hp
>
Good afternoon,
Apologies for coming back to you so late.
Again I want to thank everyone for all the help provided regarding this. The main culprit was the cron job and trying to run it as root.
Main thing is i've learned a lot from your answers about the logs.
Thank you again.
On Sun, Feb 2, 2025 at 9:33 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 2/2/25 12:04, Paul Brindusa wrote:
> I am defo sure that my syntax was fine.
>
> I have tried the same syntax to remove the logs manually from the
> folder and it worked perfectly.
>
> The cronjob was set from root, so I am assuming it has the right
> privileges over the folder in cause.
>
Did you use the correct directory in the cron command?
>
> @ Adrian the cluster runs on Rocky9
What repo are you using to install Postgres from?
>
> There is no error from cron that is the weird bit as well.
>
> I do not have MAILTO set up on cron.
I would do that, then you should get the errors.
You could also look in the system log at the time the cron job was
executed to see if it recorded the error.
>
> Regards,
>
> Paul
>
--
Adrian Klaver
adrian.klaver@aklaver.com