How to restore a Postgres database to a point in time

Point-in-time recovery gets written about as a backup feature. In practice it is an incident-response feature, and the difference matters, because the thing that decides whether you get your data back is not the technology — it's what you do in the first ten minutes while you are panicking.

So this is a runbook, in order, with the reasoning attached. It uses PandaStack's managed Postgres for the concrete commands, but every step maps onto any platform that does continuous WAL archiving, and onto self-managed Postgres with pgBackRest or WAL-G.

What PITR actually is, in one paragraph

A daily base backup is a full physical copy of the data directory at some moment. On its own it gets you back to that moment and no further. Continuous WAL archiving ships every write-ahead-log segment off the host as Postgres fills it, and the WAL is a complete, ordered record of every change. Restore the base backup, then replay the WAL forward and stop at a chosen timestamp, and you have the database exactly as it was at that instant. That's it. The daily backup is the starting point; the WAL is what makes any second in the window reachable.

The practical consequence is the one people miss: your recovery point objective isn't 24 hours because backups run daily. It's however long it takes a WAL segment to be archived — seconds, usually. The daily number describes how far back you can go, not how much you lose.

Step 1 — stop the writes, don't stop the database

If the damage is ongoing — a job looping through rows, a migration still running — kill it. If it already finished, leave the database up. There's a reflex to shut everything down that mostly hurts: a running database keeps archiving WAL, and a database you can query is a database you can investigate.

-- Find the culprit still running, if any
select pid, usename, application_name, state,
       now() - xact_start as running_for, query
from pg_stat_activity
where state <> 'idle' and pid <> pg_backend_pid()
order by xact_start;

select pg_cancel_backend(<pid>);     -- polite
select pg_terminate_backend(<pid>);  -- if cancel doesn't take

Step 2 — find the timestamp, before you need it

PITR restores to a wall-clock time, so the entire operation rests on one number: the moment just before the damage. Getting it approximately right is not good enough — a minute too late and you've replayed the bad transaction back in.

Sources for the number, in order of reliability: your deploy log or CI run timestamp; the application log line for the request that did it; the created_at or updated_at column on the affected rows; the timestamp on a Slack message where someone said 'uh oh'. If you have a column, use it, and note that it's in the database's timezone:

-- Earliest evidence of the damage
select min(updated_at) at time zone 'UTC' as first_bad_write
from orders
where status = 'cancelled' and updated_at > now() - interval '2 hours';

Take that number and subtract a small margin — thirty seconds is usually right. Write it down in UTC, in RFC 3339 form, because that's what the API wants: 2026-08-20T14:00:00Z.

Step 3 — clone to the timestamp; do not restore yet

This is the step that separates a clean recovery from a second incident. A clone provisions a brand-new database from the same archive at your chosen moment. The damaged database keeps running and keeps serving traffic. Nothing is overwritten. You get to look at the old data before you commit to anything.

curl -X POST https://api.pandastack.ai/v1/databases/$DB_ID/clone \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"label":"orders-before-the-bad-update",
       "target_time":"2026-08-20T13:59:30Z"}'
# 202 -> a new database id; poll GET /v1/databases/<new-id> until "running"

import pandastack

client = pandastack.Client(api_key="pds_...")
clone = client.databases.clone(
    db_id,
    label="orders-before-the-bad-update",
    target_time="2026-08-20T13:59:30Z",
)
print(clone["id"])

Now connect to the clone and check that the moment you picked is the moment you wanted. Two queries: does the good data exist, and does the bad data not exist?

-- On the clone
select count(*) from orders where status = 'cancelled';   -- expect: normal
select max(created_at) from orders;                        -- expect: ~13:59:30

-- Compare against production before you decide anything
select count(*) from orders where status = 'cancelled';   -- expect: catastrophic

If the numbers are wrong, clone again at a different timestamp. Clones are cheap and independent, and iterating on the timestamp costs you a couple of minutes, whereas iterating on an in-place restore costs you a second outage.

