When pg_dump causes system slowdowns, we need to examine multiple potential bottlenecks:

# Check I/O wait during backup
iostat -x 1
# Monitor memory usage
vmstat 1
# Check for locks
psql -c "SELECT pid,locktype,relation::regclass,mode FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid;"

The pipeline (pg_dump | gzip) operates sequentially but buffers data in memory. Large tables can cause memory pressure.

Here are effective ways to reduce pg_dump's impact:

# Using jobs for parallel export (PostgreSQL 9.3+)
pg_dump -j 4 --file=output_dir --format=directory

# Alternative with lower priority
nice -n 19 ionice -c 3 pg_dump -Fc dbname > backup.dump

# Split large tables
pg_dump --exclude-table-data='*.archive_*' dbname > partial.dump

When pg_dump becomes impractical, consider these approaches:

# Continuous archiving setup in postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

# pg_basebackup for binary copies
pg_basebackup -D /var/lib/postgresql/backup -Ft -z -P

Tune your OS for better backup performance:

# Increase shared buffers
echo "vm.dirty_background_ratio = 5" >> /etc/sysctl.conf
echo "vm.dirty_ratio = 10" >> /etc/sysctl.conf
sysctl -p

# Create RAM disk for temporary files
mkdir /tmp/pgdump_ram
mount -t tmpfs -o size=1G tmpfs /tmp/pgdump_ram

These symptoms suggest need for architectural changes:

• Backup windows exceeding 4 hours
• More than 30% performance degradation during backups
• Frequent lock timeouts during dump operations

For growing databases, implement a tiered backup strategy combining logical dumps (for small tables), physical backups (for large tables), and WAL archiving.

When your daily pg_dump backups start causing system performance degradation, it's crucial to identify where exactly the bottleneck occurs. Here's how to diagnose the issue:

# Check I/O wait during backup
iostat -x 1

# Monitor memory usage
vmstat 1

# Check for locks during backup
psql -c "SELECT blocked_locks.pid AS blocked_pid,
         blocking_locks.pid AS blocking_pid,
         blocked_activity.query AS blocked_statement
         FROM pg_catalog.pg_locks blocked_locks
         JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
         JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
         AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
         AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
         AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
         AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
         AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
         AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
         AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
         AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
         AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
         AND blocking_locks.pid != blocked_locks.pid
         JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid;"

Here are several approaches to make your backups less resource-intensive:

# Use jobs parameter to control parallelism
pg_dump --jobs=4 --user=xyz_system xyz | gzip > backup.gz

# Reduce priority of the backup process
nice -n 19 pg_dump --user=xyz_system xyz | gzip > backup.gz

# Use ionice to reduce I/O priority
ionice -c2 -n7 pg_dump --user=xyz_system xyz | gzip > backup.gz

# Split the dump into smaller chunks
pg_dump --user=xyz_system --table=large_table xyz | gzip > large_table.gz

When pg_dump becomes too resource-heavy, consider these alternatives:

# Use pg_dump in directory format with compression
pg_dump --user=xyz_system -Fd xyz -j 4 -Z 5 -f /path/to/backup

# Set up continuous archiving with WAL files
psql -c "ALTER SYSTEM SET wal_level = replica;"
psql -c "ALTER SYSTEM SET archive_mode = on;"
psql -c "ALTER SYSTEM SET archive_command = 'gzip < %p > /path/to/archive/%f.gz';"

# Consider using pg_basebackup for physical backups
pg_basebackup -D /path/to/backup -Ft -z -P -U replicator

Adjust your PostgreSQL configuration to better handle large backups:

# postgresql.conf optimizations for backup performance
maintenance_work_mem = 256MB
max_wal_size = 2GB
checkpoint_timeout = 30min
work_mem = 16MB

Optimize when and how backups run:

# Example crontab entry with resource limits
00 01 * * * root umask 077 && /usr/bin/nice -n 19 /usr/bin/ionice -c2 -n7 \
/usr/bin/pg_dump --user=xyz_system --jobs=4 xyz | \
/usr/bin/gzip --rsyncable > /var/xyz/backup/db/xyz/date -u +\%Y\%m\%dT\%H\%M\%S.gz