SQL Academy · Lesson

Point-in-Time Recovery

Restore to any moment with WAL.

Point-in-Time Recovery is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is Point-in-Time Recovery?

Point-in-Time Recovery (PITR) lets you restore a database to any specific moment in the past, not just to the time of the last backup snapshot.

This is possible because PostgreSQL continuously writes every change to a stream called the Write-Ahead Log (WAL). By replaying WAL records on top of a base backup you can land exactly where you need to be.

The Write-Ahead Log (WAL)

Every INSERT, UPDATE, DELETE, and DDL statement is first written to the WAL before it touches the actual data files. This guarantees durability even if the server crashes mid-write.

For PITR, archived WAL segments are the time-machine tapes. The base backup is the starting point; the WAL fills in everything that happened after it.

-- Check current WAL write location
SELECT pg_current_wal_lsn() AS current_lsn,
       pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file;

Enabling WAL Archiving

Before PITR is possible, WAL archiving must be turned on in postgresql.conf. The key parameters are wal_level, archive_mode, and archive_command.

The archive command copies each finished WAL segment to a safe location such as a local directory, NFS mount, or cloud storage bucket.

-- View current WAL and archive settings
SELECT name, setting, unit
FROM   pg_settings
WHERE  name IN (
  'wal_level',
  'archive_mode',
  'archive_command',
  'archive_status'
)
ORDER  BY name;

Taking a Base Backup

A base backup is a consistent copy of the entire data directory taken while the server is running. PostgreSQL provides the pg_basebackup utility and the low-level SQL functions to coordinate this process.

The backup, combined with archived WAL segments recorded from the backup start time onward, is everything you need for PITR.

-- Check the latest base backup information
SELECT backup_start,
       backup_end,
       pg_size_pretty(backup_total) AS total_size
FROM   pg_stat_basebackup;

Marking a Recovery Target Time

When disaster strikes, the first step is to identify the exact timestamp you want to restore to. A common approach is to find the timestamp just before the harmful event, such as an accidental mass DELETE.

Transaction log tables or application logs often hold this timestamp. WAL itself records commit timestamps when track_commit_timestamp is on.

-- Find the commit timestamp of recent transactions (requires track_commit_timestamp=on)
SELECT xid,
       pg_xact_commit_timestamp(xid::xid) AS committed_at
FROM   pg_stat_activity
WHERE  state = 'idle'
LIMIT  10;

The recovery.conf / recovery Parameters

Recovery behaviour is controlled by parameters placed in postgresql.conf (PostgreSQL 12+) or the older recovery.conf file. The most important ones are:

restore_command — how to fetch archived WAL segments
recovery_target_time — the exact timestamp to stop at
recovery_target_action — what to do when the target is reached (promote, pause, shutdown)

-- Inspect recovery settings that are currently active
SELECT name, setting
FROM   pg_settings
WHERE  name IN (
  'restore_command',
  'recovery_target_time',
  'recovery_target_action',
  'recovery_target_inclusive'
)
ORDER  BY name;

Other Recovery Target Types

Time is not the only way to pin a recovery target. PostgreSQL supports four target types:

recovery_target_time — a timestamp string
recovery_target_xid — a specific transaction ID
recovery_target_lsn — a WAL log sequence number
recovery_target_name — a named restore point created with pg_create_restore_point()

-- Create a named restore point before a risky migration
SELECT pg_create_restore_point('before_migration_2024_07_01') AS restore_lsn;

Simulating the Disaster

To practice PITR in a test environment, intentionally corrupt or delete data after noting the current time. This lets you verify that recovery lands at the right moment and that the deleted rows come back.

-- Record the 'safe' timestamp, then simulate an accident
SELECT now() AS safe_point;

-- (In a test DB only!) accidentally delete important rows
DELETE FROM orders WHERE created_at < '2024-01-01';

-- Confirm the damage
SELECT COUNT(*) AS remaining_orders FROM orders;

Performing the Recovery

Recovery involves three steps:

Stop the running PostgreSQL instance.
Restore the base backup to the data directory.
Configure recovery parameters and start PostgreSQL in recovery mode — it replays WAL until it hits the target time, then promotes to a normal read-write server.

The server logs show each WAL file being applied, making it easy to track progress.

-- After recovery completes, confirm the cluster is no longer in recovery
SELECT pg_is_in_recovery() AS in_recovery,
       pg_last_xact_replay_timestamp() AS last_replayed_txn;

Verifying the Recovery

Once the server promotes, verify that the data is in the expected state. Check row counts, timestamps of the newest rows, and any business-critical values.

It is wise to run these verification queries before allowing application traffic back in, so you can roll forward or adjust the target time if needed.

-- Verify row counts and the latest transaction timestamp after recovery
SELECT 'orders'       AS tbl, COUNT(*) AS rows FROM orders
UNION ALL
SELECT 'order_items'  AS tbl, COUNT(*) AS rows FROM order_items
UNION ALL
SELECT 'customers'    AS tbl, COUNT(*) AS rows FROM customers
ORDER  BY tbl;

PITR Best Practices

A reliable PITR setup needs more than just turning on archiving. Key practices include:

Test restores regularly — an untested backup is not a backup.
Monitor WAL archive lag — gaps in archived WAL make full recovery impossible.
Retain enough WAL history — keep segments at least as far back as your oldest valid base backup.
Use a separate storage location — if the primary disk fails, archived WAL on the same disk is lost too.

-- Check for any WAL archiving failures
SELECT archived_count,
       failed_count,
       last_archived_wal,
       last_archived_time,
       last_failed_wal,
       last_failed_time
FROM   pg_stat_archiver;

Quick Check

Test your understanding of Point-in-Time Recovery.

Recap: Point-in-Time Recovery

In this lesson you learned how PostgreSQL's Write-Ahead Log enables recovery to any past moment:

WAL archiving captures every change after a base backup.
Recovery targets can be a timestamp, transaction ID, LSN, or named restore point.
The recovery process restores the base backup, replays archived WAL, and promotes when the target is reached.
Verification confirms data integrity before reopening to traffic.
Best practices include regular restore tests, archive monitoring, and off-site WAL storage.

PITR is your safety net against human error and data corruption — master it and you master disaster recovery.

Frequently asked questions

Is the “Point-in-Time Recovery” lesson free?

Yes — the full text of “Point-in-Time Recovery” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Point-in-Time Recovery”?

Restore to any moment with WAL. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Point-in-Time Recovery” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy