Why copying live database files is not a backup · Backup & DR

Recovering a workload onto a clean cluster closed every gap except one: the bytes inside the volumes, which no manifest could reconstruct. Those bytes usually belong to a database, and this part is about protecting them. It opens with the method that protects them most often and proves the least — copying a database’s files while the database is running. The capture below is reproduced even though its first result argues against this lesson’s title, because that is the result which teaches: the naive copy started, recovered, and returned every row.

The copy started, and it returned all 45000 rows

The capture ran against PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2). A cluster was seeded with 5000 rows and then driven with a write workload that added 40000 more. While that workload was in flight, a plain cp -a of $PGDATA was taken to a second directory with no database involvement at all — no backup function called, no lock taken, no signal to the server that anything was happening. By the time the workload finished, the live database held 45000 rows. The copy was then started as a cluster in its own right.

Data-loss riskstarting a cluster from a copy nobody told the database about— EXECUTION VALIDATED on PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2). Part A of the capture: a cp -a of a live PGDATA taken mid-workload, then started with pg_ctl; the two row-count lines are the capture script's own summary.

$ pg_ctl -D /work/naive-copy start

  waiting for server to start.... done
server started
>>> exit code: 0

2026-08-28 13:34:37.611 UTC [94] LOG:  database system was interrupted; last known up at 2026-08-28 13:34:36 UTC
2026-08-28 13:34:37.613 UTC [94] LOG:  database system was not properly shut down; automatic recovery in progress
2026-08-28 13:34:37.613 UTC [94] LOG:  redo starts at 0/17615F8
2026-08-28 13:34:37.634 UTC [94] LOG:  invalid record length at 0/256A8D8: expected at least 24, got 0
2026-08-28 13:34:37.634 UTC [94] LOG:  redo done at 0/256A8B0 system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.02 s
2026-08-28 13:34:37.639 UTC [88] LOG:  database system is ready to accept connections

rows readable from the naive copy : 45000
rows in the live database         : 45000

Read that log the way the startup process wrote it. The copy carries no record of a clean shutdown, so the server reports that the system was interrupted and that it was not properly shut down, and enters exactly the path it would take after a power cut. It finds a checkpoint recorded in the copy’s pg_control, begins replaying the write-ahead log from the LSN that checkpoint names — redo starts at 0/17615F8 — and walks forward until it reaches a point where the next record cannot be parsed. invalid record length at 0/256A8D8: expected at least 24, got 0 is not a corruption message; it is the ordinary way replay recognises that it has run off the end of the log, because the space beyond the last written record is zeroes. Replay stops there, reports redo done at 0/256A8B0, and the cluster opens.

Then the two lines that make this lesson difficult. 45000 rows from the copy. 45000 rows from the live database. Not approximately, not a subset: the same number. A team that made this copy, started it, counted rows and closed the ticket would have found precisely the evidence they went looking for, and would have gone on making that copy every night for years.

Three things the outcome did not establish

The first is that the files describe any single moment. cp walked the directory tree over several seconds while pages were being written underneath it, and it read each file when it got to it. A relation file read in the first second of the walk is older than one read in the last. An index file read late can carry entries pointing at heap tuples that the heap file, read earlier, does not contain. There is no instant at which the set of files that landed in /work/naive-copy was the state of the database, and no operation in the sequence attempted to create one.

The second is that anything established transaction consistency. Because no backup function was called, PostgreSQL never performed a checkpoint on this copy’s behalf, never recorded where replay must begin for it, and was never given an opportunity to hold anything still. Recovery had no choice but to trust the copy of pg_control it found — which is itself just one more file read at one arbitrary moment during the walk — and to start redo from whatever checkpoint that file happened to name.

The third is the one the transcript makes explicit. The copy worked because every WAL record it needed happened to be inside it. pg_wal lives under PGDATA, so cp -a collected it along with everything else, and it happened to hold an unbroken run of records from 0/17615F8 through 0/256A8B0. Every full-page image and every row change that the mismatched data files needed lay somewhere on that run. That is a property of this execution — this workload, this checkpoint timing, these few seconds — not of the method. Nothing in the log distinguishes a run where the required records were present from one where they were not, because the second case would simply produce a different, plausible-looking recovery ending at a different LSN.

