青少年编程与数学 02-007 PostgreSQL数据库应用 15课题、备份与还原

课题摘要:本课题讨论了PostgreSQL中数据库备份与还原的重要性和方法。数据库备份是复制数据到其他存储介质的过程，包括热备份、冷备份、流复制和逻辑备份。数据库还原是将备份数据恢复到数据库的过程，可以是全量或增量还原。PostgreSQL提供了多种备份与还原工具，如pg_dump、pg_restore和pg_basebackup。自动备份可以通过pg_dump与cron定时任务结合实现，而物理备份可以通过pg_basebackup实现。pgAgent是一个PostgreSQL作业调度工具，可以用于设置定时备份和其他数据库维护任务。通过pgAdmin界面，用户可以创建和管理pgAgent作业和计划，实现数据库的自动化备份。正确的备份与还原策略对确保数据安全和业务连续性至关重要。

一、数据库备份与还原

在PostgreSQL中，数据库备份与还原是数据库管理的重要部分，它们确保了数据的安全性和可靠性。以下是数据库备份与还原的基本定义：

1. 数据库备份：

   • 数据库备份是指将数据库中的数据复制到另一个存储介质的过程。这可以是物理备份（如文件系统级别的拷贝）或逻辑备份（如导出SQL语句或数据转储）。
   • 备份的目的是创建数据的副本，以便在原始数据丢失、损坏或遭到破坏时可以恢复。
   • PostgreSQL支持多种备份方法，包括：
     • 热备份（Hot Backup）：在数据库运行时进行的备份，不需要关闭数据库服务。
     • 冷备份（Cold Backup）：在关闭数据库服务后进行的备份。
     • 流复制（Streaming Replication）：通过复制数据流来实现实时或准实时的数据备份。
     • 逻辑备份：使用pg_dump工具导出数据库结构和数据为SQL文件或CSV文件。

2. 数据库还原：

   • 数据库还原是指将备份的数据恢复到数据库中的过程。这是备份的逆操作，用于在数据丢失或损坏后恢复数据。
   • 还原操作可以是全量还原（恢复整个数据库）或增量还原（仅恢复自上次备份以来发生变化的数据）。
   • PostgreSQL中还原数据通常使用以下方法：
     • 使用pg_restore：这是pg_dump的逆操作，用于从备份文件中恢复数据。
     • 使用RESTORE命令：在逻辑备份中，可以使用SQL的RESTORE命令来恢复数据。
     • 使用复制技术：如果使用了流复制或逻辑复制，可以通过调整复制配置来恢复数据。

在实际操作中，数据库备份与还原需要考虑的因素包括备份的频率、备份的存储位置、备份的类型（全量或增量）、备份的安全性（如加密）以及还原的策略和测试。定期进行备份和还原测试是确保数据安全和业务连续性的关键步骤。

二、PostgreSQL中操作数据库的备份与还原

在PostgreSQL中，数据库的备份与还原可以通过多种方式进行操作，以下是一些常用的方法：

1. 使用pg_dump进行逻辑备份

pg_dump是PostgreSQL提供的一个强大的工具，用于备份数据库。它可以在数据库正在使用时进行备份，而不会影响其他用户对数据库的访问。以下是一些常用的pg_dump备份命令：

• 备份整个数据库：

  pg_dump -U username -W -F t db_name > output.tar
  

  其中-U指定连接数据库的用户，-W强制提示输入密码，-F指定输出文件的格式（t表示tar格式），db_name是要备份的数据库名，output.tar是输出文件的路径。

• 备份单个表：

  pg_dump -h 127.0.0.1 -U admin -p 5432 -W db -t table_name > backup.sql
  

  其中-h指定数据库服务器的IP地址，-p指定端口号，-t指定要备份的表名。

2. 使用pg_restore进行逻辑还原

pg_restore是用于从pg_dump生成的备份文件中恢复数据库的工具。以下是一些常用的pg_restore还原命令：

• 从tar格式的备份文件中还原：

  pg_restore -U username -d target_db_name path_to_backup_file.tar
  

  其中-U指定还原操作的用户，-d指定目标数据库，path_to_backup_file.tar是备份文件的路径。

• 从SQL脚本中还原：

  psql -U username -d target_db_name -f backup_file.sql
  

  这里使用psql工具来执行SQL脚本文件进行还原。

3. 使用pg_basebackup进行物理备份

pg_basebackup用于创建PostgreSQL数据库的物理备份。这种备份方式可以在数据库运行时进行，不需要停止数据库服务。以下是创建物理备份的命令：

pg_basebackup -D /path/to/backup/dir

这将创建一个备份，可以在必要时进行恢复。

4. 还原物理备份

物理备份的还原通常涉及复制备份的数据文件到新的数据目录，并更新PostgreSQL的配置文件以指向新的数据目录。这通常需要停机操作，并在新的服务器或数据目录中进行。

注意事项

• 在执行备份和还原操作之前，确保有足够的权限和足够的磁盘空间。
• 定期测试备份文件以确保它们可以成功还原。
• 根据实际情况选择合适的备份和还原策略，例如全量备份、增量备份或差异备份。

通过上述方法，你可以有效地对PostgreSQL数据库进行备份和还原操作。

三、自动备份

实现PostgreSQL数据库的自动备份，可以通过以下几种方法：

1. 使用pg_dump和cron定时任务

pg_dump是PostgreSQL自带的备份工具，可以结合Linux的cron定时任务来实现自动备份。

• 编写备份脚本：创建一个Shell脚本，使用pg_dump命令备份数据库。例如，脚本backup_db.sh可能包含以下内容：

#!/bin/bash
DATE=$(date +%Y%m%d%H%M%S)
BACKUP_DIR=/path/to/your_backup_directory # 备份文件存储目录
USER=postgres # 执行备份操作的postgres用户账号
PASSWORD=mypassword # Postgres密码或环境变量(注意安全性!)
DATABASES="db1 db2 db3" # 待备份的数据库集合

for DB in $DATABASES; do
  FILENAME=$DB-$DATE.sql.gz
  echo "Backing up database: ${DB}"
  PGPASSWORD=$PASSWORD /usr/bin/pg_dump -U $USER --format=c $DB | gzip > "${BACKUP_DIR}/${FILENAME}"
done
echo "$(date) Backup completed."

• 设置定时任务：使用crontab -e命令编辑cron表，添加以下行以每天凌晨2点运行备份脚本：

0 2 * * * /path/to/backup_db.sh

这样，PostgreSQL数据库将每天自动备份。

2. 使用pg_basebackup进行物理备份

pg_basebackup用于创建PostgreSQL数据库的物理备份，可以结合cron定时任务实现自动备份。

• 备份命令：

pg_basebackup -D /path/to/backup/dir

• 设置定时任务：同样使用cron定时任务，每天执行物理备份。

3. 使用第三方工具

• pgbackweb：这是一个用户友好的PostgreSQL备份管理工具，提供Web界面实现自动化定时备份。支持多版本PostgreSQL、本地和S3存储，并使用PGP加密保护数据。

• pgbackupDockerized：这是一个Docker化的PostgreSQL备份解决方案，支持本地和离线备份，具有轮换和压缩功能，项目地址：https://gitcode.com/gh_mirrors/pg/pgbackup。

4. 使用操作系统的定时任务工具

• Windows任务计划程序：在Windows服务器上，可以创建一个批处理脚本文件，例如backup.bat，并在Windows任务计划程序中设置定时执行该脚本。

• Linux cron：在Linux系统中，使用crontab -e命令编辑cron配置文件，添加定时任务以自动执行备份脚本。

通过上述方法，可以有效地实现PostgreSQL数据库的自动备份，确保数据的安全和业务的连续性。

四、pgAgent

pgAgent是一个用于PostgreSQL数据库的作业调度代理工具，它具有以下主要功能：

