Ubuntu下PostgreSQL15每10分钟增量备份及0字节备份问题解决

一、先解决0字节备份文件的问题 #

你的脚本和crontab配置存在几个关键问题，导致备份文件为空：

1. 修复pg_dump连接参数错误 #

脚本里的DATABASE_NAME写法不符合pg_dump的参数规范，pg_dump不接受将所有连接参数合并成一个字符串作为参数。正确的写法有两种：

• 方式一：拆分单个连接选项

# 替换原DATABASE_NAME行
DB_HOST="localhost"
DB_PORT="5432"
DB_NAME="new_db"
DB_USER="admin"

然后pg_dump命令改为：

pg_dump -Fc -h $DB_HOST -p $DB_PORT -U $DB_USER $DB_NAME > $BACKUP_FILE

• 方式二：使用连接字符串（需加引号）

# 注意连接字符串要整体加引号
pg_dump -Fc --dbname="host=localhost port=5432 dbname=new_db user=admin" > $BACKUP_FILE

2. 安全传递数据库密码（避免明文写在脚本） #

直接在脚本里写密码不安全，而且pg_dump可能无法识别这种方式。正确做法是：

• 给admin用户创建.pgpass文件（路径：/home/admin/.pgpass），内容为：

localhost:5432:new_db:admin:admin

• 设置文件权限：

chmod 600 /home/admin/.pgpass
chown admin:admin /home/admin/.pgpass

这样pg_dump会自动读取该文件的密码，无需在脚本里明文传递。

3. 修复备份目录权限问题 #

/home/ubuntu/Documents属于ubuntu用户，sudo切换到admin用户后，admin可能没有写入权限。可以：

• 要么把备份目录改为admin用户有权限的路径，比如/home/admin/backups，并创建目录：

sudo -u admin mkdir -p /home/admin/backups

• 要么给ubuntu的Documents目录添加admin用户的写入权限：

sudo chmod g+w /home/ubuntu/Documents
sudo chgrp admin /home/ubuntu/Documents

4. 修复crontab的环境变量问题 #

crontab的默认PATH不包含PostgreSQL的bin目录（通常是/usr/lib/postgresql/15/bin），导致找不到pg_dump命令。可以在crontab开头添加PATH，或者在脚本里使用pg_dump的绝对路径：

• 方式一：在crontab里添加PATH
  打开crontab编辑：crontab -e，在开头添加：

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/postgresql/15/bin

• 方式二：在脚本里用pg_dump绝对路径
  把pg_dump命令改为：

/usr/lib/postgresql/15/bin/pg_dump -Fc -h $DB_HOST -p $DB_PORT -U $DB_USER $DB_NAME > $BACKUP_FILE

5. 给脚本添加日志输出（便于排查错误） #

修改脚本，添加错误日志，方便定位问题：

#!/bin/bash

# 配置参数
DB_HOST="localhost"
DB_PORT="5432"
DB_NAME="new_db"
DB_USER="admin"
BACKUP_DIR="/home/admin/backups"
DATE=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_FILE="$BACKUP_DIR/backup_$DATE.tar"
LOG_FILE="$BACKUP_DIR/backup_$DATE.log"

# 创建备份目录（如果不存在）
mkdir -p $BACKUP_DIR

# 执行备份并记录日志
/usr/lib/postgresql/15/bin/pg_dump -Fc -h $DB_HOST -p $DB_PORT -U $DB_USER $DB_NAME > $BACKUP_FILE 2>&1
# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "$DATE: 备份成功" >> $LOG_FILE
else
    echo "$DATE: 备份失败，错误信息见日志" >> $LOG_FILE
    # 删除空文件
    rm -f $BACKUP_FILE
fi

二、实现PostgreSQL 15的增量备份（每10分钟） #

注意：pg_dump是逻辑备份工具，本身不支持真正的增量备份（只能全量或按表/schema备份）。要实现物理增量备份，需要结合PostgreSQL的WAL（Write-Ahead Log）归档功能，步骤如下：

1. 配置PostgreSQL开启WAL归档 #

编辑PostgreSQL配置文件（通常路径：/etc/postgresql/15/main/postgresql.conf）：

# 开启归档
archive_mode = on
# 设置归档命令（将WAL文件复制到备份目录）
archive_command = 'cp %p /var/lib/postgresql/15/wal_archives/%f'
# 设置WAL段大小（可选，默认16MB）
wal_segment_size = 16MB
# 保留足够的WAL（按需调整）
wal_keep_size = 1GB

创建WAL归档目录并设置权限：

sudo mkdir -p /var/lib/postgresql/15/wal_archives
sudo chown postgres:postgres /var/lib/postgresql/15/wal_archives
sudo chmod 700 /var/lib/postgresql/15/wal_archives

重启PostgreSQL服务：

sudo systemctl restart postgresql@15-main

2. 执行初始全量备份（物理备份） #

用pg_basebackup做全量物理备份，作为增量备份的基础：

sudo -u postgres pg_basebackup -D /var/lib/postgresql/15/full_backup -Ft -z -P -X stream

• -D：备份存储目录
• -Ft：生成tar格式备份
• -z：压缩备份
• -X stream：实时流式传输WAL，确保备份一致性

3. 实现增量备份 #

PostgreSQL 13+支持基于全量备份的物理增量备份，每10分钟执行的命令如下：

# 基于上次全量备份做增量备份，按时间命名目录
sudo -u postgres pg_basebackup -D /var/lib/postgresql/15/incremental_backup_$(date +%Y%m%d%H%M) -Ft -z -P -X stream --incremental=/var/lib/postgresql/15/full_backup

将此命令加入备份脚本，配合crontab每10分钟执行。

4. 自动清理90天前的备份 #

在备份脚本末尾添加清理命令：

# 删除90天前的备份文件和日志
find $BACKUP_DIR -type f -name "*.tar" -mtime +90 -delete
find $BACKUP_DIR -type f -name "*.log" -mtime +90 -delete
# 删除90天前的WAL归档文件
find /var/lib/postgresql/15/wal_archives -type f -mtime +90 -delete

三、用pgAdmin 4完成备份操作 #

1. 全量备份 #

• 打开pgAdmin 4，连接到你的PostgreSQL服务器
• 右键点击目标数据库new_db，选择备份
• 在备份窗口中：
  • 设置备份格式为自定义（对应pg_dump的-Fc参数）
  • 设置备份文件存储路径
  • 在选项标签页，勾选需要的备份选项（如包含权限、触发器等）
  • 点击备份开始执行

2. 定时备份（通过pgAdmin 4的调度功能） #

• 切换到对象面板，展开作业调度器 > 作业
• 右键点击作业，选择创建 > 作业
• 在作业配置窗口：
  • 填写作业名称，比如10分钟增量备份
  • 在步骤标签页，点击添加，步骤类型选择Shell，输入备份脚本的执行命令
  • 在调度标签页，设置执行频率为每10分钟，起始时间，添加清理旧备份的步骤
  • 保存作业，pgAdmin会自动在后台执行

四、crontab的正确配置 #

打开crontab编辑：crontab -e，添加以下内容（假设脚本路径为/home/admin/backup_script.sh）：

# 配置环境变量
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/postgresql/15/bin
# 每10分钟执行一次备份脚本
*/10 * * * * /home/admin/backup_script.sh

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