Postgresql学习笔记之——备份和恢复（pg_start_backup、pg_stop_backup、pg_basebackup）

最新推荐文章于 2023-03-27 22:21:29 发布

Major_ZYH

最新推荐文章于 2023-03-27 22:21:29 发布

阅读量4.4k

点赞数 2

分类专栏： Postgresql 文章标签：数据库 pgsql postgresql

本文链接：https://blog.csdn.net/qq_32838955/article/details/105049517

版权

Postgresql 专栏收录该内容

32 篇文章 22 订阅

订阅专栏

Postgresql数据库提供了三种不同的基本方法来备份和恢复Postgresql数据：

1.使用 pg_dump 和 pg_dumpall 进行转储，从SQL转储文件中恢复。

2.文件系统级别的备份。

3.增量备份和基于时间点恢复（PITR）

一、增量备份

Postgresql数据库在写入操作时，对数据文件做的任何修改都会写入WAL（预写日志）日志中，然后才会对数据文件做物理修改。当数据库服务器掉电或者意外宕机，Postgresql在启动时首先读取 WAL 日志，对数据文件进行恢复。

由于Postgresql数据库循环写WAL日志的机制，我们日常工作中要定期对WAL日志进行归档。为了减少数据恢复时间可以对数据库进行增量备份方式进行数据库备份。

1.备份前的准备-数据库配置

（1）创建归档目录
根据实际情况，在磁盘或者挂载的NFS目录上创建归档日志存放的目录，创建时要修改属主和属组为 postgres：

[root@local ~] # mkdir -p /data/archive_wals
[root@local ~] # cd /data
[root@local ~] # chown postgres:postgres /archive_wals

（2）修改 wal_level 参数
修改参数文件 postgresql.conf 中的参数 wal_level 的值。可选有 minimal、replica和logical，从minimal到logical，WAL 日志级别依次增高，WAL 记录信息越多，越详细。
由于minimal 这一级别不包含从基本的备份和WAL日志中重建数据的足够信息，所以必须至少开启 wal_level 参数值为 replica ：

postgres=# ALTER SYSTEM SET wal_level = ‘replica’；
ALTER SYSTEM

（3）修改 archive_mode 参数
archive_mode 参数可选值有 on、off 和 always。默认 off，开启归档需要修改参数值为 on ：

postgres=# ALTER SYSTEM SET archive_mode  = ‘on’；
ALTER SYSTEM

PS：wal_level 与 archive_mode 参数都需要数据库重启才能生效，可以选择在数据库使用ALTER SYSTEM 命令修改后进行数据库重启，或者直接在参数文件 postgresql.conf 中修改，然后进行数据库重启。

（4）修改 archive_command 参数
archive_command 参数默认值为空字符串，他的值可以是一个shell命令或者一个复杂的shell脚本。在archive_command 的shell命令或脚本中可以用 %p 表示将要归档的WAL 文件的包含完整路径信息的文件名称。用 %f 代表不包含路径信息的WAL文件的文件名。
参数设置：

postgres=# ALTER SYSTEM SET archive_command = ‘cp  %p  /data/archive_wals/%f’
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
-----------------
t

拷贝所有WAL日志文件到指定的归档目录。

修改archive_command 参数不需要重启数据库，reload即可。

需要注意的是，archive_command 设定的归档命令是否成功执行，如果未成功，它会周期性的重试，在此期间已有的WAL日志将不会被覆盖重用，新的WAL日志信息会不断占用 pg_wal 的磁盘空间，知道pg_wal所在磁盘沾满后数据库关闭。由于参数 wal_level 与 archive_mode 需要重启数据库，可以在安装之初启动数据库之前，开启这两个参数，然后将 archive_command 的值设置为永远为真的值，例如：/bin/true。当需要开启归档时，只需要修改 archive_command，reload即可。省去重启数据库的步骤。

如果考虑归档所占空间较多，可以在归档时将WAL日志压缩后在归档，可以用gzip、bzip2、lz4等压缩工具。例如：

把 archive_command 设置为在归档目录使用 lz4 压缩 WAL 日志文件：

postgres=# ALTER SYSTEM SET archive_command = ‘/usr/bin/lz4 -q -z %p  /data/archive_wals/%f.lz4’
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
-----------------
t

2.使用pg_start_backup 和 pg_stop_backup 创建基础备份

在较低的版本中，使用 pg_start_backup 和 pg_stop_backup 创建基础备份。从Postgresql 9开始有了 pg_basebackup 程序，用普通文件或创建tar包的方式进行基础备份。

使用低级基础工具进行基础备份主要三个步骤：

执行 pg_start_backup命令开始执行备份；
使用命令创建数据库目录的副本；
执行pg_stop_backup命令结束备份。

a）执行pg_start_backup命令
此命令作用是创建一个基础备份的准备，这些准备工作包括：

① 判断WAL归档是否已经开启。如果没有开启WAL归档，备份依然会进行，但是备份结束后会有提示信息，大概意思是备份了，但是WAL日志需要确保用其他方式将必需的WAL日志复制到归档目录。但这样备份完后手动进行复制有可能导致复制的WAL是已经被覆盖后的。

