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 1tcoLw-002qXQ-PR for pgsql-general@arkaria.postgresql.org; Tue, 28 Jan 2025 16:22:17 +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 1tcoLv-0020ZQ-Se for pgsql-general@arkaria.postgresql.org; Tue, 28 Jan 2025 16:22:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tcoLv-0020ZI-Bd for pgsql-general@lists.postgresql.org; Tue, 28 Jan 2025 16:22:15 +0000 Received: from mail-ed1-x52b.google.com ([2a00:1450:4864:20::52b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tcoLs-001y9O-1r for pgsql-general@postgresql.org; Tue, 28 Jan 2025 16:22:14 +0000 Received: by mail-ed1-x52b.google.com with SMTP id 4fb4d7f45d1cf-5d3ecae02beso8022769a12.0 for ; Tue, 28 Jan 2025 08:22:12 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738081331; x=1738686131; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=5Dxj5isdT1kVz6WRVbZ6+6KNXfLIOgHSZcx9fYYX2PM=; b=WSOCzl5eh+2+uWKEl00QRXjANi4qrRahDZchezJ9L1ZfRLWiRED7g+r57HpTOe+i3H b49z/y58OG3xvVMrrYslvwZmw9ZbnXjOLC2W2w7fY8sEP1sCNbmiXOMaCehpkUrgqA1x wR8MVAQbWV9Cvjt/hqmIITgeC/S5hino3sQmBA953y3qgd+2TLb6VMl3g0nm9E/tYE1Y dNaWnzBRHiKWczVUgTEmkYrj2YOStptCHhtEPjFDSnin/mEO92IiHwEiwPIHK7ur0oxj QHds+38ASYFf6P64dDOVbHFFH5myEOhfAAdiIEHNjv7wgQy1PzfK4SqsSQVLYKvExpDc DgLQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738081331; x=1738686131; h=cc: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=5Dxj5isdT1kVz6WRVbZ6+6KNXfLIOgHSZcx9fYYX2PM=; b=rN1yNknOs8twAwiwWiRBACe0Imwx/EmOfMXD2SawsUGSp3/XitaKZ06cIulevyDSTB 3pAPNJE5weEFAnDyZUXpTZ71cCEQMjoT/j8pfJPX/ATQ/pFSk3HV3Oh3KGvc9INBCyLE k6+PjOOdtUnQ3CkicX0/12iacX3Z+TOVsi3qy1nJAx629V06hZqQ7Mg28exEXb/U/pPo 2oV1r0jmvy3lsNNvPXH8t4WMkIP1DWo5vnIVp81Qb00B3hPHiNpTh1JzJvrsssPbWhdR SYboz8HaxrkUx2DolEOuN/qGn54TNDXijERNkvIh/v7HxjL9P8azYs1kPpFl4/Llo4QT BlEA== X-Gm-Message-State: AOJu0YxyPyzuA4/Sc9V3DZWKlMmG03dUqYgxc5jO/Ztr9mdgszlLeVAv e9yXSgltJTWngTPxo48zWnFXNn2Mk5WPmst6NFihLzXeXPU8WNYdb6b1xRCU07NN6bNnEKptm4v 5WHL5/hi7LjyL84rDl8OMmrU5W48= X-Gm-Gg: ASbGncv5BZ6jTEtP2+ZlNtLWrvT5uTbrxVR0QLPLVNGPuSMvNK67W7r5ghG8x1OoEvT N3f/NeSKdrPtQJXAZhovcrBrezyzPozgfprbvAWNAEPqzemTzr/K5KZiOXNk6fBANMThQxk8= X-Google-Smtp-Source: AGHT+IEtOAcM3qeCGAXGSYC8mEGsgjinT139lirXFTUEWQJih6ri7agTqk8DD1qd0pPv4+OnJQW9krxiT1vORL/eW9c= X-Received: by 2002:a17:907:6ea1:b0:aa6:89b9:e9c6 with SMTP id a640c23a62f3a-ab38b111647mr3296736266b.21.1738081330449; Tue, 28 Jan 2025 08:22:10 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Paul Brindusa Date: Tue, 28 Jan 2025 16:21:59 +0000 X-Gm-Features: AWEUYZnxtQ6_2WnlSAvGNt8SeKj-MDJwSn5g9z6gY4Lju6ivIrCispgz7D0MWyY Message-ID: Subject: Re: Log retention query To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000004ad516062cc698e2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004ad516062cc698e2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 u= p >>> 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=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 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 a= m >>>> 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", PostgreS= QL >>>> 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 sa= me >>>> 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! > --=20 Kind Regards, Paul Brindusa paulbrindusa88@gmail.com --0000000000004ad516062cc698e2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi everyone,=C2=A0

@Ron:=C2=A0 I've= tried=C2=A0 all the classic log tools, for some reason that I do not remem= ber logrotate does not rotate the logs.
@Adrian:=C2=A0 three = node=C2=A0 cluster with patroni again.

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

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

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

declare -i Weeks=3D8
declare -i Days=3D$Weeks*7
dec= lare Dir=3D/var/log/postgresql
de= clare OldFiles=3D$(find $Dir/p*-*log* -mtime +$Days | sort)
if [ -z &quo= t;$OldFiles" ];
then
=C2=A0 =C2=A0 echo "No old files to de= lete in ${Dir}."
else
=C2=A0 =C2=A0 rm -v $OldFiles
fi

On Tue, Jan 28, 2025 at 8:41=E2= =80=AFAM Paul Brindusa <paulbrindusa88@gmail.com> wrote:
@Junwang apologies, I should have mentioned that=C2=A0 we've tried s= etting up a crontab and it has not worked. Have you got something similar w= orking?

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

On Tue, Jan 28, 2025 at 1:28=E2=80=AFPM Laurenz Alb= e <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
=

--0000000000004ad516062cc698e2--