Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tcoc0-002syh-QC for pgsql-general@arkaria.postgresql.org; Tue, 28 Jan 2025 16:38:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tcobx-002CNS-Rx for pgsql-general@arkaria.postgresql.org; Tue, 28 Jan 2025 16:38:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tcobx-002CNK-GF for pgsql-general@lists.postgresql.org; Tue, 28 Jan 2025 16:38:49 +0000 Received: from mail-oa1-x33.google.com ([2001:4860:4864:20::33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tcobu-001zWc-2Z for pgsql-general@postgresql.org; Tue, 28 Jan 2025 16:38:49 +0000 Received: by mail-oa1-x33.google.com with SMTP id 586e51a60fabf-29fb5257e05so1856765fac.0 for ; Tue, 28 Jan 2025 08:38:47 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738082325; x=1738687125; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=29heFyu95agu+pS0EIF6njQgRHrF1WZsOcfvA5d42dw=; b=WAS2bLe+AS3qYxMaqDRpdtDgXLy/FB+vBza3SlAoETqgOleGaagj+UU8j+GulUsQ/B DRWB7vrSJ/5iJHP9VIRLzhqmDoyAN6w3tk4qmLqbFnc/E8OuaWmSjF+QlhoijdzKSIki FxHF5ev6kE7LkdXAUXvezyESNePIGi8Uw1L4O8lk41CBHKzhiOu/0HJVRRsAkP5QxNsm 6r3kTe4t/CHrqc3bb4Rpbijt/yPBr3V/srT2xnOS/8kPELDfvPsQfAsCcqzb+UZUZxE3 l0RU2LBS6Os28IvE+VqtW/XaXlIanOZaMJP1Mem2AcpSCogijXvglDYaRZNwfWQjQ4n4 V2Rw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738082325; x=1738687125; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=29heFyu95agu+pS0EIF6njQgRHrF1WZsOcfvA5d42dw=; b=aOUPeMm4x+uYSEsnqdJACKAkq6Z9KtZrExi4uyET0u4lRmQwQhLLP/7pFRS4Wwd80U Ti+EPJ4tR5OheM7R65/l94IvOnmDv5PYpoijA5mRSyaSgdE9QYB26IPL265ScP5NKyvT Z4btgCjtgPYPqZfeXNsaVw/2Iu4GqCK4hAUC9l1ZpCvEHR16kIMQwM5UaaDtfDC8/yOj e/AgacQaFIgtNFv/upBNwobE9969Z4bEVRK4Yfe5MRkiZI0/QLq2WLGAaXkP0vTfRg+Y ZfDul/FPEtOXgbT1gBtj1y6fsI5dcRY2qoHZ7DkoCoMsdNfa3m1+l3yq5mBx3U72ahaw NOVQ== X-Gm-Message-State: AOJu0Yyu6qO1R8Fc+6csgRbgu8TSzQ8fqq+5MkFTyxFrMpigHhqvBoXB ozfDJXSXtAZQT6HFIYvQFjyWXLlA2s+rChLQ1jAWfqauovxvlXY6R9DPZ/6a5GhHCR2BbwcMj5H 6rAcM8Z/7yJOa3tEr+0/qK5qYE/YbFw== X-Gm-Gg: ASbGncvMjC4AzridC/wk2mBWmbgpWVoVrgCuEHn9w99Ljh1CbKZfBPpmeSxIYWZxYjP U7uRLOqZXyvLYfgUuwJVV6KiCzT82+TWdiO9uhlOlsG9YVDaGlD29Opg/X58nNkk+fUF6Pb32CI xcMzm4KV+bQP4PI2vL6Rq2Cm7btdbSGl4= X-Google-Smtp-Source: AGHT+IHeE90cJNKWJP51W9ez1w9SR6hjyJFDlbN1dMScm7klU9spgyG9DfpiQeHZkLu5th4jfM+Uc3NsR1gH31u7Fjc= X-Received: by 2002:a05:6870:2f14:b0:29e:353b:8f25 with SMTP id 586e51a60fabf-2b1c0a29375mr22540551fac.12.1738082324635; Tue, 28 Jan 2025 08:38:44 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 28 Jan 2025 11:38:33 -0500 X-Gm-Features: AWEUYZnmOCQxCOXEBK9-3UDOGOaLtJH998VJASfKEr6qhHBkvqCVzH7Hfcgsn8I Message-ID: Subject: Re: Log retention query To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000008cea4f062cc6d312" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008cea4f062cc6d312 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Paul Brindusa 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=E2=80=AFPM Ron Johnson > wrote: > >> logrotate will, of course, also work. >> >> On Tue, Jan 28, 2025 at 9:20=E2=80=AFAM Ron Johnson >> 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=3D8 >>> declare -i Days=3D$Weeks*7 >>> declare Dir=3D/var/log/postgresql >>> declare OldFiles=3D$(find $Dir/p*-*log* -mtime +$Days | sort) >>> 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=E2=80=AFAM 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 wor= king? >>>> >>>> @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=E2=80=AFPM Laurenz Albe >>>> 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 line= s >>>>> 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 >>>> paulbrindusa88@gmail.com >>>> >>>> >>> >>> -- >>> Death to , and butter sauce. >>> Don't boil me, I'm still alive. >>> lobster! >>> >> >> >> -- >> Death to , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > > > -- > Kind Regards, > Paul Brindusa > paulbrindusa88@gmail.com > > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000008cea4f062cc6d312 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

It's impossible to=C2=A0rotate<= /i> logs with the date embedded in the name.=C2=A0 All you can do is delete= the oldest files.=C2=A0 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, 202= 5 at 11:22=E2=80=AFAM Paul Brindusa <paulbrindusa88@gmail.com> wrote:
Hi everyone,=C2=A0
@Ron:=C2=A0 I've tried=C2=A0 all the classic log tools, fo= r some reason that I do not remember logrotate does not rotate the logs.
@Adrian:=C2=A0 three node=C2=A0 cluster with patroni again.
=

On Tue, Jan 28, 2025 at 3:02=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com= > wrote:
logrotate will, of course, also work.

On Tue, Jan 28, 2025= at 9:20=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
Deleting= old log=C2=A0files is _NOT_ a Postgresql problem.=C2=A0 Bash and crontab w= ork perfectly for me.

(Could I have done a one-lin= er like Junwang?=C2=A0 Sure, but I like the obvious and explicit.)

declare -i Weeks=3D8
=
declare -i Days=3D$Weeks*7
<= font face=3D"monospace">declare Dir=3D/var/log/postgresql
= declare OldFiles=3D$(find $Dir/p*-*log* -mtime +$D= ays | sort)
if [ -z "$OldFiles" ];
then
=C2=A0 =C2=A0 ec= ho "No old files to delete in ${Dir}."
else
=C2=A0 =C2=A0 r= m -v $OldFiles
fi

On Tue= , Jan 28, 2025 at 8:41=E2=80=AFAM Paul Brindusa <paulbrindusa88@gmail.com> wro= te:
@Junwang apologies, I should have mentioned tha= t=C2=A0 we've tried setting up a crontab and it has not worked. Have yo= u got something similar working?

@Laurenz: log_filen= ame: postgresql-%Y-%m-%d.log=C2=A0 -- if we redo the syntax can we make it = trigger garbage collection on 180 days?

On Tue, Jan 28, 2025 at 1:= 28=E2=80=AFPM 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 lin= es in our config handle the log rotation for our clusters?
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 log_checkpoints: on
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 logging_collector: on
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 log_truncate_on_rotation: on
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 log_rotation_age: 1d
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 log_rotation_size: 1GB
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 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.=C2=A0
>
> Am looking to delete logs older than 180 days. What are we doing=C2=A0= wrong in the config?

It all depends on how you configured "log_filename".

If the setting is "postgresql-%a.log" or "postgresql-%d.log&= quot;, 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", t= he 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
=



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


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


--
Kind Regards,
Paul Brindusa
=



--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--0000000000008cea4f062cc6d312--