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 1tcn6F-002hOh-OV for pgsql-general@arkaria.postgresql.org; Tue, 28 Jan 2025 15:02:00 +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 1tcn6E-001fph-Ok for pgsql-general@arkaria.postgresql.org; Tue, 28 Jan 2025 15:01:58 +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 1tcn6E-001fpZ-9u for pgsql-general@lists.postgresql.org; Tue, 28 Jan 2025 15:01:58 +0000 Received: from mail-oa1-x2a.google.com ([2001:4860:4864:20::2a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tcn6B-001xUK-2b for pgsql-general@postgresql.org; Tue, 28 Jan 2025 15:01:57 +0000 Received: by mail-oa1-x2a.google.com with SMTP id 586e51a60fabf-29f87f1152cso2942057fac.2 for ; Tue, 28 Jan 2025 07:01:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738076515; x=1738681315; 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=eDrDGG7Of6lzKwuG9JWh2/iH12/myCYGudESN9ZdNPE=; b=fp/LG5NB9ujPigbjdykEZ91ZRhDte6mUa2s6HNbWWRUK+0s2/d/NJbdnDCg8iI8Sl4 tjwiX84PQWJoX9a4Yal64k6f8a/A6RKH3Wy6XsUfLrehg+swRVpmDaIBpFxQecGy/HZ/ /pcjVVSJSwg7VUJmNPC4j2kF6eH23KaIeIFoM13McVB5crOncRa2Qdos+Cn7MqOQz5OL 5fGXYarjy0HJ+VrrC6Mtc3iIvgm969ICFDsRQ9XNeGxfYYMuYcjxygn8j5P0RYYuMkec +Hn3lUtdwInLsOG+bUF5fb66eeqZ+8ctV6ffIlSqt/uXckexB18FjHsHavPdseLaUHAo E/gg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738076515; x=1738681315; 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=eDrDGG7Of6lzKwuG9JWh2/iH12/myCYGudESN9ZdNPE=; b=BzICwqkOw8G8qTfUvrBeDTspgfg/2rl6Oew4MKevCxKMXRyxZtaAXojaow0yozQkSN 6fzpzJCOeFE2UcPPFwFjfIwuRfQbI2u5C9T4iYbipwkEbI7HoJM7N0wmWPkg4j0WnC7z Z1MNlo1QJH2JV3l6CN1aYX29FxgDTsSzGG5D4ExRVQXbwv0rbzBPrsPZOCcoecymzbbR 7lX3tn1mGb+JBupr875c0sqq6Bo6Bi7DjoHrGL3igC/zadgM57CwEENMZNEkI0oTJin8 PPs6sU2S7Ak6ptOjQzccM+wPag8wDmcN/xhxANG1Tuoes8A8yQy7mJvkJYQeg7zdKrCF WVHg== X-Gm-Message-State: AOJu0YyfRSGeBGlRhaEaxqIAoS1AtqPEbLogl9fYwWPWx6iOp33dPzla ac3xNQWws7jdkdBxk7vFqa3UloshBr3o/JXGMzkBUgm6jEhICG81Zt9WhC2Zxpq0TzFg+SYxVzT azM1VAT36KaQGsYMIvT6QMgl4iB6/8w== X-Gm-Gg: ASbGnctZj8FXWVrQNs6YtdLVcHrqVrvf8/1HVRfXA25h12XMjS6dddSTggC6BYq3U3z ctKFzrDXB/6QSUUdkde9gx3uQkSsVPXVTMk09PEcsbKWcRaHke3CLvDusOEsxSX5CUXvrpJxlA0 Dd6nSGHuPORGxens1Fvan80VKVxNx0TPY= X-Google-Smtp-Source: AGHT+IExLlKJOBduEkty0yBvNkXhFYciiTuau3FYNtfGFaHXL7N4CdqVhGF1fXPMviJJ0rt2hdlNv87rDRTLVGwjQj0= X-Received: by 2002:a05:6871:410c:b0:29e:63c3:3392 with SMTP id 586e51a60fabf-2b1c0a7353emr22222402fac.15.1738076515290; Tue, 28 Jan 2025 07:01:55 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 28 Jan 2025 10:01:43 -0500 X-Gm-Features: AWEUYZk4ZPvQIu9W5_xrKFM-HM9xf4_dK5GLkrnD6H3gvst28bUiVOx2P44Xh5s Message-ID: Subject: Re: Log retention query To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000004956f9062cc57905" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004956f9062cc57905 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 obviou= s > 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 working? >> >> @Laurenz: log_filename: postgresql-%Y-%m-%d.log -- if we redo the synta= x >> 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 am >>> confused how the log collector rotates them. >>> > >>> > Am looking to delete logs older than 180 days. What are we doing wron= g >>> in the config? >>> >>> It all depends on how you configured "log_filename". >>> >>> If the setting is "postgresql-%a.log" or "postgresql-%d.log", PostgreSQ= L >>> 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 sam= e >>> 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! > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000004956f9062cc57905 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 c= rontab work perfectly for me.

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

declare -i Weeks=3D8
declare -i Days=3D$Weeks*7
declare Dir=3D/var/log/postgresql
declare OldFiles=3D$(find $Dir/p*-*log* -m= time +$Days | sort)
if [ -z "$OldFiles" ];
then
=C2=A0 = =C2=A0 echo "No old files to delete 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:

On Tue, Jan 28, 2= 025 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!
--0000000000004956f9062cc57905--