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 1tcmSq-002cFF-UF for pgsql-general@arkaria.postgresql.org; Tue, 28 Jan 2025 14:21: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 1tcmSo-001QbS-IT for pgsql-general@arkaria.postgresql.org; Tue, 28 Jan 2025 14:21:14 +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 1tcmSo-001QbK-43 for pgsql-general@lists.postgresql.org; Tue, 28 Jan 2025 14:21:14 +0000 Received: from mail-oa1-x32.google.com ([2001:4860:4864:20::32]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tcmSl-001x00-2w for pgsql-general@postgresql.org; Tue, 28 Jan 2025 14:21:13 +0000 Received: by mail-oa1-x32.google.com with SMTP id 586e51a60fabf-29ff5c75c21so3727133fac.0 for ; Tue, 28 Jan 2025 06:21:12 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738074071; x=1738678871; 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=yuzM3193orwAtCiDoN1UK+E8iWCvUcynY8Tb2Ho7dvw=; b=Lz9zrOtUm9d6HBPWJq41F8p7QNtei78LQY02kmQh3p77GYEQkvo5SOQr4S6Q23eiha MedIbK87DfZA5cVSMd9TMoPfg1Zg670WJKd1iypWEsfIM5J/POa5KzXXkKrQrwQeKdpD F5CkZr9AVQ8EWbnXS+HCm0x870BUbU0Yae7+xSo9YuPmNH5RXX5XiRhwn4YBGFTssIY5 3nRrc/5gcbP3h3uhYwbLCoFGK8RS7a3LtmHBWM6OnsLjmtNIaRSe55omsaEW+gOztR+v 7YfhTiw1AJtXg334iTlokhsuvMMwx9o98/xkHWhfuw2RNOe2MibfwOL6wrtJ/od52zNa rtyg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738074071; x=1738678871; 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=yuzM3193orwAtCiDoN1UK+E8iWCvUcynY8Tb2Ho7dvw=; b=Er21dVhneOQBM8Wz5gwzSaEgqfzOsZbiS5uBMB+NkmJfSGkghHUZOBXAEIpegw39B+ Z0JqMnJHBLFR/itoTT6GrhQlW/szj3MFpB0XpMCq5pKoB3S1Q0swGnKx2zHJzeXSPcjM pCMcHlVkKsuXwbjAUI2GMMaY7tdCDEMhoE1gQd+45Gvcg9Fccu8UV7Jvp7x2asRhcXia TXHyoj7JgPa/JHj4bt0RGb48V1nDSOhxt3lgr02kEl3C44g0lZCnrtXCoN/xGzHLP4gQ 0mR5blXClNS4xcEyB4kipdOdxsccTaHreGI1BGDf+P6Awg79kXkFYSR9qGy7LRyIRru9 0Acg== X-Gm-Message-State: AOJu0YxRoOBb+VDp5Lxx6j70Of3aDCpUr77umWAZH0Kh0yAkwQK/Sn1P XhAdZj/j2Bv0fqdJXtp7JgT9FEkn2Ov/KeCeDkigdr7jNgw7hM6TJV9KtIaSi6VNraEkOoXpFh1 MLq1RZ5APH48Jb6Dlhg9FcIJpj1iKLNR+ X-Gm-Gg: ASbGncsRFY9EqK32XYjaRb3BCuTb0sMTJNF9cE2hgZdqxVOsM7R6EbT0vvhPjvs4Fp4 UXr/4NIxiSW3fzMwyA85l2P8cZCKW5zZZjit5RgQuvrV0/yC/N16qbuvTAAhY6dHfkq02CniMXg == X-Google-Smtp-Source: AGHT+IFBjlwBkyjMc08MiJeiYOH8uQ2BSO0PSJZ/8DR8rjvG0DRGi4HQAwgRXHiyKlveT2mZZAaaz8CGy8WTJytyoiQ= X-Received: by 2002:a05:6871:3790:b0:29f:d993:a4cb with SMTP id 586e51a60fabf-2b30f4ef5b7mr2030994fac.14.1738074071182; Tue, 28 Jan 2025 06:21:11 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 28 Jan 2025 09:20:59 -0500 X-Gm-Features: AWEUYZleK8jLCNUtakI9kS61ghCmQFMZ2TOH_mEVXIf3q9ZYSMJ0RSbsZJOxaF0 Message-ID: Subject: Re: Log retention query To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000009b38ab062cc4e79c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009b38ab062cc4e79c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 i= n >> 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 > > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000009b38ab062cc4e79c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Deleting old log=C2=A0files is _NOT_ a Postgresql pro= blem.=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 o= bvious and explicit.)

dec= lare -i Weeks=3D8
declare -i Days= =3D$Weeks*7
declare Dir=3D/var/lo= g/postgresql
declare OldFiles=3D$= (find $Dir/p*-*log* -mtime +$Days | sort)
if [ -z "$OldFiles" = ];
then
=C2=A0 =C2=A0 echo "No old files to delete in ${Dir}.&qu= ot;
else
=C2=A0 =C2=A0 rm -v $OldFiles
fi
On Tue, Jan 28, 2025 at 8:41=E2=80=AFAM Paul Brindu= sa <paulbrindusa88@gmail.com= > wrote:
@Junwang apol= ogies, I should have mentioned that=C2=A0 we've tried setting up a cron= tab and it has not worked. Have you got something similar working?
=
@Laurenz: log_filename: 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.a= t> 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!
--0000000000009b38ab062cc4e79c--