PostgreSql 项目实战 7 - 备份pg数据库1 宿主机安装pg客户端 1 创建PG密钥文件 2 创建密钥执行的

1 安装pg客户端

# 安装客户端
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt noble-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt update

sudo apt install postgresql-client-17

# 验证客户端
which pg_basebackup
pg_basebackup --version

1 创建PG miyao文件

echo "192.168.1.44:5432:*:postgres:你的xxx密码" > /root/.pgpass
chmod 600 /root/.pgpass

2 创建miyao执行的脚本

export PGPASSFILE=/root/.pgpass
psql -h 192.168.1.44 -U postgres -p 5432 -c "select now();"

3 jiaoben优化

进阶版备份脚本：按日期目录 + 保留 7 天

#!/usr/bin/env bash

# 配置
export PGPASSFILE=/root/.pgpass
BACKUP_BASE="/data/pg_basebackup"
DATE_DIR=$(date +"%Y%m%d-%H%M%S")
TARGET_DIR="$BACKUP_BASE/$DATE_DIR"
LOG_FILE="$BACKUP_BASE/backup.log"

mkdir -p "$TARGET_DIR"

echo "[$(date)] Backup started: $TARGET_DIR" >> "$LOG_FILE"

# 执行全量备份
if pg_basebackup \
  -D "$TARGET_DIR" \
  -Ft -Pv \
  -Upostgres \
  -h 192.168.1.44 \
  -p 5432 \
  -R >> "$LOG_FILE" 2>&1; then
    echo "[$(date)] ✅ Backup success: $TARGET_DIR" >> "$LOG_FILE"
else
    echo "[$(date)] ❌ Backup FAILED" >> "$LOG_FILE"
    rm -rf "$TARGET_DIR"
    exit 1
fi

# 清理 7 天前的备份
find "$BACKUP_BASE" -maxdepth 1 -type d -mtime +7 -exec rm -rf {} \;  >> "$LOG_FILE" 2>&1

echo "[$(date)] 🧹 Old backups removed (>7 days)" >> "$LOG_FILE"

4 实施备份

1Panel -> 计划任务 -> 创建计划任务 -> 设置时间、脚本内容 -> 执行测试！