PITR and instance without any activity

From: Adrien Nayrat <adrien(dot)nayrat(at)anayrat(dot)info>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: PITR and instance without any activity
Date: 2022-12-12 16:53:40
Message-ID: 205ae04e-69a6-f60a-eafc-eb6f9982fee5@anayrat.info
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I came across a weird problem about PITR with a recovery_target_time and
a cluster without any activity. The issue is quite simple, we just try
to do a PITR by specifying a recovery_target_time after the backup.

The main issue, is that there is no timestamp in the wal as there is no
activity. Postgres fetches the record timestamp thanks to
getRecordTimestamp:

/*

* Extract timestamp from WAL record.

*

* If the record contains a timestamp, returns true, and saves the
timestamp
* in *recordXtime. If the record type has no timestamp, returns false.

* Currently, only transaction commit/abort records and restore points
contain
* timestamps.

*/

So, Postgres tries to replay all the wal until the end, and we got this
message:
FATAL: recovery ended before configured recovery target was reached

Before 13, promotion was done without any error:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=dc788668bb269b10a108e87d14fefd1b9301b793

I wonder if we may add a timestamp for other wal records? Maybe when we
switch xlog?

I may have spotted a bug, I tried to create restore point. I expected
Postgres will get its timestamp as comment in getRecordTimestamp.
But Postgres did not find any timestamp and keep replaying the wal even
after the restore point.

FYI, It seems I am not alone with this issue:
https://github.com/pgbackrest/pgbackrest/issues/1934
https://github.com/pgbackrest/pgbackrest/issues/1526#issuecomment-937759691

Thanks

--
Adrien NAYRAT

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Matthias Apitz 2022-12-12 17:16:39 names of the WAL files
Previous Message Ajin Cherian 2022-12-12 13:13:55 Re: Support logical replication of DDLs