Thread: PITR and instance without any activity
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
Hello, I bump this thread up, I hope to have more reaction :) TL;DR: * We can't perform PITR on a cluster without any activity since 13 * It seems creating restore point doesn't record a timestamp in wal. Thanks -- Adrien NAYRAT
On Mon, Jan 9, 2023 at 10:59 AM Adrien Nayrat <adrien.nayrat@anayrat.info> wrote:
* We can't perform PITR on a cluster without any activity since 13
* It seems creating restore point doesn't record a timestamp in wal.
I have a cron job that runs this every 5 minutes:
SELECT txid_current() WHERE (pg_last_committed_xact()).timestamp+'5min'::INTERVAL < now()
Combine that with a suitable value for archive_timeout.
On 1/9/23 11:23, Torsten Förtsch wrote: > On Mon, Jan 9, 2023 at 10:59 AM Adrien Nayrat > <adrien.nayrat@anayrat.info <mailto:adrien.nayrat@anayrat.info>> wrote: > > > * We can't perform PITR on a cluster without any activity since 13 > * It seems creating restore point doesn't record a timestamp in wal. > > I have a cron job that runs this every 5 minutes: > > SELECT txid_current() WHERE > (pg_last_committed_xact()).timestamp+'5min'::INTERVAL < now() > > Combine that with a suitable value for archive_timeout. Hello, I don't get how it could be useful ? When we perform PITR, we don't know if there was activity or not. -- Adrien NAYRAT