skip to content

Backup & Restore

PostgreSQL ships its own backup tools: pg_dump for logical copies, pg_basebackup for physical ones, and WAL archiving for point-in-time recovery. Interviewers probe which one meets an RPO.

part ofPostgreSQLread it →
on this pageshow

explore

questions

15

What does PostgreSQL's pg_basebackup copy from a running cluster, and what must the server permit before it can connect and run?

level: juniorimportance: must knowfreq 62%

answer

  1. whole cluster, never one database
  2. replication protocol, not a normal login
  3. role attribute plus a special pg_hba keyword
  4. free WAL senders and a starting checkpoint

basics

~20 s

pg_basebackup copies a running PostgreSQL cluster's files: every database and tablespace at once, never a single database. It uses a replication connection, so it needs a REPLICATION or superuser role, a pg_hba.conf replication line and free WAL senders.

solid answer

~40 s

`pg_basebackup` makes a physical, file-level copy of the entire cluster while clients keep working: all databases, the configuration files that live in the data directory and every tablespace. It cannot copy one database; that is `pg_dump`'s job. It speaks the replication protocol, so the role needs the `REPLICATION` attribute (or superuser), `pg_hba.conf` needs a line whose database field is the keyword `replication`, and `max_wal_senders` (default 10) must leave one sender for the copy plus one for WAL streaming. `wal_level` must be `replica` (the default) or `logical`. The run starts with a checkpoint, spread by default, so it can look idle for minutes; `--checkpoint=fast` forces an immediate one. Output is plain (a ready data directory, the default) or tar (`-F t`: `base.tar`, one tar per extra tablespace, `pg_wal.tar`).

code

sql · 4 lines
sql
CREATE ROLE claims_backup LOGIN REPLICATION PASSWORD 'change-me';

SELECT pid, phase, backup_total, backup_streamed
FROM pg_stat_progress_basebackup;

go deeper

for a junior

Remember that pg_basebackup copies the whole cluster, never one database, and needs a REPLICATION role plus a pg_hba.conf line using the replication keyword.

for a middle

Explain the start-of-backup checkpoint, spread versus fast, the two WAL senders the default method uses, and how plain and tar output treat extra tablespaces.

for a senior

Show you have run it at scale: throttling with --max-rate, reading pg_stat_progress_basebackup, a dedicated backup role, and backing up from a standby with its limits.

for a principal

Frame the copy's cost on the primary (checkpoint I/O, network, two WAL senders) against the recovery time a fresh physical copy buys for the whole platform.

