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

I googled and came upon your article about levels of isolation in cybertec.

 Which lead me to a concept called MVCC . 


Wow!!!   Now I understand how it works!! 

  Each transaction has it's own private copy of the data it is interested in, and each private copy of the data has
it'sunique transaction number associated with it. Depending on weather the transaction (TranID) committed or not, and
whocommitted what in which order, a final picture emerges. 
 

Coming from the mainframe world, this is new to me.


Thank you for taking the time to write.


Regards

Ram 





Freundliche Grüße

i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie

Alte Leipziger Lebensversicherung a.G.


Hallesche Krankenversicherung a.G.






______________________

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: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> 
Gesendet: Freitag, 28. November 2025 05:46
An: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-novice@lists.postgresql.org
Betreff: 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.), Frank Kettnaker, Dr. Jochen Kriegmeier,
AlexanderMayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht
BadHomburg 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.), Frank Kettnaker, 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ätigkeitensind 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