Re: how long should Archive logs be retained - Mailing list pgsql-novice

On Thu, 2025-11-27 at 10:34 +0000, Subramanian,Ramachandran wrote:
> How long should archived logs be retained?
>  
> I make pg_basebackups once  a day  with wal-method ‚stream‘  and postgresql.conf has wal_level = replica and
 archive_mode=on
>  
> Case 1:  Recovery to the point in time when the basebackup ended--  Am I correct in
>          assuming that I do not need anything else other than the base backup if I want
>          to just recover to the point in time when the basebackup ended?
>          Please correct me if I am wrong.

Correct.

> Case 2: Recovery to the latest point – For example if a server crashes, I can simply
>         restart the server and postgres and it will rollforward all the active logs since
>         the last flush oft he data buffers to disk happened . Is my understanding correct ?
>         While I understand that all the log buffers are written as soon as a commit is
>         issued, how does Postgres keep track of when the data buffers were flushed to disk?
>         In other words, how does Postgres know from which log sequence number it must begin
>         rolling forward?  Can we as users also see it?

The redo LSN of the latest checkpoint is stored in the control file, so that 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:  No archive logging and transaction that does not commit:  Let us say that we have
>          not enabled archive logging and we have a long running update that fills up all
>          the active logs (WALs) and it has wrapped around. What happens now?  Will the
>          system Hang?  Will Postgres cancel this thread and rollback?  Should we manually
>          cancel the thread?  How can we find out which thread is writing 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 never needs to
undo the modifications from an uncommitted transaction.

> Case 4: Recovery to the latest point with Archive logging enabled:  If  I have archive
>         logging enabled and I have transactions that wrap around the logs without commit
>         and the server crashes,  is it correct to assume that  I need
>    a. my base backup ( with all the ‚then copied WALs‘ )
>    b.  the current WALs in the active WAL directory
>    c. All the archived WALs from the time the base backup began to the time the crash happened
>  
> 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 since 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 pgBackRest.

Yours,
Laurenz Albe