PostgreSQL Crash Recovery: An Oracle DBA’s View

  The first PostgreSQL crash I handled felt unusually quiet. I was prepared to mount the database, check the required logs and start a recovery procedure. Instead, PostgreSQL detected the unclean shutdown, replayed WAL and opened the database without any DBA command.

That automatic restart can make crash recovery appear simpler than it really is. Recovery time still depends on checkpoint activity, WAL volume, storage performance and the availability of required WAL files. A database that normally starts in seconds can behave very differently after a write-heavy workload or storage incident.

What Happens During PostgreSQL Crash Recovery?

When PostgreSQL starts after an operating system failure, forced termination or power loss, it checks the cluster control information and identifies the latest valid checkpoint. Recovery begins from the REDO location recorded in that checkpoint, which can be earlier than the checkpoint record itself.

PostgreSQL then replays the required Write-Ahead Log records to restore physical consistency. After WAL replay completes, it performs an end-of-recovery checkpoint and starts accepting connections.

The database log normally contains messages similar to:

LOG:  database system was interrupted
LOG:  database system was not properly shut down
LOG:  automatic recovery in progress
LOG:  redo starts at ...
LOG:  redo done at ...
LOG:  database system is ready to accept connections

An invalid record length message near the end of recovery does not always indicate corruption. It can simply mean PostgreSQL reached the end of valid WAL. Always read the messages around it before treating it as a failure.

Oracle Redo and PostgreSQL WAL Are Not Identical

The write-ahead principle is familiar to Oracle DBAs. Oracle writes redo before modified blocks reach the data files, while PostgreSQL writes WAL before dirty pages are flushed. Both use these records to recover from a crash.

The main difference is how uncommitted work is handled. Oracle uses undo to roll back uncommitted transactions. PostgreSQL does not physically reverse every uncommitted tuple during startup. MVCC transaction visibility rules keep rows from uncommitted transactions invisible, and vacuum can reclaim the dead tuple versions later.

Recovery Area	Oracle	PostgreSQL
Recovery records	Online redo	Write-Ahead Log
Uncommitted transactions	Recovered using undo	Hidden through MVCC visibility
Recovery-time target	FAST_START_MTTR_TARGET	No direct equivalent
Recovery monitoring	V$INSTANCE_RECOVERY	Logs and operating-system metrics

How Checkpoints Affect Recovery Time

Checkpoint configuration influences how much work may be required after a crash, but PostgreSQL does not provide a guaranteed recovery-time target.

• checkpoint_timeout limits the time between automatic checkpoints.
• max_wal_size is a soft limit that can trigger checkpoints based on WAL volume.
• checkpoint_completion_target spreads checkpoint writes across the available interval. It does not control checkpoint frequency directly.

More frequent checkpoints may reduce the amount of WAL replay required, but they can increase normal write I/O. They may also produce more full-page-image WAL because the first change to a page after a checkpoint can require the complete page image to be logged.

This is the same broad performance-versus-recovery trade-off Oracle DBAs associate with FAST_START_MTTR_TARGET, but the controls are not equivalent.

Useful Checks for the DBA

Start with the PostgreSQL and operating-system logs. If the cluster is stopped, pg_controldata can help identify the recorded cluster state and checkpoint positions. It is not a live recovery progress tool.

# Service, recovery and storage checks
systemctl status postgresql
journalctl -u postgresql --since "30 minutes ago"
pg_controldata "$PGDATA"
iostat -xz 1

# Active checkpoint configuration

SELECT name, setting, unit, source FROM pg_settings WHERE name IN (
'checkpoint_timeout',
'checkpoint_completion_target',
'max_wal_size',
'min_wal_size',
'full_page_writes',
'wal_compression'
) ORDER BY name;

# PostgreSQL 17 and later

SELECT * FROM pg_stat_checkpointer;

# WAL generation

SELECT
wal_records,
wal_fpi,
wal_bytes,
wal_buffers_full,
stats_reset FROM pg_stat_wal;

