Scenario
A base backup is running via pg_basebackup — pg_stat_progress_basebackup shows how much data has been streamed and whether it is progressing at a reasonable rate. Diagnose it -- Available since PostgreSQL 13: SELECT p.pid, p.phase, pg_size_pretty(p.backup_total)…
Investigation Path
A base backup is running via pg_basebackup — pg_stat_progress_basebackup shows how much data has been streamed and whether it is progressing at a reasonable rate.
Diagnose it
-- Available since PostgreSQL 13:
SELECT p.pid,
p.phase,
pg_size_pretty(p.backup_total) AS backup_total,
pg_size_pretty(p.backup_streamed) AS backup_streamed,
round(
100.0 * p.backup_streamed
/ NULLIF(p.backup_total, 0),
1) AS pct_streamed,
p.tablespaces_total,
p.tablespaces_streamed,
p.backup_type -- PostgreSQL 17+
FROM pg_stat_progress_basebackup p;Phases: waiting for checkpoint to begin →
estimating backup size → streaming database files → waiting for wal archiving to finish →
transferring remaining wal files.
Note: backup_total may be NULL in the early phases before
the server has estimated the total size to transfer.
Why it happens
pg_basebackup streams the entire data directory over a replication connection.
During the backup, WAL is generated normally and must be either archived or included in the
backup (--wal-method option). The “waiting for wal archiving” phase can
stall if the archive command is slow or the archive target is unavailable.
How to fix it
If the backup is stuck in the WAL archiving phase, investigate the archive command:
check that the archive target (filesystem, S3, etc.) is reachable and that
pg_stat_archiver shows recent successful archives.
Prevent it next time
Schedule base backups during off-peak hours to reduce I/O competition. Limit backup
bandwidth with pg_basebackup --max-rate to avoid saturating storage during
production hours. Monitor backup_streamed rate to detect backups that are
running slower than expected (which may indicate a failing archive target).
Related & next steps
Career Impact
This scenario builds production judgment and operational confidence under pressure.