Message-ID: <c216fb24d1e74f5375ddea256082a6abe4589daa.camel@cybertec.at>
Subject: Re: Please correct me - PIT - Redirected recovery
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Subramanian,Ramachandran" <ramachandran.subramanian@alte-leipziger.de>,
  "pgsql-novice@lists.postgresql.org"	 <pgsql-novice@lists.postgresql.org>
Date: Wed, 18 Feb 2026 10:52:29 +0100
In-Reply-To: <8ee01402028e4fabb153a6420cce3a2a@alte-leipziger.de>
References: <8ee01402028e4fabb153a6420cce3a2a@alte-leipziger.de>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/c216fb24d1e74f5375ddea256082a6abe4589daa.camel%40cybertec.at>
Precedence: bulk

On Wed, 2026-02-18 at 08:01 +0000, Subramanian,Ramachandran wrote:
> I am formulating a backup strategy for our Production environment.
> =C2=A0
> I use pg_basebackup ( naturally with archive logging ) .
> =C2=A0
> I have successfully tested a full recovery and a point in time recovery a=
t the source server.
> =C2=A0
> I have also tested a full recovery in a secondory server with the backups=
 taken from the source server.
> =C2=A0
> My question is regarding the possibility of a PIT recovery in the seconda=
ry server.
> =C2=A0
> =C2=A0=C2=A0=C2=A01. Is it possible to do a PIT Recovery in the secondary=
 server =C2=A0WITHOUT copying over the
>       archive logs from the primary server IF the recovery point lies WIT=
HIN 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 strea=
m) 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 W=
AL included in the
pg_basebackup is "recovery_target =3D immediate".

> =C2=A0=C2=A0=C2=A02. Where does Postgres keep track of the information ab=
out which log was archived at what time?
>     =C2=A0 Is there a command =C2=A0to get this information from postgres=
?

Nowhere.  Each commit record in the WAL contains the commit timestamp, whic=
h is used for
point-in-time-recovery.  PostgreSQL simply tries to restore WAL from the ar=
chive until it gets
an error or until it decides it needs no more.  You'll see it trying to res=
tore WAL segments
that don't exist; that is to be expected.

> =C2=A0=C2=A0=C2=A03. 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