Postgresql备份和恢复之SQL转储

备份的意义在于发生意外时，具备恢复和还原的能力，及时将意外导致的损失降到最低。Postgresql的备份还原主要有：SQL转储、文件系统级备份和连续归档和时间点恢复（PITR）这三种方式。今天主要介绍的是postgresql基础备份恢复方式-SQL转储。

PostgreSQL自带的内置备份工具叫做pg_dump。这个工具是通过一系列的SQL语句读取某个指定的数据库并复制其中的内容，以它作为快照并用于日后的数据恢复。该工具的基本用法是：

pg_dumpdbname> dumpfile

pg_dump是将结果写入标准输出文件中。pg_dump可以用其他格式创建文件以支持并行和细粒度的对象恢复控制。pg_dump是一个普通的PG客户端应用，这就意味着你可以在任何可以访问该数据库的远端主机上进行备份工作。

在使用pg_dump工具进行备份之前，首先需要确保执行用户具有登录到服务器并访问将要备份的数据库或表的权限。可以通过使用psql，进行服务器登录。在客户端中输入主机名（-h），用户名（-u）和密码（-p），以及数据库名，然后就可以校验备份用户是否被授权访问。

使用pg_dump非常的简单——只需要在命令提示符后面输入将要导出的数据库名就可以进行备份工作了，如下例所示（根据您自己的安装路径更改PostgreSQL路径）：

[postgres@pgsql1~]$ /db/pgsql/bin/pg_ctl -D -h localhost -U demo(用户名)demodb(数据库名称)> demodb.sql

通过上面的命令就会创建一个名为demodb.sql的文件，文件中记录了用于恢复数据库的SQL命令。

和任何其他PostgreSQL客户端应用一样，pg_dump默认使用与当前操作系统用户名同名的数据库用户名进行连接。要使用其他名字，要么声明-U选项，要么设置环境变量PGUSER。请注意pg_dump的连接也要通过客户认证机制。

pg_dump对于其他备份方法的一个重要优势是，pg_dump的输出可以很容易的在新版本的PostgreSQL中载入，而文件级备份和连续归档都限制于服务器架构和版本。pg_dump也是唯一可以将一个数据库传送到一个不同机器架构上的方法，例如：从一个32位服务器到一个64位服务器。

由于pg_dump创建的备份在内部是一致的，SQL转储文件中是pg_dump开始运行时刻的数据库快照，且在pg_dump运行过程中发生的更新将不会被转储。因此，pg_dump工作的时候并不阻塞其他进程对数据库的操作。（但是会阻塞那些需要排它锁的操作，比如ALTERTABLE等）。

pg_dump生成的文本文件可以由psql程序读取。从转储中恢复的常用命令是：

psqldbname< dumpfile

其中dumpfile就是pg_dump命令的输出文件，也就是之前导出的demodb.sql文件。这条命令操作之前需要有待导入的数据库demodb。因此，必须在执行psql前已经创建demodb（例如，用命令createdb-Ttemplate0 demodb）。psql支持类似pg_dump的选项用以指定要连接的数据库服务器和要使用的用户名。pg_dump产生的转储是相对于template0。这意味着在template1中加入的任何语言、过程等都会被pg_dump转储。结果是，如果在恢复时使用的是一个自定义的template1，你必须从template0创建一个空的数据库。

在开始恢复之前，SQL转储库中对象的拥有者以及在其上被授予了权限的用户必须已经存在。如果不存在，那么恢复过程将无法将对象创建成具有原来的所属关系以及权限。

默认情况下，psql脚本在遇到一个SQL错误后会继续执行。也可以在遇到一个SQL错误后让psql退出，那么可以设置ON_ERROR_STOP变量来运行psql，这将使psql在遇到SQL错误后退出并返回状态3：

psql--set ON_ERROR_STOP=on dbname < infile

pg_dump每次只转储一个数据库，而且它不会转储关于角色或表空间（因为它们是集簇范围的）的信息。为了支持方便地转储一个数据库集簇的全部内容，提供了pg_dumpall程序。pg_dumpall备份一个给定集簇中的每一个数据库，并且也保留了集簇范围的数据，如角色和表空间定义。该命令的基本用法是：

pg_dumpall> dumpfile

转储的结果可以使用psql恢复：

psql-f dumpfilepostgres

在恢复一个pg_dumpall转储时常常需要具有数据库超级用户访问权限，因为它需要恢复角色和表空间信息。如果在使用表空间，需要确保转储中的表空间路径适合于新的安装。

pg_dumpall工作时会发出命令重新创建角色、表空间和空数据库，接着为每一个数据库pg_dump。这意味着每个数据库自身是一致的，但是不同数据库的快照并不同步。

集簇范围的数据可以使用pg_dumpall的--globals-only选项来多带带转储。如果在单个数据库上运行pg_dump命令，上述做法对于完全备份整个集簇是必需的。

在一些具有最大文件尺寸限制的操作系统上创建大型的pg_dump输出文件可能会出现问题。pg_dump可以写出到标准输出，因此操作时可以使用标准Unix工具来处理这种潜在的问题。有几种可能的方法：

使用压缩转储

可以使用常用的压缩程序，例如gzip：

pg_dumpdbname| gzip > filename.gz

恢复：

gunzip-c filename.gz| psql dbname

或者：

catfilename.gz| gunzip | psql dbname

使用split

split命令允许你将输出分割成较小的文件以便能够适应底层文件系统的尺寸要求。例如，让每一块的大小为1兆字节：

pg_dumpdbname| split -b 1m - filename

恢复：

catfilename*| psql dbname

使用pg_dump的自定义转储格式

如果PostgreSQL所在的系统上安装了zlib压缩库，自定义转储格式将在写出数据到输出文件时对其压缩。这将产生和使用gzip时差不多大小的转储文件，但是这种方式的一个优势是其中的表可以被有选择地恢复。下面的命令使用自定义转储格式来转储一个数据库：

pg_dump-Fc dbname> filename

自定义格式的转储不是psql的脚本，只能通过pg_restore恢复，例如：

pg_restore-d dbnamefilename

对于非常大型的数据库，你可能需要将split配合其他两种方法之一进行使用。

使用pg_dump的并行转储特性

为了加快转储一个大型数据库的速度，可以使用pg_dump的并行模式。它将同时转储多个表。可以使用-j参数控制并行度。并行转储只支持“目录”归档格式。

pg_dump-j num-F d -f out.dirdbname

可以使用pg_restore-j来以并行方式恢复一个转储。它只能适合于“自定义”归档或者“目录”归档，这个归档不一定由pg_dump-j创建。