PostgreSQL Recovery: Database Corruption in Production (2026)

Wednesday morning. 09:15 AM. An unexpected power outage. The Uninterruptible Power Supply (UPS) should have covered the load, but something went wrong. It failed. Result: sudden shutdown of all servers. Including the PostgreSQL database managing 8 years of billing data for a manufacturing company with 200 employees. The most recent backup was 48 hours old. This is the worst-case scenario for any sysadmin or DBA: a critical production database corrupted. The pressure was palpable. Stalled billing meant imminent cash flow problems. This is the story of how I managed the recovery, from initial missteps to the decisions that saved what could be saved, and the lessons learned to prevent history from repeating itself.

The Crash: PostgreSQL Fails to Start After Power Outage

The power outage caused a ‘dirty’ shutdown of the server hosting the PostgreSQL database. Upon reboot, the service did not start. Attempts to launch PostgreSQL resulted in errors in the log, indicating corruption of the internal state. It was clear: the cluster was no longer consistent and could not resume normal operations. An initial moment of panic, followed by the realization that every action had to be measured, swift, and documented.

Hours 0-2: Diagnosis — WAL Errors and Corrupted Checkpoint

The first step is always the same: consult the logs. PostgreSQL‘s verbosity is an ally in these moments. I found messages indicating corruption of WAL (Write-Ahead Log) files and the inability to restore a valid checkpoint. This is a classic symptom of a sudden crash that prevented the database from writing all pending transactions to disk and correctly updating checkpoint metadata.

To diagnose the cluster status and check the logs, I used:

# Check PostgreSQL cluster status
pg_lsclusters
# Check error logs
tail -100 /var/log/postgresql/postgresql-*.log

The logs confirmed that the problem lay in WAL integrity and the checkpoint. This meant the database could not determine a safe point to restart from to ensure data consistency.

The First Wrong Choice That Almost Made Everything Worse

Under pressure, the temptation to seek quick fixes is immense. One of the first commands that comes to mind for those with PostgreSQL emergency experience is pg_resetwal. This command is designed to ‘reset’ the WAL state, making the database believe there are no pending transactions to recover. I almost executed it. Fortunately, a more cautious colleague stopped me. pg_resetwal can allow the database to restart, but it does so by ignoring the WAL state, which can lead to significant data loss or silent inconsistency. It’s a powerful tool, but only to be used when you accept potential data loss or are absolutely certain that the data in the WALs is unrecoverable or unimportant. In this case, with critical billing data, we could not afford errors.

Recovery with pg_resetwal: When to Use It and When NOT

pg_resetwal (or pg_resetxlog in older versions) is an emergency utility. Its primary purpose is to ‘trick’ PostgreSQL into restarting after a severe crash that has irrecoverably corrupted control files or WALs. It works by rewriting cluster metadata, such as the last checkpoint and WAL position, to values that allow the database to start. However, it does not repair corrupted data and can lead to an inconsistent database. If used improperly, it can turn partial corruption into total data loss.

When to use it:

As a last resort, when all other recovery attempts have failed.
When you are willing to accept potential data loss or inconsistencies.
In test or development environments where data loss is not critical.

When NOT to use it:

On a critical production database without a recent and valid backup.
If there is a possibility of recovering data via Point-in-Time Recovery (PITR).
Without first making a complete copy of the corrupted cluster for forensic analysis or alternative recovery attempts.

In our case, I decided not to use pg_resetwal because we had a backup, albeit dated, and hoped to recover more recent transactions via archived WALs.

Restore from Backup + Point-in-Time Recovery with WAL Archiving

The winning strategy was Point-in-Time Recovery (PITR). We had a full database backup from 48 hours prior. Crucially, we also had WAL archiving configured, which copied WAL files to a separate, secure storage location. This allowed us to restore the database to the state of the backup and then ‘replay’ all transactions contained in the WAL files up to a specific point before the crash.

The steps were as follows:

