Managed Postgres: --pitr-time refused while --backup-id restore works, how do I read the recovery window?

Questions / Help
1

On a Basic-plan Managed Postgres cluster (iad) every fly mpg restore --pitr-time <RFC3339> request today was refused with:

pitr_time must be within the recovery window

including a point 104 seconds BEFORE the start of the newest completed hourly backup (Request IDs 01M2JMTPHCRVJX6198BNGWKHRK-sjc and 01M2JMY5MG1QKPQAHT9278PXW5-sjc; earlier the same day 01M2J86FX8VH277HRYBRKWY9JX-sjc and 01M2J89JGRHCEXWCJSGZTBNW17-sjc for an 11-minute-old point). No cluster was created. WAL archiving on the cluster is healthy (archive_mode=on, archive_timeout=60, last_archived_time within a minute of the request). A restore from the same cluster with --backup-id <newest completed backup> was accepted minutes later and is building fine. A --pitr-time restore on one of our clusters did succeed on 2026-09-01.

Questions:

  1. What defines the PITR recovery window, and is there a CLI/API read for its current start and end? fly mpg status, fly mpg backup list and --help don’t show it.
  2. Is point-in-time restore available on the Basic plan, or only named-backup restore?
  3. Does an idle cluster (no writes) affect the window?

We only need the rule so we can schedule restore drills; not asking for a restore to be done for us.

3

Hi there!

For a point-in-time restore, the requested time has to fall inside a window with two ends. Both ends count as inside the window.

  • Start: the completion time of the oldest backup still retained for the cluster.
  • End: the commit time of the most recent transaction on the cluster. If that commit happened within the last archive_timeout (60 seconds on your cluster), the end is set 60 seconds before the commit.

The end is based on the last committed write. It is not based on WAL archiving activity or on backups. Your archiving can be completely healthy and the window’s end will not change, if nothing has been written.

The same error message is returned whether the requested time is before the start or after the end.

Is there a CLI or API read for the window?

No. fly mpg status, fly mpg backup list, and the restore API don’t expose it. The Backups page for the cluster in the Fly.io dashboard shows the current start and end of the recovery window. You can also try to get the value the end is based on by running this against the primary:

SELECT (pg_last_committed_xact()).timestamp;

Is PITR available on the Basic plan?

Yes. Point-in-time restore isn’t limited to particular plans.

Does an idle cluster affect the window?

It does! and that’s likely what you’re seeing. With no writes, the end of the window is at the last commit, even while WAL keeps being archived and hourly backups keep completing. A time 104 seconds before your newest backup started will still be rejected if the last commit happened before that point. Restores with --backup-id don’t go through this check, which is why that one was accepted. Your successful restore on 2026-09-01 most likely targeted a time shortly before a real write.

To make PITR restore drills reliable on a cluster without a lot of activity:

  1. Make a small write on the source cluster that commits a transaction, for example updating a heartbeat row.
  2. Wait more than 60 seconds.
  3. Use a --pitr-time at or before that write, and after the completion of the oldest retained backup.

You can also check the Backups page in the dashboard, or run the query above, to confirm your chosen time is inside the window before starting the restore.

Please give it a try and let me know if it works!

4

Thank you, that explains it exactly. On the cluster in question the last committed
transaction was 2026-09-14 20:33:27 UTC (read via pg_last_committed_xact(), with
track_commit_timestamp on), and every --pitr-time we tried was after that point,
so each refusal was correct. Our drill now reads that timestamp first and asks for
a point at or before it minus 60 seconds.

Two small follow-ups:

  1. Is track_commit_timestamp guaranteed to stay on for Managed Postgres clusters?
    Our drill depends on pg_last_committed_xact() to pick a valid point.
  2. Feature request: expose the current recovery window (start and end) in
    fly mpg backup list or fly mpg status, so scripts can read it without the
    dashboard.
5

Hi!

I’ll pass the feature request along to our team.

Right now, track_commit_timestamp is on for Managed Postgres clusters. The platform sets it on every cluster unconditionally. That said, it’s an internal default with no guarantee.

Your drill will be more robust if it doesn’t depend on reading pg_last_committed_xact() yourself: 1. Record the time of your heartbeat write from the same transaction, for example with UPDATE heartbeat SET at = now() RETURNING now(). That gives you the commit point without needing commit timestamp tracking on your side. 2. Wait 60+ seconds after the write commits. 3. Use that recorded time, or a time slightly before it, as --pitr-time.

As a coherence check before starting the restore, run SELECT (pg_last_committed_xact()).timestamp; and confirm it returns a value, not NULL. A NULL result means commit timestamps aren’t being tracked. In that case the recovery window may only extend to the most recent backup, not your latest write. If the drill ever sees a NULL, fall back to a --backup-id restore and let us know so we can look into it.

Hope that helps.

6

Thanks, that settles it. We adopted your coherence check as written: the drill reads
pg_last_committed_xact() before choosing a point, and a NULL sends it to a --backup-id
restore and flags the run so we can report it to you.

One design note in case it matters for your defaults: our drill only holds a read-only
path to the production cluster, so it does not make a heartbeat write there. It uses
the last real commit minus 60 seconds instead. That means we depend on
track_commit_timestamp staying on.