PostgreSQL的逻辑备份与物理备份

简介

制定备份和恢复计划是每一个DBA最重要的工作之一，它决定了数据的有效性和完整性。也可以搭建跨越不同数据中心的流复制集群，能有效的帮助你避免单点故障。但是只有一份有效的备份能够帮助从delete或者drop的误操中恢复数据。

制定备份和恢复计划是每一个DBA最重要的工作之一，它决定了数据的有效性和完整性。也可以搭建跨越不同数据中心的流复制集群，能有效的帮助你避免单点故障。但是只有一份有效的备份能够帮助从delete或者drop的误操中恢复数据。
这篇文章中，我们将讨论PostgreSQL的一些备份选项。

备份类型

主要有两种备份类型:物理备份和逻辑备份。在设计备份策略之前，理解这一点非常重要。使用何种备份类型，也取决于你使用的备份工具。

逻辑备份

• 定义：逻辑备份是指使用软件技术从数据库中导出数据并写入一个输出文件，该文件的格式一般与原数据库的文件格式不同，只是原数据库中数据内容的一个映像。因此，逻辑备份文件只能用来对数据库进行逻辑恢复，即数据导入。
• 原理：逻辑备份是基于文件级别的备份，由于每个文件都是由不同的逻辑块组成，且这些逻辑块可能存储在分散的磁盘块上，逻辑备份在对非连续存储磁盘上的文件进行备份时需要额外的查找操作。这些额外的操作增加了磁盘的开销，降低了磁盘的吞吐率。

逻辑备份通常包含以纯文本、可读格式存储的数据，或者可以轻松转换为这种格式，甚至可以提取单行数据并还原它。
恢复整个逻辑备份可能非常慢——数据库必须处理加载数据所需的所有查询，这总是比复制数据库文件要慢。

物理备份

• 定义：物理备份是通过直接复制数据库文件的方式来创建备份。在物理备份中，数据库的原始二进制数据和结构都会包含在备份中，备份的文件通常是一个完整的数据库镜像。
• 原理：物理备份是在文件系统之下和硬件磁盘驱动之上进行的。它忽略了文件和结构，处理过程简洁，因此在执行过程中所花费在搜索操作上的开销较少，备份的性能很高。

物理备份按原样获取数据——最终，数据只是存储在某个磁盘上的一堆文件。你可以复制数据文件，并在给定的时间点拥有数据库的副本。然而在实际使用中，它并不像运行cp命令那么容易。在进行物理备份时可能会发生数据库更改。为了确保备份可以安全使用，任何物理备份都必须是一致的。

物理备份对于恢复整个数据集非常有用——速度受到硬件的限制:磁盘和网络(如果通过网络复制数据)。

PostgreSQL的数据备份

像其他数据库一样，PostgreSQL支持逻辑备份和物理备份。

逻辑备份

PostgreSQL附带了一个名为pg_dump的程序——它是一个用于在PostgreSQL数据库上执行逻辑备份的实用工具。
它支持几种输出格式:默认情况下，它将数据存储在纯文本文件中，但它也可以生成tar文件或定制的二进制格式(经过压缩)，可以使用pg_restore还原这些格式(您可以轻松地从这种格式中提取SQL内容)。让我们看看如何使用pg_dump来创建PostgreSQL服务器的备份。

首先使用默认模式，备份一个文本文件

root@vagrant-ubuntu-trusty-64:~/c# pg_dump -d pgbench -U psql -h 10.0.0.101 > pgbench.sql
Password:

在备份的文件中，你能看到类似这样:

COPY pgbench_accounts (aid, bid, abalance, filler) FROM stdin;
1       1       0
2       1       0
3       1       0
4       1       0
5       1       0
6       1       0
7       1       0
8       1       0
9       1       0
10      1       0
11      1       0
12      1       0
13      1       0
14      1       0

如果要其他格式的备份文件，只需要在命令中指定:

