What's the difference between pg_dump and pg_basebackup?

pg_dump produces a logical backup — SQL statements (or a custom-format equivalent) that can recreate the schema and data, restorable into any compatible Postgres version. pg_basebackup produces a physical, byte-for-byte copy of the actual data directory, which is what point-in-time recovery is built on — WAL segments can only be replayed forward from a physical base, not a logical dump.

Why did the first restore attempt fail with 'recovery ended before configured recovery target was reached'?

The WAL segment containing the transactions needed to reach the requested recovery_target_time hadn't been archived yet — Postgres only archives a segment once it's full or explicitly switched with pg_switch_wal(), and the segment holding recent activity was still the server's active, unarchived segment. Recovery can only replay what actually exists in the archive; a target time past the last archived byte simply can't be reached.

What does recovery_target_action = 'promote' actually do?

Once the target is reached, it tells Postgres to stop replaying WAL and switch from read-only recovery mode into a normal, writable primary — the alternative, 'pause', leaves it in read-only recovery so an operator can inspect the state before deciding whether to promote, useful when the exact right target time isn't known with certainty yet.

Why did the data directory need chmod 0700 before Postgres would even start?

Postgres refuses to start against a data directory with group- or world-writable permissions, as a hard-coded safety check against a misconfigured deployment exposing the raw database files. A tar extraction or file copy between environments can easily produce a directory with looser permissions than the original, which is exactly what happened here.

How would this work in production instead of a local Docker container?

