AW: how long should Archive logs be retained

Thank you.. You answered my question  with the below comment.

--------------------------------------------------------

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 want to recover to.  Also, you need all WAL from the start of the base
backupuntil the point in time to which you want to recover.
 


Regarding your below comment
---------------------------------------------
As I said, PostgreSQL will happily remove WAL segments that contain uncommitted transactions.  PostgreSQL will never
undoany work.  If a transaction is rolled back, all the uncommitted data are still there, they are only invisible.
 
Later, the autovacuum daemon (a kind of garbage collection) will remove these data, but that's a separate activity
(thatis again logged to WAL).
 

Question >>>  Wow !!  That is so new to me.  So
1. if log wrap around happens  and some Wal-Files containing uncomitted log records are being overwritten,   
2. AND  iin the mean time some data updates pertaining to these uncomitted transactions have been flushed from the data
buffersto disk
 
3. AND after the data buffers have been flushed the transaction fails ( and I assume releases all the locks ) 

 how does postgres know that  a particular unlocked row it finds in the disk ( or even the bufferpool for that matter )
isnot in a valid state  even though it physically exists in the table?  How can it decode this information?
 


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 20:34
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 15:49 +0000, Subramanian,Ramachandran wrote:
> Archive logging is NOT enabled  and a transaction keeps filling up the 
> logs one by one till all the logs are full WITHOUT Any COMMIT. Then 
> what happens?  Does Postgres hang or does Postgres terminate this thread ?

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

> After reading your answers, I understand that Archive logs are needed 
> ONLY if we want to go back in time to a past point of consistency ( example  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.   1. Did I understand correctly
? 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 want to recover to.  Also, you need all WAL from the start of the base
backupuntil the point in time to which you want to recover.
 

Yours,
Laurenz Albe