PostgreSQL之pg_dump

PostgreSQL运维技术 2021-06-11

5100

1、pg_dump是什么？

pg_dump是备份PostgreSQL数据库的工具。PG提供物理备份和逻辑备份，我们知道PG物理备份是wal日志的热备份，而pg_dump是PG提供的逻辑备份工具。它可以在数据库正在使用的时候进行完整一致的备份，并不阻塞其他用户对数据库的访问。

pg_dump只能备份一个数据库。要备份整个集群，或备份集群中所有数据库通用的全局对象(如角色和表空间)，需要使用pg_dumpall。

2、数据库的转储格式？

转储格式一般为纯文本或者其他归档文件格式。

纯文本：转储的格式是纯文本，包含很多SQL命令，这些SQL命令可以用于重建该数据库并将之恢复到保存脚本时的状态。可以使用psql从这样的脚本中恢复。

它们甚至可以用于在其他机器甚至是其他硬件体系的机器上重建数据库，通过对脚本进行一些修改，甚至可以在其他SQL数据库产品上重建数据库。

归档文件格式：

归档文件格式必须和pg_restore一起使用来重建数据库。它们允许pg_restore对恢复什么东西进行选择，甚至在恢复之前对需要恢复的条目进行重新排序。

举例：

自定义格式：-Fc

目录格式：-Fd

它们允许选择和重新排序所有归档项，支持并行恢复，以及默认情况下压缩的。

3、pg_dump的用法。

查看pg_dump的用法:pg_dump --help

pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump
  -B, --no-blobs               exclude large objects in dump
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -n, --schema=SCHEMA          dump the named schema(s) only
  -N, --exclude-schema=SCHEMA  do NOT dump the named schema(s)
  -o, --oids                   include OIDs in dump
  -O, --no-owner               skip restoration of object ownership in
                               plain-text format
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -t, --table=TABLE            dump the named table(s) only
  -T, --exclude-table=TABLE    do NOT dump the named table(s)
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security (dump only content user has
                               access to)
  --exclude-table-data=TABLE   do NOT dump data for the named table(s)
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
  --no-tablespaces             do not dump tablespace assignments
  --no-unlogged-table-data     do not dump unlogged table data
  --quote-all-identifiers      quote all identifiers, even if not key words
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <pgsql-bugs@postgresql.org>.

解释：

用法: pg_dump [选项]... [数据库名字]
一般选项:
  -f, --file=FILENAME 输出文件或者目录名
  -F, --format=c|d|t|p 输出格式(custom, directory, tar, plain text),默认是p
-v, --verbose             详细模式

-V, --version 输出版本信息, 然后退出

-Z, --compress=0-9       被压缩格式的压缩级别
  --lock-wait-timeout=TIMEOUT 在等待表锁超时后操作失败
--help                       显示此帮助信息, 然后退出

控制输出内容选项:
-a, --data-only          只转储数据,不包括模式
-b, --blobs              在转储中包括大对象
-c, --clean              在重新创建之前，先清除（删除）数据库对象
-C, --create             在转储中包括命令,以便创建数据库
-E, --encoding=ENCODING     转储以ENCODING形式编码的数据
-n, --schema=SCHEMA      只转储指定名称的模式
-N, --exclude-schema=SCHEMA     不转储已命名的模式
-o, --oids               在转储中包括 OID
-O, --no-owner           在明文格式中, 忽略恢复对象所属者
  -s, --schema-only        只转储模式, 不包括数据
-S, --superuser=NAME     在转储中, 指定的超级用户名
  -t, --table=TABLE        只转储指定名称的表
-T, --exclude-table=TABLE       只转储指定名称的表
  -x, --no-privileges      不要转储权限 (grant/revoke)
--binary-upgrade         只能由升级工具使用
  --column-inserts          以带有列名的INSERT命令形式转储数据
--disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号
--disable-triggers         在只恢复数据的过程中禁用触发器
--inserts                 以INSERT命令，而不是COPY命令的形式转储数据
--no-security-labels        do not dump security label assignments
--no-tablespaces           不转储表空间分配信息
--no-unlogged-table-data    do not dump unlogged table data
--quote-all-identifiers     quote all identifiers, even if not key words
--serializable-deferrable   wait until the dump can run without anomalies
--use-set-session-authorization 使用 SESSION AUTHORIZATION 命令代替ALTER OWNER 命令来设置所有权

连接选项:
-h, --host=主机名        数据库服务器的主机名或套接字目录
-p, --port=端口号        数据库服务器的端口号
-U, --username=名字      以指定的数据库用户连接数据库。
-w, --no-password        永远不提示输入口令
-W, --password           强制口令提示 (自动)
--role=ROLENAME          do SET ROLE before dump

如果没有提供数据库名字, 那么将使用 PGDATABASE 环境变量。

4、pg_dump使用示例。

a.将一个名为tesdb的数据库转储到一个sql脚本文件中。

pg_dump -h localhost -p 5432 -U root testdb > testdb.sql

b.使用psql恢复a中生成的testdb.sql文件到名为newdb的数据库。

psql -h localhost -p 5432 -U root -d newdb -f testdb.sql > out.txt

注：

newdb需要提前创建，否则提示数据库不存在异常；

如果想要恢复到同名数据库testdb，需要在dump时，指定-c选项，表示在重新创建之前，先清除（删除）数据库对象。或者在恢复前，先drop掉老的db。

否则，会在恢复时提示表已存在异常。

pg_dump -h localhost -p 5432 -U root -c testdb > testdb.sql

c.将数据库转储为自定义格式的归档文件。添加-Fc选项。

pg_dump -h localhost -p 5432 -U root -Fc -f test.dump testdb

d.将c中生成的test.dump归档文件恢复到名为newdb的数据库中。

pg_restore -h localhost -p 5432 -U root -d newdb test.dump

e.将数据库转储为目录格式的归档文件。添加-Fd选项。

pg_dump -h localhost -p 5432 -U root -c -Fd -f dumpdir testdb

f.将e中生成dumpdir目录恢复到名为newdb的数据库中。

pg_restore -h localhost -p 5432 -U root -d newdb dumpdir

参考：

https://www.postgresql.org/docs/12/app-pgdump.html

https://blog.csdn.net/windone0109/article/details/12748789

postgresql

文章转载自PostgreSQL运维技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL之pg_dump

评论