postgresql数据库备份

数据库备份基本理论

根据备份文件类型可分为物理备份和逻辑备份

根据备份时数据库状态可分为在线备份和离线备份

根据备份数据量可分为全量备份和增量备份

 

• 物理备份 （直接复制数据库的原文件，复制慢恢复快）
  • 离线备份（冷备份） 
  • 在线备份（热备份）
• 逻辑备份（生成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 导出表数据

只导出表数据有两种方法：

1. 使用pg_dump导出只有数据的SQL文件
2. 使用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文件恢复） 

 

步骤：

1. 开启归档日志模式运行
2. 基准备份
3. 使用基准备份+归档wal+当前wal恢复最新数据 

2.2.1  开启归档日志模式

为什么要备份wal？

因为wal日志文件不可以一直增长保存占用越来越多的空间的。所以wal日志文件到达一定数量就会覆盖前面的。

如果基准备份之后一段wal日志数据被覆盖就会恢复不完整了。 

 修改 postgresql.conf 文件

wal_level = replica

archive_mode = on

archive_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.重启数据库