PostgreSQL 之一 - 数据备份脚本

关于 PostgreSQL 的安装，请参考：在 ubuntu 22.04 中，通过源码编译安装 PostgreSQL 服务

备份机制优缺点

提示语：现在，你是一名专业的数据库管理员，请从您的专业角度帮我解释下全量备份、增量备份、物理备份以及逻辑备份

好的，从数据库管理的角度来看，我会详细解释一下全量备份、增量备份、物理备份以及逻辑备份的区别和优缺点：

1. 全量备份:

定义: 全量备份是指将数据库的所有数据和结构完整地复制到备份存储介质中。

优点:

备份完整，可以恢复数据库的完整状态。
操作简单，易于理解和执行。

缺点:

备份时间长，占用存储空间大。
无法恢复数据库运行期间发生的数据变更。

适用场景:

定期进行数据库完整备份，用于灾难恢复。
数据库首次部署或迁移。

2. 增量备份:

定义: 增量备份是指仅备份自上次备份以来发生更改的数据和结构。

优点:

备份时间短，占用存储空间小。
可以恢复数据库运行期间发生的数据变更。

缺点:

需要依赖上次全量备份才能恢复数据库。
操作相对复杂，需要一定的专业知识。

适用场景:

数据库频繁更新，需要经常进行备份。
需要快速恢复数据库到某个时间点。

3. 物理备份:

定义: 物理备份是指直接复制数据库文件到备份存储介质中。

优点:

备份速度快，操作简单。
可以恢复数据库的所有数据和结构，包括系统表和用户数据。

缺点:

无法恢复数据库运行期间发生的数据变更。
备份文件与操作系统和数据库版本强相关，移植性差。

适用场景:

需要快速恢复数据库到某个时间点。
需要迁移数据库到其他服务器。

4. 逻辑备份:

定义: 逻辑备份是指通过导出数据库的 SQL 语句来进行备份。

优点:

备份文件可移植性强，可以跨操作系统和数据库版本恢复。
可以恢复数据库运行期间发生的数据变更。

缺点:

备份

自己手动写的

一个 PostgreSQL 数据库备份脚本，该脚本将只保留最近7天的备份文件：

#!/bin/bash

# 设置备份目录和文件名
backup_dir="/data/backup/pg_data"
backup_file="db_backup_$(date +%Y%m%d).sql"

# 如果目录不存在，则创建目录：
if [ ! -f "$backup_dir" ]; then
  mkdir -p $backup_dir
fi

# 执行备份命令
pg_dump -U your_username -d your_database > "$backup_dir/$backup_file"

# 删除超过7天的备份文件
find "$backup_dir" -name "db_backup_*" -type f -mtime +7 -delete

Gemini 写的

提示语：现在，你是一名专业的数据库管理员，请帮我写一个关于 PostgresSQL 数据备份的脚本。脚本要实现增量备份，全量备份，物理备份和逻辑备份

好的，我可以帮您编写一个实现增量备份、全量备份、物理备份和逻辑备份的 PostgreSQL 数据库备份脚本。

脚本功能:

支持 PostgreSQL 数据库的增量备份、全量备份、物理备份和逻辑备份。
使用 set -eu 提高脚本的鲁棒性。
使用注释解释代码的功能和逻辑。
支持多种操作系统，包括 CentOS、Ubuntu 和 Debian。

脚本使用:

# 使用帮助
./backup.sh -h

# 执行全量备份
./backup.sh -f

# 执行增量备份
./backup.sh -i

# 执行物理备份
./backup.sh -p

# 执行逻辑备份
./backup.sh -l

脚本代码:

#!/usr/bin/env bash

set -eu

# 定义常量
PGHOME="/usr/local/pg"
PGDATA="/data/pg"
PGUSER="postgres"
BACKUP_DIR="/data/backup/postgres"

# 定义函数
function full_backup() {

  # 检查数据库是否正在运行
  if [[ $(pg_isready -D ${PGDATA}) ]]; then
    echo -e "${COLOR_R}The database is running, please stop it first.${COLOR_P}"
    exit 1
  fi

  # 创建备份目录
  mkdir -p ${BACKUP_DIR}/full

  # 备份数据库
  pg_basebackup -D ${PGDATA} -P -x -c fast -f ${BACKUP_DIR}/full

}

