PG备份恢复--pg

pg_dump

pg_dump 是一个逻辑备份工具。使用 pg_dump 可以在数据库处于使用状态下进行一致
性的备份,它不会阻塞其他用户对数据库的访问。

一致性备份是 pg_dump 开始运行时，给数据库打了一个快照，且在 pg_dump 运行过程
中发生的更新将不会被备份。

pg_dump 只备份单个数据库，不能备份数据库公共的全局对象（例如角色和表空间）

创建一个database mydb ，在mydb下创建一个表t1

mydb=# create table t1(id serial,info text);
CREATE TABLE
mydb=# create index on t1(id);
CREATE INDEX
mydb=# insert into t1 values(generate_series(1,10000));
INSERT 0 10000
mydb=# \1
invalid command \1
Try \? for help.
mydb=# \q

pg_dump常用参数

-h host，指定数据库主机名，或者 IP
-p port，指定端口号
-U user，指定连接使用的用户名
-W，按提示输入密码
dbname，指定连接的数据库名称，实际上也是要备份的数据库名称。
-f,–file：输出到指定文件中
-F,–format=c|d|t|p：
c 为自定义格式，也是二进制格式，压缩存储，只能使用 pg_restore 来还原, 可
以指定还原的表, 编辑 TOC 文件, 定制还原的顺序, 表, 索引等。
d 为目录
t 表示输出为 tar 包
p 为纯文本 SQL，大库不推荐；
-j,–jobs=num：指定并行导出的并行度
-a,–data-only：只导出数据，不导出表结构
-c,–clean：是否生成清理该数据库对象的语句，比如 drop table
-C,–create：是否输出一条创建数据库语句
-n,–schema：只转存匹配 schema 的模式内容
-N,–exclude-scheam：不转存匹配 schema 的模式内容
-O,–no-owner，不设置导出对象的所有权
-s,–schema-only：只导致对象定义模式，不导出数据
-t,–table：只转存匹配到的表，视图，序列，可以使用多个-t 匹配多个表
-T,–exclude-table：不转存匹配到的表。
–inserts：使用 insert 命令形式导出数据，这种方式比默认的 copy 方式慢很多，但是可
用于将数据导入到非 PostgreSQL 数据库。
–column-inserts：导出的数据，有显式列名

备份

(1)导出sql语句


一个insert插一行
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W --insert mydb >mydb.sql一次插入2行
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W --insert  --rows-per-insert=2 mydb  >mydb2.sql

（2）导出文件

-- 要转储一个数据库到一个自定义格式归档文件
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W  -Fc mydb  >mydb.dump--使用 5 个并行任务转储一个数据库到一个目录格式的归档 
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W  -Fd -j 5 mydb  -f mydbdir

（3）用法举例

-- 备份单个表 
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W testdb -t t1 --inserts > testdb.sql -- 备份多个表 
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W testdb -t t1 -t t2 --inserts > 
testdb.sql-- 如果只想备份 schema 模式中所有以 t 开头的表，但是不包括 t1 表 
pg_dump -t "public.t*" -T public.t1 testdb > testdb.sql -- 转储所有 testdb 的数据库对象，但是不包含以 1 结尾的表 
pg_dump -T '*1' testdb > testdb.sql -- 转储 testdb 中 public 和 test 这两个 schema 中的内容 
pg_dump -Fc -n public -n test testdb -f testdb.dump -- 转储 testdb 中除了 public schema 中的数据以外的所有数据 
pg_dump -Fc -N public testdb -f testdb.dump--只备份数据
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W testdb --inserts -a > testdb.sql--只备份表结构
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W testdb -s > testdb.sql

恢复


--恢复一个文本文档 
psql mydb <mydb.sql-- 要把一个归档文件重新载入到一个（新创建的）名为 newdb 的数据库： 
pg_restore -d newdb mydb.dump -- 把一个归档文件重新装载到同一个数据库（该归档正是从这个数据库中转储得来）中，
丢掉那个数据库中的当前内容 
pg_restore -d newdb --clean mydb.dump-- 备份后直接进行恢复，文件不落地 
pg_dump testdb| psql newdb -- 并行备份恢复 
pg_dump -Fd -j4 testdb -f dumpdir 
pg_restore -d newdb -j4 dumpdir

利用toc文件选择性恢复


-- 根据二进制备份文件生成 toc 文件 
方式一：pg_restore -l mydb.dump > mydb.toc 
方式二：pg_restore -l -f mydb.toc mydb.dump 
以上两种方式相同效果
-- 修改 toc 文件，用‘;’号注释掉不用还原的内容: 
-- 以 toc 文件列表做恢复 
pg_restore -Fc  -d mydb mydb.dump -L mydb.toc
-- 检查发现 t1 表没有被导入。--只恢复t1表
pg_dump -Fc -d mydb  -f mydb.dmp
pg_restore -l mydb.dmp |grep t1 >mydbt1.dmp
pg_restore -d mydb  mydb.dmp -L mydbt1.dmp

压缩备份

-- 导出并且压缩 
pg_dump testdb -f testdb.sql | gzip testdb.sql 
-- 解压并且导入，压缩文件不变： 
gunzip -c testdb.sql.gz | psql testdb -- 分割备份 
pg_dump testdb | split -b 1m 
-- 恢复 
cat filename* | psql dbname

大表备份


可以使用 -j 选项来指定执行 pg_dump 和 pg_restore 时要使用的线程数。 
可以使用目录格式 (-Fd)，它会提供压缩转储（使用 gzip）。使用 -Fd 选项可以提供超过
5 倍的压缩。对于较大的数据库（例如超过 1 TB），压缩转储可以减少磁盘 IOPS。示例：pg_dump -Fd testdb -j 5 -f dump_dir pg_restore -d newdb -j 5 dump_dir