On releases before PostgreSQL 17, checkpoint counters are available in pg_stat_bgwriter. Compare counter changes over a fixed interval instead of interpreting cumulative values in isolation.

A Production Scenario

On one write-heavy system, application latency showed regular spikes and recovery after a server failure took longer than expected. The production configuration had been inherited from a much smaller environment:

checkpoint_timeout = 5min
max_wal_size = 1GB
checkpoint_completion_target = 0.5

Checkpoint statistics showed that WAL volume was repeatedly triggering requested checkpoints before the timeout. Checkpoint writes were also compressed into a relatively short period.

After measuring WAL generation, disk capacity and storage latency, the team increased max_wal_size, extended the checkpoint interval and used a higher checkpoint_completion_target. The exact values were selected from workload measurements rather than copied from a generic recommendation.

Normal latency became more stable, but the work did not end there. Controlled crash testing was used to measure the new recovery time. Larger checkpoint intervals can improve normal performance while increasing the amount of WAL that may need to be replayed, so both sides of the change must be tested.

Common Recovery Problems

Required WAL Is Missing

This often happens because someone deleted files from pg_wal, a separate WAL filesystem was not mounted, or a restored data directory is inconsistent with the available WAL. Never delete WAL files manually to solve a space problem.

Recovery Is Running but Very Slowly

Check storage latency before concluding that PostgreSQL is stuck. WAL replay can become slow on a throttled cloud volume or a storage device reporting repeated I/O errors.

The WAL Filesystem Keeps Growing

Failed archiving, inactive replication slots, disconnected standbys and backup activity can retain WAL. Check these consumers before modifying checkpoint settings.

Unlogged Tables Lose Their Contents

Unlogged tables are not crash-safe. PostgreSQL truncates them automatically after a crash. They should never be used for data that must survive an unexpected restart.

Lessons from the Field

• A graceful restart does not test the crash-recovery path.
• Recovery time depends on storage throughput as much as checkpoint configuration.
• A large pg_wal directory does not automatically indicate a fault.
• Do not use pg_resetwal as a routine startup fix. It is a last-resort salvage utility.
• Measure checkpoint frequency, WAL generation, archive failures and actual restart duration.
• Keep the expected recovery log sequence and escalation steps in the production runbook.

Frequently Asked Questions

Does PostgreSQL require manual crash recovery?

Normally, no. PostgreSQL starts WAL replay automatically after detecting an unclean shutdown.

Can applications connect during primary crash recovery?

Regular connections are normally accepted only after crash recovery has completed. A hot standby accepting read-only connections is a different recovery scenario.

Will reducing checkpoint_timeout always improve recovery?

No. It may reduce recovery work but can increase checkpoint I/O and full-page-image WAL generation during normal operation.

Is there a PostgreSQL equivalent of V$INSTANCE_RECOVERY?

PostgreSQL does not provide a direct view showing a reliable remaining-time estimate for primary crash recovery. DBAs normally follow the logs and operating-system I/O metrics.

When should pg_resetwal be used?

Only after normal restore and WAL recovery options have been exhausted. If it is used to salvage a cluster, the safer approach is to extract the data and rebuild the database rather than return the same cluster directly to production.

Conclusion

PostgreSQL crash recovery is designed to be automatic, but it should never be treated as a black box. WAL restores physical consistency, MVCC keeps uncommitted tuples invisible, and later vacuum activity reclaims dead row versions.

For Oracle DBAs, redo and checkpoint concepts provide a useful starting point. The major differences are the absence of an Oracle-style undo rollback phase and the lack of a direct PostgreSQL equivalent to FAST_START_MTTR_TARGET or V$INSTANCE_RECOVERY.

Review checkpoint statistics, monitor WAL retention, validate storage performance and test an unclean restart in a controlled environment. Until it has been measured under realistic workload conditions, the expected recovery time is only an assumption.