Message-ID: <1f12660d233efc644edb6a23c7bd4bcc84b503af.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 14:59:39 +0100
In-Reply-To: <a02ba9c8a1c046d292766096eecd2631@alte-leipziger.de>
References: <a02ba9c8a1c046d292766096eecd2631@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/1f12660d233efc644edb6a23c7bd4bcc84b503af.camel%40cybertec.at>
Precedence: bulk

On Thu, 2025-11-27 at 10:34 +0000, Subramanian,Ramachandran wrote:
> How long should archived logs be retained?
> =C2=A0
> I make pg_basebackups once =C2=A0a day =C2=A0with wal-method =E2=80=9Astr=
eam=E2=80=98 =C2=A0and postgresql.conf has wal_level =3D replica and =C2=A0=
archive_mode=3Don
> =C2=A0
> Case 1:=C2=A0 Recovery to the point in time when the basebackup ended--=
=C2=A0 Am I correct in
>          assuming that I do not need anything else other than the base ba=
ckup if I want
>          to just recover to the point in time when the basebackup ended?
>         =C2=A0Please correct me if I am wrong.

Correct.

> Case 2: Recovery to the latest point =E2=80=93 For example if a server cr=
ashes, I can simply
>         restart the server and postgres and it will rollforward all the a=
ctive logs since
>         the last flush oft he data buffers to disk happened . Is my under=
standing correct ?
>       =C2=A0 While I understand that all the log buffers are written as s=
oon as a commit is
>         issued, how does Postgres keep track of when the data buffers wer=
e flushed to disk?
>         In other words, how does Postgres know from which log sequence nu=
mber it must begin
>         rolling forward?=C2=A0 Can we as users also see it?

The redo LSN of the latest checkpoint is stored in the control file, so tha=
t PostgreSQL
can start recovering from there.

You can use the pg_controldata command or the pg_control_checkpoint() table=
 function
to get that information.

PostgreSQL doesn't need the archive for crash recovery, it uses the WAL in =
pg_wal.

> Case 3: =C2=A0No archive logging and transaction that does not commit:=C2=
=A0 Let us say that we have
>          not enabled archive logging and we have a long running update th=
at fills up all
>          the active logs (WALs) and it has wrapped around. What happens n=
ow?=C2=A0 Will the
>          system Hang?=C2=A0 Will Postgres cancel this thread and rollback=
? =C2=A0Should we manually
>          cancel the thread?=C2=A0 How can we find out which thread is wri=
ting without commits?

Transactions are independent of checkpoints.  The WAL from the uncommitted =
transaction will
just be archived and removed from pg_wal after a checkpoint.  PostgreSQL ne=
ver needs to
undo the modifications from an uncommitted transaction.

> Case 4: Recovery to the latest point with Archive logging enabled:=C2=A0 =
If=C2=A0 I have archive
>         logging enabled and I have transactions that wrap around the logs=
 without commit
>         and the server crashes, =C2=A0is it correct to assume that =C2=A0=
I need=20
> =C2=A0=C2=A0=C2=A0a. my base backup ( with all the =E2=80=9Athen copied W=
ALs=E2=80=98 )
> =C2=A0=C2=A0=C2=A0b. =C2=A0the current WALs in the active WAL directory
> =C2=A0=C2=A0=C2=A0c. All the archived WALs from the time the base backup =
began to the time the crash happened
> =C2=A0
> to bring the system to a consistent state?

No, the WAL in pg_wal is enough.  You don't need an archive for that.
The uncommitted data just get recovered while remaining invisible, and the =
next VACUUM
run will remove them.

> My question in short is, Is it enough if I hold on to my archive logs sin=
ce the last backup
> for any recovery scenareo?

You need to retain WAL from the *start* of the earliest backup you need to =
recover on.

I'd recommend that you look into a ready-made backup solution like pgBackRe=
st.

Yours,
Laurenz Albe