Step 4 — choose your recovery shape

With a verified clone in hand you have three options, and the right one depends on how much good data was written after the damage.

Surgical: copy the affected rows back

If the blast radius was one table and the site has been taking legitimate writes since, don't roll the whole database back. Pull the affected rows out of the clone and merge them into production. This keeps every good write and is almost always the correct choice for a scoped mistake.

# From the clone: just the rows that were damaged
pg_dump "$CLONE_URL" \
  --data-only --table=orders \
  --file=/tmp/orders_good.sql

# Safer still: dump into a staging table, merge with SQL you can review
psql "$CLONE_URL" -c "\copy (
  select id, status, total_cents, updated_at from orders
  where id = any('{1001,1002,1003}'::bigint[])
) to '/tmp/fix.csv' csv header"

psql "$PROD_URL" -c "create table orders_fix (like orders including all)"
psql "$PROD_URL" -c "\copy orders_fix from '/tmp/fix.csv' csv header"

-- Review the diff BEFORE you apply it
select o.id, o.status as now_is, f.status as should_be
from orders o join orders_fix f using (id)
where o.status is distinct from f.status;

-- Then, in one transaction
begin;
update orders o set status = f.status, total_cents = f.total_cents
from orders_fix f where o.id = f.id;
-- check the row count, then:
commit;

Promote the clone

If the damage is broad and there were no meaningful writes afterwards — the incident happened at 3am, or you took the app down in step one — the clone is already a correct database. Point the application at its connection string and move on. You keep the damaged original as evidence, and delete it once you're confident.

Restore in place

The reason to prefer this over promoting a clone is connection strings. An in-place restore rolls the same database back to the timestamp and keeps the id, host, and password, so nothing that connects to it needs re-pointing — no env var changes, no redeploy, no forgotten cron job still talking to the old host.

curl -X POST https://api.pandastack.ai/v1/databases/$DB_ID/restore \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"target_time":"2026-08-20T13:59:30Z"}'
# 202; poll GET /v1/databases/$DB_ID until status is "running"

Step 5 — the part everyone skips

Sequences. If you promoted a clone or restored in place, sequences went back in time with everything else, which is correct. But if you copied rows forward from a clone into a live database, you may have inserted ids above the current sequence value, and the next INSERT will collide.

select setval(
  pg_get_serial_sequence('orders', 'id'),
  (select coalesce(max(id), 1) from orders)
);

Then the things outside Postgres that are now inconsistent with it: cached rows in Redis, search indexes, webhooks you already sent for orders that no longer exist, emails, Stripe charges. The database is the easy part of a rollback. Write down which downstream systems drifted and fix them deliberately.

Do this before you need it

An untested restore is a hypothesis. The clone path makes testing it genuinely cheap — it touches nothing in production — so there is no excuse for the first attempt being during an incident.

1. Clone your production database to a timestamp from yesterday. Time it. Now you know your actual RTO, not the one on the pricing page.
2. Check your retention window against what your compliance and your worst realistic 'we noticed a week later' scenario need. Seven days is fine for a bug you catch immediately and useless for silent corruption.
3. Verify backup health rather than assuming it. The archive is scrubbed for gaps and reports ok, warn, or error on GET /v1/databases/{id}/backups — read it on a schedule, not on the worst day of your quarter.
4. Write the timestamp-finding query for your two or three most important tables now, while you're calm.
5. Confirm that deleting a database deletes its backups — because it does, and 'clean up the old one' is a real way to lose your recovery path.

curl -sS https://api.pandastack.ai/v1/databases/$DB_ID/backups \
  -H "Authorization: Bearer $PANDASTACK_API_KEY"
# { "health": { "status": "ok", "detail": "", "checked_at": "..." }, ... }

The whole discipline reduces to one rule, and it's the rule from step three: clone before you overwrite. Everything else is detail.