Postgres and data

Restore PostgreSQL to a timestamp with recovery_target_time

2026-11-09 · 12 min read

Someone ran a DELETE without a WHERE at 10:15, or a migration dropped a table, and you have a base backup and a WAL archive. The question is now how to get the database back as it was at 10:14:59 without making things worse. This post walks the whole procedure for PostgreSQL 15, 16 and 17: pick the target, restore into a scratch directory, configure recovery, watch it finish, verify the data, and decide what to do with the result. The settings are the standard ones from the PostgreSQL documentation, and the examples assume the archive layout from WAL archiving from scratch.

Two rules before any command. Restore next to the live data directory, never over it. And do the first restore on a scratch copy, because the target time you pick from memory is rarely the right one.

Who needs this

A team that shipped a bad migration. A DROP COLUMN or a data-rewriting UPDATE ran against production. The application kept writing afterwards, so rolling the whole database back would lose good work. You need the old state of one table to compare or copy back.

An operator who ran the wrong statement by hand. A DELETE FROM orders in the wrong terminal. You know the minute. You want the database exactly as it was a second earlier.

Someone answering "what did this look like last Tuesday?" A customer dispute or an audit question about a value that has since changed. A scratch restore to that timestamp answers it without touching production.

An operator facing real disaster recovery. The disk is gone. The target is "as late as the archive allows", and the procedure is the same without recovery_target_time.

What you need before starting

• A base backup that finished before the target time. Recovery can only roll forward. A target earlier than the end of the oldest backup fails with a message about the stop point being before the consistent recovery point.
• Every WAL segment from that backup's start up to and past the target. "Past" matters: recovery has to see a record after your target to know it has reached it. After the mistake, force a switch and make sure it is archived: select pg_switch_wal();, then check pg_stat_archiver.
• A free directory with enough space for a copy of the data directory.
• The PostgreSQL binaries of the same major version that took the backup.

Step 1: pick the target time

The honest target is the last moment you are sure the data was good. Sources, in rough order of precision:

• The application log or an audit table, which show when the bad request arrived.
• The PostgreSQL log, if you log statements. log_statement = 'ddl' or 'mod' records the time of the offending statement, and log_min_duration_statement catches slow ones.
• A named restore point, if you made one before the change. This is the cheapest safeguard there is:

select pg_create_restore_point('before_migration_42');

Do that right before every risky migration or manual data fix. A name beats a guessed timestamp.

Always state the time zone. recovery_target_time is a timestamptz. Without an offset, the server's timezone setting applies, and the difference between your laptop and a UTC server is where one-hour mistakes come from. Write '2026-11-09 10:14:59+00'.

Recovery stops by transaction commit time. A transaction that committed at or before the target is kept, and one that committed after is not, regardless of when its statements ran. A long transaction that started before the target and committed after it is lost.

Step 2: copy the base backup into a scratch directory

Take the newest base backup that finished before the target. If your backups are plain-format directories, copy one:

sudo -u postgres mkdir -m 700 /srv/restore/r1
sudo -u postgres cp -a /srv/pg-archive/base/base-20261109T021000Z/. /srv/restore/r1/
sudo -u postgres chmod 700 /srv/restore/r1

The data directory must belong to the user that runs PostgreSQL and must not be readable by others. PostgreSQL refuses to start otherwise. If the base backup is a tarball, unpack it into the empty directory as the postgres user.

Look at what the backup carries:

cat /srv/restore/r1/backup_label
ls /srv/restore/r1/pg_wal

backup_label records the start location and the first WAL file needed. The pg_wal directory holds the WAL streamed during the backup, which is enough to reach a consistent state but not far beyond it.

Step 3: tell PostgreSQL to recover

Since PostgreSQL 12 there is no recovery.conf. Recovery is switched on by an empty file named recovery.signal in the data directory, and its parameters are ordinary settings in postgresql.conf or postgresql.auto.conf.

sudo -u postgres touch /srv/restore/r1/recovery.signal

Debian keeps postgresql.conf in /etc/postgresql, so a plain base backup has none. A scratch directory needs its own minimal one:

# /srv/restore/r1/postgresql.conf
port = 5433
listen_addresses = ''
unix_socket_directories = '/tmp'
archive_mode = off

restore_command = 'cp /srv/pg-archive/wal/%f %p'
recovery_target_time = '2026-11-09 10:14:59+00'
recovery_target_action = 'pause'

The server also looks for pg_hba.conf and pg_ident.conf next to that file, and a Debian base backup has neither. Copy them from the live configuration:

