PostgreSQL 备份和恢复

备份和恢复
有三种不同的基本方法来备份PostgreSQL数据
SQL转储
文件系统级备份File system level backup
连续归档

1. SQL转储

pg_dump dbname > outfile
-n schema
-t table
-bash-4.2$ pg_dump --help
pg_dump创建的备份在内部是一致的,在执行命令的时候对数据库的快照，运行过程中的数据不会被备份，备份的时候堵塞数据库的操作
（但是会阻塞那些需要排它锁的操作，比如大部分形式的ALTERTABLE）
-bash-4.2$ pg_dump hq >/data/pg_backup/pgdg_hq`date +'%Y%m%d'`.sql
[root@mysqlhq pg_backup]# ll
total 4
-rw-r--r-- 1 postgres postgres 852 Nov 29 15:33 pgdg_hq20181129.sql

1.1. 从转储中恢复

pg_dump生成的文本文件可以由psql程序读取。 从转储中恢复的常用命令是：
psql dbname < infile //其中infile就是pg_dump命令的输出文件
这条命令不会创建数据库dbname，你必须在执行psql前自己从template0创建（例如，用命令createdb -T template0 dbname）。
默认情况下，psql脚本在遇到一个SQL错误后会继续执行。
psql --set ON_ERROR_STOP=on dbname < infile //psql在遇到SQL错误后退出并返回状态3：
向psql传递-1或--single-transaction命令行选项来指定,恢复要么完全完成要么完全回滚。
pg_dump和psql读写管道的能力使得直接从一个服务器转储一个数据库到另一个服务器成为可能，例如
pg_dump -h host1 dbname | psql -h host2 dbname
重要: pg_dump产生的转储是相对于template0。这意味着在template1中加入的任何语言、过程等都会被pg_dump转储。
结果是，如果在恢复时使用的是一个自定义的template1，你必须从template0创建一个空的数据库，正如上面的例子所示。
一旦完成恢复，在每个数据库上运行ANALYZE是明智的举动

1.2. 使用pg_dumpall

pg_dump每次只转储一个数据库，而且它不会转储关于角色或表空间（因为它们是集簇范围的）的信息。
pg_dumpall > outfile
-bash-4.2$ pg_dumpall >/data/pg_backup/pgall_`date +'%Y%m%d'`.sql
psql -f infile postgres
（实际上，你可以指定恢复到任何已有数据库名，但是如果你正在将转储载入到一个空集簇
中则通常要用（postgres）。在恢复一个pg_dumpall转储时常常需要具有数据库超级用户访
问权限，因为它需要恢复角色和表空间信息。如果你在使用表空间，请确保转储中的表空间
路径适合于新的安装。
pg_dumpall工作时会发出命令重新创建角色、表空间和空数据库，接着为每一个数据库pg_dump。这意味着每个数据库自身是一致的，但是不同数据库的快照并不同步。
[root@mysqlhq pg_backup]# head -n 100 pgall_20181129.sql
--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

--
-- Roles
--

CREATE ROLE dbuser;
ALTER ROLE dbuser WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md50e5ffb9d5f02a6b89e3c70343bdf19db';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
-- Database creation
--

CREATE DATABASE exampledb WITH TEMPLATE = template0 OWNER = dbuser;
REVOKE ALL ON DATABASE exampledb FROM PUBLIC;
REVOKE ALL ON DATABASE exampledb FROM dbuser;
GRANT ALL ON DATABASE exampledb TO dbuser;
GRANT CONNECT,TEMPORARY ON DATABASE exampledb TO PUBLIC;
CREATE DATABASE hq WITH TEMPLATE = template0 OWNER = dbuser;
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
REVOKE ALL ON DATABASE template1 FROM postgres;
GRANT ALL ON DATABASE template1 TO postgres;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;

1.3. 处理大型数据库

在一些具有最大文件尺寸限制的操作系统上创建大型的pg_dump输出文件可能会出现问题。
使用压缩转储。.
pg_dump dbname | gzip > filename.gz //对文件进行压缩
恢复：
gunzip -c filename.gz | psql dbname
或者：
cat filename.gz | gunzip | psql dbname
使用split。. split命令允许你将输出分割成较小的文件以便能够适应底层文件系统的尺寸要求。例如，让每一块的大小为1兆字节：
pg_dump dbname | split -b 1m - filename
恢复：
cat filename* | psql dbname
使用pg_dump的自定义转储格式。。如果PostgreSQL所在的系统上安装了zlib压缩库，自定义转储格式将在写出数据到输出文件时对其压缩。
这种方式的一个优势是其中的表可以被有选择地恢复。
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来以并行方式恢复一个转储。

2. 文件系统级别备份

tar -cf backup.tar /usr/local/pgsql/data
这种方法有两个限制，使得这种方法不实用，或者说至少比pg_dump方法差：
1. 为了得到一个可用的备份，数据库服务器必须被关闭。
2. 文件系统备份值适合于完整地备份或恢复整个数据库集簇。
另一种文件系统备份方法是创建一个数据目录的“一致快照”

3. 连续归档和时间点恢复（PITR）

