数据库备份基本理论
根据备份文件类型可分为物理备份和逻辑备份
根据备份时数据库状态可分为在线备份和离线备份
根据备份数据量可分为全量备份和增量备份
- 物理备份 (直接复制数据库的原文件,复制慢恢复快)
- 离线备份(冷备份)
- 在线备份(热备份)
- 逻辑备份(生成SQL文件,复制快恢复慢)
- 在线备份(热备份)
- 全量备份(备份完整的数据)
- 增量备份(备份上次备份后修改的部分)
- 在线备份(热备份)
备份还原工具
注意:(pg_...为系统自带的工具)
- pg_dump:逻辑备份工具,单个数据库备份
- pg_dumpall:逻辑备份工具,集群数据库备份(用户下的多个数据库)
- pg_basebackup:物理备份工具,集群数据库备份
- psql:交互命令行工具,也可以用于逻辑还原sql文件
- pg_restore:逻辑还原工具,还原pg_dump生成的除了SQL文件的其他格式备份
- copy:pgsql专有SQL语句,可用于将表中数据复制到文件,或将文件复制到表中
1、逻辑备份与还原
使用命令 --help查看命令帮助文档
1.1 pg_dump备份单个数据库
pg_dump -h 43.168.152.154 -p 5432 -U postgres -W -d postgres -f "C:\Users\hwz\Desktop\backup\mypg.sql"
-h:数据库主机地址(本地可省略)
-p:端口(省略默认5432)
-U:用户名
-W:密码
-d:备份的数据库名
-f:备份输出目标文件
还原-sql格式
psql -h 43.138.173.134 -p 5432 -U postgres -W -d mydb7 -f "C:\Users\hwz\Desktop\backup\mypg7.sql"
备份输出其他格式
-F, --format=c|d|t|p 输出文件格式 (定制, 目录, tar,明文 (默认值))
定制格式
pg_dump -h 43.138.173.134 -p 5432 -U postgres -W -d mydb -F c -f "C:\Users\hwz\Desktop\backup\mypg1.dmp"
目录格式
pg_dump -f "C:\Users\hwz\Desktop\backup\mysql6" -h 43.138.173.134 -p 5432 -U postgres -W -Fd -d mydb
tar格式
pg_dump -f "C:\Users\hwz\Desktop\backup\mysql4.tar" -h 43.138.173.134 -p 5432 -U postgres -W -Ft -d mydb
还原
还原定制格式
pg_restore -h 43.138.173.134 -p 5432 -U postgres -W -d mydb3 "C:\Users\hwz\Desktop\backup\mypg1.dmp"
还原目录格式
pg_restore -h 43.138.173.134 -p 5432 -U postgres -W -d mydb5 "C:\Users\hwz\Desktop\backup\mysql6"
还原tar格式
pg_restore -h 43.138.173.134 -p 5432 -U postgres -W -d mydb4 "C:\Users\hwz\Desktop\backup\mysql4.tar"
1.2 导出表数据
只导出表数据有两种方法:
- 使用pg_dump导出只有数据的SQL文件
- 使用copy命令
pg_dump -h 43.138.173.134 -p 5432 -U hwz -W -d hwzdb1 -a -f "C:\Users\hwz\Desktop\backup\data\tb_user_data.sql"
2、物理备份和还原
2.1 离线备份
关闭数据库
复制备份数据库data数据(包含wal文件和放在其他目录下的tablespace)
2.1.1 离线备份恢复
用备份数据覆盖原有数据
开启数据库
2.2 在线备份
在线备份相比离线备份的优点是
1.可以不停服务
2.可以恢复备份点数据及备份点之后的数据(利用wal文件恢复)
步骤:
- 开启归档日志模式运行
- 基准备份
- 使用基准备份+归档wal+当前wal恢复最新数据
2.2.1 开启归档日志模式
为什么要备份wal?
因为wal日志文件不可以一直增长保存占用越来越多的空间的。所以wal日志文件到达一定数量就会覆盖前面的。
如果基准备份之后一段wal日志数据被覆盖就会恢复不完整了。
修改 postgresql.conf 文件
wal_level = replicaarchive_mode = onarchive_command = 'test ! -f 备份目录/%f && cp %p 备份目录/%f'archive_timeout = 300
最后重启pgsql服务端
2.2.2 基准备份
基准备份是一个全量备份。
方法一:调 用 两 个系 统 函 数 : pg_backup_start()和 pg_backup_stop()
postgres=# SELECT pg_backup_start('basebackup20230912');
标识开始备份
postgres=# SELECT pg_backup_stop();
标识备份结束
方法二:利用pg_basebackup命令备份
备份集群
pg_basebackup -D /home/pg_backup/backup2 -Ft -z -R -Pv
-D:备份到哪个文件夹下(不能为空)
-Ft:tar格式
-z:zip压缩
-R:生成 recovery.signal标识文件
-Pv:展示详细备份过程
更多命令参考PostgreSQL: 文档: 15: pg_basebackup
2.2.3 时间点恢复
假设数据库服务端故障需要进行恢复
1.将基准备份文件替换原数据库数据文件
2.将原数据库备份wal文件和还没备份的wal文件替换 基准备份的备份wal文件和还没备份的wal文件
3.设置替换数据文件的postgresql.conf备份配置
restore_command = 'cp 备份wal目录/%f %p'#recovery_target_time = "2023-9-12 12:05 GMT" #时间点恢复,不设置则全恢复
4.创建空文件recovery.signal作为恢复标识(标识这次启动进行恢复,恢复完删除这个文件,防止下次重复恢复)
5.重启数据库