What redo was actually repairing

The reason a copy of a running cluster needs recovery at all, and the reason that recovery is defined after a crash but not after a cp, is the same mechanism seen from two directions.

A running cluster on disk is never a picture of the database

PostgreSQL does not write a change into its data files when a transaction commits. It writes a description of the change into the write-ahead log and flushes that, while the modified page sits in shared buffers until a checkpoint or memory pressure evicts it. So at any instant the data files on disk are both behind and ahead of the committed state: behind, because a committed transaction’s page may not have been written yet; ahead, because the background writer may already have written out a page dirtied by a transaction that has not committed and may never commit. Neither is a defect. Correctness comes from pairing the files with the log, never from the files alone.

A checkpoint is the moment that pairing gets written down. It flushes dirty buffers and records in pg_control a redo point — an LSN with the property that replaying WAL forward from there will bring the data files into agreement with the log. That recorded point is what redo starts at 0/17615F8 is reading out of the copy. The other half of the guarantee is the full-page write: the first time a page is modified after a checkpoint, its entire image goes into WAL, so replay can restore a page that a partial write left torn instead of trying to patch one.

Now put a crash and a cp side by side. After a power cut the files on disk are a real instant. Every write that reached the device did so before the power went, the log describing those writes is on the same device with the same ordering, and replay from the recorded redo point is defined to converge. After a cp of a live cluster the files are not one instant; they are a smear of instants spread across the walk, and pg_control is one more sample inside that smear. Redo from the point it names is not defined to converge. It is merely likely to, because the log usually reaches past everything the files need.

Removing that “usually” is exactly what the backup functions are for. pg_backup_start performs a checkpoint and pins a redo start point for this copy, independent of whatever pg_control inside the copy later says; pg_backup_stop ends the backup and returns the label content to store alongside it, together with the WAL range the copy requires. The files are then allowed to be a smear, because replaying the named WAL range drives every one of them to the same instant.

`PANIC: could not locate a valid checkpoint record at 0/2F20158`

The capture has a second half, and it fails the way people expect the first half to. A very common production arrangement puts the data directory on one volume and pg_wal on another — separate devices, separate logical volumes, separate virtual disks — for entirely sound performance reasons. The nightly snapshot then covers the volume holding the data. The capture reproduced that arrangement directly, by taking the same copied directory and emptying pg_wal.

Service impact possiblethe same directory, WAL missing: what a one-volume snapshot produces— EXECUTION VALIDATED on PostgreSQL 18.6. Part B of the capture: the copied data directory with pg_wal emptied, reproducing a snapshot of a data volume whose WAL lives on a volume that was not snapshotted.

$ pg_ctl -D /work/nowal start

  waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
>>> exit code: 1

2026-08-28 13:34:37.879 UTC [132] LOG:  creating missing WAL directory "pg_wal/archive_status"
2026-08-28 13:34:37.879 UTC [132] LOG:  creating missing WAL directory "pg_wal/summaries"
2026-08-28 13:34:37.879 UTC [132] LOG:  invalid checkpoint record
2026-08-28 13:34:37.879 UTC [132] PANIC:  could not locate a valid checkpoint record at 0/2F20158
2026-08-28 13:34:37.941 UTC [126] LOG:  startup process (PID 132) was terminated by signal 6: Aborted
2026-08-28 13:34:37.941 UTC [126] LOG:  terminating any other active server processes
2026-08-28 13:34:37.942 UTC [126] LOG:  shutting down due to startup process failure
2026-08-28 13:34:37.943 UTC [126] LOG:  database system is shut down

The first two lines are worth noticing: the server cheerfully creates pg_wal/archive_status and pg_wal/summaries because they are absent. It will rebuild the container, but it cannot invent the contents. pg_control in that directory records where the last checkpoint record lives — LSN 0/2F20158 — but it does not contain the record. The record is a WAL record, and the WAL was on the volume nobody captured. With no checkpoint record there is no redo point, and with no redo point no defined way to reconcile the data files with anything, so the startup process aborts rather than guess. Signal 6 is abort(); the PANIC above it is PostgreSQL declining to open a cluster whose correctness it cannot establish.

