pg_dump备份

pg提供了pg_dump和pg_dumpall命令进行数据库的备份，pg_dumpall是将整个pg集群转储到一个脚本文件中，而pg_dump命令可以选择一个数据库或者部分表进行备份。

pg_dump 把一个数据库转储为纯文本文件或者是其它格式，使用方法如下：

[postgres@pg01 ~]$ pg_dump --help
​
用法:
  pg_dump [选项]... [数据库名字]
​
**一般选项**:
  -f, --file=FILENAME          输出文件或目录名
  -F, --format=c|d|t|p         输出文件格式 (c=custom, d=directory, t=tar,p=plain,plain就是sql纯文本 (默认值))
  -j, --jobs=NUM               执行多个并行任务进行备份转储工作
  -v, --verbose                详细模式
  -V, --version                输出版本信息，然后退出
  -Z, --compress=0-9           被压缩格式的压缩级别，0表示不压缩
  --lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败
  --no-sync                    不用等待变化安全写入磁盘
  -?, --help                   显示此帮助, 然后退出
​
**控制输出内容选项(常用)**:
  -a, --data-only              只转储数据,不包括模式,只对纯文本输出有意义
  -s, --schema-only            只转储模式, 不包括数据
  -c, --clean                  在重新创建之前，先清除（删除）数据库对象，如drop table。只对纯文本输出有意义
  -C, --create                 指定输出文件中是否生成create database语句,只对纯文本输出有意义
  -n, --schema=PATTERN         指定要导出的schema，不指定则导出所有的非系统schema
  -N, --exclude-schema=PATTERN 排除导出哪些schema
  -O, --no-owner               在明文格式中, 忽略恢复对象所属者
  -t, --table=PATTERN          指定导出的表、视图、序列，可以使用多个-t匹配多个表，使用-t之后，-n和-N就失效了
  -T, --exclude-table=PATTERN  排除表
  -x, --no-privileges          不要转储权限 (grant/revoke)
​
  --disable-triggers           在只恢复数据的过程中禁用触发器 
  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)
  --if-exists                  当删除对象时使用IF EXISTS
  --inserts                    以INSERT命令，而不是COPY命令的形式转储数据，使用该选项可以把数据加载到非pg数据库，会使恢复非常慢
                               该选项为每行生成1个单独的insert命令，?在恢复过程中遇到错误，将会丢失1行而不是全部表数据
  --column-inserts             以带有列名的INSERT命令形式转储数据，例如insert into table_name(column,...) values(value1,...)                             
  --load-via-partition-root    通过根表加载分区
  --no-comments                不转储注释
  --no-tablespaces             不转储表空间分配信息
  --no-unlogged-table-data     不转储没有日志的表数据
  --on-conflict-do-nothing     将ON CONFLICT DO NOTHING添加到INSERT命令
​
**控制输出内容选项(不常用)**:
  -S, --superuser=NAME         指定关闭触发器时需要用到的超级用户名。 它只有在使用了--disable-triggers时才有影响。一般情况下，最好不要输入该参数，而是用 超级用户启动生成的脚本。
  -b, --blobs                  在转储中包括大对象
  -B, --no-blobs               排除转储中的大型对象 
  -E, --encoding=ENCODING      转储以ENCODING形式编码的数据
  --binary-upgrade             只能由升级工具使用
  --enable-row-security        启用行安全性（只转储用户能够访问的内容）
  --extra-float-digits=NUM     覆盖extra_float_digits的默认设置
  --disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号
  --no-publications            不转储发布
  --no-security-labels         不转储安全标签的分配
  --no-subscriptions           不转储订阅
  --no-synchronized-snapshots  在并行工作集中不使用同步快照
  --quote-all-identifiers      所有标识符加引号，即使不是关键字
  --rows-per-insert=NROWS      每个插入的行数；意味着--inserts
  --section=SECTION            备份命名的节 (数据前, 数据, 及 数据后)
  --serializable-deferrable    等到备份可以无异常运行
  --snapshot=SNAPSHOT          为转储使用给定的快照
  --strict-names               要求每个表和(或)schema包括模式以匹配至少一个实体
  --use-set-session-authorization
                               使用 SESSION AUTHORIZATION 命令代替
                               ALTER OWNER 命令来设置所有权
​
**联接选项**:
  -d, --dbname=DBNAME      对数据库 DBNAME备份
  -h, --host=主机名        数据库服务器的主机名或套接字目录
  -p, --port=端口号        数据库服务器的端口号
  -U, --username=名字      以指定的数据库用户联接
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  --role=ROLENAME          在转储前运行SET ROLE

pg_restore恢复

对于pg_dump的自定义备份custom和tar类型的备份，需要使用pg_restore进行恢复，pg_restore语法如下：

[postgres@pg01 pg_backup]$ pg_restore --help
pg_restore 从一个归档中恢复一个由 pg_dump 创建的 PostgreSQL 数据库.
​
用法:
  pg_restore [选项]... [文件名]
​
一般选项:
  -d, --dbname=名字        连接数据库名字
  -f, --file=文件名       输出文件名(- 对于stdout)
  -F, --format=c|d|t       备份文件格式(应该自动进行)
  -l, --list               打印归档文件的 TOC 概述
  -v, --verbose            详细模式
  -V, --version            输出版本信息, 然后退出
  -?, --help               显示此帮助, 然后退出