## What pg_basebackup produces A **base backup** is a physical, file-level copy of a PostgreSQL **cluster**: the set of databases served by one server instance and stored under one data directory. `pg_basebackup` takes it from a running server without stopping it and without blocking other clients. The copy holds: - every database in the cluster, because files are copied, not rows; - the configuration files that sit inside the data directory, such as `postgresql.conf`, `pg_hba.conf` and `postgresql.auto.conf`, when your packaging keeps them there; - every additional **tablespace**; - by default, the WAL (write-ahead log) the copy needs so that a server can start on it by itself. There is no option to copy one database or one table. `--dbname` takes a connection string, and the server ignores the database name in it. A selective copy is a logical dump, which is `pg_dump`'s territory. ## What the server must allow `pg_basebackup` does not log in like an application. It opens a connection that speaks the **replication protocol** and is served by a **WAL sender** process. Four things must line up: 1. **The role.** It needs the `REPLICATION` attribute or must be a superuser. Table privileges are irrelevant. A dedicated role is usual: `CREATE ROLE claims_backup LOGIN REPLICATION;`. 2. **`pg_hba.conf`.** A physical replication connection matches only a line whose database field is the keyword `replication`. A line for `all` does not admit it. 3. **WAL senders.** `max_wal_senders` (default 10, changed only with a restart) must leave one sender free for the copy and one more for WAL streaming, which is the default WAL method. 4. **`wal_level`.** It must be `replica` (the default) or `logical`. With `minimal`, the server refuses with "WAL level not sufficient for making an online backup". ## What happens during a run On the server, `pg_stat_progress_basebackup` shows the run phase by phase: | Phase | What is going on | |---|---| | `waiting for checkpoint to finish` | the backup must begin at a checkpoint | | `estimating backup size` | the server sizes the data (skip with `--no-estimate-size`) | | `streaming database files` | files are sent; `backup_streamed` grows | | `waiting for wal archiving to finish` | backup mode ends; with `-X none` it waits for the backup's WAL to be archived | The first phase is where people get confused. With the default `--checkpoint=spread`, the server completes a normal, throttled checkpoint, which on a busy primary can take minutes, and the tool looks hung. `--checkpoint=fast` requests an immediate checkpoint and starts sooner, at the price of an I/O burst on the primary. Other everyday levers: `--max-rate` caps the transfer (kilobytes per second, or an `M` suffix for megabytes), and `-P` prints approximate progress. ## Plain or tar output | | `-F plain` (`p`, default) | `-F tar` (`t`) | |---|---|---| | Layout | a ready data directory | `base.tar`, one `<OID>.tar` per extra tablespace, `pg_wal.tar` with `-X stream` | | Extra tablespaces | written to the **same absolute path** as on the source, unless `-T olddir=newdir` | each in its own tar file | | Compression | server-side only | `-z` or `-Z` with gzip, lz4 or zstd | | Before starting a server | nothing to unpack | unpack each tar into place | Plain output on the same host as the source fails when tablespaces exist without `-T`, because the copy would land on the original tablespace paths. ## The claims cluster, nightly For the 2 TB insurance-claims cluster, the nightly copy to the backup host looks like this: ```bash pg_basebackup -h claims-db1 -U claims_backup \ -D /backup/claims/2026-10-06 -F tar -z \ -X stream --checkpoint=spread -P ``` The target directory must not exist or must be empty. If the run fails, the tool removes what it created unless you pass `--no-clean`. ## Mistakes interviewers listen for - Expecting a per-database copy from `pg_basebackup`. - Granting table privileges instead of the `REPLICATION` attribute. - Adding an `all` line to `pg_hba.conf` and wondering why the replication connection is still refused. - Calling the checkpoint wait a hang and killing the job. - Forgetting that two WAL senders are used by the default method.

  • Can pg_basebackup copy a PostgreSQL standby instead of the primary, and what is different?
    Yes, if the standby accepts replication connections (`max_wal_senders`, `hot_standby`, a `pg_hba.conf` replication line) and `full_page_writes` is on at the primary. The limits: no backup history file is written in the cluster backed up, the standby cannot force a WAL segment switch at the end, and the backup fails if the standby is promoted mid-run.
  • How do you stop a nightly pg_basebackup of a 2 TB PostgreSQL cluster from saturating the primary's disks or network?
    Use `--max-rate`, in kilobytes per second or with an `M` suffix, between 32 kB/s and 1024 MB/s. It always throttles the data directory transfer, but it throttles WAL only with `-X fetch`. Keep the default spread checkpoint as well, so the start does not add an I/O burst.

  • pg_basebackup can copy a single database if you pass --dbname
  • pg_basebackup locks tables or pauses writes while it copies files
  • any role with CONNECT and SELECT privileges can run pg_basebackup
  • a pg_hba.conf line for all databases also admits replication connections
  • plain format moves extra tablespaces under the target directory automatically
open as a page

What are PostgreSQL pg_dump's four output formats, and which tool restores each of them?

level: juniorimportance: must knowfreq 72%

basics

~10 s

pg_dump writes plain SQL (-Fp), replayed with psql, or one of three archives read by pg_restore: custom (-Fc, one compressed file), directory (-Fd, per-table files, the only parallel-dump format) and tar (-Ft, uncompressed).

open as a page

In PostgreSQL, which settings turn on continuous WAL archiving, and what must an archive_command guarantee before it exits with status zero?

level: juniorimportance: must knowfreq 62%

basics

~10 s

Set wal_level to replica or higher, archive_mode = on (restart required) and either archive_command or archive_library. The command must exit zero only after the segment is durably stored, because PostgreSQL then recycles it.

open as a page

In PostgreSQL, what do pg_basebackup's -X none, -X fetch and -X stream do, and why is a copy without its WAL unusable?

level: middleimportance: must knowfreq 58%

basics

~20 s

Copied files are consistent only after replaying the WAL written from the backup's start to its end. -X stream (default) receives that WAL live, -X fetch collects it at the end, and -X none leaves it to the WAL archive.

open as a page

