Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vOZKs-00DDKa-31 for pgsql-novice@arkaria.postgresql.org; Thu, 27 Nov 2025 10:34:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vOZKr-005hGL-1M for pgsql-novice@arkaria.postgresql.org; Thu, 27 Nov 2025 10:34:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vOZKr-005hGA-0G for pgsql-novice@lists.postgresql.org; Thu, 27 Nov 2025 10:34:49 +0000 Received: from mx1.alte-leipziger.de ([91.217.145.231]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vOZKo-001liW-35 for pgsql-novice@lists.postgresql.org; Thu, 27 Nov 2025 10:34:49 +0000 X-CSE-ConnectionGUID: 5R5pupN5ROu2pKRirPk9OA== X-CSE-MsgGUID: AyzM44ZfQz2sMaup1HzwaQ== From: "Subramanian,Ramachandran" To: "pgsql-novice@lists.postgresql.org" Subject: how long should Archive logs be retained Thread-Topic: how long should Archive logs be retained Thread-Index: AdxfhJ+2q9JL8lOaTSmvl/OAQnUaGg== Date: Thu, 27 Nov 2025 10:34:40 +0000 Message-ID: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-tmase-matchedrid: GS9sgRD2mCZw0hoJ16zIuxqsS/8jaA/KwqMZFCdDzWHfjCw04FTRLM9E QY6CrFvksDjnOBk03aMtUBe4IrvOV/VUZDyWvrKuiwAyD/hxCLytCYto2DaFq6ZfNYIV43ODC5K K2gbuWm/B5VKDiY3o0XIaAKl2R22/ x-tm-as-user-approved-sender: No x-tm-as-user-blocked-sender: No x-tmase-result: 10--5.490600-8.000000 x-tm-snts-smtp: 5A8C228572B1A1BD4FAC599776F13E2BBCC5D27120ADBC5A7E44A26537A978EE2000:8 x-alsentwithgood: false Content-Type: multipart/alternative; boundary="_000_a02ba9c8a1c046d292766096eecd2631alteleipzigerde_" MIME-Version: 1.0 X-GBS-PROC: 4CJLfqUnsgHWfeqPKbZTdf7tn4RuW1mHt5d36vZyLqCcX0WqpoIoAzATkszQq4TA X-GBS-PROCJOB: =?utf-8?Q?+vbCT+n4tyyEdinU7oUzbpNtMgbt2LPZLNG/URCxcBU6qnBcgSkEqbKP5InU?= =?utf-8?Q?4hT85Pe2taSWTNk9XnAUJADE3WlIFky+fjHpqQ9X5L2pQpMK+9BD60aEzcDr?= =?utf-8?Q?m99agsUBCoq32rp4eaoR65SPWLMYHu8tAGO1XU0FxF26Oc8cf8Y=3D?= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_a02ba9c8a1c046d292766096eecd2631alteleipzigerde_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, Kindly excuse my lack of knowledge. I am new to Postgres and I want= to make sure that I am doing the right thing. How long should archived logs be retained=3F I make pg_basebackups once a day with wal-method =27stream=27 and post= gresql.conf has wal_level =3D replica and archive_mode=3Don 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=3F Please correct me if I am wrong. 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 t= he active logs since the last flush oft he data buffers to disk happened = . Is my understanding correct =3F While I understand that all the log bu= ffers are written as soon as a commit is issued, how does Postgres keep t= rack of when the data buffers were flushed to disk=3F In other words, how= does Postgres know from which log sequence number it must begin rolling = forward=3F Can we as users also see it=3F 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=3F Will the system Hang=3F Will Postgres cancel this= thread and rollback=3F Should we manually cancel the thread=3F How can= we find out which thread is writing without commits=3F Case 4: Recovery to the latest point with Archive logging enabled: If I= have archive logging enabled and I have transactions that wrap around th= e logs without commit and the server crashes, is it correct to assume th= at I need 1. my base backup ( with all the =27then copied WALs=27 ) 2. the current WALs in the active WAL directory 3. All the archived WALs from the time the base backup began to the ti= me the crash happened to bring the system to a consistent state=3F My question in short is, Is it enough if I hold on to my archive logs sin= ce the last backup for any recovery scenareo=3F Thank you so much for your time LG Ram Freundliche Gr=FC=DFe 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 Obe= rursel Vors. des Aufsichtsrats: Dr. Walter Botermann =B7 Vorstand: Christoph Boh= n (Vors.), Dr. J=FCrgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Joche= n Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcs= ek Sitz Oberursel (Taunus) =B7 Rechtsform VVaG =B7 Amtsgericht Bad Homburg v= . d. H. HRB 1583 =B7 USt.-IdNr. DE 114106814 =20 Hallesche Krankenversicherung a.G., L=F6ffelstra=DFe 34-38, 70597 Stuttg= art Vors. des Aufsichtsrats: Dr. Walter Botermann =B7 Vorstand: Christoph Boh= n (Vors.), Dr. J=FCrgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Joche= n Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek Sitz Stuttgart =B7 Rechtsform VVaG =B7 Amtsgericht Stuttgart HRB 2686 = =B7 USt.-IdNr. DE 147802285 Beitr=E4ge zu privaten Kranken- und Pflegekrankenversicherungen unterlieg= en nicht der Versicherungsteuer (=A7 4 Nr. 5 VersStG) =B7 Versicherungsle= istungen sowie Ums=E4tze aus Versicherungsvertreter-/Maklert=E4tigkeiten = sind umsatzsteuerfrei =20 =20 Die Pflichtangaben der ALH Gruppe gem=E4=DF =A7 35a GmbHG bzw. =A7 80 Akt= G finden Sie hier: https://www.alte-leipziger.de/impressum=20 ______________________ 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 --_000_a02ba9c8a1c046d292766096eecd2631alteleipzigerde_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hello,

&nbs= p;

&nbs= p;

 &nb= sp;  Kindly excuse my lack of knowledge.  I am new to Postgres = and I  want to make sure that I am doing the right thing.

&nbs= p;

&nbs= p;

How long = should archived logs be retained=3F

&nbs= p;

I make pg= _basebackups once  a day  with wal-method ‚stream‘ =  and postgresql.conf has wal_level =3D replica and  archive_mod= e=3Don

&nbs= p;

Case 1:&n= bsp; 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=3F   Please correct me if I am wrong.

&nbs= p;

Case 2: R= ecovery to the latest point – For example if a server crashes, I ca= n 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 =3F  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= =3F In other words, how does Postgres know from which log sequence number it must begin rolling forward=3F  Can we as use= rs also see it=3F

&nbs= p;

Case 3: &= nbsp;No archive logging and transaction that does not commit:  Let u= s 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=3F  Will the system Hang=3F  = Will Postgres cancel this thread and rollback=3F  Should we manually= cancel the thread=3F  How can we find out which thread is writing w= ithout commits=3F

&nbs= p;

Case 4: R= ecovery to the latest point with Archive logging enabled:  If  = I have archive logging enabled and I have transactions that wrap around t= he logs without commit and the server crashes,  is it correct to assume that  I need

  1. my base backup ( w= ith all the ‚then copied WALs‘ )
  2.  the current WALs in the active WAL directory=
  3. All the archi= ved WALs from the time the base backup began to the time the crash happen= ed

 

to bring the system to a consistent state=3F

 

 

My question in short is, Is it enough if I hold= on to my archive logs since the last backup for any recovery scenareo= =3F

 

 

Thank you so much for your time

 

 

LG

 

Ram


Freundliche Gr=FC=DFe


i. A. Ramachandran Subrama= nian =20

Zentralbereich Informationst= echnologie

=20

Alte Leipziger Lebensversicherung a. G.<= /SPAN>

Hallesche Krankenversicherung a. G.

=20

______________________

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

Alte Leipziger Lebensversich= erung a. G., Alte Leipziger-Platz 1, 61440 Oberursel

Vors. des Aufsichtsra= ts: Dr. Walter Botermann =B7 Vorstand: Christoph Bohn (Vors.), Dr. J=FCrg= en Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexand= er Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek

Sitz Oberursel (Taunus) =B7 = Rechtsform VVaG =B7 Amtsgericht Bad Homburg v. d. H. HRB 1583 =B7 USt.-Id= Nr. DE 114106814


Hallesche Krankenversicherun= g a. G., L=F6ffelstra=DFe 34-38, 70597 Stuttgart

Vors. des Aufsichtsrats: Dr.= Walter Botermann =B7 Vorstand: Christoph Bohn (Vors.), Dr. J=FCrgen Bier= baum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Maye= r, Christian Pape, Wiltrud Pekarek, Udo Wilcsek

Sitz Stuttgart =B7 Rechtsfor= m VVaG =B7 Amtsgericht Stuttgart HRB 2686 =B7 USt.-IdNr. DE 147802285

Beitr=E4ge zu privaten Krank= en- und Pflegekrankenversicherungen unterliegen nicht der Versicherungste= uer (=A7 4 (1) Nr. 5 b VersStG) =B7 Versicherungsleistungen sowie Ums=E4t= ze aus Versicherungsvertreter-/Maklert=E4tigkeiten sind umsatzsteuerfrei<= /SPAN>


= Pflichtangaben der A= LH Gruppe gem=E4=DF =A7 35a GmbHG bzw. =A7 80 AktG --_000_a02ba9c8a1c046d292766096eecd2631alteleipzigerde_--