When a PostgreSQL base backup is taken with a storage snapshot between pg_backup_start and pg_backup_stop, what must the script get right for it to restore?
answer
- one session from start to stop
- the stop call returns two files' contents
- never into the live data directory
- standby waits only with archive_mode always
basics
~10 sCall pg_backup_start and pg_backup_stop in the same open session, with WAL archiving working. Write pg_backup_stop's labelfile to backup_label (and spcmapfile to tablespace_map) inside the backup, byte for byte, and keep the archived WAL.
solid answer
~40 sThe low-level API lets a snapshot or file-copy tool make the base backup. Archiving must already work. `pg_backup_start(label, fast => false)` waits for a checkpoint (`fast => true` forces one) and must run in a session that stays connected: if that session ends, backup mode is aborted, so start and stop in two separate `psql -c` calls fails. After the copy, `pg_backup_stop(wait_for_archive => true)` in the same session returns `lsn`, `labelfile` and `spcmapfile`; write `labelfile` to `backup_label` in the backup's root and a non-empty `spcmapfile` to `tablespace_map`, byte for byte, never into the live data directory. Without `backup_label`, recovery does not know where replay must start. On a standby the stop waits for archiving only with `archive_mode = always`.
code
sql · 9 lines-- One psql session, kept open for the whole backup
SELECT pg_backup_start(label => 'claims-snap-2026-10-06', fast => false);
-- (snapshot the data, WAL and tablespace volumes from another shell)
SELECT lsn, labelfile, spcmapfile
FROM pg_backup_stop(wait_for_archive => true);
-- write labelfile to <backup>/backup_label
-- write spcmapfile, if not empty, to <backup>/tablespace_mapgo deeper
Know that pg_basebackup does this for you and that the low-level functions are pg_backup_start and pg_backup_stop.
Explain the sequence: archiving on, start, copy, stop in the same session, and saving labelfile as backup_label inside the backup.
Spot the classic script bugs: separate sessions, backup_label lost or written to the live directory, wait_for_archive false, and standby waits needing archive_mode = always.
Judge when storage snapshots beat streaming copies for a multi-terabyte cluster, and what verification each approach still needs.
## When the low-level API is used `pg_basebackup` puts the server into and out of backup mode for you. The **low-level API** lets another tool make the copy instead: a storage or volume snapshot, `rsync` or `tar`. It is how snapshot-based backups of a large cluster are done when a single atomic snapshot of everything is impossible, for example when the 2 TB insurance-claims cluster keeps its data directory, its WAL and a tablespace on separate volumes that cannot be snapshotted at the same instant. If one snapshot really does capture the data directory, WAL and every tablespace at the same instant, it is **crash-consistent**: a server started on it replays WAL as after a crash, and no API call is needed. The API covers every other case. ## The sequence 1. **Make sure WAL archiving works.** The copy is useful only together with the WAL written while it was taken. 2. **Enter backup mode** as a superuser or a role granted `EXECUTE` on the function (it is revoked from `PUBLIC`): `SELECT pg_backup_start(label => 'claims-snap-2026-10-06', fast => false);`. With `fast => false`, the default, the call waits for the next scheduled checkpoint; `true` requests an immediate one at the cost of an I/O burst. 3. **Keep that session open.** If the connection that called `pg_backup_start` ends, the backup is aborted. 4. **Snapshot or copy** the data directory and every tablespace. Files changing during the copy is normal, so tell "file changed" warnings apart from real errors. Leave out the contents of `pg_wal/`, plus `postmaster.pid` and `postmaster.opts`, and usually the contents of `pg_replslot/`. 5. **Leave backup mode in the same session**: `SELECT * FROM pg_backup_stop(wait_for_archive => true);`. 6. **Save its output.** It returns `lsn`, `labelfile` and `spcmapfile`. Write `labelfile` to `backup_label` in the backup's root and `spcmapfile`, when not empty, to `tablespace_map`, byte for byte. `backup_label` records the label, the start time and the starting WAL location. Its presence and contents are what tell recovery where replay must begin for this copy. ## What goes wrong | Mistake | Result | |---|---| | Start and stop in two separate `psql -c` calls | the first session ends and aborts backup mode; the stop fails with "backup is not in progress" | | `backup_label` not saved with the copy | recovery starts from the checkpoint in the copied `pg_control`, which can be later than the copy began, so early-copied pages are never repaired | | `backup_label` written into the live data directory | after a crash, the primary fails to restart | | `wait_for_archive => false` without watching the archive | the last needed segment may never be archived; the backup is incomplete | | `statement_timeout` cutting `pg_backup_stop` short | the backup may not be valid | ## Primary versus standby - On a **primary**, `pg_backup_stop` switches to a new WAL segment so the last one can be archived, and, when `archive_mode` is on and `wait_for_archive` is true, returns only after that segment is archived. It also writes a backup history file into the archive. - On a **standby**, it cannot switch segments and waits only if `archive_mode = always`. Running `pg_switch_wal()` on the primary hurries the last segment along when writes are sparse. Several backups can run at once, whether started through this API or by `pg_basebackup`. ## Restoring The snapshot plus `backup_label` (and `tablespace_map`) plus the archived WAL form a base backup like any other. Restoring it uses `recovery.signal` and `restore_command`, which belong to point-in-time recovery.
- If one storage snapshot captures the PostgreSQL data directory, pg_wal and all tablespaces at the same instant, do you still need pg_backup_start?No. That snapshot is crash-consistent: a server started on it replays WAL as after a crash. The API is needed when the volumes cannot be frozen simultaneously, or when you want the copy combined with archived WAL for point-in-time recovery.
- Why should pg_replslot contents usually be left out of a low-level PostgreSQL base backup?Copied slots would exist on a standby built from the backup while their clients keep using the primary's slots. The standby could then retain WAL indefinitely, and the primary could bloat if hot standby feedback is on. On a new primary, the slots would be badly out of date anyway.
- pg_backup_start and pg_backup_stop can run from separate psql invocations
- the archive's backup history file can stand in for backup_label
- write backup_label into the live data directory so recovery finds it
- set wait_for_archive => false to make the backup finish faster safely
- a non-atomic snapshot of separate volumes is crash-consistent without the API