This is the better failure of the two. It happens immediately, exits 1, names the LSN it wanted, and surfaces in a recovery drill rather than silently at the moment the drill is skipped. Set the two halves beside each other and the only difference is which files landed inside the copy: in Part A the WAL happened to be there, in Part B it was not, and the copying method was identical. A method that produces a working database or an abort() depending on where a mount point sits is not a method.

A volume boundary is a backup boundary

Snapshot atomicity stops at the edge of the thing being snapshotted. A logical volume snapshot captures one logical volume at one instant; two snapshots of two volumes are two instants, and nothing coordinates them. That is why the PostgreSQL documentation, having allowed a frozen filesystem snapshot as an alternative to shutting the server down, warns in the same breath that a cluster spread across multiple filesystems may offer no way to obtain simultaneous frozen snapshots of all of them.

The consequence is a question to answer before the incident: which filesystems does this cluster occupy, and does the mechanism protecting it capture all of them at one instant? Data and WAL on separate volumes is the common case, and tablespaces on their own volumes are the case people forget. Where the answer is no, the low-level backup functions or pg_basebackup are not a refinement, they are the only supported options.

The three arrangements PostgreSQL actually documents

The upstream position is not ambiguous. File System Level Backup states the restriction plainly: “The database server must be shut down in order to get a usable backup.” A cold copy of a stopped cluster is a valid backup, and it is the only form of naive copy that is. The same page allows the frozen-snapshot alternative and attaches the multi-filesystem caveat quoted above. And Continuous Archiving and Point-in-Time Recovery documents the third form: a copy of a running cluster bracketed by pg_backup_start and pg_backup_stop, called from the same connection, with every WAL segment generated between the two calls included in the backup. All three define a redo starting point and a WAL range. The cp -a in Part A defined neither.

In practice the bracketing is delegated to a tool that cannot forget a step. The capture used pg_basebackup, invoked exactly like this:

BACKUP=/work/base
pg_basebackup -D "$BACKUP" -X stream -c fast

Both arguments matter. -X stream collects the WAL generated while the copy runs and ships it with the copy, so the segments the backup depends on are inside it rather than chased out of an archive later — the exact gap Part B fell into. -c fast takes the checkpoint that opens the backup immediately rather than spread out, the same choice the documentation exposes on pg_backup_start. Together they give the directory a declared starting point and a self-contained log.

What that produces differs from the naive copy in a way the recovery log makes visible. The base backup exited 0 and contained 45000 rows; the business then continued, 5000 more orders arrived, and somebody ran an unqualified DELETE that left 0 rows. The archive held five WAL segments, with pg_stat_archiver reporting archived=6 failed=0; an archiver that has quietly stopped is how this arrangement usually fails. Recovering the base backup forward through that archive to a target time before the mistake produced this:

Service impact possiblewhat a backup that declared its own starting point does at recovery— EXECUTION VALIDATED on PostgreSQL 18.6. Part C of the capture, excerpt of the full recovery log: the /work/base backup taken with pg_basebackup -X stream, replayed forward from the WAL archive to a chosen target time.

$ pg_ctl -D /work/base start

  2026-08-28 13:35:12.701 UTC [631] LOG:  starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1
2026-08-28 13:35:12.707 UTC [631] LOG:  restored log file "000000010000000000000003" from archive
2026-08-28 13:35:12.708 UTC [631] LOG:  starting point-in-time recovery to 2026-08-28 13:34:40.077562+00
2026-08-28 13:35:12.708 UTC [631] LOG:  redo starts at 0/3000028
2026-08-28 13:35:12.713 UTC [631] LOG:  restored log file "000000010000000000000004" from archive
2026-08-28 13:35:12.713 UTC [631] LOG:  completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120
2026-08-28 13:35:12.713 UTC [631] LOG:  consistent recovery state reached at 0/3000120
2026-08-28 13:35:12.713 UTC [625] LOG:  database system is ready to accept read-only connections

