1. pg_dump 使用及示例

2023-10-11 23:50:28

1.pg_dump 概述

1.1 pg_dump 介绍

https://www.postgresql.org/docs/14/app-pgdump.html
pg_dump是用于备份一种PostgreSQL数据库的工具。即使数据库正在被并发使用，它也能创建一致的备份。pg_dump不阻塞其他用户访问数据库（读取或写入）。
pg_dump只转储单个数据库。要备份一个集簇或者集簇中对于所有数据库公共的全局对象（例如角色和表空间），应使用 pg_dumpall。

可以选择一个数据库或部分表进行备份，恢复过程可以跨平台迁移
可以在数据库正在使用时进行完整一致的备份，并不阻塞其它用户对数据库的访问
只能备份单个数据库，不会导出角色和表空间相关的信息

1.2 pg_dump四种转储格式

Plain、custom、directory、tar
Plain 是纯文本格式跟pg_dumpall转储一样。你可以用psql加载它，如果转储很大，提取部分可能会很复杂。
使用pg_restore程序恢复所有其他格式（custom，directory和tar）

Pg_dump四种转储格式:
-F t  tar格式的转储输出格式，不支持压缩，在早期版本有单表8G限制，目前版本已经改善
-F d 以目录的格式创建备份，备份出来的是一个包含若干个目录的备份文件，可以指定并行备份-j
-F c 备份为二进制格式, 压缩存储. 并且可被pg_restore用于精细还原，输出输入 IO 比较稳定
-F p 备份为文本, 大库不推荐， 文本方式 ，对 IO 不可控，不稳定

2.pg_dump 使用

pg_dump --help

2.1 Usage:

  pg_dump [OPTION]... [DBNAME]

2.2 General

一般选项:
  -f, --file=FILENAME          输出文件或目录名 
  -F, --format=c|d|t|p         输出文件格式 (custom, dir, tar)  tezt(默认值)) 
  -j, --jobs=NUM               执行多个并行任务进行备份转储工作
  -v, --verbose                详细模式
  -V, --version                输出版本信息，然后退出
  -Z, --compress=0-9           被压缩格式的压缩级别
  --lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败
  --no-sync                    不需要等待，执行刷盘
  -?, --help                   显示此帮助, 然后退出

2.3 output content

控制输出内容选项:
  -a, --data-only              只转储数据,不包括模式
  -b, --blobs                  在转储中包括大对象
  -B, --no-blobs               在转储中排除大对象
  -c, --clean                  在重新创建之前，先清除（删除）数据库对象      *****
  -C, --create                 在转储中包括命令,以便创建数据库
  -E, --encoding=ENCODING      转储以ENCODING形式编码的数据
  -n, --schema=PATTERN         只转储指定名称的模式
  -N, --exclude-schema=PATTERN 不转储已命名的模式
  -O, --no-owner               在明文格式中, 忽略恢复对象所属者
  -s, --schema-only            只转储模式, 不包括数据
  -S, --superuser=NAME         在明文格式中使用指定的超级用户名
  -t, --table=PATTERN          只转储指定名称的表
  -T, --exclude-table=PATTERN  不转储指定名称的表
  -x, --no-privileges          不要转储权限 (grant/revoke)

  --binary-upgrade             仅供升级实用程序使用
  --column-inserts             以带有列名的INSERT命令形式转储数据
  --disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号
  --disable-triggers           在恢复数据的过程中禁用触发器
  --enable-row-security        启用行安全性（只转储用户能够访问的内容）
  --exclude-table-data=PATTERN  不转储指定名称的表中的数据
  --extra-float-digits=NUM     覆盖 extra_float_digits 的默认设置
  --if-exists                  当删除对象时使用IF EXISTS
  --inserts                    以INSERT命令，而不是COPY命令的形式转储数据

  --load-via-partition-root    通过根表加载分区
  --no-comments                不转储评论
  --no-publications            不转储出版物
  --no-security-labels         不转储安全标签分配
  --no-subscriptions           不转储订阅
  --no-synchronized-snapshots  在并行作业中不使用同步快照
  --no-tablespaces             不转储表空间分配
  --no-unlogged-table-data     不转储未记录的表数据
  --on-conflict-do-nothing     添加 ON CONFLICT DO NOTHING 到 INSERT 命令
  --quote-all-identifiers      引用所有标识符，即使不是关键字
  --rows-per-insert=NROWS      每个插入的行数；暗示 --inserts
  --section=SECTION            转储命名节（前数据、数据或后数据）
  --serializable-deferable     等到转储可以无异常运行
  --snapshot=SNAPSHOT          使用给定的快照进行转储
  --strict-names               要求表和/或模式包含模式以匹配每个至少一个实体
  --use-set-session-authorization
                               使用 SET SESSION AUTHORIZATION 命令而不是 ALTER OWNER 命令来设置所有权