root@vagrant-ubuntu-trusty-64:~# pg_dump -d pgbench -U psql -h 10.0.0.101 --format c  -f /root/c/pgdump.c
Password:

恢复数据也很简单。如果是默认SQL格式的数据，只需使用psql将其加载到数据库中。
如果使用其他格式，可以使用pg_restore进行恢复。
如果您仍然拥有旧数据，并且希望恢复整个数据库，则可能需要删除现有数据库，然后重新创建它。

-c, --clean                  clean (drop) database objects before recreating
--if-exists                  use IF EXISTS when dropping objects

备份时加上这两个参数，能够确保所有已经存在的对象都会被重写。

postgres=# drop database pgbench;
DROP DATABASE
postgres=# create database pgbench;
CREATE DATABASE
root@vagrant-ubuntu-trusty-64:~# pg_restore --format c /root/c/pgdump.c -d pgbench -U psql -W -h 10.0.0.101
Password:

物理备份

物理备份是全有或全无的——你获取数据库的完整备份，并且需要还原所有备份。物理备份面临的最主要问题是数据一致性。当然，最简单的方法是完全停掉数据库，然后使用系统命令例如cp,scp等复制一份数据。但不幸的是，大多数情况下我们无法关闭数据来做这件事。

因此，你需要采取一些预防措施来确保备份是一致的。
要进行手动、一致的物理备份，需要以下步骤:

• 在备份开始创建一个检查点pg_start_backup(‘some label’)
• 拷贝目录中的数据
• 使用命令pg_stop_backup()停止备份
• 所有这些可以通过运行pg_basebackup实现

root@vagrant-ubuntu-trusty-64:~# pg_basebackup -U psql -h 10.0.0.101 -x -D /pgbase/

如果想要即时恢复，你需要配置WAL开启归档

wal_level = archive
archive_mode = on

恢复基本备份很容易——将数据复制到空的PostgreSQL数据目录中就可以了，一旦启动PostgreSQL进程，就可以恢复到备份时的数据。

如果要使用WAL归档日志恢复到一个时间点，你仍然需要先进行基数数据的恢复，同时创建一个recovery.conf文件。使用恢复命令，告诉PostgreSQL如何将存档的WAL段复制回来，以便在PostgreSQL进程开始时重新执行。

区别点分析

1. 高效性
   • 物理备份：备份性能高，因为它避免了当文件出现小的改动时就需要对整个文件做备份的问题，只是会去做改动部分的备份。
   • 逻辑备份：备份性能较差，因为即使文件只有很小的改变，也需要将整个文件备份。
2. 实时性
   • 物理备份：可以做到高效的实时备份，因为它基于磁盘扇区进行数据的写入和备份，能够被迅速识别。
   • 逻辑备份：实时备份的效率较低，因为文件的修改很难实时捕获，需要把整个文件读一遍再发到备机。
3. 支持度
   • 物理备份：不受文件系统限制，可以支持各种文件系统包括RAW分区。
   • 逻辑备份：具有很高的灵活性和兼容性，不依赖于特定的数据库引擎版本和硬件平台，通常可以跨平台使用。
4. 恢复方式
   • 物理备份：可以恢复到与原数据库一样的状态，适合用于系统恢复和整个数据库的迁移。
   • 逻辑备份：通常用于数据导入和逻辑恢复，可以重新组织表、消除行迁移、在用户或数据库之间移动对象等。
5. 存储需求
   • 物理备份：文件大小通常较大，对存储资源要求较高。
   • 逻辑备份：以文本文件或自定义的数据文件形式存在，存储需求相对较小。

结论

逻辑备份和物理备份各有其特点和适用场景。在选择备份方式时，需要根据实际情况和需求进行评估和选择。对于需要快速恢复、对存储空间要求不高的场景，物理备份是更好的选择；而对于需要跨平台使用、对数据迁移有需求的场景，逻辑备份则更具优势。