【施工中】PostgreSQL备份和恢复

发表于 | 更新于: | 分类于 | 阅读次数:
字数统计: 960

说明

基于PostgreSQL 10.8版本做演示

其他版本自行调整

逻辑备份

pg_dump

备份的逻辑

  • pg_dump的一次完整的备份是在一个事务中完成的, 事务隔离级别为serializable 或者 repeatable read。
  • pg_dump在备份数据开始前, 需要对进行备份的对象加ACCESS SHARE锁。为了防止无休止的锁等待，可以使用--lock-wait-timeout设置锁超时时间
  • 一切准备就绪后，pg_dump开始备份数据

备份的格式

  • p或者plain
    • 默认格式，可读写的文本文件，里面是SQL语句
  • c或者custom
    • 自定义格式，默认开启数据压缩，还原时可以调整对象还原顺序，必须使用pg_restore命令还原
  • d或者directory
    • 目录归档格式，需要用pg_restore命令还原，目录归档格式下会创建一个目录, 然后每个表或者每个大对象对应一个备份输出文件，加上TOC文件名描述备份的详细信息, 这个格式默认支持压缩, 同时支持并行导出
  • t或者tar
    • tar归档格式, 不支持压缩, 同时限制每个表最大不能超过8GB, 同样需要使用pg_restore还原

全库一致性

指定是集群中的单个数据库的一致性备份。

因为备份不同的数据库需要切换连接，无法在不同的数据库之间共享snapshot，因此只能单库一致。

非全库一致性备份

如果单个库数据量越大，pg_dump备份时间就越长，持有锁的时间也就越长。

这时候会阻塞DDL语句。

未解决这个问题，可以将pg_dump的粒度缩小，只备份相关联且有一致性需求的数据表

pg_dumpall

pg_dumpall最主要的是用于备份全局数据, 例如表空间的DDL, 创建用户的DDL

简单的备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#!/bin/bash
set -ex
# 定义PostgreSQL数据库连接
export PGHOST='192.168.1.1'
export PGPORT='5432'
export PGUSER='postgres'
export PGPASSWORD='postgres_passwd'
# 定义目录
BASE_DIR='/data/pgsql-dump'
# 定义文件名
FILENAME='pgsql-dump'
# 定义时间格式，20190329-00
DATE=$(date +%Y%m%d)
TIME=$(date +%H%M)
# 根据时间格式创建文件夹
BACKUP_DIR="${BASE_DIR}/${DATE}/${TIME}"
mkdir -p ${BACKUP_DIR}
# 过滤需要备份的数据库
DATABASES=$(psql --host=${PGHOST} --port=${PGPORT} --username=${PGUSER} -c "\l" | awk '{print$1}' | xargs)
# 对每个数据库单独做逻辑备份
for DB in $DATABASES;do
    pg_dump --host=${PGHOST} --port=${PGPORT} --username=${PGUSER} --verbose --clean --create ${DB} | gzip -c > ${BACKUP_DIR}/${FILENAME}_${DB}.gz
done
# 备份整个数据库
pg_dumpall --host=${PGHOST} --port=${PGPORT} --username=${PGUSER} --verbose --clean | gzip -c > ${BACKUP_DIR}/${FILENAME}_all.gz
# 查找备份目录修改时间大于30天的文件并清理
find ${BASE_DIR}/ -mtime +30 -exec rm -rf {} \;

物理备份

在线热备份

pg_basebackup

PostgreSQL自带的一个远程热备工具，可以将远程PostgreSQL热备到本地目录

工作流程

  • 连接到一个远程PostgreSQL

  • 执行pg_start_backup

  • 将整个数据目录传输到本地

  • 执行pg_stop_backup命令

命令示例

使用postgres用户将192.168.1.1:5432PostgreSQL的数据备份到/path/to/data_dir目录

1
pg_basebackup -h 192.168.1.1 -p 5432 -U postgres -D /path/to/data_dir

pg_rman

PostgreSQL的备份与恢复工具，支持全量、增量、归档三种备方式，支持数据压缩与备份集管理。

pg_rman跑的不是流复制协议，而是文件拷贝，所以pg_rman必须与数据库服务器跑在同一台机器。

具体用法可以看德哥的文档

项目首页

工作流程

  • 连接到本地PostgreSQL
  • 执行pg_start_backup
  • 全量备份文件或者通过比较数据文件块的lsn号进行增量备份
  • 执行pg_stop_backup命令
  • 备份归档日志

前提条件

  • 开启归档
    • archive_mode = on
    • archive_command = 'cp %p /data/pg-archlog/%f'
  • 配置csvlog
    • log_destination = csvlog
    • log_directory = pg_log

恢复