1. 定时任务管理：pgAgent可以设置在特定时间、特定间隔或特定日期执行任务，如数据备份、清理、维护等操作。pgAgent可以用于自动备份PostgreSQL数据库。根据搜索结果，pgAgent支持定时执行多种类型的任务，包括数据库备份。具体来说，可以通过pgAgent调度自动备份PostgreSQL数据库，使用pg_dump命令来实现数据库的备份。在备份命令中，可以包括特定用户名、数据库名称以及--clean标志，该标志有助于在输出任何正在创建的命令之前删除或“清理”数据库对象。同时，使用--file标志指定备份文件的保存位置，而date +%Y-%m-%d-%H-%M-%S用于动态生成日期和每个备份的多个文件，以便于跟踪每个备份文件。此外，还可以在pgAdmin中创建作业和计划，以安排在特定时间执行备份任务。因此，pgAgent是一个功能强大的工具，可以帮助用户自动化和简化数据库的备份过程。

2. 多步批处理和SQL任务执行：pgAgent能够运行多步批处理或shell脚本以及复杂调度的SQL任务。

3. 作业调度代理：pgAgent在Unix系统上作为守护进程运行，在Windows系统上作为服务运行，主要在数据库服务器上运行。

4. 数据库表和其他对象的支持：pgAgent需要一些数据库表和其他对象的支持，因此需要先安装pgAgent插件。

5. 通过SQL或可视化方式创建任务：pgAgent支持用户通过SQL方式或者pgAdmin可视化方式创建和管理任务。

6. 独立于pgAdmin提供：从pgAdmin v1.9开始，pgAgent作为单独的应用程序提供，之前是作为pgAdmin的一部分。

7. 灵活的调度功能：与常用的cron相比，pgAgent具有更强大的调度功能，因为它专门用于处理Postgres任务。

8. 创建和管理作业：使用pgAgent可以创建、管理和监控数据库任务，而不需要编写额外的脚本或程序。

9. 支持定时执行多种类型的任务：pgAgent支持定时执行多种类型的任务，具有任务调度、多步操作等功能。

10. 作业执行日志：pgAgent可以记录作业的执行结果和错误信息，方便监控和调试。

综上所述，pgAgent是一个功能强大的PostgreSQL定时任务和作业调度工具，它可以帮助数据库管理员自动化和简化数据库的常规维护任务。

五、pgAgent定时备份

使用pgAgent设置定时备份的步骤如下：

1. 安装pgAgent：

   • 确保你的系统中已经安装了pgAgent。如果尚未安装，可以参考DigitalOcean的教程进行安装。

2. 配置pgAgent服务：

   • 确保pgAgent服务已经启动并运行。你可以使用系统服务命令来启动和管理pgAgent服务，例如在Ubuntu上使用systemctl start pgagent.service来启动服务。

3. 创建作业（Job）：

   • 打开pgAdmin，导航到“pgAgent Jobs”部分，右键点击并选择“Create” -> “pgAgent Job…”来创建一个新的作业。在“General”标签页中填写作业的名称，例如“sammy_backup”，并选择作业类别，例如“Routine Maintenance”。

4. 添加步骤（Steps）：

   • 在作业编辑界面，切换到“Steps”标签页。点击右上角的“+”符号来创建一个新的步骤，例如命名为“step1”。在“Code”标签页中，输入备份命令，如：

     pg_dump --username=sammy --dbname=sammy --clean --file=/home/sammy/backup-`date +%Y-%m-%d-%H-%M-%S`.sql
     

     确保使用绝对路径来指定备份文件的保存位置。

5. 创建计划（Schedules）：

   • 切换到“Schedules”标签页，点击“+”符号来添加一个新的计划，例如命名为“schedule1”。在“General”标签页中设置计划的名称，并确保“Enabled”选项被激活。在“Start”和“End”选项中指定作业的开始和结束时间。如果你要测试计划，确保当前时间在“Start”和“End”时间范围内。

6. 设置重复（Repeat）：

   • 在“Repeat”标签页中，定制作业的执行频率。你可以选择具体的周、月、日期、小时或分钟。如果留空，则默认为“Select All”。例如，要每分钟执行一次备份，你需要在小时的分钟数（00到59）中选择每分钟。

7. 保存并测试：

   • 保存作业和计划后，你可以在pgAdmin中监控作业的执行情况。点击作业或计划图标可以查看详细信息，包括每个步骤的执行时间和结果。

通过以上步骤，你可以使用最新版本的pgAgent来设置PostgreSQL数据库的定时备份。