function incremental_backup() {

  # 检查数据库是否正在运行
  if [[ ! $(pg_isready -D ${PGDATA}) ]]; then
    echo -e "${COLOR_R}The database is not running, please start it first.${COLOR_P}"
    exit 1
  fi

  # 检查上次备份时间
  last_backup_time=$(ls -1 ${BACKUP_DIR}/incremental | sort -n | tail -1)
  if [[ -z ${last_backup_time} ]]; then
    echo -e "${COLOR_R}No previous backup found, please perform a full backup first.${COLOR_P}"
    exit 1
  fi

  # 创建备份目录
  mkdir -p ${BACKUP_DIR}/incremental/${last_backup_time}

  # 备份数据库
  pg_basebackup -D ${PGDATA} -P -x -c fast -i ${last_backup_time} -f ${BACKUP_DIR}/incremental/${last_backup_time}

}

function physical_backup() {

  # 检查数据库是否正在运行
  if [[ $(pg_isready -D ${PGDATA}) ]]; then
    echo -e "${COLOR_R}The database is running, please stop it first.${COLOR_P}"
    exit 1
  fi

  # 创建备份目录
  mkdir -p ${BACKUP_DIR}/physical

  # 备份数据目录
  tar -zcf ${BACKUP_DIR}/physical/pgdata.tar.gz ${PGDATA}

}

function logical_backup() {

  # 检查数据库是否正在运行
  if [[ ! $(pg_isready -D ${PGDATA}) ]]; then
    echo -e "${COLOR_R}The database is not running, please start it first.${COLOR_P}"
    exit 1
  fi

  # 创建备份目录
  mkdir -p ${BACKUP_DIR}/logical

  # 备份数据库
  pg_dumpall -U ${PGUSER} -f ${BACKUP_DIR}/logical/database.dump

}

# 处理参数
while getopts "fhilp" arg; do
  case $arg in
    f) full_backup;;
    i) incremental_backup;;
    p) physical_backup;;
    l) logical_backup;;
    h) echo -e "Usage: $0 [-fhilp]\n\n-f: Full backup\n-i: Incremental backup\n-p: Physical backup\n-l: Logical backup\n-h: Help";;
  esac
done

注意事项:

请根据您的实际环境修改脚本中的常量。

ChatGPT写的

以下是一个关于 PostgreSQL 数据备份的脚本，包括增量备份、全量备份、物理备份和逻辑备份功能：

#!/bin/bash

# 设置数据库连接参数
DB_USER="postgres"
DB_PASS="your_database_password"
DB_NAME="your_database_name"
DB_HOST="localhost"
DB_PORT="5432"

# 备份目录
BACKUP_DIR="/data/backup/pg_data"

# 备份文件名
DATE=$(date +"%Y-%m-%d_%H-%M-%S")

# 全量备份
full_backup() {
    pg_dump -U ${DB_USER} -h ${DB_HOST} -p ${DB_PORT} -Fc ${DB_NAME} > ${BACKUP_DIR}/full_backup_${DATE}.dump
    echo "Full backup completed: ${BACKUP_DIR}/full_backup_${DATE}.dump"
}

# 增量备份
incremental_backup() {
    pg_dump -U ${DB_USER} -h ${DB_HOST} -p ${DB_PORT} -Fc ${DB_NAME} --format=custom --file=${BACKUP_DIR}/incremental_backup_${DATE}.dump -b -v
    echo "Incremental backup completed: ${BACKUP_DIR}/incremental_backup_${DATE}.dump"
}

# 物理备份
physical_backup() {
    pg_basebackup -U ${DB_USER} -h ${DB_HOST} -p ${DB_PORT} -D ${BACKUP_DIR}/physical_backup_${DATE} -Ft -Xf -z
    echo "Physical backup completed: ${BACKUP_DIR}/physical_backup_${DATE}"
}

# 逻辑备份
logical_backup() {
    pg_dumpall -U ${DB_USER} -h ${DB_HOST} -p ${DB_PORT} -f ${BACKUP_DIR}/logical_backup_${DATE}.sql
    echo "Logical backup completed: ${BACKUP_DIR}/logical_backup_${DATE}.sql"
}

# 主菜单
main_menu() {
    echo "PostgreSQL Backup Script"
    echo "1. Full Backup"
    echo "2. Incremental Backup"
    echo "3. Physical Backup"
    echo "4. Logical Backup"
    echo "5. Exit"
    read -p "Enter your choice: " choice
    case $choice in
        1) full_backup;;
        2) incremental_backup;;
        3) physical_backup;;
        4) logical_backup;;
        5) exit;;
        *) echo "Invalid choice";;
    esac
}

# 执行主菜单
main_menu