在任何时间，PostgreSQL在数据集簇目录的pg_xlog/子目录下都保持有一个预写式日志（WAL）。
-bash-4.2$ ll
total 16384
-rw------- 1 postgres postgres 16777216 Nov 29 15:56 000000010000000000000001
drwx------ 2 postgres postgres 6 Sep 28 09:57 archive_status
-bash-4.2$ pwd
/var/lib/pgsql/data/pg_xlog
如果系统崩溃，可以“重放”从最后一次检查点以来的日志项来恢复数据库的一致性。
注意: pg_dump和pg_dumpall不会产生文件系统级别的备份，并且不能用于连续归档方案。这类
转储是逻辑的并且不包含足够的信息用于WAL重放。

3.1. 建立WAL归档

一个运行中的PostgreSQL系统产生一个无穷长的WAL记录序列。系统从物理上将这个序列划分成WAL 段文件，通常是每个16MB。
要启用WAL归档，需设置wal_level配置参数为archive或更高，设置archive_mode为on，并且使用archive_command配置参数指定一个shell命令。
# - Archiving -
#archive_mode = off # enables archiving; off, on, or always
# (change requires restart)
#archive_command = '' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
archive_command = ’test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f
归档命令将在运行PostgreSQL服务器的同一个用户的权限下执行。
当且仅当归档命令成功时，它才返回零退出。
在得到一个零值结果之后，PostgreSQL将假设该文件已经成功归档， 因此它稍后将被删除或者被新的数据覆盖。
归档命令通常应该被设计成拒绝覆盖已经存在的归档文件。
请注意尽管 WAL 归档允许你恢复任何对你的PostgreSQL数据库中数据所做的修改， 但它不会恢复对配置文件的
修改（即postgresql.conf、pg_hba.conf 和 pg_ident.conf），因为这些文件都是手工编辑的，而不是通过 SQL 操作来编辑的。

3.2. 制作一个基础备份

执行一次基础备份最简单的方法是使用pg_basebackup工具。它将会以普通文件或一个tar归
档的方式创建一个基础备份。

3.3. 使用低级API制作一个基础备份

1. 确保WAL归档被启用且正在工作。
2. 作为超级用户连接到数据库并发出以下命令：
SELECT pg_start_backup('label');
这里label是任何你希望用来唯一标识这个备份操作的字符串（一个好的习惯是使用你将要放置备份转储文件的完整路径）
要尽快开始备份，可使用：
SELECT pg_start_backup('label', true);这会使检查点尽可能快地被完成。
3. 使用任何方便的文件系统备份工具执行备份，例如tar或cpio（不是pg_dump或pg_dumpall）。在此期间，不需要也不值得停止正常的数据库操作。
4. 再次作为一个超级用户连接到数据库，并且发出以下命令
SELECT pg_stop_backup();
这将终止备份模式，并且执行一个自动切换到下一个WAL段。进行切换的原因是将在备份期间生成的最新WAL段文件安排为可归档。
5. 一旦备份期间活动的WAL段文件被归档，你的工作就完成了。

3.4. 使用一个连续归档备份进行恢复

需要从你的备份进行恢复。这里是其过程
1. 如果服务器仍在运行，停止它。
2. 如果你具有足够的空间，将整个集簇数据目录和表空间复制到一个临时位置，稍后你将用到它们。注意这种预防措施将要求在你
的系统上有足够的空闲空间来保留现有数据库的两个拷贝。如果你没有足够的空间，你至少要保存集簇的pg_xlog子目录的内容，因
为它可能包含在系统垮掉之前还未被归档的日志。
3. 移除所有位于集簇数据目录和正在使用的表空间根目录下的文件和子目录。
4. 从你的文件系统备份中恢复数据库文件。注意它们要使用正确的所有权恢复并且使用正确的权限。如果你在使用表空间，你应该验
证pg_tblspc/中的符号链接被正确地恢复。
5. 移除pg_xlog/中的任何文件，这些是来自于文件系统备份而不是当前日志，因此可以被忽略。如果你根本没有归档pg_xlog/，
那么以正确的权限重建它。注意如果以前它是一个符号链接，请确保你也以同样的方式重建它。
6. 如果你有在第2步中保存的未归档WAL段文件，把它们拷贝到pg_xlog/
7. 在集簇数据目录中创建一个恢复命令文件recovery.conf
8. 启动服务器。服务器将会进入到恢复模式并且进而根据需要读取归档WAL文件。恢复可能因为一个外部错误而被终止，可以简单
地重新启动服务器，这样它将继续恢复。恢复过程结束后，服务器将把recovery.conf重命名为recovery.done（为了阻止以后意外
地重新进入恢复模式），并且开始正常数据库操作。
9. 检查数据库的内容来确保你已经恢复到了期望的状态。如果没有，返回到第1步。如果一
切正常，通过恢复pg_hba.conf为正常来允许用户连接。
所有这些的关键部分是设置一个恢复配置文件，它描述你希望如何恢复以及恢复要运行到
什么程度。你可以使用recovery.conf.sample
restore_command = ’cp /mnt/server/archivedir/%f %p’
它将从目录/mnt/server/archivedir中拷贝之前归档的WAL段
通常，恢复将会处理完所有可用的WAL段，从而将数据库恢复到当前时间点（或者尽可能接近给定的可 用WAL段）。
因此，一个正常的恢复将会以一个“文件未找到”消息结束，错误消息的准确文 本取决于你选择的restore_command。
如果你希望恢复到之前的某个时间点（例如，恢复到丢弃了你主要的交易表之前），只需要 在recovery.conf中指定要求的停止点。
你可以使用日期/时间、命名恢复点或一个 指定事务ID的结束时间来定义停止点（也被称为“恢复目标”）。在这种写法中，只
有日期/时 间和命名恢复点选项非常有用，因为没有工具可以帮助你准确地确定要用哪个事务ID。
注意: 停止点必须位于基础备份的完成时间之后，即pg_stop_backup的完成时间。
如果恢复找到被破坏的WAL数据，恢复将会停止于该点并且服务器不会启动。
在这种情况下，恢复进程需要从开头重新开始运行，并指定一个在损坏点之前的“恢复目标”以便恢复能
够正常完成。
如果恢复由于一个外部原因失败，例如一个系统崩溃或者WAL归档变为不可访问，则该次恢复可以被简单地重启并且它将会从几乎是上次失败的地方继续。

