Message-ID: <d615d389a140169a5d338cf540a0c17ed3ec346e.camel@cybertec.at>
Subject: Re: how long should Archive logs be retained
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Subramanian,Ramachandran" <ramachandran.subramanian@alte-leipziger.de>,
  "pgsql-novice@lists.postgresql.org"	 <pgsql-novice@lists.postgresql.org>
Date: Thu, 27 Nov 2025 20:34:10 +0100
In-Reply-To: <329482d836f34a548d071aab63961f11@alte-leipziger.de>
References: <a02ba9c8a1c046d292766096eecd2631@alte-leipziger.de>
	 <1f12660d233efc644edb6a23c7bd4bcc84b503af.camel@cybertec.at>
	 <329482d836f34a548d071aab63961f11@alte-leipziger.de>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/d615d389a140169a5d338cf540a0c17ed3ec346e.camel%40cybertec.at>
Precedence: bulk

On Thu, 2025-11-27 at 15:49 +0000, Subramanian,Ramachandran wrote:
> Archive logging is NOT enabled=C2=A0 and a transaction keeps filling up t=
he logs one by one
> till all the logs are full WITHOUT Any COMMIT. Then what happens?=C2=A0 D=
oes Postgres hang
> or does Postgres terminate this thread ?

Neither.
As I said, PostgreSQL will happily remove WAL segments that contain uncommi=
tted
transactions.  PostgreSQL will never undo any work.  If a transaction is ro=
lled back,
all the uncommitted data are still there, they are only invisible.
Later, the autovacuum daemon (a kind of garbage collection) will remove the=
se data,
but that's a separate activity (that is again logged to WAL).

> After reading your answers, I understand that Archive logs are needed ONL=
Y if we want
> to go back in time to a past point of consistency ( example=C2=A0 Current=
 date minus 5 days ) .

No, archive logs are needed whenever you you need to go back in time at all=
.
With the WAL in pg_wal, all you can do is recover after a crash, which will=
 recover
all committed transactions.

> Ofcourse a begining backup is needed on this day as well ( a backup taken=
 on current
> day - 5 days ) . Then using the backup and the Logs that were archived on=
 this day ,
> we can rollforward to a Point in time on this day.=C2=A0=C2=A0 1. Did I u=
nderstand correctly ?=C2=A0 2.
> Is there any other scenareo where Archive logs will be used?

I am not sure I can follow.

You need a WAL archive whenever you want to undo *anything*.  For that, you=
 have to
restore a base backup that *finished* before the point in time that you wan=
t to
recover to.  Also, you need all WAL from the start of the base backup until=
 the point
in time to which you want to recover.

Yours,
Laurenz Albe