PostgreSQL 10.10 Documentation Chapter 25. Backup and Restore
备份方式
备份方式可以分为
- SQL转储: 使用
pg_dump或pg_dumpall工具，创建一个由SQL命令组成的文件。 - 文件系统级别备份: 直接复制PostgreSQL用于存储数据库中数据的文件。
对于后者，为了得到一个可用的备份，数据库服务器必须被关闭，在恢复数据之前也需要关闭服务器。并且不可通过相应的文件或目录来备份或恢复特定的表或数据库。
因为包含在这些文件中的信息只有配合提交日志文件pg_xact/*才有用，提交日志文件包含了所有事务的提交状态。一个表文件只有和这些信息一起才有用。当然也不可能只恢复一个表及相关的 pg_xact 数据，因为这会导致数据库集簇中所有其他表变得无用。因此文件系统备份只适合于完整地备份或恢复整个数据库集簇。
综上，采用SQL转储结合定时任务来完成 PostgreSQL 定时备份与恢复。
pg_dump 和 pg_dumpall
区别
pg_dump与pg_dumpall的区别在于
- pg_dump: 把PostgreSQL数据库抽取为一个脚本文件或其他归档文件。
- pg_dumpall: 将一个PostgreSQL数据库集簇抽取到一个脚本文件中。
pg_dump是用于备份一种PostgreSQL数据库的工具，只转储单个数据库。即使数据库正在被并发使用，它也能创建一致的备份。它不阻塞其他用户访问数据库（读取或写入）。
pg_dumpall工具可以一个集簇中所有的 PostgreSQL 数据库写出到（转储）一个脚本文件，它会对集簇中的每个数据库调用pg_dump来完成该工作。同时还转储对所有数据库公用的全局对象（pg_dump不保存这些对象），目前这包括适数据库用户和组、表空间以及适合所有数据库的访问权限等属性。
pg_dump [connection-option...] [option...] [dbname]
pg_dumpall [connection-option...] [option...]
详细描述与参数见
pg_dump
pg_dumpall
这里使用pg_dump即可。
使用 pg_dump
备份
转储可以被输出到脚本或归档文件格式。
脚本转储是包含 SQL 命令的纯文本文件，它们可以用来重构数据库到它被转储时的状态。要从这样一个脚本恢复，可以将它“喂”给psql。
归档文件格式必须与pg_restore配合使用来重建数据库。它们允许pg_restore能选择恢复什么，或者甚至在恢复之前对条目重排序。归档文件格式被设计为在架构之间可移植。
pg_restore详细描述与参数见 pg_restore
当使用归档文件格式之一并与pg_restore组合时，pg_dump提供了一种灵活的归档和传输机制。pg_dump可以被用来备份整个数据库，然后pg_restore可以被用来检查归档并/或选择数据库的哪些部分要被恢复。最灵活的输出文件格式是“自定义”格式-Fc和“目录”格式-Fd。它们允许选择和重排序所有已归档项、支持并行恢复并且默认是压缩的。“目录”格式是唯一一种支持并行转储的格式。
这里使用脚本转储，即输出一个纯文本形式的SQL脚本文件的形式。
全部参数应当参照上放官方文档，这里只列出一些主要的、常用的参数。
| 参数 | 解释 |
|---|---|
| -U username –username=username |
要作为哪个用户连接。 |
| dbname | 指定要被转储的数据库名。如果没有指定，将使用环境变量PGDATABASE。如果环境变量也没有设置，则使用指定给该连接的用户名。 |
| -h host –host=host |
指定服务器正在运行的机器的主机名。默认主机是本地主机或你的PGHOST环境变量指定的主机。 |
| -p port –port=port |
指定服务器正在监听连接的 TCP 端口。默认端口是环境变量PGPORT或（如果PGPORT不存在）内建的默认值。 |
| -c –clean |
在输出创建数据库对象的命令之前输出清除（删除）它们的命令 （除非也指定了–if-exists，如果任何对象不存在于目的数据库中，恢复可能会产生一些伤害性的错误消息）。这个选项只对纯文本格式有意义。对于归档格式，你可以在调用pg_restore时指定该选项。 |
| -F format —format=format |
p plain 输出一个纯文本形式的SQL脚本文件（默认值）。 c custom 输出一个适合于作为pg_restore输入的自定义格式归档。和目录输出格式一起，这是最灵活的输出格式，它允许在恢复时手动选择和排序已归档的项。这种格式在默认情况还会被压缩。 d directory 输出一个适合作为pg_restore输入的目录格式归档。这将创建一个目录，其中每个被转储的表和大对象都有一个文件，外加一个所谓的目录文件，该文件以一种pg_restore能读取的机器可读格式描述被转储的对象。一个目录格式归档能用标准 Unix 工具操纵，例如一个未压缩归档中的文件可以使用gzip工具压缩。这种格式默认情况下是被压缩的并且也支持并行转储。 t tar 输出一个适合于输入到pg_restore中的tar-格式归档。tar 格式可以兼容目录格式，抽取一个 tar 格式的归档会产生一个合法的目录格式归档。不过，tar 格式不支持压缩。还有，在使用 tar 格式时，表数据项的相对顺序不能在恢复过程中被更改。 |
| -n schema –schema=schema |
只转储匹配schema的模式，这会选择模式本身以及它所包含的所有对象。当没有指定这个选项时，目标数据库中所有非系统模式都将被转储。多个模式可以通过书写多个-n开关来选择。 |
| -N schema –exclude-schema=schema |
不转储匹配schema模式的任何模式。该模式被根据-n所用的相同规则被解释。-N可以被给定多次来排除匹配几个模式中任意一个的模式。当-n和-N都被给定时，该行为是只转储匹配至少一个-n开关但是不匹配-N开关的模式。如果只有-N而没有-n，那么匹配-N的模式会被从一个正常转储中排除。 |
| -t table –table=table |
只转储名字匹配table的表，“table”还可以包括视图、物化视图、序列和外部表。通过写多个-t开关可以选择多个表。当-t被使用时，-n和-N开关不会有效果，因为被-t选择的表将被转储而无视那些开关，并且非表对象将不会被转储。 |
| -T table –exclude-table=table |
不转储匹配table模式的任何表。该模式被根据-t所用的相同规则被解释。-T可以被给定多次来排除匹配几个模式中任意一个的模式。当-t和-T都被给定时，该行为是只转储匹配至少一个-t开关但是不匹配-T开关的表。如果只有-T而没有-t，那么匹配-T的表会被从一个正常转储中排除。 |
| –quote-all-identifiers | 强制引用所有标识符。当从PostgreSQL主版本与pg_dump不同的服务器上转储一个数据库时或者当输出准备载入到一个具有不同主版本的服务器时，推荐使用这个选项。默认情况下，pg_dump只对在其主版本中是被保留词的标识符加上引号。在转储其他版本服务器时，这种默认行为有时会导致兼容性问题，因为那些版本可能具有些许不同的被保留词集合。使用–quote-all-identifiers能阻止这种问题，但代价是转储脚本更难阅读。 |
那么备份脚本如此即可
#!/bin/bash
# 备份存放位置
BACKUP_DIR=/var/lib/backup/
# 如果不存在就创建
if [ ! -d "$BACKUP_DIR" ]; then
mkdir -p "$BACKUP_DIR"
fi
cd $BACKUP_DIR
# 删除30天之前的备份
deleteDIR=$(date --date='30 day ago' +%Y-%m-%d)
rm -rf $deleteDIR
# 假设每天凌晨执行 备份数据文件夹名为昨日 yyyy-MM-dd 格式
dateDIR=$(date --date='1 day ago' +%Y-%m-%d)
mkdir $dateDIR
# 备份指定的数据库
backupDB=(db_name0 db_name1 db_name2)
backupDBArr=$(echo ${backupDB[@]})
# 使用压缩转储每一个数据库
for i in $backupDBArr; do
pg_dump -U postgres $i | gzip > $BACKUP_DIR$dateDIR/${i}_${dateDIR}.gz
done
结合 crontab 定时任务完成定时备份
01 00 * * * sh /.../pg_backup.sh
恢复
# 把脚本重新载入到一个新创建的名为 newdb 的数据库中
psql -d newdb -f db.sql
gunzip -c filename.gz | psql dbname
cat filename.gz | gunzip | psql dbname
使用split。split命令允许你将输出分割成较小的文件以便能够适应底层文件系统的尺寸要求。例如，让每一块的大小为1兆字节：
# 备份
pg_dump dbname | split -b 1m - filename
# 恢复
cat filename* | psql dbname