PostgreSQL逻辑备份与恢复

点击上方“IT那活儿”，关注后了解更多内容，不管IT什么活儿，干就完了！！！

逻辑备份和恢复介绍

pg_dump是一个用来对PostgreSQL数据库进行逻辑备份的工具。即使数据库正在被并发使用，它也能创建一致的备份。

由pg_dump创建的备份在内部是一致的，也就是说，这个备份是pg_dump开始运行时刻的数据库快照，且在pg_dump运行过程中发生的更新将不会被备份。

pg_dump的全库一致性备份指的是实例中的单个数据库的一致性备份，因为备份不同的数据库需要切换连接，无法在不同的数据库之间共享snapshot，因此只能单库一致.这意味着每个数据库自身是一致的，但是不同数据库的快照并不同步。

pg_dump工作的时候并不阻塞其他的对数据库的操作。（但是会阻塞那些需要排它锁的操作，比如大部分形式的ALTER TABLE）。

pg_dump相对于其他备份方法的一个重要优势是，pg_dump的输出可以很容易地在新版本的PostgreSQL中载入，而文件级备份和连续归档都对服务器版本有限定。

pg_dump也是唯一可以将一个数据库传送到一个不同机器架构上的方法，例如从一个32位服务器到一个64位服务器。

pg_dump只转储单个数据库。要备份一个实例中对于所有数据库公共的全局对象（例如角色和表空间），则需要使用pg_dumpall。

pg_restore是一个用来从pg_dump创建的非文本格式归档恢复PostgreSQL数据库的工具。它将数据库重建成它备份时的状态。这些归档文件还允许pg_restore选择恢复哪些内容或者在恢复前对恢复项重排序。

pg_dump常用的备份选项：

-f,--file 备份脚本输出文件名；

-F,--format=c|d|t|p  #c自定义格式,d目录，t 输出为tar包,p 纯文本SQL；

-j,--jobs=num 当使用目录格式时，可以同时对多个表进行dump；

-a,--data-only 只dump表中的数据；

-c,--clean 在重建之前，先DROP到重建的对象；

-C,--create 包含创建database的命令；

-n,--schema；

-N,--exclude-scheam；

-s,--schema-only 只备份表结构；

-t,--table 只备份指定的表；

-T,--exclude-table；

--inserts #使用insert语句替换copy；

--column-inserts #insert语句包含完整的列名。

备份与恢复实验

1. 生成测试数据

创建一个测试数据库sncdb。

$ createdb sncdb

$ pgsql sncdb

在这个测试数据库中创建测试表并生成测试数据。

记录一下各个表的hash值，用于恢复数据后校验。

sncdb=# select sum(hashtext(t1.*::text)) from t1;

sum

-------------

-2510573620

(1 row)



sncdb=# select sum(hashtext(t2.*::text)) from t2;

sum

-----------

197834427

(1 row)



sncdb=# select sum(hashtext(t3.*::text)) from t3;

sum

-----------

395668854

(1 row)



sncdb=# select sum(hashtext(t4.*::text)) from t4;

sum

------------

2840903814

(1 row)

2. 使用pg_dump备份数据库到文本文件并使用psql恢复数据

使用pg_dump将数据库sncdb备份到一个sql文本文件db_sncdb.sql。

$ pg_dump sncdb > db_sncdb.sql

备份完成后，删除这个数据库sncdb。

新创建一个数据库newdb。

检查数据库没有任何表存在。

使用psql将sql文本db_sncdb.sql恢复到刚才新创建的数据库newdb。

psql -d newdb -f db_sncdb.sql

数据恢复完成后，我们可以在newdb查看到这些表了。

检查表的hash值，与前面记录的hash值一致，数据恢复成功。

3. 使用pg_dump备份指定表到文本文件并使用psql恢复数据

使用pg_dump将表t4备份到一个sql文本文件t4.sql。

$ pg_dump -t t4 newdb > t4.sql

新创建一个数据库newdb2。

$ createdb newdb2

使用psql将sql文本t4.sql恢复到刚才新创建的数据库newdb2。

$ psql -d newdb2 -f t4.sql

数据恢复完成后，我们可以在newdb2查看到表t4了。

4. 备份数据库到归档文件并使用pg_restore恢复数据

创建一个新的数据库sncdb2，并把数据导入。

使用pg_dump将数据库sncdb2备份到一个归档文件sncdb2.dump。

$ pg_dump -Fc sncdb2 > sncdb2.dump

删除数据库sncdb2并使用pg_restore从归档文件sncdb2.dump中恢复它。

$ dropdb sncdb2
$ pg_restore -C -d postgres sncdb2.dump

数据恢复完成后，我们可以在恢复出来的数据库sncdb查看到这些表了。

我们也可以将归档文件恢复到一个名为sncdb3的新创建的数据库中。

新创建一个数据库sncdb3。

$ createdb -T template0 sncdb3

将归档文件sncdb2.dump恢复到新创建的数据库sncdb3中。

$ pg_restore -d sncdb3 sncdb2.dump

数据恢复完成后，我们可以在数据库sncdb_new查看到这些表了。

5. 更多pg_dump的示例

1）pg_dump仅导出数据库结构：

pg_dump -U TestRole1 -s -f TestDb1.sql TestDb1

2）备份某个database，备份结果以自定义压缩格式输出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb

3）备份某个database，备份结果以SQL文本方式输出，输出结果中需包括CREATE DATABASE语句：

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

4）备份某个database中所有名称以“pay”开头的表，备份结果以自定义压缩个数输出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *.pay* -f pay.backup mydb

5）备份某个database中hr和payroll这两个schema中的所有数据，备份结果以自定义压缩格式输出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -n hr -n payroll -f hr_payroll.backup mydb

6）备份某个database中除了public schema中的数据以外的所有数据，备份结果以自定义压缩格式输出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -N public -f all_sch_except_pub.backup mydb

6. pg_dumpall实例级别逻辑备份

pg_dumpall常用的备份选项：

-f,--file 备份输出文件名；

-a,--data-only 只备份数据；

-c,--clean在重建之前，先DROP到重建的对象；

-E,--endcoding 备份文件字符集；

-s,--schema-only 只备份表结构；

建议每天对角色和表空间定义等全局对象进行备份，但不建议每天使用pg_dumpall来备份全库数据，因为pg_dumpall仅支持导出为SQL文本格式，而使用这种庞大的SQL文本备份来进行全库级别的数据库恢复时及其耗时的，所以一般只建议使用pg_dumpall来备份全局对象而非全库数据。