PostgreSQL备份与恢复（第一章）

引言

PostgreSQL官方文档指定了以下三种备份方法，详见：

-「SQL转储」，用pg_dump或pgdump_all进行备份，也是一种逻辑备份的方法，这种方法很容易操作，但是缺点就是一旦数据库太大，导入导出文件的效率就会降低。但是有了并行备份恢复和split拆分，也可以在这方面稍微优化。另一个缺点是无法恢复到故障发生的时刻。例如，你使用crontab定时任务在凌晨3点进行备份，结果12点就出故障，如果进行恢复，就会损失9小时的数据。

-「文件系统级备份」，可以在数据目录中执行'一致性快照'，然后将快照复制到备份服务器上。这样就可以在异机进行恢复。

-「连续归档和时间点恢复(PRIP)」。要了解PITR，首先必须了解什么是wal，wal代表预写日志文件，基本上对数据库每次插入、更新、删除在实际应用之前，就写入了日志中。这样就算数据库突然出现了crash，在重新启动的过程中，PostgreSQL能够查看wal文件进行恢复并将数据库还原到可用的状态。

实际上PostgreSQL的备份软件有很多种，可以根据实际的需要来选择备份工具

ToolLicenseMakesbasebackupsMakesbasebackupsfromreplicasManagesbackupsCreatesreplicasMonitorsreplicationdelaySupportsautomatedfailoverTransportusedSourceincludesreplicationtestspg_basebackupPostgreSQLYesYesNoManualNoNoPostgreSQLconnectionBarmanGPLv3YesYesYesNoNoSSH/S3/Azure/GCSyesOmniPITRPostgreSQLYesYesNoManualWALarchivedelayNorsync/SSHpghoardApacheYesYesYesYesNoNoS3,Azure,Ceph,GCSyespg-rmanBSDYesYesYesManualNoNolocal/NFSmountrepmgrGPLv3NoNoNoYesYesYesrsync/SSHpg_probackupPostgreSQLYesYesYesManualNoNolocal/NFSmountyespitreryBSD-2YesYesYesManualYesYesrsync/SSHongoing

只能备份单个数据库，不会导出角色和表空间相关的信息-Fc备份为二进制格式,压缩存储.并且可被pg_restore用于精细还原-Fp备份为文本,大库不推荐pg_dump恢复$psqldbname-Uusernamebakfile或pg_restore–pg_restore[option][filename]–pg_restore-ddbnamebakfile二进制格式的备份只能使用pg_restore来还原,可以指定还原的表,编辑TOC文件,定制还原的顺序,表,索引等。文本格式的备份还原,直接使用用户连接到对应的数据库执行备份文本即可,例如$_dump备份恢复示例：1）创建数据库$testdb=createtablett(espacetbls_t;testdb=insertintota)vals(2);4）查看数据testdb=简单语法,可结合选项灵活备份6）删除数据库testdb$dropdbtestdb7）创建新数据库（恢复之前需创建数据库）$createdbtestdb8）恢复数据$psqltestdbdm_bk/）查看数据是否回复$psqltestdbtestdb=二进制格式备份文件:-Fc$pg_可以以解析为文本内容，类似于pg_dump备份$pg_查看控制信息$pg_把dump文件中的数据导入到postgres数据库中利用toc文件选择性备份恢复：1）根据二进制备份文件生成toc文件pg_）修改toc文件，用‘;’号注释掉不用还原的内容:3）以toc文件列表做恢复pg_）检查发现t1表没有被导入。使用unix管道压缩备份恢复：1）导出并且压缩pg_dumptestdb|或者：pg_|）解压并且导入，压缩文件不变：|psqltestdbcreatetabletb1(aint);db1=createtabletb2(aint);db2=如果遇到当前有数据库或者表，会提示表已经存在，数据照常导入。psql–fdb_）查看数据库db1是否恢复psqldb1testdb=select*fromtb2;a---2(1rows)至此，数据已成功恢复！

压缩与分割的原理都是利用Linux的管线（PIPE）命令。

1）压缩备份pg_dumptestdb|）恢复|psqldbname3）分割备份pg_dumpdbname|split-b1m4）恢复catfilename*|psqldbname

语法COPY命令概述

copy命令用于表与文件(和标准输出，标准输入)之间的相互拷贝；copyto由表至文件，copyfrom由文件至表；copy命令始终是到数据库服务端找文件，以超级用户执行导入导出权限要求很高，适合数据库管理员操作;\copy命令可在客户端执行导入客户端的数据文件，权限要求没那么高，适合开发人员，测试人员使用。COPY基本语法参考：NameCOPY--copydatabetweenafileandatableSynopsisCOPYtable_name[(column])]FROM{'filename'|command'|STDIN}[[WITH](option[,])]COPY{table_namecolumn_name[,])]|(query)}TO{'filename'|PROGRAM'command'|STDOUT}[[WITH](option[,])]whereoptioncanbeoneof:FORMATformat_nameOIDS[boolean]FREEZE[boolean]DELIMITER'delimiter_character'NULL'null_string'HEADER[boolean]QUOTE'quote_character'ESCAPE'escape_character'FORCE_QUOTE{(column_name[,])|*}FORCE_NOT_NULL(column_name[,])ENCODING'encoding_name'

COPY命令示例：

表与文件(和标准输出，标准输入)之间的相互拷贝，类似于Oracle的sqlldr，把文本文件的内容导入到数据库，同时也可以把表的数据导出生成文本文件，实现数据库表和文本文件之间数据的迁移，非常方便，比sqlldr易用性好。[postgres@db2ttdata]$cattest_注意：上面列之间要用tab键隔开。[postgres@db2ttdata]$psqlpsqlType'help'=\copytest_copyfrom/home/postgres/test_;\copytest_copyto/home/postgres/test_;\copytest_copyto/home/postgres/test_;\copytest_copyfrom/home/postgres/test_;testdb=select*fromtb1;a---1(1rows)7）查看数据库db2是否恢复psqldb2testdb=createtabletb(aint);arch=selectpg_start_backup('baseline');pg_start_backup-0/E000060(1row)观察归档日志目录下的归档文件，会产生一个带有备份标记的归档日志：00000001000000000000000，通过分析，跟备份时的标记是一样的。5）备份整个data目录tar-jcv-f/home/postgres/ol_bk/$PGDATA6）停止备份arch=insertintot1select*fromt1;INSERT01testdb=insertintot1select*fromt1;arch=insertintot1select*fromt1;arch=select*fromtb;a---1234(4rows)至此，数据已经成功恢复！