②**强制进入全页写模式。**判断当前是否是全页写模式，当参数 full_page_writes的值为 off 时表示关闭了全页写模式，但当检查备份准备时，如果没有开启，则会强制性将参数 full_page_writes 值设置为 on。

③创建一个检查点。

④**排他基础备份的情况下还会创建backup_label文件。**一个backup_label文件包含以下五项：
START WAL LOCATION：25/2B002118
CHECKPOINT LOCATION：记录由命令创建的检查点的 LSN 位置。
BACKUP METHOD：做基础备份的方法，值为 pg_start_backup 或是 pg_basebackup，如果只是配置流复制，值时streamed。
BACKUP FROM：备份来源，指的是从master或standby做的基础备份。
START TIME：执行pg_start_backup的时间戳。
LABEL：在pg_start_backup中指定的标签

b）使用命令创建数据目录的副本
使用rsync、tar、cp、scp等命令都可以创建数据目录的副本，说白了就是讲$PGDATA下目录和涉及到的用户自定义表空间目录全部复制一份。

c）执行 pg_stop_backup 命令
执行此命令时，进行五个操作来结束备份：

① 如果在执行 pg_start_backup 命令时，全页写参数 full_page_writes 被强制修改，则恢复参数为之前的值。

② 写一个备份结束的 XLOG 记录。

③ 切换WAL段文件。

④ 创建一个备份历史文件，改文件包含 backup_label 文件的内容以及执行 pg_stop_backup 的时间戳。

⑤ 删除backup_label文件，此文件对于从基本备份进行恢复时必须的，一旦进行复制，原始数据库集群中就不想要该文件了。

示例如下：

（1）执行 pg_start_backup 开始备份

[postgres@local pgsql_data]$ psql
psql (12.1)
Type "help" for help.

postgres=# select pg_start_backup('base',false,false);
 pg_start_backup 
-----------------
 0/40000060
(1 row)

（2）创建数据目录的副本

[postgres@local ~]$ mkdir -p /data/pgbackupdir
[postgres@local ~]$ cd /data/
[postgres@local data]$ tar -cvf backup/base.tar.gz pgsql_data/

（3）执行 pg_stop_backup命令结束备份

postgres=# select pg_stop_backup(false);
NOTICE:  all required WAL segments have been archived
                               pg_stop_backup                                
