skip to content

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