public inbox for pgsql-novice@postgresql.orghelp / color / mirror / Atom feed
Please correct me - PIT - Redirected recovery 2+ messages / 2 participants [nested] [flat]
* Please correct me - PIT - Redirected recovery @ 2026-02-18 08:01 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> 2026-02-18 09:52 ` Re: Please correct me - PIT - Redirected recovery Laurenz Albe <laurenz.albe@cybertec.at> 0 siblings, 1 reply; 2+ messages in thread From: Subramanian,Ramachandran @ 2026-02-18 08:01 UTC (permalink / raw) To: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org> 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Please correct me - PIT - Redirected recovery 2026-02-18 08:01 Please correct me - PIT - Redirected recovery Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> @ 2026-02-18 09:52 ` Laurenz Albe <laurenz.albe@cybertec.at> 0 siblings, 0 replies; 2+ messages in thread From: Laurenz Albe @ 2026-02-18 09:52 UTC (permalink / raw) To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org> 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-02-18 09:52 UTC | newest] Thread overview: 2+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-02-18 08:01 Please correct me - PIT - Redirected recovery Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> 2026-02-18 09:52 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox