Postgresql学习笔记之——备份（SQL转储和文件系统级别的备份）

在Postgresql数据库中除了使用归档增量备份的方式，还有通过SQL转储和文件系统级别的备份方式，应用于日常备份操作。例如将Postgresql数据库中的某些表的数据迁移到其他的关系型数据库中。

一、SQL转储

SQL转储就是将数据对象通过工具输出到有SQL命令组成的文件中，Postgresql数据库提供了 pg_dump 和 pg_dumpall 工具进行SQL转储，这两个工具不会阻塞其他数据库请求。两个工具的使用方法类似，区别在于 pg_dump 只能转储单个数据库，如果需要转储数据库的全局对象，则使用 pg_dumpall 工具。

pg_dump 生成的备份文件可以是一个SQL 脚本或归档文件。SQL 脚本文件是一个纯文本格式的文件，它包含许多SQL命令，执行这些SQL命令可以重建该数据库，并将之恢复到保存成脚本时的状态。使用 psql 程序来执行这个SQL 脚本文件即可恢复数据，它们甚至可以在其他机器或其他硬件系统的机器上重建该数据库，对脚本进行一些修改后，还可以在非 Postgresql 的数据库上执行这个SQL脚本文件而重建备份的表。

重建数据库时，归档格式的备份文件必须和 pg_restore 一起使用，它允许 pg_restore 选择恢复哪些数据，甚至可以在恢复之前对需要恢复的条目重新排序。归档格式的备份文件也可以设计成能跨平台移植的。

pg_dump 可生成归档格式的备份文件，然后与 pg_restore 配合使用，从而提供一种灵活的备份和恢复机制。 pg_dump 可以将整个数据库备份到一个归档格式的备份文件中，而 pg_restore 则可从这个归档格式的备份文件中选择性的恢复部分表或数据库对象。

归档格式的备份文件分两种，最灵活的输出文件格式是 “custom” 自定义格式（使用命令项参数 -Fc 来执行），它允许对归档元素进行选取和重新排列，并且默认时是压缩的，另一种格式是 tar 格式（使用命令项参数 -Ft 来执行），这种格式的文件不是压缩的，并且加载时不能重排列，但是它也很灵活，可以用标准的 UNIX 下的tar 工具进行处理。通常使用 “custom” 自定义格式。

1.pg_dump、pg_restore 工具使用

pg_dump、pg_restore 工具参数可参考：https://blog.csdn.net/qq_32838955/article/details/105123502

运行 pg_dump 时，注意查看输出，是否有警告存在。

导出示例：

1.dump 数据库mydb 成一个 SQL 脚本文件：

$ pg_dump mydb > db.sql

2.加载SQL脚本文件到一个新的数据库newdb中：

$ psql -d newdb -f db.sql

3.dump 一个数据库到一个自定义格式的归档文件：

$ pg_dump -Fc mydb > db.dump

4.dump一个数据库到一个目录格式归档：

$ pg_dump -Fd mydb -f dumpdir

如果没有指定绝对路径，则会在当前目录下生成文件夹dumpdir，文件夹中时归档文件：

[postgres@local ~]$ ll
total 4K
drwx------ 2 postgres postgres 4.0K Mar 27 09:48 dumpdir

