Thread

1. Please correct me - PIT - Redirected recovery

   Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-02-18T08:01:12Z

   Hello,
   
     Kindly excuse my beginner question.
   
   I am formulating a backup strategy for our Production environment.
   
   I use pg_basebackup ( naturally with archive logging ) .
   
   I have successfully tested a full recovery and a point in time recovery at the source server.
   
   I have also tested a full recovery in a secondory server with the backups taken from the source server.
   
   My question is regarding the possibility of a PIT recovery in the secondary server.
   
   
     1.  Is it possible to do a PIT Recovery in the secondary server  WITHOUT copying over the archive logs from the primary server IF the recovery point lies WITHIN THE RANGE of the logs contained in the pg_basebackup ? In other words is it possible to rollforward within the range of the logs present in pg_wal directory.
   
   
     1.  Where does Postgres keep track of the information about which log was archived at what time?  Is there a command  to get this information from postgres?
   
   
   
   
     1.  I have not tested this yet, but logic tells me that IF I COPY over the ARCHIVE LOGS from the source to the target server, then PIT Recovery should be possible. Please correct me if I am wrong.
   
   Thank you in advance for your kind answers.
   
   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.subramanian@alte-leipziger.de
   www.alte-leipziger.de
   www.hallesche.de
   
   

2. Re: Please correct me - PIT - Redirected recovery

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-02-18T09:52:29Z

   On Wed, 2026-02-18 at 08:01 +0000, Subramanian,Ramachandran wrote:
   > I am formulating a backup strategy for our Production environment.
   >  
   > I use pg_basebackup ( naturally with archive logging ) .
   >  
   > I have successfully tested a full recovery and a point in time recovery at the source server.
   >  
   > I have also tested a full recovery in a secondory server with the backups taken from the source server.
   >  
   > My question is regarding the possibility of a PIT recovery in the secondary server.
   >  
   >    1. Is it possible to do a PIT Recovery in the secondary server  WITHOUT copying over the
   >       archive logs from the primary server IF the recovery point lies WITHIN THE RANGE of the
   >       logs contained in the pg_basebackup ? In other words is it possible to rollforward within
   >       the range of the logs present in pg_wal directory.
   
   The WAL included in the base backup (if you use the default option -X stream) is only up to the
   end of the backup, so you will only be able to recover that point in time (as this is also the
   earliest point in time you can recover to.
   
   In other words: the only recovery target that will work reliably with the WAL included in the
   pg_basebackup is "recovery_target = immediate".
   
   >    2. Where does Postgres keep track of the information about which log was archived at what time?
   >       Is there a command  to get this information from postgres?
   
   Nowhere.  Each commit record in the WAL contains the commit timestamp, which is used for
   point-in-time-recovery.  PostgreSQL simply tries to restore WAL from the archive until it gets
   an error or until it decides it needs no more.  You'll see it trying to restore WAL segments
   that don't exist; that is to be expected.
   
   >    3. I have not tested this yet, but logic tells me that IF I COPY over the ARCHIVE LOGS from the
   >       source to the target server, then PIT Recovery should be possible. Please correct me if I am wrong.
   
   Correct.
   
   But rather than doing such manipulations by hand, I recommend that you use a tool like pgBackRest.
   That way, you don't have to re-invent the wheel.
   
   Yours,
   Laurenz Albe