逻辑备份 pg_dump pg_dumpall copy（上）

逻辑备份

逻辑备份在恢复时，介于逻辑备份与故障时间点之间的数据难以恢复，而且如果表比较

大时，备份时间会很长，而且恢复时间会更长，故一般不采取逻辑备份方式进行数据库备份；

对于小表和跨平台跨版本    的数据迁移，适合用逻辑备份。并且逻辑备份可以当作物理备份的

辅助备份手段。

逻辑备份主要以下三种：

pg_dump 命令可以选择一个数据库或部分表进行备份，只能备份单个数据库，而且恢复

的时候需要创建空数据库。

pg_dumpall 是将一个 PostgreSQL 数据库集群全部转储到一个脚本文件中，可以备份所

有数据库，并且备份角色、表空间。

Copy 用于表与文件(和标准输出，标准输入)之间的相互拷贝。

pg_dump 介绍

pg_dump 只能备份单个数据库，不会导出角色和表空间相关的信息。

pg_dump 不会阻塞其他用户读取或写入数据库，即使数据库正在被并发使用，也能创

建一致的备份。

备份的数据可以是脚本或归档文件格式， -F c 备份为二进制格式 , 压缩存储 . 并且可被

pg_restore 用于精细还原，归档文件格式必须与 pg_restore 配合使用来重建数据库。 -F p 备

份为脚本 , 大库不推荐。

脚本格式的内容包含 sql 命令的纯文本数据，可用来

1) 、重建数据库到它被备份时的状态。

2) 、在其他机器上重构数据库。

3) 、在经过一些修改后，还可以在其他 sql 数据库产品上重构数据库。

pg_dump 参数

命令的使用方法如下（选了比较重要的参数）：

-j ：指定并行导出的并行度。

-a ：该选项只对纯文本格式有意义，只输出数据，不输出数据定义的 SQL 语句。

-b ：在转储中是否包含大对象。

-c ：该选项只对纯文本格式有意义，输出的脚本中是否生成清理该数据库对象的语句（如

drop table 命令）。

-C ：该选项只对纯文本格式有意义，指定脚本中是否输出一条 create database 语句。如

果备份的源数据库与目标数据库名称一致时指定该参数。

-E ：以指定的字符集编码创建转储，默认转储是依据数据库编码创建的。

-f ：输出到指定的文件。如果没有指定此参数，则输出到标准输出。

-F ：选择输出的格式，可以是 p 、 c 或 t 。 p 纯文本 SQL 脚本文件的格式，这是默认值。

c 输出一个适合 pg_restore 使用的自定义格式存档。该格式默认是压缩的。 t 输出一个适合 输入 pg_restore 的 tar 格式的归档。 tar 格式不支持压缩，且对独立表的大小限制为 8GB 。

-n ：只转储匹配 schema 模式的内容，包括模式本身及其包含的对象。如果指定了 -n ，

那么 pg_dump 将不会转储模式所依赖的其他数据库对象，因此无法保证转储的内容一定能

够在另一个干净的数据库中成功恢复。非模式对象，比如大对象，不会在指定 -n 时被转储。

可以使用 --blobs 明确要求转储大对象。

-N ：不转储任何匹配 schema 模式的内容。匹配规则与“

-n ”完全相同。

-s ：只输出对象定义（模式），不输出数据。

-t ：只转储匹配 table 的表、视图、序列。可以使用多个 -t 选项匹配多个表。

-T ：不转储任何匹配 table 模式的表。

-v ：执行过程中打印更详细的信息。

-Z ：指定要使用的压缩级别。

--binary-upgrade ：该选项是专为升级工具准备的，其功能可能会在将来的版本中有所改

变。

--inserts ：该选项像 INSERT 命令一样转储数据。主要用于把数据加载到非 PostgreSQL 数

据库。

--column-inserts ：若目标表列的顺序与源表列的顺序不一样，恢复操作可能会完全失败。

pg_dump 连接选项参数如下。

-h ：指定运行服务器的主机名。

-p ：指定服务器正在侦听的 TCP 端口。

-U ：指定要连接的用户名。

-w ：从不提示密码。如果服务器请求密码身份认证，而且密码不能通过其他方式

（如 .pgpass 文件）来获得，则此命令会导致连接失败。

-W ：连接到一个数据库之前提示密码。当不提供 -W 选择时， pg_dump 将会浪费一个连

接并试图找出服务器是否需要密码 ( 不适合脚本 ) 。

-d ：指定连接的数据库名。

pg_dump 应用

pg_dump 在脚本中应用 （

-w ）

生成文本格式： pg_dump -h 127.0.0.1 -U txadmin -d postgres -p 6868 -w>/data/backup/test.sql

生 成 归 档 格 式 ： pg_dump -h 127.0.0.1 -U txadmin -d postgres -p 6868 -w

-Fc>/data/backup/test.tar.gz

pg_dump 在人工操作中的应用 （

-W ）