[postgres@local ~]$ ll dumpdir/*
-rw-rw-r-- 1 postgres postgres  100 Mar 27 09:48 dumpdir/3053.dat.gz
-rw-rw-r-- 1 postgres postgres 1.2K Mar 27 09:48 dumpdir/3054.dat.gz
-rw-rw-r-- 1 postgres postgres   25 Mar 27 09:48 dumpdir/3055.dat.gz
-rw-rw-r-- 1 postgres postgres   25 Mar 27 09:48 dumpdir/3056.dat.gz
-rw-rw-r-- 1 postgres postgres 4.0K Mar 27 09:48 dumpdir/toc.dat

从目录格式归档中恢复数据：

$ pg_restore -d newdb -Fd dumpdir/

5.dump一个数据库到一个目录格式归档，使用5个工作进程进行：

$ pg_dump -Fd mydb -j 5 -f dumpdir

6.加载（恢复）归档文件到指定的数据库newdb中：

$ pg_restore -d newdb db.dump

7.加载（恢复）归档文件是指定其中一个表到新的数据库newdb中：

$ pg_restore -d newdb -t tb_mydb_test db.dump

8.将归档文件重新加载到转储它的数据库中，并清除该数据库的相同名字的表，如果导入的表在目标数据库中没有，会有一些没有影响的错误提示要清除的表不存在:

$ pg_restore -d postgres --clean  db.dump

9.dump 数据库 mydb中的一张表：

$ pg_dump -t mytab mydb > db.sql

10.要转储名称以emp开头的所有表，除了名为employee_log的表:

$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

11.转储名称以东或西开始、以gsm结尾的所有模式，不包括模式名称中含有的test的模式:

$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

同样也可以使用正则表达式：

$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

12.转储除以ts_开头的表以外的所有数据库对象:

$ pg_dump -T 'ts_*' mydb > db.sql

要在-t和相关开关中指定大写或混合大小写名称，需要双引号括起名称;否则它将被折叠成小写(参见模式)。但是双引号对于shell来说是特殊的，所以它们必须依次被引号括起来。因此，要转储具有混合大小写名称的单个表，您需要类似的操作

$ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql

13.从 192.168.10.11服务器上备份数据库 mydb，然后恢复到 192.168.20.20 服务器上：

pg_dump -h 192.168.10.11 -U postgres mydb -Fc >mydb.dmp
pg_restore -h 192.168.20.20 -U postgres -d newdb mydb.dmp

一般导出格式如：sql、dmp可以通过 psql 工具识别。如果是自定义格式，或者归档目录方式，非文本的方式，可以通过pg_restore恢复。

二、文件系统级别备份

最简单的物理备份就是冷备份，就是讲数据库停掉，然后把数据库的PGDATA 目录拷贝下来就可以了。Postgresql把与数据库实例有关的配置文件和数据文件都放在PGDATA目录下。

还有一种物理备份是不停止数据库的备份方式。称为热备份或在线备份。在Postgresql中通常的热备份方式有两种：

1.使用数据库的PITR方法进行热备份。搭建 hot standby 数据库。

2.使用文件系统或块设备级别的快照功能完成本分。

本次介绍一下使用快照功能的备份方式。在Linux系统下最简单的方法是使用 LVM 的快照功能，这要求数据库建立在 LVM 上。在 Solaris 下可以使用 ZFS 的快照功能。用户可以在快照上直接启动数据库或把数据库从快照所在的文件系统中备份出来。

LVM对数据库做在线备份

1）数据库时安装在Linux的一个LV逻辑卷上，然后对整个逻辑卷创建快照：
（创建快照前在数据库中做一下checkpoint）

lvcreate -s -n snap20200327 /dev/VgGroup/lv_data -L 500M

-s ：表示创建快照
/dev/VgGroup/lv_data ： 创建快照的逻辑卷
-L ：表示快照使用的空间大小（根据实际使用大小判断，否则快照失效）

创建的快照可以用命令 lvs 查看。

2）创建的快照可以像系统镜像文件一样挂载：

mount -o nouuid /dev/VgGroup/snap20200327  -t xfs /snapdata

其中参数 nouuid是必须要加的，因为快照的UUID与实际文件系统一致，加此参数不与现有逻辑卷冲突。

3）快照挂载后，进入挂载点，可以将里面的数据库目录打包：

cd /snapdata
tar -cvf /data/backup-snap20200327.tar.gz    pgsql_data

创建完成后就可以卸载掉快照文件并删除了。

4）压缩的tar包可以放到其他服务器上解压，并通过 postgresql 软件进行启动：

tar -xvf backup-snap20200327.tar.gz

将解压后的文件也就是 PGDATA 放到与原目录相同目录结构上，就可以直接启动：

$ pg_ctl start -D /data/pgsql_data

注意除了目录结构相同，新服务器上的postgresql数据库版本也要保持一致。最好不要直接在本地打开快照的数据库，因为快照中记录的与实际运行的一样的数据库服务进程，如果自定义了表空间，都会同时指向相同的表空间路径，会与现有的数据库服务冲突。