PostgreSql备份和恢复

有三种不同的备份方法：

• SQL dump
• 文件系统级备份（File system level backup）
• 连续归档（Continuous archiving）

每种都有优势和劣势。

 

一、SQL Dump

dump方法是生成含有SQL命令的文本文件，当反馈回服务器时，将顺序执行dump中的命令。postgreSql使用pg_dump工具，基础用例是：

pg_dump dbname > outfile

这个命令可以在任意可以连接数据库的远程机器上运行，但他需要读表的权限，所以大多数是用superuser用户运行这个命令。

连接指定的数据库可以使用-h host和-p port命令选项。默认的host是local host或由PGHOST环境变量指定。

使用-U选项设置连接数据库的用户。

pg_dump的输出文件可以被更高版本的PostgreSql读取，它也是唯一可以在不同系统间（比如：32位->64位）转移数据的方法。

pg_dump不阻塞数据库的运行。

 

1、恢复

pg_dump生成的文件由psql读取，一般命令是：

psql dbname < infile

当infile是由pg_dump命令生成的，dbname不会被命令创建，所以在执行psql前需要手动创建表。（如：createdb -T template0 dbname）

在执行恢复前，有适当权限的用户必须存在。

默认情况下，psql遇到SQL错误会继续执行。你可以使用ON_ERROR_STOP变量使psql遇到SQL错误时退出，退出状态码是3。

psql --set ON_ERROR_STOP=on dbname < infile

如果你想要么全执行，要么全会滚，可以使用-1或--single-transaction选项。

 

也可以直接从一个服务器备份到另一个服务器：

pg_dump -h host1 dbname | psql -h host2 dbname

重要提示：pg_dump生成的转储文件是相对template0中。这意味着，任何语言，程序等通过template1的加入也将经由pg_dump转储。因此，还原时，如果您使用的是客户化的template1，必须从template0中创建空数据库，如上面的例子。

 

在还原备份后，明智的做法是在每个数据库上运行ANALYZE，以便查询优化器可以使用有效的统计数据。

 

2、使用pg_dumpall

pg_dump一次转储一个数据库，并且不转储用户角色和表空间。为了方便的转储数据库集群的全部内容，可以使用pg_dumpall

pg_dumpall > outfile

 恢复是：

psql -f infile postgres

恢复pg_dumpall的转储文件需要superuser用户，另外也要确认表空间的路径在新数据库中可用。

pg_dumpall使用emitting命令重建角色、表空间和空数据库，然后对每个数据库使用pg_dump。这意味着虽然每个数据库内会保持一致，但不同数据库的快照可能不完全同步。

 

3、处理大数据库

一些操作系统有文件大小限制。有几种方法可以处理：

使用压缩

pg_dump dbname | gzip > filename.gz

 恢复：

gunzip -c filename.gz | psql dbname

 或者：

cat filename.gz | gunzip | psql dbname

 使用拆分。可以设置拆分的文件大小：

pg_dump dbname | split -b 1m - filename

 恢复：

cat filename* | psql dbname

使用客户化的转储格式。如果系统有zlib压缩库，输出的客户化格式可以被压缩，压缩文件比gzip更小，它有一个优点，就是可以选择性的恢复某表。

pg_dump -Fc dbname > filename

 客户化的转储文件不能使用psql恢复，要使用pg_restore：

pg_restore -d dbname filename

使用pg_dump的并行转储。要加快大数据库的转储，可以使用pg_dump的并行模式，这可以同时转储多个表。你可以通过-j参数控制并行深度。并行转储只支持目录存储格式。

pg_dump -j num -F d -f out.dir dbname

你可以使用pg_restore -j来并行恢复数据。

二、文件系统级别备份

还有一个备份的策略是直接拷贝PostgreSQL的存储文件，可以用如下命令：

tar -cf backup.tar /usr/local/pgsql/data

这种方法有两个弱点，但它并不逊色与pg_dump：

1.数据库服务必须关闭，才能得到有用的备份。一个折中的办法是阻止所有连接。在恢复数据前也要关闭服务。