2.4 Connection options

Connection options
  -d, --dbname=DBNAME          对数据库 DBNAME备份
  -h, --host=HOSTNAME          数据库服务器的主机名或套接字目录
  -p, --port=PORT              数据库服务器的端口号
  -U, --username=NAME          以指定的数据库用户联接
  -w, --no-password            永远不提示输入口令
  -W, --password               强制口令提示 (自动)
  --role=ROLENAME              在转储前运行SET ROLE

3. pg_dump 示例

3.1 默认的文本方式

# 备份数据库
pg_dump -Fp-f/tmp/testdb.sql  -C -E  UTF8 -h 127.0.0.1 -U postgres testdb  
#或 简单语法 ,默认就是文本的方式
pg_dump testdb >/tmp/testdb.sql                                                                      

# 将数据恢复到 testdb1   , 导入数据时首先创建数据库
createdb testdb1
psql  testdb1</tmp/testdb.sql

3.2 二进制备份文件

#二进制格式备份文件， 指定类开 -F c
pg_dump -F c -f  /tmp/testdb.dmp  -C -EUTF8 -h 127.0.0.1 -U postgres testdb

#可选，解析二进制格式的备份文件 ,查看二进制文件的备份内容
pg_restore      -f   /tmp/a.log       /tmp/testdb.dmp        
#可选，-l  生成备份集的toc例表，可以编辑该toc文件 ，注释其中的表，恢复部份对象
pg_restore -l  -f   /tmp/a.toc    /tmp/testdb.dmp     

# 二进制格式 ，tar 格式 ，目录格式 ，都需要用pg_restore 来恢复 
pg_restore -d testdb1   /tmp/testdb.dmp     #需要先创建目标库，还原恢复

4.pg_dump 扩展

4.1.生成toc文件进行选择性恢复

1）根据二进制备份文件生成toc文件
    pg_restore -l -f /tmp/toc1 /tmp/testdb.dmp
2）修改 toc文件，以首行加分号“；”的方式注释掉不用还原的内容
vi /tmp/toc1 
    265; 1259 25280 TABLE public postgres_log postgres
    266; 1259 25293 TABLE public t2 postgres
    213; 1259 16385 TABLE public test postgres
    3660; 0 25280 TABLE DATA public postgres_log postgres
    ;3661; 0 25293 TABLE DATA public t2 postgres                                      # 这个表用;  注释了，不会恢复
    ;3659; 0 16385 TABLE DATA public test postgres                                   # 这个表用;  注释了，不会恢复
    3520; 2606 25287 CONSTRAINT public postgres_log postgres_log_pkey postgres
    3442; 1259 25269 INDEX public idx_test postgres

3）以toc文件列表做恢复
    pg_restore –F c -L /tmp/toc -d testdb  /tmp/testdb.dmp

4.2使用unix管道备份恢复

1. 压缩与解压
pg_dump testdb| gzip > /tmp/testdbbak.sql.gz      #操作系统压缩
gunzip -c /tmp/testdbbak.sql.gz | psql testdb2      #解压缩恢复

2.备份恢复数据不落盘
pg_dump testdb | psql testdb1      #备份testdb  ，马上又还原到 testdb1,testdb1库要先创建, 数据不落盘，这路对小数据进行测试是非常方便的。   

3.分割备份文件 
备份： pg_dump dbname | split-b1m- filename
恢复： catfilename* | psql dbname

4.3.并行处理

pg_dump -Fd -j4 -f /tmp/db.dir testdb     #-F d 以目录的格式创建备份
pg_restore -d testdb3 -j4 /tmp/db.dir
-j 参数指定同时几个进程来同时执行，每个进程同时只处理一个表的数据。

5.补充

逻辑备份，相对灵活，可以排除大表。可以结合物理备份使用。
如果备份以默认的明文方式保存，输出输入 IO 不可控，生产环境中建议使用 -F c 二进制方式备份比较稳定。

来自为知笔记(Wiz)

码农公寓