生成文本格式： pg_dump -h 127.0.0.1 -U txadmin -d postgres -p 6868 -W>/data/backup/test.sql

生成归档格式： pg_dump -h 127.0.0.1 -U txadmin -d postgres -p 6868 -W -

Fc>/data/backup/test.tar.gz

备份 public 模式下的对象

pg_dump -h 127.0.0.1 -U txadmin -d postgres -p 6868 -n public>/data/backup/test.sql

备份 public 和 pgxz 模式下的对象，多个模式使用多组

pg_dump -h 127.0.0.1 -U txadmin -d postgres -p 6868 -n tbase -n pgxz>/data/backup/test.sql 备份数据表 public.t

pg_dump -h 127.0.0.1 -U txadmin -d postgres -p 6868 -t 'public.t' >/data/backup/test.sql

备份数据表 public.t ， pgxz.t

pg_dump -h 127.0.0.1 -U txadmin -d postgres -p 6868 -t public.t' -t

'pgxz.t1'>/data/backup/test.sql

备份模式 public 下所有 t 开头的数据表

pg_dump -h 127.0.0.1 -U txadmin -d postgres -p 6868 -t 'public.t*' >/data/backup/test.sql

pg_dumpall 介绍

pg_dumpall 是用于备份所有数据库的工具，所以不能指定数据库。不阻塞其他用户读

取或写入数据库，即使数据库正在被并发使用，也能创建 一致的备份。

与 pg_dump 不同的是 pg_dumpall 可以用于备份全局对象，如用户和表空间定义。

pg_dumpall 备份时无法指定备份数据的格式，即无法使用-Fc 这样的参数。

pg_dumpall 参数

pg_dumpall 命令的使用方法如下（选了比较重要的参数）：

-a: 只转储数据，不转储模式（数据定义）。

-c: 包括那些重建之前清理（删除）数据库对象的 SQL 命令。

-d: 把数据当作 INSERT 命令输出（而不是 COPY ）。这样将令恢复过程非常缓慢 , 这主

要是用于让转储输出可以用于其它非 PostgreSQL 数据库。

-D: 把数据以带着明确字段名（

INSERT INTO table (column, ...) VALUES ... ）的 INSERT 命令

形式转储出来。这样会令恢复非常慢，这主要是用于让转储输出可以用于其它非

PostgreSQL 数据库。

-g: 只转储全局对象（用户和组），而不转储数据库。

-s: 只转储模式（数据定义），不转储数据。

-v: 打印进度信息。

pg_dumpall 连接参数参照 pg_dump 。

pg_dumpall 应用

在脚本中应用 （-w）

pg_dumpall -h 127.0.0.1 -U txadmin -p 6868 -w>/data/backup/test.sql

在人工操作中的应用 （-W）

pg_dumpall -h 127.0.0.1 -U txadmin -p 6868 -W>/data/backup/test.sql

只备份全局对象

pg_dumpall -h 127.0.0.1 -U txadmin -p 6868 -g >/data/backup/test.sql

只备份用户角色

pg_dumpall -h 127.0.0.1 -U txadmin -p 6868 -r >/data/backup/test.sql

只备份表空间

pg_dumpall -h 127.0.0.1 -U txadmin -p 6868 -t>/data/backup/test.sql

只备份对象定义

pg_dumpall -h 127.0.0.1 -U txadmin -p 6868 -s>/data/backup/test.sql

copy 介绍

copy to 由表至文件， copy from 由文件至表。

copy 与 \copy 命令都能实现数据文件与表的数据传递，两者都在 psql 环境下执行。主要

不同在于数据文件的路径寻址：

1 ） copy 命令始终是到数据库服务端找文件；以超级用户执行导入导出权限要求很高，适合

数据库管理员操作 ;

2 ） \copy 命令可在客户端执行导入客户的数据文件。权限要求没那么高，适合开发人员，

测试人员使用。

copy 参数

copy 命令的使用方法如下：

format ：指复制到文件的文件类型 , 如： csv,text 。

oids ： boolean 类型，指复制到文件时带上 oid ，但是当某个表没有 oid 时就会出错。

freeze ： boolean 类型，冻结数据，然后执行 vacuum freeze 。

delimiter ：指在导出文件时的分隔符。在 text 时默认为 tab ， csv 文件默认是逗号。

header ： boolean 类型，指在复制到文件时带上表字段名称。

null ：指定 null 值，默认为 \n 。

encoding ：指定文件的编码，如果没有指定就默认使用客户端的字符集。

stdin ：指的是客户端程序的输入流。

stdout ：指向是客户端的输出流。

copy 应用

常用的数据文件列之间默认是 tab 制表符，可以用 csv 格式，列之间以逗号隔离，导出

字段名称。

copy (select * from t) to '/tmp/t.csv' (format 'csv' ,delimiter ',',header true);

客户端输出 csv 文件类型并以逗号分割

copy t1 to stdout(format 'csv');