2.如果你已经查看了数据库文件系统的目录结构，你可能想尝试备份和恢复某个特定的表或库。这是不行的，因为没有提交日志文件，这些目录中的信息是不能用的，pg_clog/*包含所有事务的提交状态。一个表文件只有和这些提交状态信息一起才能使用。当然只恢复一个表文件和关联的pg_clog数据也是不行的，因为数据库的其他表都不可用了。所以文件系统备份必须整体备份数据库。

如果你的系统支持，也可以使用文件系统的“一致性快照”进行备份。

要注意的是，文件系统备份通常比pg_dump大。但是文件系统备份会快一些。

 

三、连续归档和Point-in-time恢复(PITR)

在任何时候，PostgreSql在pg_xlog/目录中维护一个写日志(WAL)。日志文件记录了数据库的每次修改。这个日志的主要目的是崩溃安全：如果系统崩溃了，数据库可以通过从上次的检查点“回放”日志来恢复数据库。然而我们可以用日志做备份的第三种策略。你可以混合一个文件系统级别的备份和WAL文件备份。如果需要恢复，我们先恢复文件系统备份，再回放WAL的内容，使数据库恢复到当前状态。这种方法比前两种更复杂，但它也有一些优势：

• 我们不需要一个完全一致的文件系统备份起始点。在备份中的任何内部一致时间点，都可以正确回放。（这和系统崩溃后恢复是差不多的）所以我们不需要一个文件系统快照功能，只要tar压缩或类似的归档工具。
• 既然我们可以整合一个无限长的WAL文件序列进行回放，所以持续备份可以通过简单的连续归档WAL文件实现。对于大型数据库，频繁的完全备份是很不方便的。
• 我们不需要回放整个WAL文件。我们可以在任何时间点停止回放，可以得到那个时间点的数据库快照。因此，这种技术支持点即时恢复（point-in-time recovery）：基于你的基础备份，可以恢复数据库到任何时间状态。
• 如果我们连续提供WAL文件序列给另一台机器，让这台机器读取相同的备份文件，那么我们就有了一个热备份系统：在任何时候我们可以使用第二台机器，它有和第一台几乎一样（最近时间点备份）的数据。

注意：pg_dump和pg_dumpall不能用于持续备份方案，它们的转储文件不包含逻辑和必要的信息可以使用WAL回放。

 

普通文件备份技术只支持恢复整个数据库集，不能只恢复一个子系统。此外它需要大量的归档存储：基础备份可能很大，一个繁忙的系统会产生很多兆的WAL文件，这些都需要传输。然而这种方式仍然是高可用性的首选备份技术。

 

要使用连续归档成功恢复（也被成为“在线备份”），你需要从最后备份文件时间点后的连续序列的WAL归档文件。所以在基础备份之前你需要设置和测试WAL归档文件。因此我们首先讨论WAL归档机制。

 

1、设置WAL归档

理论上，一个运行的PostgreSql系统会产生无限长的WAL记录序列。系统雾里找份WAL序列，一般是16M一份（可以在编译PostgreSql是改变该值）。拆分的片段文件用反应位置的数字命名。当不适用WAL归档时，系统一般创建几个片段文件并通过重命名“回收”他们。系统假设那些包含检查点之前内容片段文件不会被关注，所以可以回收。

当使用WAL归档时，我们需要捕获每个片段文件被填满的事件，保存这个片段文件到其他位置以防止系统回收。根据不同的应用和硬件可以有很多种方法实现“保存片段文件到其他位置”：你可以将片段文件复制到NFS挂载的另一台机器上、将文件写入一个磁带驱动器或刻录到CD上。为了数据库管理员的灵活性，关于如何保存归档，PostgreSql没有做任何假定。相反，PostgreSql让管理员指定shell命令去执行拷贝片段文件的操作。命令可以是简单的cp命令或者一个复杂的shell脚本，这都取决于你。

 

要启动WAL归档，设置参数wal_level为archive（或hot_standby），archive_mode为on，并指定shell命令给参数archive_command。这些参数都在postgresql.conf文件中。在archive_command中，把%P替换为路径名（路径名是当前工作目录，即数据库集群的data目录），把%f替换为文件名，%%转移%。最简单有用的命令是这样：

archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix
archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"'  # Windows

这将拷贝WAL归档片段文件到/mnt/server/archivedir目录。（这是一个例子，并不推荐，不是在所有平台都能正常工作。）

%p和%f被替换后，命令看起来是这个样子：

test ! -f /mnt/server/archivedir/00000001000000A900000065 && cp pg_xlog/00000001000000A900000065 /mnt/server/archivedir/00000001000000A900000065

针对每个新WAL片段文件生成一个类似的命令。

归档命令（archive command）在PostgreSql服务器的所属用户下被执行。WAL归档文件包含了你数据库的所有信息，你需要确认你的信息不被泄露，保存归档目录不要有组或全局读权限。

归档命令只有成功时才返回0并退出。PostgreSql得到0，认为文件被成功归档，系统记录并回收改文件。PostgreSql如果得到一个非零状态，则认为文件未被归档，她会再次尝试知道成功归档。

归档命令一般应设计成拒绝覆盖任何预先存在的归档文件。这可以防止系统管理员的错误导致归档的完整性被破坏（如发送两个不同服务器的WAL文件到一个归档目录中）。

明智的做法是测试你的归档命令确保不会覆盖已存在的归档文件，并返回非空值。上面例子中Unix的命令包含一个测试（test）步骤。在一些Unix平台上，cp命令有一个-i的参数，可以防止覆盖，但是你不应该依靠这个返回状态值（一般情况下GNU cp的-i在目标文件已存在时会返回0）。

当你设计归档时，需要考虑如果归档命令反复失败会发生什么，因为有些方面需要操作员的干预或存档空间已用完的情况。

比如如果你将归档文件存入磁带，当磁带被写满，并没有更换的时候。你应该确保任何错误条件或需要人为操作的时候给出佘当的提示，以便可以快速解决问题。pg_xlog目录里会继续写入WAL片段文件，直到问题被解决。（如果pg_xlog目录被写满，PostgreSql将做恐慌关机(PANIC shutdown)，没有提交的事务都将被丢失，数据库将保持脱机状态直到你释放一些空间。）

归档命令的速度并不重要，只要它能赶上服务器产生WAL文件的平均速度就可以。即使归档进程落后一点也能正常工作。如果归档明显落后，将导致在发生灾难时有大量数据无法恢复，并且pg_xlog目录中会存在大量的未归档文件片段，可能导致空间不足。你应该监视你的归档进程，已确保它按你的意图工作。在写归档命令时，文件名可以是最多64位包含任何ASCII字母，数字和点的组合。

注意，WAL归档可以还原数据库中的任何修改，但不会还原设置文件的修改（通常是postgresql.conf、pg_hba.conf和pg_ident.conf文件），因为这些都是手动编辑而不是通过SQL操作。

归档命令只在完成的WAL片段文件执行。因此，如果你的服务产生很少的WAL流量（或有空闲时间），有可能同步会有较长的延时。可以通过archive_timeout强制服务器切换到新的WAL片段文件。注意，被强制切换的WAL文件的大小和正常WAL文件一样大。因此设置archive_timeout很短是不明智的，他会占用你的存储空间。此外，如果你想确保刚完成的事务被归档，可以手动使用pg_switch_xlog强制切换片段文件。

当wal_level设置成最小，一些SQL命令被优化已防止WAL记录。如果在执行这些SQL命令时归档或复制数据流开启，WAL不包含足够的信息用于归档恢复。（崩溃恢复不受影响）。处于这个原因，wal_level只能在服务器启动时改变。但是，archive_command可以通过重加载配置文件来改变。如果你想临时停止归档，一种方法是设置archive_command为空字符串（''）。这导致WAL文件积累在pg_xlog目录中，直到archive_command重新建立。

 

2、建立初始备份

最简单的基础备份方法是使用pg_basebackup工具。它可以生成普通文件或tar压缩文件的基础备份。如果需要比pg_basebackup更灵活，可以使用低级别API。

生成基础备份需要大量的时间，这不必在意。但是，如果你运行服务时禁止了full_page_writes，你可能注意到在运行备份时性能下降了。

要使用备份，你需要保留生成基础备份期间和以后的所有WAL片段文件。为了帮助实现这个，基础备份进程创建备份历史文件保存到WAL归档区域。文件的命名是在第一个需要的WAL片段文件后增加一些内容。比如：如果开始的WAL文件是0000000100001234000055CD，那么备份历史文件的命名会像这样0000000100001234000055CD.007C9330.backup。（文件名的第二部分代表的WAL文件内的确切位置，通常可忽略不计。）一旦你有个安全的文件系统备份和WAL片段文件，所有之前的WAL片段在恢复数据时都不再需要。但是你应该考虑保留一些备份已确保你可以恢复数据。

备份历史文件只是一个小的文本文件。它包含了你给pg_basebackup的标签字符串，以及备份的开始和结束时间，备份的WAL片段。如果你使用唯一的标签标示转储文件，那么归档历史文件足以告诉你哪些转储文件用于恢复。

因为你要保留从基础备份之后的所有WAL归档文件，所以要适当选择两个基础备份的时间间隔。你也需要考虑准备花多长时间经行恢复，恢复时系统需要回放自最近一次基础备份以后的所有WAL片段，这可能需要一段时间。

 

3、使用低级别API制作基础备份

使用低级别API比pg_basebackup有较多的步骤，并且必须按步骤执行。

1）、确认WAL归档设为可用（enabled）并正常工作。

2）、用superuser登陆数据库，并实施如下命令：

SELECT pg_start_backup('label');

label是你要标示此次备份操作的唯一字符串。（一个好的做法是使用您想要用的备份转储文件的完整路径。）pg_start_backup创建一个备份标签文件，名为backup_label，在数据库集群的目录里有备份信息，包括开始时间和标签字符串。这些文件的完整性很重要，你需要用他们做恢复。

连接数据库集群中那个数据库并不重要。你可以忽略函数返回值，但如果报告失败，需要在执行前处理。

默认情况下，pg_start_backup可能需要很长的时间才能完成。这通常是你想要的，因为它最大限度地减少对查询处理的影响。如果你想尽快开始备份，使用方法：

SELECT pg_start_backup('label', true);

这样强制检查点尽快完成。

3）、执行备份，使用任何方便的文件系统备份工具，比如tar或cpio（不要使用pg_dump或者pg_dumpall）。做此步骤时不需要也不应该停止数据库服务。

4）、再用superuser连接数据库，并执行如下命令：

SELECT pg_stop_backup();

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

5）、在生成备份期间，一旦WAL片段被归档，你就完了。确定pg_stop_backup的结果是完整备份的最后的一个片段。如果archive_mode是enabled，pg_stop_backup会等当前的片段被归档后才返回。当你定义了archive_command后归档会自动执行。在大多数情况下，这发生的很快，但还是建议你监视归档系统，确保没有延迟。如果因为归档命令失败，导致归档重复尝试，pg_stop_backup也会一直等待。如果你希望限定执行pg_stop_backup，可以设置statement_timeout。

 

文件系统备份工具在拷贝正被修改的文件时发出警告或错误。当制作基础备份时，这种情况很常见而且并不是一个错误。但是你需要确认并从真正的错误区分开。幸运的是，GNU的tar 1.16及更高的版本，这种情况返回1，其他错误返回2.在GNU的tar 1.23及更高版本，你可以使用警告选项 --warning=no-file-changed --warning=no-file-removed 以隐藏相关警告信息。

确认你的备份转储包括了数据库集群目录下的所有文件（例如：/usr/local/pgsql/data）。如果你使用的表空间不在这个目录内，注意把他们也包括在内（并确保你的备份转储归档符号链接的链接，否则恢复将破坏你的表空间）。

你可以省略pg_xlog子目录中的备份转储文件。这样会降低恢复的风险。

值得注意的是，pg_start_backup创建的名字是备份标签的文件会被pg_stop_backup删除掉。这个文件会归档到你的备份转储文件中。

服务停止时，一可疑制造一个备份。这这种情况下，你显然不能使用pg_start_backup或pg_stop_backup。

 

4、使用连续备份存档恢复

从备份中恢复，下面是步骤：

1）、停止服务。

2）、拷贝整个数据库集目录和任何表空间到一个临时位置。

3）、删除数据库集目录内的所有文件和子目录，删除表空间目录。

4）、从文件系统备份还原数据库。确定你恢复到正确的用户所有权下（数据库系统用户，不是root）和权限。如果你使用表空间，确认在pg_tblspc链接被正确还原。

5）、删除pg_xlog里的所有文件，这些文件来自文件系统备份，可能已过时。如果你在文件备份时没有备份pg_xlog，那需要重新创建，并给与适当的权限。

6）、如果第2步中有未归档的wal片段文件，拷贝这些文件到pg_xlog目录中。

7）、在数据库集目录里创建恢复命令文件recovery.conf。（参考官方文档26章http://www.postgresql.org/docs/9.3/static/recovery-config.html）你可能需要临时修改pg_hba.conf以阻止客户端的连接，直到你确定恢复成功。

8）、启动服务。服务进入恢复模式，读取需要的WAL归档文件。因为外部错误而使恢复终止，可以通过重启继续进行恢复。恢复过程完成后，服务器会重命名recovery.conf成recovery.done（防止再进入恢复模式），然后开始正常的数据库操作。

9）、检查数据库的内容，以确保已恢复到理想状态。如果没有返回第1步，如果一些正常，你需要修改pg_hba.conf回到正常允许客户连接。

 

以上最关键的部分就是建立描述如何恢复的配置文件。你可以用recovery.conf.sample（通常位于share目录里）为原型。在recovery.conf中必须指定的是restore_command，它告诉PostgreSql如何检索已归档的WAL片段文件。这是一个shell命令字符串。它可以包含%f，将被替换日志文件的名称，以及%p，将被替换日志文件路径。（路径名是当前工作目录，比如：数据库集目录）用%%转移%。最简单的命令像这样：

restore_command = 'cp /mnt/server/archivedir/%f %p'

这将从目录/mnt/server/archivedir拷贝之前的WAL归档片段文件。在pg_xlog目录里没有找到已归档的WAL片段文件，允许使用最近的未归档的片段文件。但是已归档的文件优先于未归档文件。通常情况下，恢复会使用所有可用的WAL片段，直到当前时间点（或尽可能接近给定的可用WAL片段）。因此，一般恢复会以“文件未找到(file not found)”结束，这取决于你使用的restore_command命令。你可能会在恢复一开始看到一条错误消息，类似00000001.history。这是正常的。

如果你要恢复到某个之前的时间点，只需要在recovery.conf中指定结束点。可以通过日期/时间来指定，被称为“恢复目标”。恢复目标只能在基础备份之后，基础备份中和以前的时间不能作为恢复目标。

如果恢复发现损坏的WAL数据，恢复将停止在这一点上，服务器将无法启动。在这种情况下，恢复过程可以从一开始重新运行，指定恢复目标在错误点之前。之后可以分析错误原因，如果是外部原因，比如系统损坏，WAL文件不可读等，可以重启服务，它会从失败点从新开始。

 

5、时间线

将数据库恢复到以前的时间点的能力产生了一些复杂的是类似于关于时间旅行和平行宇宙的科幻故事。例如，在数据库的原始历史中，假设在星期二下午5:15你删掉了一个表，但是直到星期三中午才意识到这是错误的。你拿出的的备份，恢复到星期二下午5:14,并启动和运行。在这段数据库宇宙历史中，你从未删除表。但是，假设你以后意识到这不是个好主意，并想反悔原来的星期三早上某个时刻。你将无法做到这点，如果你的数据库已经运行。服务已经重写了一部分WAL片段，因此为了避免这种情况，你需要区分各个历史版本的WAL记录。

为了解决这个问题，PostgreSql有时间线的概念。每当存档恢复完成后，会创建一个新的唯一的时间线去标示一系列回复后的WAL文件。时间线编号是WAL片段文件名字的一部分，所以新时间线的WAL文件不会覆盖旧的时间线的文件。实际中可能会产生多个不同的时间线归档。

每创建一个时间线，PostgreSql创建一个“时间线历史(timeline history)”文件，显示时间线何时从何分支。这些历史文件使得系统可以从多时间线归档中选择正确的WAL文件进行恢复。因此它也在WAL归档区域内，就像WAL片段文件。历史文件是很小的文本文件，如果你愿意，你可以往里添加注释记录为什么创建此时间线等。恢复的默认方式是沿着和基础本分相同的时间线进行。如果你想恢复到其他分支，你需要在recovery.conf中指定时间表的ID。你不能恢复到基础备份之前的时间线分支。

 

6、提示和示例

1）独立的热备份

使用PostgreSql的备份特性，可以建立一个热备份。这些备份不能用于某时间点恢复，但是一般比pg_dump的备份和恢复快很多。（它的备份文件也比pg_dump大很多）

如果使用基础备份，最简单的方法是使用pg_basebackup工具制造独立热备份。如果你调用它时包括-X参数，备份使用的所有事务日志都将被自动包含在备份中，所以在恢复时不需要什么特别的操作。

如果你需要更灵活的备份，可以使用较底层的过程。设置wal_level为archive（或hot_standby），archive_mode为on并设置一个archive_command来执行归档。例如：

archive_command = 'test ! -f /var/lib/pgsql/backup_in_progress || (test ! -f /var/lib/pgsql/archive/%f && cp %p /var/lib/pgsql/archive/%f)'

在/var/lib/pgsql/backup_in_progress存在时，命令执行归档，否则返回0退出（允许PostgreSql回收不需要的WAL文件）。

基于上述的准备，可以用类似下面的脚本进行备份：

touch /var/lib/pgsql/backup_in_progress
psql -c "select pg_start_backup('hot_backup');"
tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
psql -c "select pg_stop_backup();"
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/

首先创建交换文件/var/lib/pgsql/backup_in_progress，备份结束后，删除交换文件。然后将WAL归档文件添加到备份中，使得基础备份和所有WAL文件放在同一个tar文件中。请记住在你的脚本中添加错误处理。

 

2）、压缩归档日志

如果归档文件的存储比较敏感，你可以使用gzip去压缩归档文件：

archive_command = 'gzip < %p > /var/lib/pgsql/archive/%f' 

在恢复的时候你需要gunzip：

restore_command = 'gunzip < /mnt/server/archivedir/%f > %p'

 

3）、archive_command脚本

很多人使用脚本定义archive_command，看起来像这样：

archive_command = 'local_backup_script.sh "%p" "%f"'

使用脚本比单个命令有优势，它可以更完善。

一般脚本应该具有一下功能点：

• 将数据复制到安全的异地数据存储。
• 批量处理WAL文件，每三小时转移一次，不是一次一个。
• 与其他备份和恢复软件的接口。
• 监控软件来报告错误的接口。

提示：当使用archive_command脚本时，可以开启logging_collector，这样脚本的输出也同将在数据库日志中记录，这有助于容易的查找脚本的错误。

 

注意事项

在这个版本中（9.3）持续归档技术有一些限制。这可能会在以后的版本中修复：

对哈希索引的操作不会体现在WAL记录中，所以回放不会更新这些索引。当恢复完成后，建议您手动REINDEX每个这样的索引。

在进行基础备份的期间，执行CREATE DATABASE，对应的模板数据库被修改，在恢复时这可能会传播到已被创建的数据库中。为了避免这个风险，最好在做基础备份时不要修改模板数据库。

CREATE TABLESPACE命令在WAL记录中包含绝对路径，回放时会创建同样绝对路径的表空间。这可能是不希望的，如果回放是在不同的机器上进行。即使是相同机器回放，也可能是危险的，回放会覆盖原来表空间的内容。为了皮面这种风险，最好的办法是用新的基础备份还原后，再创建或删除表空间。

还应当指出的是，缺省的WAL格式是相当笨重的，因为它包括许多磁盘页快照。这些网页的快照被设计用于支持崩溃恢复，因为我们可能需要修复部分写入磁盘的页面。根据您的系统硬件和软件，部分写入的风险可能足以忽略，在这种情况下，你可以使用full_page_writes参数关闭网页快照减少归档日志的总容积小。 关闭网页快照并不影响使用为PITR操作所记录的日志。