如何在运行pg_basebackup前计算PostgreSQL从库所需磁盘空间

Absolutely, there are several practical, reliable ways to calculate the disk space your replica will need before launching pg_basebackup—no guesswork required. Let’s break down the most effective methods:

1. Use PostgreSQL's Dedicated Backup Size Function (PostgreSQL 12+) #

Starting with PostgreSQL 12, there’s a built-in function that directly estimates the exact size a base backup will occupy. Run this query on your primary server:

SELECT pg_size_pretty(pg_basebackup_size());

It returns a human-readable size that accounts for all files pg_basebackup will copy: the entire data directory, configuration files, and necessary WAL segments. This is the most accurate out-of-the-box option for newer versions.

2. Measure the Primary's Data Directory Size #

If you’re on an older PostgreSQL version, check the disk usage of your primary’s data directory directly. On the primary’s command line:

# Replace with your actual PostgreSQL data directory path
du -sh /var/lib/postgresql/14/main

This gives you the total size of the data directory, which pg_basebackup will replicate almost exactly. Just make sure to exclude any non-PostgreSQL temporary files in the directory if they exist.

If you don’t have shell access to the primary, use this SQL query instead:

SELECT pg_size_pretty(pg_directory_size(current_setting('data_directory')));

It directly measures the size of the primary’s data directory through PostgreSQL itself.

3. Account for WAL Log Storage #

Don’t forget to reserve extra space for WAL logs on the replica. During the base backup, the primary will keep generating WAL, and the replica needs to store these until it fully catches up. To estimate this:

• Check the current size of the primary’s WAL directory:

  du -sh /var/lib/postgresql/14/main/pg_wal
  

• Calculate the primary’s WAL generation rate over time to estimate how much will be created during the backup:

  SELECT pg_size_pretty(sum(wal_bytes)) AS total_wal_generated,
         now() - stats_reset AS measurement_period
  FROM pg_stat_bgwriter;
  

Multiply the hourly/daily rate by the expected backup duration (plus a buffer) to get the extra WAL space you’ll need.

4. Add a Safety Buffer #

Always leave 10-20% extra space beyond your calculated total. This covers:

• Data changes on the primary between your initial calculation and the end of the backup
• Future growth of the replica once it’s online
• Unexpected temporary files or log accumulation

Quick Note on Compression #

If you use compression flags like -z or -Z with pg_basebackup, the backup file itself will be smaller. But remember: you’ll still need enough space on the replica to decompress it to the full size of the primary’s data directory.

内容的提问来源于stack exchange，提问作者Niraj Nandane