-----------------------------------------------------------------------------
 (0/40000170,"START WAL LOCATION: 0/40000060 (file 000000010000000000000040)+
 CHECKPOINT LOCATION: 0/40000098                                            +
 BACKUP METHOD: streamed                                                    +
 BACKUP FROM: master                                                        +
 START TIME: 2020-03-23 17:42:19 CST                                        +
 LABEL: base                                                                +
 START TIMELINE: 1                                                          +
 ","16817 /pgsql/pg_data                                                    +
 ")
(1 row)

至此完成了一个基础本分的过程！

3.使用pg_basebackup工具备份

因为pg_basebackup 命令时整合了 pg_start_backup和pg_stop_backup命令，所以执行在shell上执行即可：

-Ft：打包文件为tar包形式。
-Pv：输出日志备份信息。
-R：在输出目录中（或者当使用 tar 格式时再基础归档文件中）建立 standby.signal 并附加连接设置到postgresql.auto.conf 来简化设置一个后备服务器。 postgresql.auto.conf文件将记录连接设置（如果有）以及pg_basebackup所使用的复制槽，这样流复制后面就会使用相同的设置。

[postgres@local ~]$ pg_basebackup -D /data/pgbackupdir -Ft -Pv  -U postgres -W  -R
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
2021-08-16 18:12:43.684 CST [44600] WARNING:  skipping special file "./.s.PGSQL.1921"
WARNING:  skipping special file "./.s.PGSQL.1921"
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2A000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_44601"
2021-08-16 18:12:45.613 CST [44600] WARNING:  skipping special file "./.s.PGSQL.1921"
WARNING:  skipping special file "./.s.PGSQL.1921"
467667/467667 kB (100%), 2/2 tablespaces                                         
pg_basebackup: write-ahead log end point: 0/2A000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

[postgres@local ~]$ cd 
[postgres@local pgbackupdir]$ ll -h /data/postgresql/pgbackupdir
total 473M
-rw------- 1 postgres postgres 215M Aug 16 18:12 16400.tar
-rw------- 1 postgres postgres 243M Aug 16 18:12 base.tar
-rw------- 1 postgres postgres  17M Aug 16 18:12 pg_wal.tar

使用pg_basebackup备份成功后一般会有三种文件，例如上面备份的：
16400.tar ：为用户自定义表空间的备份。
base.tar ：数据目录的备份。
pg_wal.tar ：归档日志的备份。

二、备份恢复

当出现故障进行恢复时，通过重做WAL日志可以将数据库恢复到最近的时间点或指定的时间点，还可以恢复到指定的还原点。

---------- 恢复到最近的时间点 ----------

1.创建测试数据

[postgres@local data]$ psql
psql (12.1)
Type "help" for help.

postgres=# select * from employee ;
 id | name  | sex | age | emp_no 
----+-------+-----+-----+--------
  1 | zhang | t   |  23 |    210
  2 | Lee   | f   |  33 |    211
  3 | Smith | t   |  28 |    212
  
(4 rows)

postgres=# insert into employee values(4,'Sun',false,22,213);
INSERT 0 1

postgres=# select * from employee ;
 id | name  | sex | age | emp_no 
----+-------+-----+-----+--------
  1 | zhang | t   |  23 |    210
  2 | Lee   | f   |  33 |    211
  3 | Smith | t   |  28 |    212
  4 | Sun   | f   |  22 |    213
(4 rows)

2.按照基础备份方式将数据库备份
按照前面介绍的pg_basebackup的方式使用最基础的备份方式备份，但是因为是测试，WAL日志没有写满16M不会进行归档，所以在执行完基础备份后搜狐懂进行一次 WAL 切换，如下：

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/43003838
(1 row)

3.恢复到最近时间点

实验恢复到最近时间点的恢复，通过以下步骤：

（1）移除数据库的数据目录，造成目录删除的故障

[postgres@local ~]$ pg_ctl stop -D $PGDATA
waiting for server to shut down... done 
server stopped
[postgres@local ~]$ cd /data/pgsql_data
[postgres@local ~]$ rm -rf *

（2）创建新的数据目录并解压创建的基础备份

[postgres@local ~]$ cd /data/pgbackupdir  
[postgres@local pgbackupdir]$ tar xf 16400.tar -C /data/pgsql_data
[postgres@local pgbackupdir]$ tar xf base.tar -C /data/pgsql_data
[postgres@local pgbackupdir]$ tar xf pg_wal.tar -C /data/archive_wals

（3）配置 recovery参数
Postgresql 12 将 recovery.conf 整合到了 postgresql.conf中，需要编辑参数文件 postgresql.conf 中关于 recovery 的参数：

[postgres@local data]$ cd pgsql_data
[postgres@local pgsql_data]$ vim postgresql.conf
.....
restore_command = 'cp /data/pgbackupdir/%f %p' 
......
recovery_target_timeline = 'latest'
......

其中recovery_target_timeline 参数是默认恢复到最近时间点 latest。

（4）启动服务开始恢复

[postgres@local data]$ pg_ctl start -D $PGDATA

[postgres@local ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# select * from employee ;
 id | name  | sex | age | emp_no 
----+-------+-----+-----+--------
  1 | zhang | t   |  23 |    210
  2 | Lee   | f   |  33 |    211
  3 | Smith | t   |  28 |    212
  4 | Sun   | f   |  22 |    213
(4 rows)

但是启动后的pg是只读模式，无法进行数据修改，例如创建新库提示如下：

postgres=# create database test;
ERROR:  cannot execute CREATE DATABASE in a read-only transaction

此时查看pg运行日志可以看到相关提示：

[postgres@local130 log]$ tailf postgresql-2021-08-17_095859.log 
2021-08-17 09:58:59.901 CST [44768] LOG:  database system was shut down in recovery at 2021-08-17 09:58:58 CST
cp: cannot stat ?.data/postgresql/arch/00000002.history?. No such file or directory
2021-08-17 09:58:59.905 CST [44768] LOG:  entering standby mode
2021-08-17 09:59:00.370 CST [44768] LOG:  restored log file "00000001000000000000002A" from archive
2021-08-17 09:59:00.626 CST [44768] LOG:  redo starts at 0/2A000028
2021-08-17 09:59:00.626 CST [44768] LOG:  consistent recovery state reached at 0/2A000138
2021-08-17 09:59:00.626 CST [44768] LOG:  recovery stopping after reaching consistency
2021-08-17 09:59:00.626 CST [44768] LOG:  recovery has paused
2021-08-17 09:59:00.626 CST [44768] HINT:  Execute pg_wal_replay_resume() to continue.
2021-08-17 09:59:00.627 CST [44766] LOG:  database system is ready to accept read only connections
2021-08-17 09:59:43.184 CST [44779] ERROR:  cannot execute CREATE DATABASE in a read-only transaction

按照提示中需要执行pg_wal_replay_resume()才可以继续：

postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)

postgres=# create database test;
CREATE DATABASE

①②③④⑤⑥⑦⑧⑨⑩

Major_ZYH

关注

2
点赞
踩
14

收藏

觉得还不错? 一键收藏
打赏
1
评论
Postgresql学习笔记之——备份和恢复（pg_start_backup、pg_stop_backup、pg_basebackup）

Postgresql数据库提供了三种不同的基本方法来备份和恢复Postgresql数据：1.使用 pg_dump 和 pg_dumpall 进行转储，从SQL转储文件中恢复。2.文件系统级别的备份。3.增量备份和基于时间点恢复（PITR）一、增量备份Postgresql数据库在写入操作时，对数据文件做的任何修改都会写入WAL（预写日志）日志中，然后才会对数据文件做物理修改。当数据库服务器...
复制链接

扫一扫