Every line here is a claim the Part A log could not make. starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080 is the server reading a starting point that the backup itself recorded, rather than guessing from a pg_control of unknown vintage. completed backup recovery says the backup’s own WAL range was fully replayed. And consistent recovery state reached at 0/3000120 is the statement that has no counterpart anywhere in Part A: the cluster is now a transaction-consistent image of a defined instant. The naive copy never printed that line, because nothing in its construction entitled it to.

The same test, for any database engine

None of this is a PostgreSQL peculiarity. Every transactional engine keeps three things — data files, an in-memory working set, and a log that relates them — and enforces rules across all three that no file-copy utility knows about. A copy taken from outside the engine sees only the files, at whatever moments it happened to read each one. Whether the result is usable depends on whether it happens to contain a valid starting point and an unbroken run of log from there to some later instant. Different engines name the pieces differently; the dependency is the same.

So the question to ask of any database backup, whatever the product, has three possible correct answers and one common wrong one. The engine is stopped for the duration of the copy. Or the copy is taken through the engine’s own backup interface, which defines the instant and names the log the copy needs. Or the copy is one atomic snapshot spanning every filesystem the engine uses, taken at one instant. If the arrangement in front of you is none of those three, the copy is a bet on the contents of a log directory, and it will be settled on a day you did not choose.

The final discipline is what counts as having won the bet. Part C did not stop at a server that started: it compared the recovered database against an independently recorded property of the business data — 50000 rows and sum(amount)=825025000, both captured before the incident and both matching afterwards. A database that starts has told you it can start. One whose contents match a number written down beforehand has told you something about the data.

What to take from this

A cp -a of a live PGDATA taken during a 40000-row write workload produced a copy that started: database system was not properly shut down; automatic recovery in progress, redo starts at 0/17615F8, redo done at 0/256A8B0, database system is ready to accept connections, and 45000 rows out — matching the live database exactly, on PostgreSQL 18.6.
That outcome established only that this particular copy happened to contain every WAL record its data files needed. cp walked the tree over several seconds, and nothing in the sequence defined a transaction-consistent instant.
invalid record length at 0/256A8D8: expected at least 24, got 0 is how replay recognises the end of the log, not a corruption report. It appeared in the run that succeeded.
The same directory with pg_wal emptied — the arrangement produced by snapshotting a data volume whose WAL lives elsewhere — logged invalid checkpoint record, then PANIC: could not locate a valid checkpoint record at 0/2F20158, and startup process (PID 132) was terminated by signal 6: Aborted, exit 1. That is the safer of the two outcomes.
A backup that defines its own starting point says so at recovery: the base backup replayed with starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080 and reached consistent recovery state reached at 0/3000120. Part A produced no equivalent line.
The documentation describes three arrangements under which a file-system-level copy of a cluster is a backup: the server shut down, one atomic snapshot across every filesystem it occupies, or a copy bracketed by pg_backup_start and pg_backup_stop in one connection with all WAL generated in between.

Cross-course references

PostgreSQL for Production Sysadmins — Part XII (WAL, Checkpoints and Crash Recovery) is the engine-side treatment of the mechanism this lesson leans on, and Part XIII (Backup, Archiving and Point-in-Time Recovery) covers the backup functions and pg_basebackup as database administration; read those for how the commands work, and this lesson for why a copy that starts and returns the right rows still cannot be reported as a backup.
Linux for Production Sysadmins — Part XVI (LVM) explains that a snapshot is taken of one logical volume at one instant, which is the precise reason the Part B failure exists: a cluster whose data directory and pg_wal sit on two logical volumes cannot be captured at a single instant by one snapshot, and the result here was a PANIC rather than a warning.
Proxmox VE for Production Operators — Part XIII (Proxmox Backup Server) captures a running guest at the block layer, which produces the same class of crash-consistent image analysed in Part A above; the gap between that image and a validated database backup is this lesson’s subject, and it is why a guest-level backup of a database server is the start of a database backup strategy rather than the whole of it.