pg_dump/pg_restore备份和恢复

postgresql-10.4数据库备份与恢复

postgresql数据库的备份与恢复需要用到pg_dump、pg_restore、以及psql,这些命令位于pg数据库安装目录的bin目录下。（若设置了PGHOME，则可以直接使用）

pg_dump介绍

[peter@localhost bin]$ ./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	转储以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  在转储中包括OID
  -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以带有列名的INSERT命令形式转储数据
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting取消美元 (符号) 引号, 使用 SQL 标准引号
  --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  以INSERT命令，而不是COPY命令的形式转储数据
  --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 使用SET会话授权命令而不是ALTER OWNER命令设置所有权

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.

pg_restore介绍

[peter@localhost bin]$ ./pg_restore --help
pg_restore restores a PostgreSQL database from an archive created by pg_dump.

(pg_restore从pg_dump创建的存档中还原PostgreSQL数据库)

Usage:
  pg_restore [OPTION]... [FILE]

提示：pg_restore 选项参考pg_dump选项。

psql介绍

[peter@localhost bin]$ psql --help
psql is the PostgreSQL interactive terminal.（psql是PostgreSQL交互终端。）

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:(一般选项)
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "peter")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
  -V, --version            output version information, then exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive)
  -?, --help[=options]     show this help, then exit
      --help=commands      list backslash commands, then exit
      --help=variables     list special variables, then exit

Input and output options:（输入输出选项）
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:（输出格式选项）
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING
                           field separator for unaligned output (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           record separator for unaligned output (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator for unaligned output to zero byte
  -0, --record-separator-zero
                           set record separator for unaligned output to zero byte

Connection options:（连接选项）
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "peter")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

For more information, type "\?" (for internal commands) or "\help" (for SQL commands) from within psql, or consult the psql section in the PostgreSQL documentation.

例子

纯文本格式的脚本

• 只导出postgres数据库的数据，不包括模式 -s

  pg_dump -U postgres -f /home/peter/Desktop/pg1.sql -s postgres

• 导出postgres数据库（包括数据）

  pg_dump -U postgres -f /home/peter/Desktop/pg2.sql postgres

• 导出postgres数据库中表test1的数据

  pg_dump -U postgres -f /home/peter/Desktop/t1.sql -t test1 postgres

• 导出postgres数据库中表test1的数据,以insert语句的形式

  pg_dump -U postgres -f /home/peter/Desktop/t2.sql -t test1 --column-inserts postgres

  相关代码如下

[peter@localhost Desktop]$ cd /opt/pg10.4/bin
[peter@localhost bin]$ ./pg_dump -U postgres -f /home/peter/Desktop/pg1.sql -s postgres
[peter@localhost bin]$ ./pg_dump -U postgres -f /home/peter/Desktop/pg2.sql  postgres
[peter@localhost bin]$ ./pg_dump -U postgres -f /home/peter/Desktop/t1.sql  -t test1 postgres
[peter@localhost bin]$ ./pg_dump -U postgres -f /home/peter/Desktop/t2.sql  -t test1 --column-inserts postgres

• 恢复数据到postgres10数据库

psql -U postgres -f /home/peter/Desktop/pg2.sql postgres10

使用归档文件格式的脚本

使用pg_restore纯文本恢复纯文本格式的脚本，无法恢复

[peter@localhost bin]$ pg_restore -U postgres -d postgres10 /home/peter/Desktop/t2.sql
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

创建测试表格

create table tbl_test (id int, info text, c_time timestamp);
insert into tbl_test select generate_series(1,100000),md5(random()::text),clock_timestamp();

• 备份归档文件格式：-F t(备份下来有6.3MB)
  pg_dump -U postgres -F t -f /home/peter/Desktop/vendemo.tar -t tbl_test postgres

• 恢复到postgres10数据库：
  pg_restore -U postgres -d postgres10 /home/peter/Desktop/vendemo.tar

• 备份归档文件格式：-F c(备份下来有2.5MB)
  pg_dump -U postgres -F c -f /home/peter/Desktop/vendemo1.tar -t tbl_test postgres

• 恢复到postgres10数据库：
  pg_restore -U postgres -d postgres10 /home/peter/Desktop/vendemo1.tar

压缩备份与恢复

处理大数据库

使用压缩的转储. 使用你熟悉的压缩程序，比如说 gzip。

• 备份：
  pg_dump -U postgres postgres | gzip > /home/peter/Desktop/pg.gz
• 恢复：
  gunzip -c /home/peter/Desktop/pg.gz | psql -U postgres postgres10
  或者
  cat /home/peter/Desktop/pg.gz | gunzip | psql -U postgres postgres10

使用split,split 命令允许你 你用下面的方法把输出分解成操作系统可以接受的大小。 比如，让每个块大小为 2 兆字节：

• 备份：
  pg_dump -U postgres -d postgres | split -b 2000k - /home/peter/Desktop/vend

导出来的样子是
   vendaa 2000k
   vendab 2000k
   vendac 2000k
   vendad 434.5k

• 恢复：
  cat /home/peter/Desktop/vend* | psql -U postgres postgres10