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

Hello

  Thank you so much for your Answers.  

I am unable to understand the answer to Case 3.  May I restate it again with your permission.

Archive logging is NOT enabled  and a transaction keeps filling up the logs one by one  till all the logs are full
WITHOUTAny COMMIT. Then what happens?  Does Postgres hang or does Postgres terminate this thread ? 
 

After reading your answers, I understand that Archive logs are needed ONLY if we want to go back in time to a past
pointof consistency ( example  Current date minus 5 days ) .  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
rollforwardto a Point in time on this day.   1. Did I understand correctly ?  2. Is there any other scenareo where
Archivelogs will be used?
 


I will look into a pre made solution like pgbackrest. But I want to understand the concepts before that. 


Thank you again for taking the time for my questions.

LG

Ram



Freundliche Grüße

i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie

Alte Leipziger Lebensversicherung a.G.


Hallesche Krankenversicherung a.G.







Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.),
FrankKettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
 
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814





 
Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.),
FrankKettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
 
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5
VersStG)· Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
 
 



 
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier:
https://www.alte-leipziger.de/impressum
 





______________________

ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de



-----Ursprüngliche Nachricht-----
Von: Laurenz Albe <laurenz.albe@cybertec.at> 
Gesendet: Donnerstag, 27. November 2025 15:00
An: Subramanian,Ramachandran IT-md-db <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org
Betreff: Re: how long should Archive logs be retained

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
removedfrom 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