AW: how long should Archive logs be retained

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's unique transaction number associated with it. Depending on weather the transaction (TranID) committed or not, and who committed 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(dot)subramanian(at)alte-leipziger(dot)de
www.alte-leipziger.de
www.hallesche.de

-----Ursprüngliche Nachricht-----
Von: Subramanian,Ramachandran <ramachandran(dot)subramanian(at)alte-leipziger(dot)de>
Gesendet: Freitag, 28. November 2025 05:46
An: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; pgsql-novice(at)lists(dot)postgresql(dot)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 backup until 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 undo any 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 (that is 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 buffers to 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 ) is not 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, 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.), 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ä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(dot)subramanian(at)alte-leipziger(dot)de
www.alte-leipziger.de
www.hallesche.de

-----Ursprüngliche Nachricht-----
Von: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Gesendet: Donnerstag, 27. November 2025 20:34
An: Subramanian,Ramachandran IT-md-db <ramachandran(dot)subramanian(at)alte-leipziger(dot)de>; pgsql-novice(at)lists(dot)postgresql(dot)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 undo any 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 (that is 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 backup until the point in time to which you want to recover.

Yours,
Laurenz Albe