PostgreSQL WAL archiving stalls during pg_basebackup when archive_command uses rsync over SSH

Environment

Primary: PostgreSQL 13.6 on Ubuntu 20.04, physical host with 1 Gbps NIC. archive_mode = on, archive_command = 'rsync --relative %p backup@backup-host:/data/pg_wal/%f'. pg_basebackup run from backup host to create base backup while WAL shipping active.

Symptoms

During pg_basebackup, WAL archiving appears to stall: primary's pg_wal directory accumulates files and pg_stat_archiver shows a growing backlog. archive_command exits with success in some cases but remote archive directory is missing files or shows partial transfers. Backups cannot complete while archiving is stalled, causing replication slot WAL retention to grow.

Observations

postgres log shows repeated archive attempts with no explicit error. rsync logs on backup host show abrupt connection resets for long files. Running rsync manually for one WAL file sometimes works, sometimes times out mid-transfer. Network captures show TCP retransmits and occasional connection resets during large file transfers coinciding with high disk IO on backup host. SSH keepalive options were not configured. Disk space on backup host had short spikes of near-fullness due to concurrent backups.

Already attempted

Increased log verbosity for archive_command and rsync. Retried base backup during off-peak hours (less disk pressure) and observed fewer failures. Switched archive_command to scp for a test — similar intermittent failures. Increased ssh ConnectionTimeout and added ServerAliveInterval/ServerAliveCountMax on backup host; saw improvement but not elimination of stalls. Considered using WAL streaming to backup host instead of rsync-based archiving.

Question

What are recommended, robust approaches for WAL archiving during concurrent base backups to avoid archiving stalls? Are there safe configuration changes (archive_command, SSH options, rsync flags, or alternate transport mechanisms) that reduce the chance of partial transfers and stuck archiving without risking WAL loss?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

Network instability or intermittent connection resets between primary and backup host causing rsync/ssh transfers to abort mid-file. 95%

Backup host disk IO contention or temporary full-disk conditions leading to rsync failures and connection terminations. 90%

Resource limits on SSH connection (session limits, timeouts) or lack of keepalive leading to idle connection termination during large transfers. 85%

Summary

Stalls during archive_command using rsync/SSH are likely due to network or disk IO instability on the backup side compounded by the fact archive_command is executed synchronously by Postgres: if an archive_command hangs or fails to reliably transfer files, Postgres will retry, causing WAL accumulation. Measures that make archive_command more robust (timeouts, retries, atomic transfers) and decouple archiving from synchronous operations (e.g., local staging + async uploader) reduce risk. Also ensure backup target has stable disk space and IO to avoid mid-transfer resets.

Suggested Fixes

Diagnostic step 1

Change archive_command to write WAL files to a local staging directory first (simple mv or cp), return success to Postgres, and have a separate asynchronous process (systemd timer, cron, or dedicated uploader) reliably transfer staged files to the backup host with logging, retries, and timeouts.

Diagnostic step 2

If continuing to use rsync/ssh, enable SSH keepalive (ServerAliveInterval and ServerAliveCountMax) and configure rsync with --timeout and --partial-dir to avoid corrupt/partial targets and allow resumption. Ensure rsync exit codes are properly handled and logged.

Diagnostic step 3

Monitor backup host disk usage and IO during base backups; add alerting for high utilization. Consider throttling concurrent base backups or scheduling during low-load windows.