sudo -u postgres cp /etc/postgresql/15/main/pg_hba.conf /etc/postgresql/15/main/pg_ident.conf /srv/restore/r1/

Here is what each recovery setting does.

Two cautions.

First, the exit codes of restore_command carry meaning. A non-zero exit is read as "that file is not in the archive". That is correct at the true end of the archive and wrong when the archive is merely unreachable, because recovery would then end early and promote a database missing data. PostgreSQL treats an exit caused by a shell error, such as "command not found" or exit status 126, as fatal and aborts recovery. The Postgres PITR Kit's fetch-wal.sh uses exactly this: exit 1 for "not in the archive", exit 126 when the archive cannot be read. If your archive is on a network share, do the same in a wrapper.

Second, the server needs settings that are at least as large as the source's, because recovery runs with hot_standby on. max_connections, max_worker_processes, max_wal_senders, max_prepared_transactions and max_locks_per_transaction must be greater than or equal to the values on the server that made the backup, or PostgreSQL refuses to start recovery and tells you which one. The kit saves these values with each base backup for this reason. If the archive was compressed, restore_command must decompress, for example gunzip < /srv/pg-archive/wal/%f.gz > %p.

Step 4: start the scratch instance and watch

sudo -u postgres /usr/lib/postgresql/15/bin/pg_ctl -D /srv/restore/r1 -l /srv/restore/r1.log start
tail -f /srv/restore/r1.log

Use your version's bin directory. You will see lines like these, in this order:

starting point-in-time recovery to 2026-11-09 10:14:59+00
restored log file "000000010000000000000007" from archive
redo starts at 0/7000028
consistent recovery state reached at 0/70000F8
recovery stopping before commit of transaction 1234, time 2026-11-09 10:15:01.2+00
pausing at the end of recovery
Execute pg_wal_replay_resume() to promote.

The wording of the middle lines varies with the target and PostgreSQL version, but the pattern is stable: it restores segments from the archive, reaches a consistent state, replays up to the target, and then either pauses or promotes depending on recovery_target_action. It requests a segment that is not there at the very end of the archive, and the "file not found" message that follows is normal when the target was reached first.

If you see recovery ended before configured recovery target was reached, the archive ran out before the target. The usual cause is that the WAL containing the mistake has not been archived yet. Force a switch on the live server, wait for pg_stat_archiver to show it, and retry. Do not accept a promotion past the end of what you have: a database that stopped early is a stale database that looks healthy.

Step 5: look before you promote

Rule of thumb: a wrong target costs a few minutes on a scratch copy. Promote only after the numbers match.

With pause, the server is up and read-only at the target. Connect and check:

psql -h /tmp -p 5433 -d postgres

select pg_is_in_recovery(), pg_is_wal_replay_paused();
select pg_last_xact_replay_timestamp();
select count(*), max(created_at) from orders;

pg_last_xact_replay_timestamp() is the commit time of the last transaction replayed. It should be just before your target. The row counts and maximum timestamps should match what you expect from before the mistake. This is the moment to adjust. If it is wrong, stop the instance, delete the scratch directory, and run again with a different time. Scratch restores cost minutes, and they are the safe way to find the right second.

If the state is right, resume:

select pg_wal_replay_resume();

When you resume from a pause at the target, recovery ends and the server is promoted. If you used recovery_target_action = 'promote' from the start, it did this on its own.

Step 6: timelines

When recovery ends and the server opens for writes, PostgreSQL starts a new timeline. The old one was timeline 1, the restored one is timeline 2. You will see selected new timeline ID: 2 in the log, a file 00000002.history in the data directory, and, if archiving is on, in the archive. The history file records where timeline 2 forked from its parent.

Why this exists: timeline 1 still contains the bad DELETE and everything after it. Timeline 2 branches off just before it. The WAL files carry the timeline in the first 8 hex digits of their names (00000002...), so the new history cannot overwrite the old one in the archive.

It matters for later recoveries, because recovery_target_timeline decides which branch you follow. With the default latest the server follows the newest timeline. We tested this: with a target time that lay after the DELETE, the default latest produced a database in which the DELETE never happened (it followed timeline 2), while recovery_target_timeline = '1' produced the one where it had happened, and created timeline 3. That is the behavior you want to know about before a second restore from the same archive, not during it.

Check the timeline of a running server:

select timeline_id from pg_control_checkpoint();

Step 7: verify the result

"The server started" proves nothing about the data. Check against something you know:

-- compare against numbers recorded before the mistake, or from a report
select count(*) from orders;
select sum(amount) from orders where created_at < '2026-11-09 10:15:00+00';
select count(*) from audit_log;

