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
