PostgreSQL核心操作之数据备份恢复

一、备份恢复的方式

PG备份恢复方式主要分为两类，逻辑备份恢复和物理备份恢复。

1、逻辑备份恢复

有三种，分别为pg_dump、pg_dumpall和copy。

pg_dump：可以选择一个数据库或部分表进行备份

pg_dumpall：备份集簇服务所有数据库

copy：导入导出表数据

2、物理备份恢复

有两种，分为冷备份和热备份。

冷备份及恢复（文件系统复制）

热备份及恢复（基于时间点的备份恢复）

二、逻辑备份与恢复

下面来详细介绍逻辑备份的几种方式和原理。

（一）逻辑备份-pg_dump

pg_dump 是一个普通的 PostgreSQL 客户端应用，可在任何可以访问数据库的远端主机上进行备份。它可以选择一个数据库或部分表进行备份，并支持跨平台恢复。pg_dump 在数据库使用中实现完整一致的备份，不阻塞其他用户访问，但仅能备份单个数据库，不导出角色和表空间信息。备份数据库涉及权限，几乎总是需要超级用户权限。

1. 基本用法：pg_dump dbname > dumpfile
   
2. 在线帮助：pg_dump --help
   
3. 主要选项：
   
4. -F c 备份为二进制格式, 压缩存储， 并且可被pg_restore用于精细还原
   
5. -F p 备份为文本, 大库不推荐
   
6. 示例：
   
7. pg_dump --table=tbl -d db
   
8. pg_dump --schema=scm -d db
   

主要特点：

pg_dump 可以用其他格式创建文件，以支持并行和细粒度的对象恢复控制。其输出可以轻松载入新版 PostgreSQL，是唯一能将数据库迁移到不同架构的方法，如从32位服务器迁移至64位。运行过程中发生的更新不会被转储，且不阻塞其他数据库操作（但会阻塞需要排它锁的操作，比如大部分 ALTER TABLE）。

pg_dump生成的文本文件可以由psql程序读取。

从转储中恢复的常用命令是：

1. psql dbname < dumpfile
   

dumpfile是 pg_dump命令的输出文件。这条命令不会创建数据库 dbname，必须在执行 psql 前用 createdb -T template0 dbname 自行创建。psql 类似于 pg_dump 支持选项来指定要连接的数据库服务器和用户名。非文本文件转储可以用 pg_restore 工具恢复。在开始恢复前，转储库中对象的拥有者及被授予权限的用户必须已存在，否则恢复过程无法保留原有的所属关系和权限。一旦恢复完成，有必要在每个数据库上运行 ANALYZE，以便优化器获取有用的统计数据。

（二）逻辑备份-pg_dumpall

由超级用户执行备份整个集簇和数据库，包括角色和表空间，生成 psql 脚本。pg_dumpall 只支持文本格式，并在内部调用 pg_dump。

1. 基本用法： pg_dumpall > dumpfile
   
2. 可以使用psql恢复： psql -f dumpfile postgres
   
3. 常用选项：
   
4. --data-only        提供没有对象定义的数据转储
   
5. --globals-only    备份转储角色和表空间
   
6. --clean        包括删除数据库，角色和表空间
   

逻辑恢复要点

文本格式的备份文件, 直接使用用户连接到对应的数据库执行备份文本；

1. psql dbname -f filename
   
2. psql dbname -U username < filename
   

二进制格式的备份文件只能使用pg_restore来还原；可以指定还原的表, 编辑TOC文件, 定制还原的顺序, 表, 索引等；

1. pg_restore [option] ... [filename]
   
2. pg_restore -d dbname bakfile
   

（三）逻辑备份恢复-copy命令

COPY 命令用于在表与文件（以及标准输入输出）之间进行相互拷贝；COPY TO 从表到文件，COPY FROM 从文件到表。COPY 命令在数据库服务端操作文件，并需超级用户权限，适合数据库管理员使用；\COPY 命令在客户端操作数据文件，对权限要求较低，适合开发和测试人员使用。COPY 与 \COPY 命令都能实现数据文件与表的数据传递，且都在 psql 环境下执行。

三、物理备份与恢复

（一）冷备份恢复

冷备份恢复

使用操作系统支持的各种拷贝命令，把整个 PGDATA 备份。

冷备份前，建议干净的关闭数据库(pg_ctl stop -m fast)。

在同架构同平台同数据库版本的迁移需求中，使用该方式，且极大节省迁移时间，且操作简单，安全性高。

1. 备份示例：tar -jcv -f /home/postgres/bak/dbbak0817.tar.bz2 $PGDATA
   
2. 恢复示例：tar -jxv -f /home/postgres/bak/dbbak0817.tar.bz2 -C /
   

（二）在线热备份

1、在线热备份恢复原理

运用在线备份以及即时恢复(PITR)原理,利用Postgresql 数据库的WAL（Write Ahead Logging ）预写日志和基础备份( $PGDATA目录文件tar包 )，恢复到数据库崩溃前时间点，保证数据量最少丢失或者不丢失.

如果数据库崩溃，我们就可以通过热备产生的备份文件data_bak.tar包 ($PGDATA目录文件tar包)和archive_command产生的WAL及我们自己备份的WAL(pg_xlog)来进行数据库的 recovery 。

2、在线热备份三种方式