Our own drill records a row count and a checksum over every row of each table just before the mistake, then compares after the restore. For one table, a cheap equivalent is select count(*), sum(hashtext(t::text)) from orders t;. It needs to run the same way on both sides, and hashtext is not a stable cross-version function, so use it only for comparing within one major version. The habit of comparing numbers you wrote down beforehand matters more than which checksum you pick; when the restore succeeded but the data was wrong covers why.

Step 8: what to do with the restored copy

You have three realistic options.

1. Extract and merge. The live database is fine except for one table or a set of rows. Move the table out of the scratch copy into its own schema, dump it, load it into the live database, and merge the rows you need. The production database never stops. This is the best option when only part of the data is damaged.

``bash psql -h /tmp -p 5433 -d app -c "create schema recovered; alter table public.orders set schema recovered;" pg_dump -h /tmp -p 5433 -d app -n recovered | psql -d app ``

1. Replace the live database. The whole cluster is ruined, or you accept losing everything after the target. Stop PostgreSQL, move the old data directory aside, put the restored one in place, and start it. On Debian, do that with the cluster's normal configuration in /etc/postgresql, which recovery reads at start:

``bash sudo pg_ctlcluster 15 main stop sudo mv /var/lib/postgresql/15/main /var/lib/postgresql/15/main.broken # copy the base backup in, create recovery.signal, then add the recovery settings # (restore_command, recovery_target_time, recovery_target_action) to /etc/postgresql/15/main/conf.d/recovery.conf sudo pg_ctlcluster 15 main start ``

Once recovery has ended, remove those settings so a later restart does not carry stale targets. Keep the .broken directory until you are sure.

1. Inspect and discard. You wanted to know what the data looked like. Stop the scratch instance (pg_ctl -D /srv/restore/r1 stop) and delete the directory.

After a real recovery, take a new base backup straight away, on the new timeline, and make sure archiving is on for the restored server. A drill should leave archiving off, so a scratch instance cannot write into your real archive. A real recovery needs it on, so the new timeline's WAL and history file get archived.

Mistakes we see

Restoring over the live directory in a hurry. You lose the one copy that might still hold the data. Restore next to it, check, then swap.

Giving a time without a zone. The server's zone decides, and it may not be yours.

A target before the base backup finished. Recovery fails with the consistent-state error. Choose an older base backup, or accept a later target.

A target after the end of the archive. Recovery must see a record later than the target. Force a WAL switch after the incident and wait for it to be archived.

Forgetting /etc/postgresql. On Debian the config is outside the data directory and is not in the base backup. Back it up separately, or the restored data directory has no configuration to run with.

Setting two recovery targets. Only one of recovery_target_time, _lsn, _name, _xid and recovery_target = 'immediate' may be set. PostgreSQL refuses to start with two.

Treating restore_command exit codes loosely. A script that exits 1 for everything makes an unreachable archive look like the end of the archive. See step 3.

Not rehearsing. The first time you run this should not be during the incident. A destroy-your-server recovery drill is the habit, and a written restore runbook keeps the steps in front of whoever is on call.

FAQ

Does recovery_target_time include the transaction at that exact time?

Yes by default. recovery_target_inclusive is on, so transactions that committed at exactly the target are kept, and recovery stops after them. Set it to off to stop just before the first commit at that time. In practice, choose a target a second or two before the mistake and the question disappears.

What does recovery_target_action pause do?

It holds the server at the target, in recovery, read-only, so you can inspect the data. You then call select pg_wal_replay_resume();, after which recovery ends and the server is promoted, or you shut it down and try another target. With promote the server opens for writes by itself, and shutdown stops it at the target.

Why did my restore stop with "recovery ended before configured recovery target was reached"?

The archive ended before the target time. Either the WAL after your target has not been archived yet, or a segment in the middle is missing. Force a switch on the live server with pg_switch_wal(), wait until pg_stat_archiver shows it archived, and retry. If a segment really is missing, an older base backup cannot help past the gap, so recover to a point before it.

Do I need recovery.signal or standby.signal?

For point-in-time recovery you want recovery.signal. It makes the server recover to the target and then end recovery. standby.signal starts a server that keeps following a primary, which is a different job. If both files exist, PostgreSQL treats the server as a standby.

Can I restore just one table to a point in time?

Not directly, because recovery restores the whole cluster. Restore a scratch copy to the target time, move the table into a separate schema there, dump that schema and load it into the live database. The live server keeps running.

The Postgres PITR Kit wraps these steps in restore-to-time.sh with time, named-point and LSN targets and a gap check, and its drill runs a full destroy and restore so you see the result before you need it.

postgrespitrrecoverywalbackup