开发者学堂课程【PostgreSQL 快速入门：7 PostgreSQL 物理备份和还原，逻辑备份和还原】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/16/detail/93
7 PostgreSQL 物理备份和还原，逻辑备份和还原
内容介绍：
一、数据库热备份
二、数据库物理备份
三、数据库物理还原
四、数据库逻辑备份
五、数据库逻辑还原
一、PostgreSQL 数据库热备份
热备份是指数据库开启并允许应用程序对其进行 rdm 操作的备份。
1、物理备份与还原：
（1）备份$PGDATA，归档文件，以及所有的表空间目录，适用于跨小版本的恢复，但是不能跨平台。
例如输入 cd $PGDATA 命令，查看一个数据库中，$PGDATA 是 initdb -d 指定的目录，里面包含了 base、global、pg_clog、pg_snapshots 等表空间、配置文件和提交日志 pg_clog。
在备份$PGDATA 的同时也需要备份 pg-tblspc，输入 cd pg_tblspc/命令，查看如图：
在pg-tblspc里是一些软连接，真正的数据文件存在于对应的目录里。输入psql，就可以看到创建的三个表空间：
同时也需要备份归档文件：因为在备份的过程中会产生数据库的读写。
输入 less postgresql.conf，/achieve 查看归档文件，这些归档文件是配置在 command 里面
具体归档到的目录标注在：
如上就归档到/ssd4/pg93/arch/目录下面，输入命令:cd /ssd4/pg93/arch和ll命令就可以查看。
输入du -sh *命令可查看/ssd4/pg93/arch/目录中归档时写入的信息。
（2）开启归档。
做物理备份与还原需开启归档，确认 archive_mode = on,
同时 wal-level 需要归档或者被赋予 hot-standby 的值
（3）目前 PG 还不支持基于数据文件数据块变更的增量备份，仅仅支持数据文件＋归档的备份方式。
以上仅仅是基于归档的增量备份，并不支持基于数据文件数据块变更的增量备份，因其没有机制去跟踪数据块的变更。
（4）目前PG官方还不支持基于表空间的备份和还原，但是可模拟；
https://blog.163.com/digoal@126/blog/static/16387704020123261422581/通过此链接可具体了解只需要还原表空间的方法。
2、逻辑备份与还原：
备份数据，适用于跨版本和跨平台的恢复。例如 PostgreSQL 8.4的数据迁移到 PostgreSQL 9.3，无法通过物理的备份还原，仅可使用逻辑备份，把数据备份出来，到 PostgreSQL 9.3新建一个数据库，再把数据导入。
二、PostgreSQL 数据库物理备份
1、归档：
首先要开启归档、日志模式>=archive 级别，步骤如下：
（1）确定当前配置 wal-level=hot-standby
归档模式打开为 Archive-mode=on
其次得到 Archive-command 归档使用的命令：
以上 archive_commd 表示需要命令，令 DATE 的变量改为年月日；用 ssd4/pg93/arch/$DATE下的目录；<test表示检查目录是否存在，如果目录不存在，则 mkdir-p $DIR；如果目录创建命令成功，则拷贝%p，%p表示相对路径，相对于$PGDATAM,目录下有一个 pg_xlogpg，输入命令：ll 93pg_xlogpg\可查看如图。
例如此时归档图中选中文件，则 pg_xlog/00000001 00000001 B0000001F就是%p。
拷贝%p至路径%f,%f为文件名：00000001 00000001 B0000001F。%p为路径 pg_xlog/00000001 00000001 B0000001F 包括文件名。
（2）创建归档目录
.mkdir-p/ssd4/pg93/arch
.chown-R pg93:pg93/ssd4/pg93/arch
（3）配置归档命令
①%p 表示 xlog 文件名$PGDATA 的相对路径：
如 pg_xlog/00000001000000190000007D
②%f表示 xlog 文件名：
如00000001000000190000007D
③Vi$PGDATA/postgresql.conf
④archive_mode=on
⑤archive_command='DATE=`date+%Y%m%d`;
DIR="/ssd4/pg93/arch/$DATE"；(test-d$DIR | | mkdir-p$DIR)&&cp%p$DIR/%f
（4）配置日志模式
vi$SPGDATA/postgresql.conf；(wal_level=hot_standby)
（5）重启数据库(可选，如果以前已经开启了归档的话则不需要重启数据库)
如果 archive_mode 归档没有打开，改参数就需要重启数据库<change requires restart>;以及达标日志，如果 wal-level 原来等于 minimal，改成 archive 或者 hot_standby 也需要重启数据库：<change requires restart>。
（6）测试归档是否正常
可在归档目录中打出查看是否正常：
.digoal=#checkpoint;
.digoal=#select pg_switch_xlog();
假设今天12月29号，来到归档目录:/ssd4/pg93/arch/。有20131229目录，输入 cd 20131229因为处于归档状态，该目录会一直创建，如今归档文件写到如图最后一个文件：
输入命令：psql 和-# checkpoint;做 checkpoint;。命令：-# select pg_switch_xlog(); 切换xlog（）文件，产生一条00000001 00000001 A00000017的新记录，说明 切换 xlog（）文件时会自动拷贝到归档目录，表示归档完成。
2、PostgreSQL 数据库物理备份的方式：
在开启归档，同时归档正常情况下可开启物理备份
方法一：通过 pg_basebacku，流复制协议备份（数据文件备份），(本地使用时必须用 tar 模式，异地无所谓，如果要同目录结构的话使用p模式)。首先可以把想要备份到的库里的大文件使用命令:drop 文件名;去清理掉，以便备份速度快。
①创建 replication 权限的角色，或者超级用户的角色
.digoal=#create role rep nosuperuser replication login
connection limit 32 encrypted password 'rep123';
.CREATE ROLE
如果不是超级用户，若从主机172-16-3-150备份到另一台主机172-16-3-33的/home/postgres/pgbak 的目录下，首先使用 psql - h 172.16.3.150 -p 1921 -U postgres postgres 命令测试172.16.3.150主机是否能连接1921端口，用户postgres 到 postgres这个数据库。若提示未开启选项，输入命令：cd $PGDATA 和 vi pg_hba.conf 将其打开，允许访问。则可以通过 pg_basebacku，流复制协议备份。
若用 replication 权限，则需要创建一个角色；同样，超级用户也可以做这样的备份，只是需要在 pg_hba.conf 里面需要做一个配置:host replication rep 0.0.0.0/0 md5允许任何地址访问使用流复制 replication 地址来连接，最后测试是否能通过 ip 去访问：psql -h 172.16.3.150 -p 1921 -U rep postgres。
命令：-# alter role rep UALID util ‘2099-12-18‘设置密码到期。
命令：-# alter role rep encryted password ‘321REP123’修改密码为321REP123。
②超级用户备份需要配置 pg_hba.conf；
.host replication rep 0.0.0.0/0 md5
.pg_ctl reload
——replication 流复制协议允许使用任何一个IP地址使用流复制连接一个用户 rep。
③备份,因为使用流复制协议，所以支持异地备份.
.mkdir`date+%F`;pg_basebackup-F t -x -D./`date+%F`-h
172.16.3.150 -p 1921 -U rep
l t表示压缩格式，-D./`date+%F`为备份目的地，-h 172.16.3.1
50 表示连接到172.16.3.150,-p 1921表示端口是1921，-U rep 用户 rep。最后输入密码即可。
以下表示备份后生成的目录文件
输入 cd $PGDATA/、cd pg_tbllspc/和ll 命令，可查看备份的原文件：
两者内容符合，可看会将其采用.tar 压缩格式去备份会将数据文件进行压缩：每个表空间作为一个压缩文件，pgdata也作为一个压缩文件，即 base.tar。依次输入 tar --list ./base.tar 和 main tar 来查看 base.tar其中内容。
④备份目录如下
.pg93@db-172-16-3-150->11
.total 13G
.-rw-rw-r-- 1 pg93 pg93 19M Dec 29 17:51 66372.tar
.-rw-rw-r-- 1 pg93 pg93 185M Dec 29 17:51 66422.tar
.-rw-rw-r-- 1 pg93 pg93 6.5M Dec 29 17:51 base.tar
⑤数字目录代表表空间的备份包:
pg93@db-172-16-3-150->cd$PGDATA
.pg93@db-172-16-3-150->cdpg_tblspc/
.pg93@db-172-16-3-150->11
.total 0
.lrwxrwxrwx 1 pg93 pg93 18 Oct 27 07:34 66372->/ssd4/pg93/tbs_idx
.Lrwxrwxrwx 1 pg93 pg93 21 Oct 28 09:16 66422>/ssd3/pg93/tbs_digoal
⑥base 目录代表$PGDATE 的备份包:
-rw-rw-r-- 1 pg93 pg93 242M Dec 29 17:51 base.tar
检查表空间是否已经备份好，输入：
pg93@db-172-16-3-150-> cd_tblspc
查看 base.tar 文件内容:
pg93@db-172-16-3-150->tar -tvfbase.tar |less
（2）方法二：手工拷贝目录的方式备份：归档文件备份
直接连到数据库，执行以下语句：
①首先要打开强制检查点
.pg93@db-172-16-3-150->psql
.psql (9.3.1)
.Type “help” for help.
.digoal=# select pg_start_backup(now（）::text);
.pg_start_backup
.---------------------
.18/27000028
.(1row)
②在$PGDATA 目录里设置一个标签，表示现在开始备份$PGDATA 和表空间目录。
另一个命令：执行 pg_is_in_backup 命令后，备份与 pg_start_backup 相同的文件（备份的内容一样，备份的方式不同）。
③然后备份$PGDATA 和表空间目录，例如拷贝到网络存储（执行命令后才可以拷贝）。要执行-# selcet pg_start_backup<new<>::text>命令后再去拷贝，以此确保备份集有效。
④拷贝完后，关闭强制检查点
执行 digoal=# select pg_stop_backup（）；
⑤最后拷贝强制检查点之间的所有归档文件，确保备份有效性。
三、PostgreSQL 物理还原
1、物理还原,顺序读取 XLOG 的信息进行恢复(xlog 数据块中包含了 DB 数据块的变更，事务状态信息等，深究可以去看一下 xlog 的头文件以及 p&_xlogdump)
2、还原点介绍（数据库的还原会用到备份及归档）：还原到当前的一个时间点；
.#recovery target name =‘ ’ #e.g. daily backup 2011-01
-26' --不支持 inclusive 配置.因为它不需要从 abot 或 commit 判断结束点.
.#
.#recovery_target_time = ’# e.g. 2004-07-14 22:39:00 E
ST’--时间格式使用当前系统配置的格式,或从时间函数获取
.#
.#recovery target xid ="#
.recovery _target_inclusive = true
3、举例：
（1）创建一个表： abc<id int>;
（2）同时向里面插入一个有效记录：
digoal=# insert into abc values <1>;
（3）其次：digoal=# checkpoint;
digoal=# select pg_switch_xlog<>;
确保已经拷贝到归档目录里。
（4）关掉数据库：
g93@db-172-16-3-150->pg_ctl stop -m fast
删掉数据库全部的目录以及表空间；
（5）下面就需要做一个还原：需要用到备份文件（提前拷贝到172.16.3.150主机）
以上会拷贝到 pgbak 的目录里；
（6）再把这些备份文件分别拷贝到对应的目录里，再解压：
输入命令：
.cp base.tar $PGDATA
将Base.tar文件拷贝到$PGDATA 目录下
.cp 66372.tar /ssd4/pg93/tbs_ids/
将66372.ta文件拷贝到/ssd4/pg93/tbs_ids/目录下
.cp 66422.tar /ssd3/pg93/tbs_digoal/
将66422.tar文件拷贝到/ssd3/pg93/tbs_digoal/目录下
.mkdir -p /ssd2/pg93/pg_root/tbs_test
创建/ssd2/pg93/pg_root/tbs_test目录（不存在时创建）
.cp 93057.tar /ssd2/pg93/pg_root/tbs_test/
.将93057.tar文件拷贝到/ssd2/pg93/pg_root/tbs_test目录下
（7）拷贝完成后需要解压：
来到￥PGDATA 目录下先解压：tar -xvf base.tar,解压成功如下：
解压好后删除 base.tar文件：rm -f base.tar，到对应的表空间里的目录去解压。
只有放在空间以外的表空间不会备份，放在$PGDATA 目录所有的文件都会备份。
解压对应表空间的目录:tar -xvf 66372.tar。
解压好后可以删掉：rm -f 66372.tar。
在$PGDATA 目录中，把需要的 recovery 文件进行拷贝。
.cd $PFDATA
来到$PGDATA 目录中
.cp/home/pg93/pgsql/share/recovery.conf.sample./
将 recovery 文件进行拷贝
并且重命名：mv recovery.conf.sample recovery.conf 然后再配置将#restore_commd=’ ‘变为restore_commd=’ ‘。
（8）还原命令：
假设数据库是从备份文件取出，还原命令就是把归档文件拷贝过来,找到归档文件:cd /ssd4/pg93/，ll,cd arch/,ll。
.归档文件：
通过配置命令去拷贝。因从此配置拷贝而来，因为是当天生成的文件，将其改为：
.restore_commd=’ cp/ssd4/pg93/arch/20131229/%f %p #e.g.
‘cp/mnt/server/archivedir/%f %p。
若文件跨天，命令需要重新输入，需要今天和明天，以上配置完成。
恢复到指定时间点：（若只需要恢复到最新的时间点则此步骤不用操作）。
①#recoverytargct name=‘ ’
在命令里创建一个还原点：-# select pg_create_restore_point(‘2013122901’)
#e.g. ‘daily backup 2011-01-26’--不支持 inclusivc 配置，因为它不需要从 abort 或 commit 判渐结束点。
. 命名的还原点：
如果数据库中有多个重复命名的还原点，遇到第一个则停止。
同时因为还原点的信息写在单独的 xlog 数据块中，不是一条 transactionrecord 块，所以也没有包含或不包含的概念,直接截止不需要判断 recovery_target_inclusive。
②#recovery_target_time =‘’ # e.g. ‘2004-07-1422:39:00 EST’时间格式使用当前系统配置的格式，或从时间函数获取：
recovery_target_time 恢复完后自动更名为.done,若，config 表示仍在还原。
基于时间还原点在同一个时间点，可能有多个事务 COMMIT/ABORT 同时提交。所以 recovery_target_inclusive 在这里起到的作用是:recovery_target_inclusive=true，则截止于这个时间点的第一个提交的事务后(包含这个时间点第一个遇到的提交/回滚的事务);recovery_target_inclusive=false，则截止于这个时间点提交的最后一个事务后(包括这个时间点提交/回滚的所有事务)。
如：要还原如下东八区时间，和自身的时间2004-7-14 22：39：00 EST不同。
③#recovery_target xid=‘ ’ #recovery_target inclusive=true
数据库还原的是事物的提交记录点，而不是触发点。
.默认支持3种还原点
.如果不设置还原点则不会停止恢复，一般用于建立流复制或容灾环境
https://blog.163.com/digoal@126/blog/ststic/163877040201303082942271/
XID 还原点
. 以 commit 或 abort 的 xid 到达为准（先提交先还原）。
.xid 按请求顺序分配，但是 abort 和 commit 点的 xid 在 XLOG 顺序中是无序的，只要从读取的 XLOGabort/commit信息到达指定 xid 就停止恢复。
命名的还原点
. 如果数据库中有多个重复命名的还原点,遇到第一个则停止.
.同时因为还原点的信息写在单独的 xlog 数据块中,不是一条 transaction record 块,所以也没有包含或不包含的概念,直接截止.
.不需要判断 recovery_target_inclusive .
时间还原点
.在同一个时间点,可能有多个事务 COMMIT/ABORT.所以 recovery_target_inclusive
在这里起到的作用是:截止于这个时间点的第一个提交的事务后(包含这个时间点第一个遇到的提交/回滚的事务);
.或者截止于这个时间点提交的最后一个事务后(包括这个时间点
提交/回滚的所有事务).
4、物理还原步骤：
（1）配置还原参数；
（2）模板文件；
.$PGHOME/share/recovery.conf.sample
.vi$PGDATA/recovery.conf
.mrestore_command =’cp/mnt/server/archivedir/%f%p’
.recovery_target_timeline = ‘latest’
（3）启动数据库
. pg_ctl start
（4）配置 hot_standby 参数，便于判断是否已经到达还原点(可选，仅做 PITR 时需要，一般都是恢复到最后)
（5）检查是否到达指定还原点.(可选,仅做 PITR 时需要,一般都是恢复到最后
（6）激活数据库
5、物理还原的原理：
通过数据库持续产生的变更日志作一个归档，而基础备份+归档日志不断去取归档将其写入，最后作回滚。
（1）写 WAL(或叫 XLOG)
（2）为了确保数据库在 recovery 的时候，可以恢复到一个一致的状态 sharedbuffer 中的脏数据页在 flush 到磁盘数据文件中之前。应该确保这个脏页的改变量已经 write through 到 XLOG 文件了
（3）如何确保先写 XLOG 再改变 DATA-PAGE 呢?PAGE 头信息里面包含了一个 pdIsn 位。用于记录 XLOG 写该 PAGE 信息的最后一个字节的下一个字节
（4）在写脏页到数据文件前只要确保大于或等于 pdIsn 的 XLOG 已经 writethrough 到磁盘了就行. lsn:
log sequence number---in practice, a WAL file location
（5）通过数据库持续产生的数据块变更，产生的WAL日志做一个归档，把基础备份和归档日志拿出来做数据的恢复。只要有备份就可以让数据库还原到任意一个时间点。
四、PostgreSQL 数据库逻辑备份
1、执行 pg_dump 命令，把数据导出：
（1）-Fc备份为二进制格式（custom），压缩存储，并且可被 pg_restmme 用于精细还原（指定还原对象）。
①利用以下内容进行备份：
②Restore 还原之前可以通过这个命令去解读备份的内容。
③删除数据库做还原：
# drop database digoal;
先连接一个已经存在的数据库
\l可查看创建起的库：
④检测：
④发现无法找到 database，需要自己创建数据库，可以直接连接到 digoal 库进行还原（在过程中出现的错误可能是因为之前已经存在的）。
（-a：只备份数据；-b：包括大对象也备份；-c：在创建之前删掉数据库；-C：创建数据库也备份；-n：备份schema；-s:只备份 schema，不备份数据；-S：还原时指定超级用户；-l：输出备份清单；-g：globals-only 只备份全局对象；）
（2）-Fp备份为文本,大库不推荐.
2、执行 pg_dumpall 命令
（1）可以备份全局元数据对象,例如用户密码,数据库，表空间.
（2）pg_dumpall 只支持文本格式.
3、COPY 命令,在数据库中执行 COPY,用于 SQL 子集或表的备份,表的还原。
表的还原代码如下：
. -# copy <select *from abc>to stdout
.-# copy <select *from pg_class>to stdout
stdout 表示标准输出。
备份文件做一个指定输出即可：to ‘/home/pg93/pg_class.bak’;
由此可将其备份到指定的/home/pg93/pg_class.bak 文件中。
六、数据库逻辑还原
1、二进制格式的备份只能使用 pgrestore 来还原，可以指定还原的表编辑 TOC 文件定制还原的顺序、表索引等。
.TOC 文件：
（1）分号开头表示这行被注释掉了
（2）TOC 文件中 entry 的意思截取自源代码：scr/bin/pg_backup_archiver.c
输入命令：
查看 TOC 源码内容：
2878, 1262 16386 DATABASE- digoal postgres 该行2878 对应 dumpld。
.1262 对应 catalogld.tableoid。
.16386 对应 catalogld.oid 。
.DATABASE 对应 desc。
.-对应te->namespace?te>namespace:"_" 。
. digoal 对应 tag。
. postgres 对应 owner。
（3）通过调整顺序和添加注释可以达到定制化还原的目的，调整顺序时需要注意依赖关系，如创建 plpgsql 的子句必须在创建函数前面.创建表必须在创建这个表的约束和索引.触发器等前面
（4）下面我们调整一下顺序.把 user_info_4表创建提前到 userinfo 前面：（把不需要恢复的表注释掉）
179: 1259 25163 TABLE digoal user_info_4 digoal
175: 1259 25139 TABLE digoal user_info_O digoal
176: 1259 25145 TABLE digoal user_infoat digoal
177: 1259 25151 TABLE digoal user_info_2 digoal
178: 1259 25157 TABLE digoel user_info 3 "digoal
（5）利用这个 TOC 文件还原看看顺序是否变更，pg_restore -Fc-L J digoal list-c-s-h 127.0.0.1-U postgres /digoa l.dmp 截取一段还原日志如下，反映出来这个顺序调整已经 OK 了。
（6）自行编辑 TOC 文件还原举例：
①创建数据库后，不想还原 iso_test，则：
②把文件输出，并修改 TOC 文件：
Ios_test 可查看 Toc 文件：
③将相关表和表数据注释（不想还原某行数据，在行开头加；该行就会注释掉了）
通过-L:list-file 指定通过 list 文件还原；
2、文本格式的备份还原,直接使用用户连接到对应的数据库执行备份文本即可，例如 psqldbname-f bak.sql。
以上就是数据库还原的全部内容。