1、pg_basebackup

2、pg_start_backup()和 pg_stop_backup()

3、文件系统快照的备份

1、pg_basebackup

pg_basebackup 简介

是从postgresql 9.1版本开始提供的一个方便基础备份的工具

它会把整个数据库实例的数据都拷贝出来，而不只是把实例中的部分（如某个数据库或表）单独备份

注意：归档日志需要单独备份

pg_basebackup工作原理

1）创建检查点，打开FPW，创建备份标签（存储检查点位置，时间等信息）

2）通过流复制协议与数据库建立连接，WAL Sender进程向pg_basebackup发送数据库物理文件

3）pg_basebackup接收到文件后写入目标位置（压缩或不压缩）

pg_basebackup 示例

要创建服务器mydbserver的一个基础备份并将它存储在本地目录/pgbak/data中：

1. $ pg_basebackup -h mydb -D /pgbak/data
   

要创建本地服务器的一个备份，为其中每一个表空间产生一个压缩过的 tar 文件，并且将它存储在目录backup中，在运行期间显示一个进度报告：

1. $ pg_basebackup -D backup -Ft -z -P
   

要创建一个单一表空间本地数据库的备份并且使用bzip2压缩它：

1. $ pg_basebackup -D - -Ft -X fetch | bzip2 > backup.tar.bz2
   

（如果在该数据库中有多个表空间，这个命令将失败）。

要创建一个本地数据库的备份，其中/opt/ts中的表空间被重定位到./backup/ts：

1. $ pg_basebackup -D backup/data -T /opt/ts=$(pwd)/backup/ts
   

pg_basebackup 备份过程

数据库版本：PostgreSQL 14.12

1）开启归档

创建归档目录

1. mkdir -p /ssd/pg1412/arch
   
2. chown -R postgres:postgres /ssd/pg1412/arch
   

配置归档

1. vi $PGDATA/postgresql.conf
   
2. archive_mode = on
   
3. archive_command = 'DATE=`date +%Y%m%d`; DIR="/ssd/pg1412/arch/$DATE"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'
   

2）重启数据库使参数生效，验证归档。

pg_ctl stop;
pg_ctl start;

3）进入数据库

1. postgres=#checkpoint;
   
2. postgres=#SELECT pg_switch_wal();
   
3. [postgres@localhost 20240617]cd /ssd/pg1412/arch/20240617
   
4. [postgres@localhost 20240617]ll
   

3）创建replication权限的角色, 或者超级用户的角色。

create role repl nosuperuser replication login connection limit 32 encrypted password ‘111111’;

4）配置pg_hba.conf，添加以下内容

1. host replication repl 0.0.0.0/0 md5
   

物理备份恢复示例

1）执行备份（因为使用流复制协议, 所以支持异地备份）

pg_ctl reload #执行加载配置的命令

mkdir date +%F ;

pg_basebackup -F t -x -D /home/postgres/bak/date +%F -h 192.168.59.223 -p 5785 -U repl

2）备份完毕，查看备份文件

下面有文件即备份正常。

pg_basebackup热备还原

1）在需要备份的库中创建标记表，并检查点和归档指令

1. create table t_flag(id int) tablespace tbls01;
   
2. insert into t_flag values(1);
   
3. checkpoint;        #刷新内存脏页到磁盘
   
4. select pg_switch_xlog();    #手动日志归档
   

2）停止数据库并删除数据目录，将pg_basebackup生成的备份包分别解压到相应目录

3）recovery.conf文件配置还原参数

1. $cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf
   
2. vi $PGDATA/recovery.conf   #备注
   
3. restore_command = 'cp /ssd/pg1412/arch/20240617/%f %p'
   
4. recovery_target_timeline = 'latest'
   

4）启动数据库并做数据查看验证是否恢复完成

1. pg_ctl start
   

2、pg_start_backup() 备份

pg_start_backup()和 pg_stop_backup() 备份过程

1）开启归档（方法参考前面章节）

2）超级用户连接数据库，执行命令：

1. select pg_start_backup(now()::text);
   

物理备份恢复

4）执行备份

使用操作系统工具比如 tar 或 cp -ra 等，或直接把数据目录复制到备份位置。过程中既不需要关闭数据库，也不需要停止数据库的任何操作。

1. tar -jcv -f ~/bak/pgdata.tar.bz2 $PGDATA
   

注意手动备份表空间路径，即目录的pg_tblspc软连接指向目录。

1. tar -jcv -f ~/bak/dbbak/tbls.tar.bz2 /pgtbls/tbls01
   

5）再次以数据库超级用户身份连接数据库，然后发出命令：

1. select pg_stop_backup();
   

这将终止备份模式并自动切换到下一个 WAL 段。

6）最后拷贝强制检查点之间的所有归档日志文件, 确保备份有效性。

四、基于时间点的恢复

基于时间点的恢复（PITR）

定点恢复，又称基于时间点的数据恢复(Point-In-Time Recovery)，根据给定的时间点，将数据库快速恢复至该点，是数据库误操作后进行救援的常规手段。

原理是依据之前的物理备份文件加上wal的预写日志模式备份做的恢复；

恢复条件

一个基础备份

一份完整的WAL日志

一个明确的恢复标志

文中的概念来源于网络，如有侵权，请联系我删除。