Subject: Re: Log retention query

From: Paul Brindusa <[email-masked]>
Date: Tue, 28 Jan 2025 13:40:42 +0000

@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 <[email-masked]>
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
>

-- 
Kind Regards,
Paul Brindusa
[email-masked]

---

From: Ron Johnson <[email-masked]>
Date: Tue, 28 Jan 2025 09:20:59 -0500

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

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Ron Johnson <[email-masked]>
Date: Tue, 28 Jan 2025 10:01:43 -0500

logrotate will, of course, also work.

On Tue, Jan 28, 2025 at 9:20 AM Ron Johnson <[email-masked]> 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?
>>
>>> > 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

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 28 Jan 2025 08:16:25 -0800

On 1/28/25 05:40, Paul Brindusa wrote:
> @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
> [email-masked] <mailto:[email-masked]>
> 

-- 
Adrian Klaver
[email-masked]

---

From: Paul Brindusa <[email-masked]>
Date: Tue, 28 Jan 2025 16:21:59 +0000

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 <[email-masked]> wrote:

>> (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
>>>> > Am looking to delete logs older than 180 days. What are we
>>>> doing wrong in the config?
>>>>

-- 
Kind Regards,
Paul Brindusa
[email-masked]

---

From: Ron Johnson <[email-masked]>
Date: Tue, 28 Jan 2025 11:38:33 -0500

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 <[email-masked]>
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.
>>>> @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?
>>>>
>>>>> > I have been deleting the logs manually for the last month, since I
>>>>> am confused how the log collector rotates them.
>>>>> >

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sun, 2 Feb 2025 09:26:18 +0100

On 2025-01-28 13:40:42 +0000, Paul Brindusa wrote:
> 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

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: Paul Brindusa <[email-masked]>
Date: Sun, 2 Feb 2025 12:12:07 +0000

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/

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 2 Feb 2025 09:07:14 -0800

> The cron job was set from root and it did not remove the logs/

What OS and version of same?

Do you get an error message from cron?

Do you have MAILTO set up in cron?

>>> up a

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sun, 2 Feb 2025 20:57:13 +0100

> 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

---

From: Paul Brindusa <[email-masked]>
Date: Sun, 2 Feb 2025 20:04:09 +0000

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

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 2 Feb 2025 13:33:51 -0800

> 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?

> 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.

---

From: Paul Brindusa <[email-masked]>
Date: Wed, 12 Feb 2025 13:15:57 +0000

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.

-- 
Kind Regards,
Paul Brindusa
[email-masked]