7.3 Physical Base Backups with pg_basebackup

7.3 Physical Base Backups with pg_basebackup

[!NOTE] Logical vs. Physical Backups: While pg_dump exports logical SQL definitions and row data from a single database, a physical base backup takes a raw, binary snapshot of the entire PostgreSQL cluster filesystem directory (PGDATA) along with all external tablespaces. Physical backups capture all databases simultaneously, system catalogs, transaction status logs (pg_xact), and configuration files. Physical backups are dramatically faster to restore than logical dumps because they bypass SQL parsing, query planning, and index generation during recovery.

The core utility for physical base backups in modern PostgreSQL is pg_basebackup. It coordinates with the running database engine to produce a consistent, standalone physical snapshot suitable for disaster recovery or for bootstrapping streaming replication standbys.

---

How pg_basebackup Operates

Unlike traditional operating system file-copy commands (cp or rsync)—which fail on live database clusters due to fractured blocks (torn pages written concurrently while the file is copied)—pg_basebackup coordinates directly with the PostgreSQL storage engine.

The Streaming Replication Connection

pg_basebackup connects to the PostgreSQL server over the standard PostgreSQL port (default 5432) using the PostgreSQL Streaming Replication Protocol (a specialized sub-protocol running over libpq).

To allow pg_basebackup to connect, two prerequisite security requirements must be satisfied:

1. User Role Attribute: The connecting database user must possess the REPLICATION attribute (or be a superuser):

   CREATE ROLE backup_user WITH REPLICATION LOGIN PASSWORD 'SecurePass123!';
   

2. Authentication Rules in pg_hba.conf: The server's host-based authentication configuration must explicitly permit replication connections for that user:

   # TYPE  DATABASE        USER         ADDRESS        METHOD
   host    replication     backup_user  10.0.1.0/24    scram-sha-256
   

Internal Execution Mechanics

When pg_basebackup connects, it issues internal replication commands:

1. It sends the BASE_BACKUP replication command.
2. The engine executes a checkpoint to flush dirty data buffers to disk and records a baseline Log Sequence Number (LSN).
3. The engine creates a backup_label file recording the backup start time, checkpoint location, and start LSN.
4. Raw disk blocks and files from PGDATA are streamed across the replication connection.
5. Upon completion, the engine logs a backup-end WAL record and reports the stop LSN.

---

Output Formats: Plain Directory vs. Tar Archives

pg_basebackup supports two primary output formats, chosen using the -F (or --format) parameter:

1. Plain Directory Format (-F p or --format=plain)

• Default Mode: When no format is specified, pg_basebackup defaults to plain directory format.
• Output: Generates an exact replica of the PGDATA filesystem directory in the specified target folder (-D <directory>).
• Readiness: The resulting directory is immediately ready to run as a functional PostgreSQL instance (or streaming replica).
• Tablespace Handling (-T / --tablespace-mapping): If the cluster contains external tablespaces located outside PGDATA, plain format requires the administrator to specify --tablespace-mapping (or -T) to relocate tablespaces to new directory paths. If omitted and the backup is executed on the same host, the backup will abort to prevent overwriting live tablespace data!

# Plain directory backup with tablespace remapping
pg_basebackup -h localhost -U backup_user -D /var/backups/pgdata_plain -F p \
  -T /mnt/ssd/indexes=/var/backups/tablespaces/indexes

2. Tar Archive Format (-F t or --format=tar)

• Output: Generates a set of .tar files in the destination directory.
  • The primary cluster directory (PGDATA) is written to base.tar.
  • Each external tablespace is written to a separate tar file named after its tablespace OID (e.g., <tablespace_oid>.tar).
• Compression Support (-z / --gzip): Tar format supports direct gzip compression during streaming, reducing network transfer volume and storage consumption.
• Standard Output Streaming: Tar archives can be streamed directly to standard output (-D -) to pipe backups into cloud storage or encryption utilities.

# Gzip-compressed tar backup
pg_basebackup -h localhost -U backup_user -D /var/backups/pgdata_tar -F t -z -P

---

Handling WAL During Physical Backups: -X fetch vs. -X stream

Because a physical backup of a large database can take hours to complete, the data files copied early in the backup process will become out-of-sync with files copied later. To ensure that a restored physical backup can reach a consistent, recoverable state, the database engine requires all Write-Ahead Log (WAL) records generated between the backup start checkpoint and backup completion.

pg_basebackup controls WAL capture through the -X (or --wal-method) switch:

-X stream | --wal-method=stream  (Stream WAL concurrently via 2nd connection - RECOMMENDED)
-X fetch  | --wal-method=fetch   (Fetch WAL segments at the conclusion of the backup)
-X none   | --wal-method=none    (Do not collect WAL; relies on external WAL archiving)

1. -X stream (Concurrent Streaming - Modern Default)

When -X stream is configured, pg_basebackup opens two concurrent streaming replication connections to the server:

• Connection 1 streams the raw filesystem data files.
• Connection 2 streams WAL records in real time as they are generated by the primary.