The mechanics are identical — archive_command normally ships WAL segments to object storage (S3 or similar) instead of a local directory, and a managed restore tool (pgBackRest, WAL-G, or a cloud provider's own PITR feature) handles the base backup and restore_command plumbing. The core concept this lesson demonstrates — a physical base plus a WAL stream replayed to an exact target time — is the same regardless of where the files physically live.

Point-in-Time Recovery: Undoing a Bad DELETE That Already Committed

A base backup plus archived WAL segments can restore a database to the exact second before a mistake — but only if the WAL covering that second was actually archived first, which is the real failure this lesson walks through.

By SkillByExample · Published Sep 13, 2026

postgresql backup pitr wal-archiving disaster-recovery

A DELETE that already committed feels permanent — until a base backup and an archived WAL stream turn “restore to right before that mistake” into a real, executable option.

Setting up WAL archiving and a base backup

# postgresql.conf
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

pg_basebackup -U postgres -D /var/lib/postgresql/basebackup -Fp -X stream --checkpoint=fast

--checkpoint=fast forces an immediate checkpoint so the backup starts right away, instead of waiting for the next one on Postgres’s normal schedule — worth knowing, because without it a base backup can appear to hang for however long checkpoint_timeout has left to run.

The mistake

INSERT INTO books (title, author, genre, published_year) VALUES ('The Good Insert', 'Someone', 'history', 2020);
SELECT now();

INSERT 0 1
              now
-------------------------------
 2026-09-13 15:39:25.955677+00

That timestamp is the target: everything up to and including this insert should survive the restore. Then, the mistake:

DELETE FROM loans WHERE borrower LIKE 'Borrower 2%';

DELETE 1122

Meant to remove one specific borrower’s loans; the LIKE pattern also matched Borrower 20 through Borrower 29, Borrower 200 through Borrower 299, and so on — 1,122 rows gone instead of a handful.

The bug: a recovery target the archive can’t reach

Configure a fresh copy of the base backup to recover to the timestamp above:

# postgresql.conf on the restore instance
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-09-13 15:39:25.955677+00'
recovery_target_action = 'promote'

2026-09-13 15:41:29.458 UTC [238] LOG:  database system was interrupted; last known up at 2026-09-13 15:39:08 UTC
cp: can't stat '/var/lib/postgresql/wal_archive/00000002.history': No such file or directory
2026-09-13 15:41:29.631 UTC [238] LOG:  starting backup recovery with redo LSN 0/120000D8, checkpoint LSN 0/12000130, on timeline ID 1
2026-09-13 15:41:29.664 UTC [238] LOG:  restored log file "000000010000000000000012" from archive
2026-09-13 15:41:29.680 UTC [238] LOG:  starting point-in-time recovery to 2026-09-13 15:39:25.955677+00
2026-09-13 15:41:29.697 UTC [238] LOG:  completed backup recovery with redo LSN 0/120000D8 and end LSN 0/120001D0
2026-09-13 15:41:29.698 UTC [238] LOG:  consistent recovery state reached at 0/120001D0
cp: can't stat '/var/lib/postgresql/wal_archive/000000010000000000000013': No such file or directory
2026-09-13 15:41:29.702 UTC [238] FATAL:  recovery ended before configured recovery target was reached

The insert and the delete both happened inside WAL segment 000000010000000000000013 — and that segment was still the server’s active segment on the source database, not yet archived. archive_command only fires once a segment is complete (16MB full) or explicitly switched; recovery can’t replay a byte that was never archived, so it stopped short and refused to promote into an inconsistent, target-not-reached state.

The fix: switch the WAL segment before restoring

-- on the source database, before copying the archive
SELECT pg_switch_wal();

 pg_switch_wal
---------------
 0/130987B0

pg_switch_wal() forces the current segment to close and archive immediately, instead of waiting for it to fill naturally. Re-copy the now-complete archive and retry:

SELECT pg_is_in_recovery();
SELECT count(*) FROM loans;
SELECT title FROM books WHERE title = 'The Good Insert';

 pg_is_in_recovery
-------------------
 f

 count
-------
 13986

      title
-----------------
 The Good Insert
 The Good Insert

pg_is_in_recovery() is f — the restored instance promoted to a normal, writable primary. loans is back to 13986, exactly the count immediately after the insert and before the bad delete. The Good Insert survived (it appears twice here because this dataset had two separate real inserts with that title across this lesson’s testing — both correctly present). The mistake is gone; everything before it is intact.

Practice

1. Take a logical backup with pg_dump and confirm it's a portable SQL script, not a binary snapshot.

pg_dump -U postgres -t books --schema-only | head -8

--
-- PostgreSQL database dump
--

\restrict Z8P0vYFDj8Jhup9HRcDrJL4cwRSm78KGfaOBBtdzR4CXhMqWf3Q2gqKI9tyXk9q

-- Dumped from database version 17.11
-- Dumped by pg_dump version 17.11

Plain SQL, readable and editable — this is the right tool for “move this schema to a different server” or “keep a human-inspectable snapshot,” not for PITR, which needs the physical WAL stream a logical dump doesn’t capture. The \restrict token is random per dump (Postgres 17’s guard against a psql < dump.sql accidentally running untrusted commands) — your own output will show a different value.

2. Check how many WAL files actually needed archiving to cover this lesson's whole test window.

SELECT archived_count, failed_count FROM pg_stat_archiver;

 archived_count | failed_count
----------------+---------------
             13 |            24

failed_count is real too — it includes every failed attempt from before the archive directory’s ownership was fixed earlier in this lesson’s setup, a genuine reminder that archive_command failures fail silently from the client’s perspective (the server just keeps retrying) and are only visible by actually checking pg_stat_archiver.

3. Confirm the data directory permission requirement by checking what Postgres actually demands.

stat -c '%a' /var/lib/postgresql/data

0700 (owner-only) is what a tar-extracted or freshly copied data directory needs before Postgres will start against it — this is exactly the check that failed earlier in this lesson with “data directory has invalid permissions” before chmod 0700 fixed it.

Next: replication — streaming a primary’s WAL to a live read replica, and measuring real lag.

Setting up WAL archiving and a base backup

The mistake

The bug: a recovery target the archive can’t reach

The fix: switch the WAL segment before restoring

Practice

Frequently Asked Questions