20TB数据库网络备份及PostgreSQL每1TB分卷pg_dump方案咨询

Great questions—handling a 20TB PostgreSQL database over the network definitely requires some targeted strategies, so let’s break this down step by step.

1. 实现每1TB生成一个pg_dump自定义格式备份文件 #

First off, yes—you absolutely can split your pg_dump custom format backup into 1TB chunks, and this is a smart way to mitigate those network disconnects you’re facing. Here’s how to do it:

备份命令 #

Pipe the output of pg_dump directly to the split command to split the backup stream into 1TB blocks:

pg_dump -Fc -d your_database_name | split -b 1T - backup_20tb_chunk_

Let’s break down the key parts:

• -Fc: Enables PostgreSQL’s custom format—this is ideal for large databases because it’s compressed, supports parallel restores, and is portable.
• -d your_database_name: Replace this with your actual database name.
• split -b 1T: Splits the input into 1TB chunks (use 1024G if your system doesn’t recognize the T shorthand).
• backup_20tb_chunk_: The prefix for your split files—you’ll end up with files named backup_20tb_chunk_aa, backup_20tb_chunk_ab, etc.

恢复方法 #

To restore from these chunks, concatenate them back into a single stream and pipe to pg_restore:

cat backup_20tb_chunk_* | pg_restore -d target_database_name

If a chunk fails to transfer, you only need to re-run the backup command (the split tool will overwrite incomplete chunks) or use rsync to re-send the missing/corrupted file.

避免会话中断 #

To prevent network disconnects from killing your backup entirely, run the command in a persistent terminal session using screen or tmux:

1. Start a screen session: screen -S pg_backup_session
2. Run your backup command inside the session
3. Detach with Ctrl+A, D—you can reattach later with screen -r pg_backup_session

2. 20TB数据库网络备份的整体优化方案 #

A full 20TB logical backup over the network is resource-intensive, so let’s look at ways to make this more efficient and reliable:

优先采用增量备份 #

Full backups every time aren’t feasible for 20TB databases. Instead, use incremental strategies:

• 逻辑增量备份: Take a full backup once, then subsequent backups only capture changed data. For example, use pg_dump --data-only --where "updated_at > 'last_backup_time'" for tables with timestamp columns. Don’t forget to back up global objects (roles, settings) separately with pg_dumpall --globals-only.
• 物理增量备份: If you can switch to physical backups (faster for large databases), use pg_basebackup to take an initial full backup, then enable WAL archiving to ship incremental changes to the remote server. This way, you only transfer small WAL files instead of the entire 20TB each time.

优化网络与备份性能 #

• 强化压缩: The custom format already compresses data, but you can adjust the compression level with -Z (values 1-9; 5 balances speed and compression):

  pg_dump -Fc -Z 5 -d your_database_name | split -b 1T - backup_20tb_chunk_
  

• 并行备份: Use the -j flag to run multiple backup jobs in parallel, reducing the total time your network connection is active. Adjust the number based on your CPU cores (e.g., -j 8 for 8 cores):

  pg_dump -Fc -j 8 -d your_database_name | split -b 1T - backup_20tb_chunk_
  

• 使用专用网络: Avoid public networks for backups—use a private dedicated line or VPN to prevent bandwidth throttling and security risks.

验证与监控备份 #

• 检查备份完整性: After transferring all chunks, verify the backup is valid by running:

  pg_restore --list backup_20tb_chunk_aa
  

  This will read the backup’s table of contents (stored in the first chunk) to confirm the file isn’t corrupted.
• 实时监控进度: Use the pv tool to track transfer speed, elapsed time, and estimated completion:

  pg_dump -Fc -d your_database_name | pv | split -b 1T - backup_20tb_chunk_
  

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