A PostgreSQL base backup does not establish point-in-time recovery unless the required write-ahead log, or WAL, is also available. When archiving fails, identify the missing segment and the usable recovery boundary before retention or manual cleanup removes the remaining evidence. Restore the archive's correct success behaviour, then verify retrieval and recovery.1
This article uses PostgreSQL 18. It concerns native continuous archiving around a physical base backup, not a logical SQL dump or a VMware freeze/thaw hook. No archive command, backup, restore or server configuration was executed for it.
Separate three states of a WAL segment
A segment can exist in the primary's local WAL directory, be reported as archived, and be independently retrievable from the recovery archive. Those are different observations. The protection objective usually depends on the last of them, together with a complete sequence from the base backup to the chosen recovery point.
PostgreSQL requires a continuous archived WAL sequence extending back at least to the start of the base backup for continuous-archive recovery.1 A recently copied segment cannot fill an earlier missing interval simply because its filename sorts later.
Also record the archive's server/cluster identity and timeline. Recovery creates a new timeline, and the timeline is represented in WAL naming and history. Retaining files from several histories is legitimate, but the restore must follow the intended branch.1 Do not merge unrelated servers' archive directories on the assumption that all WAL files are interchangeable.
Confirm the actual archive implementation
Record the PostgreSQL major version, backup method, WAL/archive settings, archive destination and process owner. PostgreSQL 18 supports a shell archive_command or an archive_library module; the implementation determines where detailed failure evidence lives.1
Read the configured route using existing authorised access, without publishing embedded secrets or executing the command by hand. If a script calls an object-storage client, include its result handling and destination identity in the review. The database only receives the success/failure signal implemented by that route.
For a shell archive command, PostgreSQL treats zero exit status as successful archiving and can subsequently recycle or remove the local file. A nonzero result triggers later retries. The command must therefore return zero only when the required archival operation actually succeeded.1
An especially dangerous defect is a wrapper that starts an asynchronous copy, logs a message and exits successfully before durable completion. That is an implementation question to investigate, not proof that a particular environment has the defect. Trace the real command's contract with its destination.
Inspect counters and logs together
The following read-only example selects documented PostgreSQL 18 archiver statistics. Use an authorised diagnostic role and retain the capture time. The example is untested.
clock_timestamp() records the actual current date and time with a time zone; it is not fixed at transaction start.3
SELECT clock_timestamp() AS captured_at,
archived_count,
last_archived_wal,
last_archived_time,
failed_count,
last_failed_wal,
last_failed_time,
stats_reset
FROM pg_stat_archiver;
The counters describe archive activity since their recorded reset. A failed attempt can be followed by a successful retry, so last_failed_wal is not by itself a list of permanently missing archive segments.2
Conversely, zero failures does not establish a healthy archive. PostgreSQL documents certain command/shell failures and archive-module errors that cause the archiver process to restart without being counted in pg_stat_archiver.1 Correlate server logs and the archive implementation's own logs with the counter history.
Do not infer that every segment older than last_archived_wal was successfully archived. The statistics reference explicitly says archival order is not guaranteed in all circumstances, including promotion and crash-recovery cases.2 Verify the required sequence itself.
Locate the failed stage
Use the exact segment named in the incident and distinguish these cases:
| Evidence | Next investigation |
|---|---|
| Command fails before accessing the destination | Executable, configuration, runtime environment and service-identity access. |
| Destination rejects the write | Capacity, permissions, credentials and destination-specific errors. |
| Existing object/file has the same name | Determine whether it is the correct identical archive or a namespace collision; do not overwrite blindly. |
| Database reports success but retrieval fails | Review the wrapper's completion contract, destination identity and retention. |
| Archiving progresses but the target remains unrecoverable | Check earlier gaps, base-backup dependency and timeline selection. |
These are diagnostic branches, not a request to rotate credentials, change policies or clear the archive during inspection. Preserve the original errors and exact timestamps before making a correction.
PostgreSQL advises archive implementations to avoid overwriting pre-existing archive files, and explains that a file can legitimately be presented for archiving again after a crash.1 A correct implementation must distinguish an already preserved identical segment from a conflicting file. Do not solve repeated failure with an unconditional overwrite or a success response that ignores the difference.
Assess exposure and local capacity
State the newest recovery point supported by a complete, retrievable sequence and the interval whose protection is uncertain. The age of the latest archive action is not an exact count of lost business records. Keep the application's independent transaction evidence available for reconciliation.
If local WAL usage is growing while archiving is blocked, assess storage reserve and the rate of new WAL generation. Other retention mechanisms, including replication slots, can also retain WAL; do not assign all growth to the archiver merely because it has an error.
Never manually delete files from pg_wal to make room. Preserve the recovery model and obtain a DBA-approved capacity or workload decision while correcting the archive path. A temporary increase in capacity is containment; it does not repair a missing archived segment.
With low write activity, a segment may not complete quickly. PostgreSQL documents archive_timeout for limiting that delay, but warns that forced early switches can increase archive storage because archived segments retain their full length.1 A timeout is therefore a design setting with a capacity trade-off, not a way to manufacture missing historical WAL.
Correct the cause without destroying the archive
Prepare a scoped change tied to the failed stage: fix result propagation, restore authorised destination access, correct the target namespace or provide adequate capacity. Record the previous configuration and explain what success means for the selected archive tool.
Changing an archive command is not enough if required WAL was already recycled after a false success. Assess whether a complete sequence still exists elsewhere. If not, the affected recovery interval remains unavailable; a new validated base and archive sequence can protect future recovery but cannot recreate the missing past.
Before any retention change, map the oldest retained base and all WAL/timeline history it requires. Do not use a filename-age rule that discards a still-needed segment. Preserve archive access independently of the primary so the same failure does not remove both production and recovery access.
Verify retrieval and recovery
Design a disposable recovery exercise using a known base backup, the intended timeline and independently recorded synthetic transactions. Retrieve the required segments through the actual recovery route, not a privileged shortcut unavailable during a real incident.
The exercise should distinguish a complete sequence from a missing segment. It should stop visibly when a required segment cannot be supplied and should reach the intended transaction boundary when the full set is available. No such exercise was run here.
Confirm application records and keep the restored copy isolated from production integrations. Record exactly which point and dependencies were demonstrated. One successful recovery does not establish every older retained interval; catalogue coverage and retention checks remain necessary.
After an approved correction, monitor new archival success and independent retrieval for a bounded acceptance period. Preserve the before/after configuration and failures. Returning to an older command must not reintroduce a false-success behaviour; the return plan may instead require pausing the change and preserving WAL while the owner resolves the defect.
Learning notes
- A base backup and one recent WAL file are not a complete recovery chain.
- Archiver counters have reset, ordering and failure-reporting limits.
- Correct success signalling and independent retrieval are essential parts of archive protection.
Acceptance checklist
- [ ] Base backup, timeline and the complete required WAL interval are identified.
- [ ] Archive-command outcomes and counter/reset limitations are recorded.
- [ ] Independent retrieval succeeds through the real recovery route.
- [ ] Isolated recovery reaches the intended boundary and fails visibly when a required segment is missing.
References
- PostgreSQL: Documentation: 18: 25.3. Continuous Archiving and Point-in-Time Recovery (PITR). Accessed 2026-11-01.
- PostgreSQL: Documentation: 18: 27.2. The Cumulative Statistics System. Accessed 2026-11-01.
- PostgreSQL: Documentation: 18: 9.9. Date/Time Functions and Operators. Accessed 2026-11-01.