Backup of the corrupted cluster: Before any operation, I made a complete copy of the PostgreSQL data directory. This is a critical step to have a rollback point in case of further errors.
Restore the base backup: I restored the full database backup to a new data directory.
Configuration of recovery.conf (or postgresql.conf in recent versions): I modified the configuration file to indicate the location of the archived WALs and the desired recovery target time.

# postgresql.conf (or recovery.conf in older versions)
restore_command = 'cp /backup/wal/%f %p'  # Ensure the path is correct
recovery_target_time = '2026-06-10 09:45:00' # The exact time of the crash or just before

Start the database in recovery mode: Upon restart, PostgreSQL read the backup, then began applying the archived WALs sequentially until it reached recovery_target_time. This process reconstructed the database state up to a few minutes before the power outage.

Once recovery was complete, it was crucial to verify data integrity. I used pg_dumpall | grep -c 'CREATE TABLE' to get a quick estimate of the number of tables and compare it with expectations. Then, I performed more in-depth checks on critical data.

# Verify consistency after recovery (quick example)
pg_dumpall | grep -c 'CREATE TABLE'

How Much We Lost and Why It Could Have Been Zero

The recovery was a success, but not without loss. We lost about 15 minutes of transactions. This was because the WAL archiving frequency was set to 15 minutes. All transactions that occurred between the last archived WAL and the time of the crash were lost. For a company that continuously issues invoices, 15 minutes of missing data is a significant problem, even if manageable by manually reconstructing the last operations.

This loss could have been zero if the WAL archiving frequency had been more aggressive (e.g., every minute) or, ideally, if streaming replication with a standby server had been configured. The latter option would have ensured a near-zero RPO (Recovery Point Objective), minimizing data loss to a few seconds or even zero in case of automatic failover.

Post-Incident Setup: Streaming Replication and Backup Policy

The post-mortem of an incident is the best time to implement improvements. I immediately implemented two critical changes:

Streaming Replication: I configured synchronous replication (or asynchronous, depending on RPO requirements) on a separate server. This ensures there is always an up-to-date copy of the database, ready to take over if the primary fails. This drastically reduces RPO and RTO (Recovery Time Objective).
Backup Policy Revision: The backup policy was revised to include not only daily full backups but also continuous WAL archiving, with an archiving frequency reduced to 1 minute, and regular testing of the recovery process. It’s not enough to have a backup; it is essential to know that it works and how long recovery takes.

An organization with 2,000 endpoints and an environment with 300+ VMs cannot afford a 48-hour RPO. The lessons learned in this incident reinforced the need for a robust Disaster Recovery and Business Continuity strategy, not just in words, but with concrete and tested implementations. Incidents like this, though painful, are opportunities to strengthen the infrastructure.

Common Errors and Troubleshooting

Not having a recent backup: This seems trivial, but it is the main cause of disasters. Regular and tested backups are non-negotiable.
Not archiving WALs: Without WAL archiving, PITR is impossible, and the RPO is tied only to the frequency of full backups.
Not testing recovery: An untested backup is a non-existent backup. Regularly simulate disaster scenarios.
Blindly using pg_resetwal: Always assess the risk of data loss. A longer but secure recovery is better than a quick but inconsistent one.
Ignoring logs: PostgreSQL logs contain all the information to understand what happened and how to proceed.

Conclusions with Operational Takeaways

The PostgreSQL database corruption incident was a brutal reminder of the importance of a well-designed and regularly tested Disaster Recovery strategy. It’s not enough to have a backup; it’s critical that the backup is valid and the recovery process is efficient. The main takeaway is that WAL archiving and, ideally, streaming replication, are non-negotiable components for any production PostgreSQL database with high availability and low data loss requirements. A 15-minute RPO is a luxury few companies can afford. A near-zero RPO is the goal, achievable with the right implementations and a culture of resilience.

For more details on PostgreSQL Point-in-Time Recovery, you can consult the official documentation: https://www.postgresql.org/docs/current/backup-recovery.html

Updated: June 2026