​
恢复控制选项:
  -a, --data-only              只恢复数据, 不包括模式
  -c, --clean                  在重新创建之前，先清除（删除）数据库对象
  -C, --create                 创建目标数据库
  -e, --exit-on-error          发生错误退出, 默认为继续
  -I, --index=NAME             恢复指定名称的索引
  -j, --jobs=NUM               执行多个并行任务进行恢复工作
  -L, --use-list=FILENAME      从这个文件中使用指定的内容表排序
                               输出
  -n, --schema=NAME            在这个模式中只恢复对象
  -N, --exclude-schema=NAME    不恢复此模式中的对象
  -O, --no-owner               不恢复对象所属者
  -P, --function=NAME(args)    恢复指定名字的函数
  -s, --schema-only            只恢复模式, 不包括数据
  -S, --superuser=NAME         使用指定的超级用户来禁用触发器
  -t, --table=NAME             恢复命名关系（表、视图等）
  -T, --trigger=NAME           恢复指定名字的触发器
  -x, --no-privileges          跳过处理权限的恢复 (grant/revoke)
  -1, --single-transaction     作为单个事务恢复
  --disable-triggers           在只恢复数据的过程中禁用触发器
  --enable-row-security        启用行安全性
  --if-exists                  当删除对象时使用IF EXISTS
  --no-comments                不恢复注释
  --no-data-for-failed-tables  对那些无法创建的表不进行
                               数据恢复
  --no-publications            不恢复发行
  --no-security-labels         不恢复安全标签信息
  --no-subscriptions           不恢复订阅
  --no-tablespaces             不恢复表空间的分配信息
  --section=SECTION            恢复命名节 (数据前、数据及数据后)
  --strict-names               要求每个表和(或)schema包括模式以匹配至少一个实体
  --use-set-session-authorization
                               使用 SESSION AUTHORIZATION 命令代替
                               ALTER OWNER 命令来设置所有权
​
联接选项:
  -h, --host=主机名        数据库服务器的主机名或套接字目录
  -p, --port=端口号        数据库服务器的端口号
  -U, --username=名字      以指定的数据库用户联接
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  --role=ROLENAME          在恢复前执行SET ROLE操作
​
选项 -I, -n, -N, -P, -t, -T, 以及 --section 可以组合使用和指定
多次用于选择多个对象.
​
如果没有提供输入文件名, 则使用标准输入.

对fxj库进行备份，保存为sql格式

/data/pg_install/bin/pg_dump --username=fxj --password --host=x.x.x.x --port=8836 --dbname=fxj --format=plain --file=a.sql --verbose

对fxj库进行备份，保存为sql格式，带有create database操作

[postgres@pg01 pg_backup]$ pg_dump --username=fxj --password --host=x.x.x.x --port=8836 --dbname=db1 --format=plain --file=/home/postgres/pg_backup/db1_create.sql --create --verbose
# 可以在导出的sql文件中看到创建数据库的语句 
CREATE DATABASE db1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8'; 
ALTER DATABASE db1 OWNER TO postgres;

对fxj库进行备份，保存为sql格式使用--clean选项创建带有drop table、drop index的sql文件

[postgres@pg01 pg_backup]$ pg_dump --username=fxj --password --host=x.x.x.x --port=8836 --dbname=db1 --table=schema1.t1 --format=plain --file=/home/postgres/pg_backup/db1_schema1_t1.sql --verbose --clean

对db1数据库进行备份，保存为转储dmp格式

pg_dump --username=fxj --password --host=x.x.x.x --port=8836 --dbname=fxj --format=custom --file=/data/pg_backup/fxj.dump --verbose

备份多个表

[postgres@pg01 pg_backup]$ pg_dump --username=fxj --password --host=x.x.x.x --port=8836 --dbname=db1 --table=schema1.t1  --table=schema1.t2 --format=plain --file=/home/postgres/pg_backup/db1_schema1_t1_t2.sql --verbose

使用pg_restore恢复

跨db恢复

把备份fxj库的数据恢复到fxj1库，注意要用dump格式的文件，不能用sql文件，sql文件只能恢复到备份的fxj库里面，另外也要先建好fxj1库。

/data/pg_install/bin/psql -d fxj -p 8836 -W -U fxj -h x.x.x.x      
 Password:  
 psql (12.4) 
 Type "help" for help. 
 fxj=# create database fxj1; 
 CREATE DATABASE 
 fxj=# \q 
 [root@VM-8-17-centos pg_backup]# /data/pg_install/bin/pg_restore --username=fxj --password --host=x.x.x.x --port=8836 --dbname=fxj1 --format=custom  /data/pg_backup/fxj.dump  --verbose 
 pg_restore: connecting to database for restore 
 Password:  
 pg_restore: creating TABLE "public.fxj" 
 pg_restore: processing data for table "public.fxj"

恢复sql文件

这里是使用psql工具恢复，不是用pg_restore恢复

删掉fxj文件，然后恢复

/data/pg_install/bin/psql --username=fxj --password --host=x.x.x.x --port=8836 --dbname=fxj --file=/data/pg_data/a.sql

其他恢复

使用--schema-only只恢复schema

使用--data-only只恢复数据