Copying one PostgreSQL database to staging with pg_dump, why do owner and grant statements fail on restore, and how do you fix that?

level: middleimportance: must knowfreq 61%

basics

~10 s

pg_dump exports one database but not the cluster-wide roles its owners and grants name, so those statements fail on a fresh cluster. Restore the roles first with pg_dumpall --globals-only, or restore with --no-owner --no-privileges.

open as a page

In PostgreSQL 18, which signal file and which settings make a restored base backup replay archived WAL only up to a chosen timestamp?

level: middleimportance: must knowfreq 58%

basics

~10 s

Create an empty recovery.signal in the restored data directory, set restore_command and recovery_target_time in postgresql.conf, and start the server. Since PostgreSQL 12 there is no recovery.conf; a leftover one stops the server starting.

open as a page

How do pg_dump -j and pg_restore -j parallelise a PostgreSQL dump and restore, and what limits each one?

level: middleimportance: should knowfreq 50%

basics

~20 s

pg_dump -j dumps several tables at once, needs the directory format and opens N+1 connections sharing one snapshot. pg_restore -j loads data and builds indexes and constraints concurrently from a custom or directory archive, never with --single-transaction.

open as a page

On a PostgreSQL primary, pg_wal keeps growing and pg_stat_archiver.failed_count keeps rising; what is going on, and how do you fix it safely?

level: middleimportance: should knowfreq 50%

basics

~20 s

archive_command is exiting non-zero, so the archiver keeps retrying the oldest unarchived segment and nothing behind it can be recycled. Read the server log and pg_stat_archiver, fix the destination, and let the backlog drain in order.

open as a page

In PostgreSQL 17 and later, how do summarize_wal, pg_basebackup --incremental and pg_combinebackup fit together, and what breaks an incremental chain?

level: seniorimportance: should knowfreq 45%

basics

~20 s

With summarize_wal on, the server records which blocks WAL touched; pg_basebackup --incremental, given an earlier backup's manifest, copies only changed blocks; pg_combinebackup rebuilds a full backup from the chain. Missing summaries or a deleted earlier backup break it.

open as a page

After a nightly pg_basebackup of a PostgreSQL 18 cluster, what does pg_verifybackup check against backup_manifest, and what can it still miss?

level: seniorimportance: should knowfreq 38%

basics

~20 s

pg_verifybackup checks the manifest itself, then missing, extra or resized files, then each file's checksum, then parses the needed WAL with pg_waldump. It cannot prove the backup restores: wrong-but-valid WAL and anything outside the manifest pass.

open as a page

Copying a PostgreSQL customer database to staging, how do you skip a huge log table's rows and control what pg_restore restores with --filter, --section and --list?

level: seniorimportance: should knowfreq 38%

basics

~20 s

Dump with --exclude-table-data (or a --filter file line exclude table_data) to keep the log table's definition without rows. Then edit pg_restore -l output and replay it with -L, or restore pre-data, data and post-data sections separately.

open as a page

After restoring a PostgreSQL 18 pg_dump archive into staging, queries plan badly at first; what do pg_dump's --statistics option and a post-restore ANALYZE each cover?

level: seniorimportance: should knowfreq 32%

basics

~20 s

A logical restore loads rows without planner statistics. PostgreSQL 18's pg_dump --statistics, off by default, dumps table and column statistics that pg_restore applies, but not extended statistics, so ANALYZE is still needed for those and after masking.

open as a page

In a PostgreSQL point-in-time recovery, what do recovery_target_action's pause, promote and shutdown do at the target, and why is pause the default?

level: seniorimportance: should knowfreq 40%

basics

~20 s

pause holds the server read-only at the target for inspection; promote ends recovery and opens for writes on a new timeline; shutdown stops the server there. Pause is the default because replay can move forward but never back.

open as a page

In PostgreSQL, when the WAL archive already holds a newer timeline from an earlier recovery, what does recovery_target_timeline decide, and when must you change its default?

level: seniorimportance: should knowfreq 33%

basics

~20 s

recovery_target_timeline picks which branch of WAL history recovery replays. The default, latest, follows the newest timeline in the archive; set current or an explicit ID to stay on the original history after an earlier recovery branched it.

open as a page

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?

level: seniorimportance: nice to knowfreq 26%

basics

~10 s

Call 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.

open as a page