Advantages: Because WAL records are streamed concurrently, the backup is completely immune to WAL recycling on the primary! It creates a completely self-contained, standalone physical backup that can be recovered without relying on an external WAL archive. In modern PostgreSQL, -X stream is the default behavior.

[!IMPORTANT] max_wal_senders Requirement: Because -X stream consumes two replication connections simultaneously, the PostgreSQL server configuration parameter max_wal_senders must be set to at least 2.

2. -X fetch (Fetch at End)

With -X fetch, pg_basebackup uses a single connection to copy all data files first. Once all data files have transferred, it issues a request to download the WAL segments accumulated during the backup.

The Recycled WAL Hazard: If a database experiences high write activity and the backup takes several hours, the PostgreSQL server may recycle or delete older WAL segments before pg_basebackup gets around to fetching them at the end. In this scenario, the backup immediately aborts with the fatal error: pg_basebackup: error: could not get WAL for checkpoint: ERROR: requested WAL segment ... has already been removed. For this reason, -X fetch is discouraged in high-throughput production environments unless wal_keep_size is set to an extraordinarily high value.

---

Checkpoint Mode: -c fast vs. -c spread

Before streaming data, pg_basebackup must initiate a checkpoint on the server to establish a clean starting baseline. The administrator controls how aggressively this checkpoint runs using -c (or --checkpoint):

• -c fast: Forces PostgreSQL to complete the checkpoint as rapidly as possible, bypassing I/O throttling. The backup begins transferring data almost immediately, but the aggressive checkpoint can cause a temporary I/O write spike that impacts concurrent production queries.
• -c spread (Default): Instructs the checkpointer to throttle I/O writes smoothly across the time interval governed by checkpoint_completion_target. This minimizes production performance degradation, but pg_basebackup may pause for several minutes before beginning data transfer.

---

Bootstrapping Replicas: -R / --write-recovery-conf

A primary use case for pg_basebackup is provisioning new streaming replication read replicas. Specifying the -R (or --write-recovery-conf) flag automates replica setup:

1. It creates an empty trigger file named standby.signal in the target directory, informing PostgreSQL upon startup to run as a read-only standby.
2. It appends connection parameters (host, port, user, replication password) to postgresql.auto.conf via the primary_conninfo directive.

# Provision a standby instance ready to start
pg_basebackup -h primary.example.com -U replicator -D /var/lib/postgresql/data -F p -R -P

---

Backup Manifests and Verification with pg_verifybackup

Beginning in PostgreSQL 13, pg_basebackup automatically creates a metadata file named backup_manifest at the root of the backup directory.

The backup_manifest Architecture

The manifest is a structured JSON file containing:

• Backup metadata: start LSN, stop LSN, timeline ID, and checkpoint timestamp.
• A comprehensive inventory of every file in the backup, including exact byte lengths and a per-file checksum. The default algorithm is CRC32C, not a cryptographic hash; pg_basebackup --manifest-checksums can select NONE, CRC32C, SHA224, SHA256, SHA384, or SHA512 when you need a cryptographic digest instead.
• A list of all WAL segments required to achieve consistency.

Verifying Integrity via pg_verifybackup

Taking a backup is useless if bit rot, disk corruption, or aborted network transfers render it unbootable. PostgreSQL includes the standalone command-line verification utility pg_verifybackup:

# Verify physical backup integrity against the manifest
pg_verifybackup /var/backups/pgdata_plain

# Verify a backup with an external manifest path
pg_verifybackup -m /secure/backup_manifest /var/backups/pgdata_plain

pg_verifybackup reads the manifest, recalculates checksums across every file on disk, verifies file lengths, and validates that all required WAL ranges are present. If a single page has corrupted bits or a file was deleted, pg_verifybackup reports the discrepancy immediately.

[!TIP] CRC32C is fast and detects accidental corruption, but it is not a cryptographic hash and does not defend against deliberate tampering of an archived backup. If your compliance regime requires tamper evidence, take the backup with pg_basebackup --manifest-checksums=SHA256, which costs measurable extra CPU on the backup host.

---

Summary of Key pg_basebackup Flags

---

Exam Tips and Common Pitfalls

• Exam Trap: Protocol and Permissions: pg_basebackup does not run over standard SQL query sessions; it connects via the Streaming Replication Protocol. The connecting role must have the REPLICATION attribute, and pg_hba.conf must have a replication database entry.
• Exam Trap: Missing Connections for -X stream: If an administrator configures pg_basebackup -X stream and receives connection errors, verify max_wal_senders. Streaming WAL concurrently requires at least two available WAL sender slots.
• Exam Trap: Tablespace Overwrite Hazard: When taking a plain-format (-F p) backup of a database with external tablespaces on the same server, you must use -T (--tablespace-mapping). Otherwise, pg_basebackup will attempt to write the tablespace into its original live directory and abort to prevent data corruption.
• Exam Trap: Manifest Verification: The backup_manifest file is generated automatically by pg_basebackup in PostgreSQL 13+, and is validated offline using pg_verifybackup. Its per-file checksums default to CRC32C; SHA-family digests are available but must be requested with --manifest-checksums.