PostgreSQL data recovery checklist: what to preserve before you touch PGDATA

A failed PostgreSQL server invites fast action. That is also when a recoverable incident can become permanent data loss.

Before changing a control file, forcing a reset, or trying a different PostgreSQL binary, preserve the original state. Start by producing a verified working copy so that recovery attempts do not gamble the only surviving data.

This checklist is for corrupted or offline clusters, missing relation files, damaged storage, and dropped-table incidents. If you first need to decide whether the job is backup restore, PITR, or physical salvage, read the PostgreSQL data recovery decision guide.

A common PG data recovery handoff contains only a PGDATA directory and an approximate version. That leaves out the tablespaces, WAL, and command history needed to judge what happened.

Freeze the incident

Stop applications, connection pools, scheduled jobs, and monitoring tools that can write to the cluster. If PostgreSQL is still running and stable enough to serve reads, capture a logical export of the most important data before shutting it down. Do not restart it merely to see whether it comes back.

Check whether the storage itself is failing. Repeated scans of a disk with increasing read errors may destroy the remaining chance to image it. For unstable media, make a block-level image with a tool designed for read errors, then work from the image.

Record the time and every command used. A short incident log prevents two engineers from trying conflicting repairs and makes later results easier to explain.

Preserve more than the PGDATA directory

A PostgreSQL cluster can depend on files outside PGDATA. Copying only the directory named by SHOW data_directory may leave the recovery set incomplete.

Preserve:

• the complete PGDATA directory, including global/pg_control, base/, pg_multixact/, pg_commit_ts/, and every other subdirectory;
• every tablespace target referenced by symbolic links under pg_tblspc/;
• the active WAL directory, named pg_wal in PostgreSQL 10 and later or pg_xlog in older releases;
• any WAL archive that may extend the recovery point;
• PostgreSQL logs, operating-system logs, configuration files, and service definitions;
• extension packages and shared libraries used by the damaged cluster;
• the best available backup, even when it is old or suspected to be incomplete.

If the cluster is running, a normal filesystem copy is not a consistent backup. The PostgreSQL file-system backup documentation requires a shutdown or a consistent snapshot and explains why simultaneous snapshots matter when tablespaces or WAL use other volumes. Copy tablespace targets in the same snapshot set.

Record the exact PostgreSQL identity

Physical files are tied to a PostgreSQL major version, page layout, architecture, and build options. "It was probably PostgreSQL 14" is not enough.

Start with read-only checks:

cat "$PGDATA/PG_VERSION"
"/path/to/matching/bin/postgres" --version
"/path/to/matching/bin/pg_controldata" "$PGDATA" > pg_controldata.txt
find "$PGDATA/pg_tblspc" -maxdepth 1 -type l -printf '%p -> %l\n' \
  > tablespaces.txt

Use pg_controldata from the matching major version. Also record the operating system, CPU architecture, package name, collation libraries, installed extensions, and any vendor-specific PostgreSQL build. Keep a copy of the original binaries if possible.

Catalog DDL is valuable even when it comes from an older backup. Table definitions, column order, data types, TOAST relationships, and extension versions can determine whether raw tuples are decoded correctly.

Make a verified working copy

Keep the first complete copy untouched. Mount it read only when practical, and perform every experiment on a second copy.

Create a file manifest before recovery work begins. At minimum, record path, size, modification time, and a cryptographic hash for each copied file. Store the manifest outside the recovery copy. If a later test changes global/pg_control or a relation file, the hashes will show it immediately.

For a cluster on healthy storage, preserve ownership, permissions, symbolic links, sparse files, and extended attributes. For a failing device, use a block-imaging workflow instead of asking ordinary copy tools to retry bad regions indefinitely.

Do not repair the only copy

Several commands can make a damaged cluster start while reducing what remains recoverable. PostgreSQL describes pg_resetwal as a last resort and warns that the database may be inconsistent afterward. Never run these operations against the original evidence:

• pg_resetwal;
• initdb inside or over the old directory;
• VACUUM FULL, CLUSTER, or a rewrite-heavy schema change;
• REINDEX before preserving the relation files;
• deletion of files that PostgreSQL reports as invalid;
• repeated crash-recovery attempts with different server versions.

Deleting postmaster.pid is also unsafe until you have proved that no postmaster process is using the directory. The file may be stale, but guessing can create two writers against one cluster.

Choose the least destructive recovery path

Use the highest-level method that still works.

1. Restore a verified backup and apply archived WAL when the backup chain is complete. This gives PostgreSQL control of consistency and is usually the safest result.
2. If the old cluster starts and the required tables can be read, export them logically with pg_dump or COPY.
3. If the server cannot start but relation files remain, recover from the physical files on an offline copy. The corrupted instance guide covers this path.
4. If damage is limited to known relation files, isolate those files and follow the corrupted data file guide.
5. For a dropped table without a usable backup, preserve both the relation files and WAL before consulting the dropped-table recovery guide.

Do not treat a successful server start as proof of a successful recovery. A reset may discard WAL history, and a readable table can still contain missing pages or undecodable TOAST values.

Build a recovery handoff package

When another engineer or a recovery tool will handle the data, send enough context to reproduce the file layout:

• the untouched copy or storage image;
• the file and hash manifests;
• PostgreSQL version and pg_controldata output;
• tablespace link targets and the copied target directories;
• WAL files and archive inventory;
• DDL, extension list, logs, and a plain account of what failed;
• a chronological list of commands already attempted.

Do not change filenames or flatten tablespace directories to make the package look tidy. Physical paths and segment suffixes such as .1 and .2 carry meaning.

Validate recovered data in isolation

Import recovered output into a clean cluster that is isolated from production. Keep the source copy read only.

Compare schema objects, row counts, primary-key ranges, and business totals against a known reference. Check large values that use TOAST, non-default data types, and tables near reported bad blocks. Record skipped pages, tuple parse failures, and import errors rather than hiding them in a final row count.

Recovery is complete only when the result is usable and the known losses are documented. Until then, keep every preserved copy and manifest.

A short version for the incident channel

If time is tight, send this:

Stop avoidable writes. Do not restart or run pg_resetwal. Preserve PGDATA, tablespaces, WAL, logs, the exact PostgreSQL binaries, and any backup. Hash the untouched copy, work on a second copy, and validate recovered data in an isolated cluster.