3.5. 时间线

将数据库恢复到一个之前的时间点的能力带来了一些复杂性
PostgreSQL有一个时间线概念。无论何时当一次归档恢复完成，一个新的时间线被创建来标识恢复之后生成的WAL记录序列。
时间线ID号是WAL段文件名的一部分，因此一个新的时间线不会重写由之前的时间线生成的WAL数据。
每次当一个新的时间线被创建，PostgreSQL会创建一个“时间线历史”文件，它显示了新时间
线是什么时候从哪个时间线分支出来的。

3.6. 建议和例子

3.6.1. 单机热备份
使用PostgreSQL的备份功能来产生单机热备份。这些备份不能被用于时间点恢复，然而
备份和恢复时要比使用pg_dump转储更快
在基础备份的帮助下，产生一个单机热备份最简单的方式是使用pg_basebackup工具。
如果你在调用它时使用了-X参数，使用该备份所需的所有事务日志将会被自动包含在该备份中，并且恢复该备份也不需要特殊的动作
如果在复制备份文件时需要更多灵活性，也可以使用一个较低层的处理来创建单机热备
份。要为低层 单机热备份做准备，将wal_level设置为archive或更高， archive_mode设
置为on，并且设置一个archive_command，该命令只当一个开关文件存在时执行归档。例
如：
archive_command = ’test ! -f /var/lib/pgsql/backup_in_progress || (test ! -f /var/lib/pgsql/backup_in_progress)
通过这样的准备，可以使用一个如下所示的脚本来建立备份：
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文件则被加入到备份中，这样
基础备份和所有需要的WAL文件都是同一个tar文件的组成部分。请记住在你的备份脚本中加
入错误处理。
3.6.2. 压缩的归档日志
archive_command = 'gzip < %p > /var/lib/pgsql/archive/%f'
那么在恢复时你将需要使用gunzip：
restore_command = 'gunzip < /mnt/server/archivedir/%f > %p'
3.6.3. archive_command脚本
archive_command = 'local_backup_script.sh "%p" "%f"'
任何时候如果你希望在归档处理中使用多个命令，明智的方法是使用一个独立的脚本文件。
需要在一个脚本内解决的需求例子包括：
• 将数据拷贝到安全的场外数据存储
• 批处理WAL文件，这样它们可以每三小时被传输一次，而不是一次一个
• 与其他备份和恢复软件交互
• 与监控软件交互以报告错误

3.7. 警告

在编写此文档时，连续归档技术存在一些限制。这可能会在未来的发布中被修复：
1 哈希索引上的操作目前不被WAL记录，因此重放不会更新这些索引。这将意味着任何新的
插入都会被索引忽略，被更新的行显然会消失而被删除的行将仍然保留有指针。换句话
说，如果你修改了一个具有哈希索引的表，那么你将在一个后备服务器上得到不正确的查
询结果。当恢复结束时，推荐你手工REINDEX每一个这样的索引。
2 如果一个CREATE DATABASE命令在基础备份时被执行，然后在基础备份进行时CREATE
DATABASE所复制的模板数据库被修改，恢复中可能会导致这些修改也被传播到已创建的数
据库中。这当然是我们不希望的。为了避免这种风险，最好不要在创建基础备份时修改任
何模板数据库。
3 CREATE TABLESPACE命令会WAL以其字面绝对路径记录，并且因此将在重放时以相同的
绝对路径来创建表空间。当日志在一台不同的机器上被重放时，这可能也不是我们希望
的。即使日志在同一台机器上被重放也是危险的，就算是恢复到一个新的数据目录重放过
程也会覆盖原来表空间的内容。为了避免这种潜在的陷阱，最佳做法是在创建或丢弃表空
间后创建一个新的基础备份。
还需要注意的是，默认的WAL格式相当庞大，因为它包括了很多磁盘页快照。

转载于:https://www.cnblogs.com/yhq1314/p/10044028.html