• postgresql逻辑备份工具pg_dump和pg_resotre学习

    (一)pg_dump备份

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

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

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

    (二)pg_restore恢复

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

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

    (三)使用pg_dump备份的例子

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

    1. [postgres@pg01 pg_backup]$ pg_dump --username=lijiaman --password --host=192.168.10.11 --port=5432 --dbname=db1 --format=custom --file=/home/postgres/pg_backup/db1.dump --verbose
    2. 口令:
    3. pg_dump: 最后的内置 OID 是 16383
    4. pg_dump: 读扩展
    5. pg_dump: 识别扩展成员
    6. pg_dump: 读取模式
    7. pg_dump: 读取用户定义表
    8. pg_dump: 读取用户定义函数
    9. pg_dump: 读取用户定义类型
    10. pg_dump: 读取过程语言
    11. pg_dump: 读取用户定义聚集函数
    12. pg_dump: 读取用户定义操作符
    13. pg_dump: 读取用户定义的访问方法
    14. pg_dump: 读取用户定义操作符集
    15. pg_dump: 读取用户定义操作符
    16. pg_dump: 读取用户定义的文本搜索解析器
    17. pg_dump: 读取用户定义的文本搜索模板
    18. pg_dump: 读取用户定义的文本搜索字典
    19. pg_dump: 读取用户定义的文本搜索配置
    20. pg_dump: 读取用户定义外部数据封装器
    21. pg_dump: 读取用户定义的外部服务器
    22. pg_dump: 正在读取缺省权限
    23. pg_dump: 读取用户定义的校对函数
    24. pg_dump: 读取用户定义的字符集转换
    25. pg_dump: 读取类型转换
    26. pg_dump: 读取转换
    27. pg_dump: 读取表继承信息
    28. pg_dump: 读取事件触发器
    29. pg_dump: 查找扩展表
    30. pg_dump: 正在查找关系继承
    31. pg_dump: 正在读取感兴趣表的列信息
    32. pg_dump: 正在查找表"public.t1"的列和类型
    33. pg_dump: 正在查找表"public.v1"的列和类型
    34. pg_dump: 正在查找表"public.t2"的列和类型
    35. pg_dump: 正在查找表"public.t2"的默认表达式
    36. pg_dump: 正在查找表"schema1.t1"的列和类型
    37. pg_dump: 正在查找表"schema1.t2"的列和类型
    38. pg_dump: 正在查找表"schema1.v_t2"的列和类型
    39. pg_dump: 在子表里标记继承字段
    40. pg_dump: 读取索引
    41. pg_dump: 为表"public.t1"读取索引
    42. pg_dump: 为表"public.t2"读取索引
    43. pg_dump: 为表"schema1.t1"读取索引
    44. pg_dump: 为表"schema1.t2"读取索引
    45. pg_dump: 在分区表中标记索引
    46. pg_dump: 读取扩展统计信息
    47. pg_dump: 读取约束
    48. pg_dump: 读取触发器
    49. pg_dump: 读取重写规则
    50. pg_dump: 读取策略
    51. pg_dump: 为表"public.t1"读取行安全性启用状态
    52. pg_dump: 为表"public.t1"读取策略
    53. pg_dump: 为表"public.v1"读取行安全性启用状态
    54. pg_dump: 为表"public.v1"读取策略
    55. pg_dump: 为表"public.seq1"读取行安全性启用状态
    56. pg_dump: 为表"public.seq1"读取策略
    57. pg_dump: 为表"public.t2_id_seq"读取行安全性启用状态
    58. pg_dump: 为表"public.t2_id_seq"读取策略
    59. pg_dump: 为表"public.t2"读取行安全性启用状态
    60. pg_dump: 为表"public.t2"读取策略
    61. pg_dump: 为表"schema1.t1"读取行安全性启用状态
    62. pg_dump: 为表"schema1.t1"读取策略
    63. pg_dump: 为表"schema1.t2"读取行安全性启用状态
    64. pg_dump: 为表"schema1.t2"读取策略
    65. pg_dump: 为表"schema1.v_t2"读取行安全性启用状态
    66. pg_dump: 为表"schema1.v_t2"读取策略
    67. pg_dump: 为表"schema1.seq1"读取行安全性启用状态
    68. pg_dump: 为表"schema1.seq1"读取策略
    69. pg_dump: 读取发布
    70. pg_dump: 读取发布成员资格
    71. pg_dump: 为表"public.t1"读取发行会员资格
    72. pg_dump: 为表"public.t2"读取发行会员资格
    73. pg_dump: 为表"schema1.t1"读取发行会员资格
    74. pg_dump: 为表"schema1.t2"读取发行会员资格
    75. pg_dump: 读取订阅
    76. pg_dump: 正在读取大对象
    77. pg_dump: 读取从属数据
    78. pg_dump: 正在保存encoding = UTF8
    79. pg_dump: 正在保存standard_conforming_strings = on
    80. pg_dump: 正在保存search_path =
    81. pg_dump: 保存数据库定义
    82. pg_dump: 正在转储表"public.t1"的内容
    83. pg_dump: 正在转储表"public.t2"的内容
    84. pg_dump: 正在转储表"schema1.t1"的内容
    85. pg_dump: 正在转储表"schema1.t2"的内容

    (3.2)对db1数据库进行备份，保存为sql格式

    1. [postgres@pg01 pg_backup]$ pg_dump --username=lijiaman --password --host=192.168.10.11 --port=5432 --dbname=db1 --format=plain --file=/home/postgres/pg_backup/db1.sql --verbose
    2. 口令:
    3. pg_dump: 最后的内置 OID 是 16383
    4. pg_dump: 读扩展
    5. pg_dump: 识别扩展成员
    6. pg_dump: 读取模式
    7. pg_dump: 读取用户定义表
    8. pg_dump: 读取用户定义函数
    9. pg_dump: 读取用户定义类型
    10. pg_dump: 读取过程语言
    11. pg_dump: 读取用户定义聚集函数
    12. pg_dump: 读取用户定义操作符
    13. pg_dump: 读取用户定义的访问方法
    14. pg_dump: 读取用户定义操作符集
    15. pg_dump: 读取用户定义操作符
    16. pg_dump: 读取用户定义的文本搜索解析器
    17. pg_dump: 读取用户定义的文本搜索模板
    18. pg_dump: 读取用户定义的文本搜索字典
    19. pg_dump: 读取用户定义的文本搜索配置
    20. pg_dump: 读取用户定义外部数据封装器
    21. pg_dump: 读取用户定义的外部服务器
    22. pg_dump: 正在读取缺省权限
    23. pg_dump: 读取用户定义的校对函数
    24. pg_dump: 读取用户定义的字符集转换
    25. pg_dump: 读取类型转换
    26. pg_dump: 读取转换
    27. pg_dump: 读取表继承信息
    28. pg_dump: 读取事件触发器
    29. pg_dump: 查找扩展表
    30. pg_dump: 正在查找关系继承
    31. pg_dump: 正在读取感兴趣表的列信息
    32. pg_dump: 正在查找表"public.t1"的列和类型
    33. pg_dump: 正在查找表"public.v1"的列和类型
    34. pg_dump: 正在查找表"public.t2"的列和类型
    35. pg_dump: 正在查找表"public.t2"的默认表达式
    36. pg_dump: 正在查找表"schema1.t1"的列和类型
    37. pg_dump: 正在查找表"schema1.t2"的列和类型
    38. pg_dump: 正在查找表"schema1.v_t2"的列和类型
    39. pg_dump: 在子表里标记继承字段
    40. pg_dump: 读取索引
    41. pg_dump: 为表"public.t1"读取索引
    42. pg_dump: 为表"public.t2"读取索引
    43. pg_dump: 为表"schema1.t1"读取索引
    44. pg_dump: 为表"schema1.t2"读取索引
    45. pg_dump: 在分区表中标记索引
    46. pg_dump: 读取扩展统计信息
    47. pg_dump: 读取约束
    48. pg_dump: 读取触发器
    49. pg_dump: 读取重写规则
    50. pg_dump: 读取策略
    51. pg_dump: 为表"public.t1"读取行安全性启用状态
    52. pg_dump: 为表"public.t1"读取策略
    53. pg_dump: 为表"public.v1"读取行安全性启用状态
    54. pg_dump: 为表"public.v1"读取策略
    55. pg_dump: 为表"public.seq1"读取行安全性启用状态
    56. pg_dump: 为表"public.seq1"读取策略
    57. pg_dump: 为表"public.t2_id_seq"读取行安全性启用状态
    58. pg_dump: 为表"public.t2_id_seq"读取策略
    59. pg_dump: 为表"public.t2"读取行安全性启用状态
    60. pg_dump: 为表"public.t2"读取策略
    61. pg_dump: 为表"schema1.t1"读取行安全性启用状态
    62. pg_dump: 为表"schema1.t1"读取策略
    63. pg_dump: 为表"schema1.t2"读取行安全性启用状态
    64. pg_dump: 为表"schema1.t2"读取策略
    65. pg_dump: 为表"schema1.v_t2"读取行安全性启用状态
    66. pg_dump: 为表"schema1.v_t2"读取策略
    67. pg_dump: 为表"schema1.seq1"读取行安全性启用状态
    68. pg_dump: 为表"schema1.seq1"读取策略
    69. pg_dump: 读取发布
    70. pg_dump: 读取发布成员资格
    71. pg_dump: 为表"public.t1"读取发行会员资格
    72. pg_dump: 为表"public.t2"读取发行会员资格
    73. pg_dump: 为表"schema1.t1"读取发行会员资格
    74. pg_dump: 为表"schema1.t2"读取发行会员资格
    75. pg_dump: 读取订阅
    76. pg_dump: 正在读取大对象
    77. pg_dump: 读取从属数据
    78. pg_dump: 正在保存encoding = UTF8
    79. pg_dump: 正在保存standard_conforming_strings = on
    80. pg_dump: 正在保存search_path =
    81. pg_dump: 创建SCHEMA "schema1"
    82. pg_dump: 创建SCHEMA "schema2"
    83. pg_dump: 创建TYPE "public.mood"
    84. pg_dump: 创建FUNCTION "public.variadic_example(numeric[])"
    85. pg_dump: 创建SEQUENCE "public.seq1"
    86. pg_dump: 创建TABLE "public.t1"
    87. pg_dump: 创建TABLE "public.t2"
    88. pg_dump: 创建SEQUENCE "public.t2_id_seq"
    89. pg_dump: 创建SEQUENCE OWNED BY "public.t2_id_seq"
    90. pg_dump: 创建VIEW "public.v1"
    91. pg_dump: 创建SEQUENCE "schema1.seq1"
    92. pg_dump: 创建TABLE "schema1.t1"
    93. pg_dump: 创建TABLE "schema1.t2"
    94. pg_dump: 创建VIEW "schema1.v_t2"
    95. pg_dump: 创建DEFAULT "public.t2 id"
    96. pg_dump: 为表"public.t1"处理数据
    97. pg_dump: 正在转储表"public.t1"的内容
    98. pg_dump: 为表"public.t2"处理数据
    99. pg_dump: 正在转储表"public.t2"的内容
    100. pg_dump: 为表"schema1.t1"处理数据
    101. pg_dump: 正在转储表"schema1.t1"的内容
    102. pg_dump: 为表"schema1.t2"处理数据
    103. pg_dump: 正在转储表"schema1.t2"的内容
    104. pg_dump: 执行 SEQUENCE SET seq1
    105. pg_dump: 执行 SEQUENCE SET t2_id_seq
    106. pg_dump: 执行 SEQUENCE SET seq1
    107. pg_dump: 创建CONSTRAINT "public.t2 t2_pkey"
    108. pg_dump: 创建CONSTRAINT "schema1.t1 t1_pkey"
    109. pg_dump: 创建INDEX "public.idx_id"
    110. pg_dump: 创建INDEX "schema1.idx_age"
    111. pg_dump: 创建ACL "public.TABLE t1"

    查看其备份结果：

    1. [postgres@pg01 pg_backup]$ cat db1.sql
    2. --
    3. -- PostgreSQL database dump
    4. --
    5.  
    6. -- Dumped from database version 12.5
    7. -- Dumped by pg_dump version 12.5
    8.  
    9. -- Started on 2022-08-05 04:42:07 CST
    10.  
    11. SET statement_timeout = 0;
    12. SET lock_timeout = 0;
    13. SET idle_in_transaction_session_timeout = 0;
    14. SET client_encoding = 'UTF8';
    15. SET standard_conforming_strings = on;
    16. SELECT pg_catalog.set_config('search_path', '', false);
    17. SET check_function_bodies = false;
    18. SET xmloption = content;
    19. SET client_min_messages = warning;
    20. SET row_security = off;
    21.  
    22. --
    23. -- TOC entry 9 (class 2615 OID 16405)
    24. -- Name: schema1; Type: SCHEMA; Schema: -; Owner: postgres
    25. --
    26.  
    27. CREATE SCHEMA schema1;
    28.  
    29.  
    30. ALTER SCHEMA schema1 OWNER TO postgres;
    31.  
    32. --
    33. -- TOC entry 6 (class 2615 OID 16406)
    34. -- Name: schema2; Type: SCHEMA; Schema: -; Owner: postgres
    35. --
    36.  
    37. CREATE SCHEMA schema2;
    38.  
    39.  
    40. ALTER SCHEMA schema2 OWNER TO postgres;
    41.  
    42. --
    43. -- TOC entry 639 (class 1247 OID 16397)
    44. -- Name: mood; Type: TYPE; Schema: public; Owner: postgres
    45. --
    46.  
    47. CREATE TYPE public.mood AS ENUM (
    48.     'sad',
    49.     'ok',
    50.     'happy'
    51. );
    52.  
    53.  
    54. ALTER TYPE public.mood OWNER TO postgres;
    55.  
    56. --
    57. -- TOC entry 213 (class 1255 OID 16451)
    58. -- Name: variadic_example(numeric[]); Type: FUNCTION; Schema: public; Owner: postgres
    59. --
    60.  
    61. CREATE FUNCTION public.variadic_example(VARIADIC numeric[]) RETURNS integer
    62.     LANGUAGE sql
    63.     AS $$SELECT 1$$;
    64.  
    65.  
    66. ALTER FUNCTION public.variadic_example(VARIADIC numeric[]) OWNER TO postgres;
    67.  
    68. --
    69. -- TOC entry 206 (class 1259 OID 16392)
    70. -- Name: seq1; Type: SEQUENCE; Schema: public; Owner: postgres
    71. --
    72.  
    73. CREATE SEQUENCE public.seq1
    74.     AS integer
    75.     START WITH 5
    76.     INCREMENT BY 1
    77.     NO MINVALUE
    78.     MAXVALUE 10
    79.     CACHE 2;
    80.  
    81.  
    82. ALTER TABLE public.seq1 OWNER TO postgres;
    83.  
    84. SET default_tablespace = '';
    85.  
    86. SET default_table_access_method = heap;
    87.  
    88. --
    89. -- TOC entry 204 (class 1259 OID 16385)
    90. -- Name: t1; Type: TABLE; Schema: public; Owner: postgres
    91. --
    92.  
    93. CREATE TABLE public.t1 (
    94.     id integer,
    95.     name character varying(50)
    96. );
    97.  
    98.  
    99. ALTER TABLE public.t1 OWNER TO postgres;
    100.  
    101. --
    102. -- TOC entry 208 (class 1259 OID 16409)
    103. -- Name: t2; Type: TABLE; Schema: public; Owner: postgres
    104. --
    105.  
    106. CREATE TABLE public.t2 (
    107.     id integer NOT NULL,
    108.     name character varying(20)
    109. );
    110.  
    111.  
    112. ALTER TABLE public.t2 OWNER TO postgres;
    113.  
    114. --
    115. -- TOC entry 207 (class 1259 OID 16407)
    116. -- Name: t2_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
    117. --
    118.  
    119. CREATE SEQUENCE public.t2_id_seq
    120.     AS integer
    121.     START WITH 1
    122.     INCREMENT BY 1
    123.     NO MINVALUE
    124.     NO MAXVALUE
    125.     CACHE 1;
    126.  
    127.  
    128. ALTER TABLE public.t2_id_seq OWNER TO postgres;
    129.  
    130. --
    131. -- TOC entry 3734 (class 0 OID 0)
    132. -- Dependencies: 207
    133. -- Name: t2_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
    134. --
    135.  
    136. ALTER SEQUENCE public.t2_id_seq OWNED BY public.t2.id;
    137.  
    138.  
    139. --
    140. -- TOC entry 205 (class 1259 OID 16388)
    141. -- Name: v1; Type: VIEW; Schema: public; Owner: postgres
    142. --
    143.  
    144. CREATE VIEW public.v1 AS
    145.  SELECT t1.id,
    146.     t1.name
    147.    FROM public.t1;
    148.  
    149.  
    150. ALTER TABLE public.v1 OWNER TO postgres;
    151.  
    152. --
    153. -- TOC entry 212 (class 1259 OID 16452)
    154. -- Name: seq1; Type: SEQUENCE; Schema: schema1; Owner: postgres
    155. --
    156.  
    157. CREATE SEQUENCE schema1.seq1
    158.     START WITH 1
    159.     INCREMENT BY 1
    160.     NO MINVALUE
    161.     NO MAXVALUE
    162.     CACHE 1;
    163.  
    164.  
    165. ALTER TABLE schema1.seq1 OWNER TO postgres;
    166.  
    167. --
    168. -- TOC entry 209 (class 1259 OID 16438)
    169. -- Name: t1; Type: TABLE; Schema: schema1; Owner: lijiaman
    170. --
    171.  
    172. CREATE TABLE schema1.t1 (
    173.     id integer NOT NULL,
    174.     name character varying(50)
    175. );
    176.  
    177.  
    178. ALTER TABLE schema1.t1 OWNER TO lijiaman;
    179.  
    180. --
    181. -- TOC entry 210 (class 1259 OID 16441)
    182. -- Name: t2; Type: TABLE; Schema: schema1; Owner: postgres
    183. --
    184.  
    185. CREATE TABLE schema1.t2 (
    186.     id integer,
    187.     age integer,
    188.     address character varying(100)
    189. );
    190.  
    191.  
    192. ALTER TABLE schema1.t2 OWNER TO postgres;
    193.  
    194. --
    195. -- TOC entry 211 (class 1259 OID 16444)
    196. -- Name: v_t2; Type: VIEW; Schema: schema1; Owner: postgres
    197. --
    198.  
    199. CREATE VIEW schema1.v_t2 AS
    200.  SELECT t2.id,
    201.     t2.age,
    202.     t2.address
    203.    FROM schema1.t2
    204.   WHERE (t2.id > 1);
    205.  
    206.  
    207. ALTER TABLE schema1.v_t2 OWNER TO postgres;
    208.  
    209. --
    210. -- TOC entry 3586 (class 2604 OID 16412)
    211. -- Name: t2 id; Type: DEFAULT; Schema: public; Owner: postgres
    212. --
    213.  
    214. ALTER TABLE ONLY public.t2 ALTER COLUMN id SET DEFAULT nextval('public.t2_id_seq'::regclass);
    215.  
    216.  
    217. --
    218. -- TOC entry 3721 (class 0 OID 16385)
    219. -- Dependencies: 204
    220. -- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres
    221. --
    222.  
    223. COPY public.t1 (id, name) FROM stdin;
    224. 1       a
    225. \.
    226.  
    227.  
    228. --
    229. -- TOC entry 3724 (class 0 OID 16409)
    230. -- Dependencies: 208
    231. -- Data for Name: t2; Type: TABLE DATA; Schema: public; Owner: postgres
    232. --
    233.  
    234. COPY public.t2 (id, name) FROM stdin;
    235. 1       a
    236. 2       b
    237. \.
    238.  
    239.  
    240. --
    241. -- TOC entry 3725 (class 0 OID 16438)
    242. -- Dependencies: 209
    243. -- Data for Name: t1; Type: TABLE DATA; Schema: schema1; Owner: lijiaman
    244. --
    245.  
    246. COPY schema1.t1 (id, name) FROM stdin;
    247. 1       a
    248. 2       b
    249. \.
    250.  
    251.  
    252. --
    253. -- TOC entry 3726 (class 0 OID 16441)
    254. -- Dependencies: 210
    255. -- Data for Name: t2; Type: TABLE DATA; Schema: schema1; Owner: postgres
    256. --
    257.  
    258. COPY schema1.t2 (id, age, address) FROM stdin;
    259. 1       11      beijing
    260. 2       12      shenzheng
    261. \.
    262.  
    263.  
    264. --
    265. -- TOC entry 3735 (class 0 OID 0)
    266. -- Dependencies: 206
    267. -- Name: seq1; Type: SEQUENCE SET; Schema: public; Owner: postgres
    268. --
    269.  
    270. SELECT pg_catalog.setval('public.seq1', 5, false);
    271.  
    272.  
    273. --
    274. -- TOC entry 3736 (class 0 OID 0)
    275. -- Dependencies: 207
    276. -- Name: t2_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
    277. --
    278.  
    279. SELECT pg_catalog.setval('public.t2_id_seq', 2, true);
    280.  
    281.  
    282. --
    283. -- TOC entry 3737 (class 0 OID 0)
    284. -- Dependencies: 212
    285. -- Name: seq1; Type: SEQUENCE SET; Schema: schema1; Owner: postgres
    286. --
    287.  
    288. SELECT pg_catalog.setval('schema1.seq1', 1, false);
    289.  
    290.  
    291. --
    292. -- TOC entry 3589 (class 2606 OID 16414)
    293. -- Name: t2 t2_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
    294. --
    295.  
    296. ALTER TABLE ONLY public.t2
    297.     ADD CONSTRAINT t2_pkey PRIMARY KEY (id);
    298.  
    299.  
    300. --
    301. -- TOC entry 3591 (class 2606 OID 16449)
    302. -- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: schema1; Owner: lijiaman
    303. --
    304.  
    305. ALTER TABLE ONLY schema1.t1
    306.     ADD CONSTRAINT t1_pkey PRIMARY KEY (id);
    307.  
    308.  
    309. --
    310. -- TOC entry 3587 (class 1259 OID 16424)
    311. -- Name: idx_id; Type: INDEX; Schema: public; Owner: postgres
    312. --
    313.  
    314. CREATE INDEX idx_id ON public.t1 USING btree (id);
    315.  
    316.  
    317. --
    318. -- TOC entry 3592 (class 1259 OID 16450)
    319. -- Name: idx_age; Type: INDEX; Schema: schema1; Owner: postgres
    320. --
    321.  
    322. CREATE INDEX idx_age ON schema1.t2 USING btree (age);
    323.  
    324.  
    325. --
    326. -- TOC entry 3733 (class 0 OID 0)
    327. -- Dependencies: 204
    328. -- Name: TABLE t1; Type: ACL; Schema: public; Owner: postgres
    329. --
    330.  
    331. GRANT ALL ON TABLE public.t1 TO lijiaman;
    332.  
    333.  
    334. -- Completed on 2022-08-05 04:42:10 CST
    335.  
    336. --
    337. -- PostgreSQL database dump complete
    338. --

    (3.3)备份db1数据库为sql文件，并使用--create选项创建带有create database db1语句的文件

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

    (3.4)备份多个表

    备份db1数据库中的schema1.t1和schema.t2表为sql文件

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

    结果如下：

    1. [postgres@pg01 pg_backup]$ cat  db1_schema1_t1_t2.sql
    2. --
    3. -- PostgreSQL database dump
    4. --
    5.  
    6. -- Dumped from database version 12.5
    7. -- Dumped by pg_dump version 12.5
    8.  
    9. -- Started on 2022-08-05 05:03:36 CST
    10.  
    11. SET statement_timeout = 0;
    12. SET lock_timeout = 0;
    13. SET idle_in_transaction_session_timeout = 0;
    14. SET client_encoding = 'UTF8';
    15. SET standard_conforming_strings = on;
    16. SELECT pg_catalog.set_config('search_path', '', false);
    17. SET check_function_bodies = false;
    18. SET xmloption = content;
    19. SET client_min_messages = warning;
    20. SET row_security = off;
    21.  
    22. SET default_tablespace = '';
    23.  
    24. SET default_table_access_method = heap;
    25.  
    26. --
    27. -- TOC entry 209 (class 1259 OID 16438)
    28. -- Name: t1; Type: TABLE; Schema: schema1; Owner: lijiaman
    29. --
    30.  
    31. CREATE TABLE schema1.t1 (
    32.     id integer NOT NULL,
    33.     name character varying(50)
    34. );
    35.  
    36.  
    37. ALTER TABLE schema1.t1 OWNER TO lijiaman;
    38.  
    39. --
    40. -- TOC entry 210 (class 1259 OID 16441)
    41. -- Name: t2; Type: TABLE; Schema: schema1; Owner: postgres
    42. --
    43.  
    44. CREATE TABLE schema1.t2 (
    45.     id integer,
    46.     age integer,
    47.     address character varying(100)
    48. );
    49.  
    50.  
    51. ALTER TABLE schema1.t2 OWNER TO postgres;
    52.  
    53. --
    54. -- TOC entry 3710 (class 0 OID 16438)
    55. -- Dependencies: 209
    56. -- Data for Name: t1; Type: TABLE DATA; Schema: schema1; Owner: lijiaman
    57. --
    58.  
    59. COPY schema1.t1 (id, name) FROM stdin;
    60. 1       a
    61. 2       b
    62. \.
    63.  
    64.  
    65. --
    66. -- TOC entry 3711 (class 0 OID 16441)
    67. -- Dependencies: 210
    68. -- Data for Name: t2; Type: TABLE DATA; Schema: schema1; Owner: postgres
    69. --
    70.  
    71. COPY schema1.t2 (id, age, address) FROM stdin;
    72. 1       11      beijing
    73. 2       12      shenzheng
    74. \.
    75.  
    76.  
    77. --
    78. -- TOC entry 3580 (class 2606 OID 16449)
    79. -- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: schema1; Owner: lijiaman
    80. --
    81.  
    82. ALTER TABLE ONLY schema1.t1
    83.     ADD CONSTRAINT t1_pkey PRIMARY KEY (id);
    84.  
    85.  
    86. --
    87. -- TOC entry 3581 (class 1259 OID 16450)
    88. -- Name: idx_age; Type: INDEX; Schema: schema1; Owner: postgres
    89. --
    90.  
    91. CREATE INDEX idx_age ON schema1.t2 USING btree (age);
    92.  
    93.  
    94. -- Completed on 2022-08-05 05:03:38 CST
    95.  
    96. --
    97. -- PostgreSQL database dump complete
    98. --

    (3.5)备份db1数据库的表schema1.t1，并使用--clean选项创建带有drop table、drop index的sql文件

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

    结果如下:

    1. [postgres@pg01 pg_backup]$ cat db1_schema1_t1.sql
    2. --
    3. -- PostgreSQL database dump
    4. --
    5.  
    6. -- Dumped from database version 12.5
    7. -- Dumped by pg_dump version 12.5
    8.  
    9. -- Started on 2022-08-05 05:12:33 CST
    10.  
    11. SET statement_timeout = 0;
    12. SET lock_timeout = 0;
    13. SET idle_in_transaction_session_timeout = 0;
    14. SET client_encoding = 'UTF8';
    15. SET standard_conforming_strings = on;
    16. SELECT pg_catalog.set_config('search_path', '', false);
    17. SET check_function_bodies = false;
    18. SET xmloption = content;
    19. SET client_min_messages = warning;
    20. SET row_security = off;
    21.  
    22. ALTER TABLE ONLY schema1.t1 DROP CONSTRAINT t1_pkey;
    23. DROP TABLE schema1.t1;
    24. SET default_tablespace = '';
    25.  
    26. SET default_table_access_method = heap;
    27.  
    28. --
    29. -- TOC entry 209 (class 1259 OID 16438)
    30. -- Name: t1; Type: TABLE; Schema: schema1; Owner: lijiaman
    31. --
    32.  
    33. CREATE TABLE schema1.t1 (
    34.     id integer NOT NULL,
    35.     name character varying(50)
    36. );
    37.  
    38.  
    39. ALTER TABLE schema1.t1 OWNER TO lijiaman;
    40.  
    41. --
    42. -- TOC entry 3709 (class 0 OID 16438)
    43. -- Dependencies: 209
    44. -- Data for Name: t1; Type: TABLE DATA; Schema: schema1; Owner: lijiaman
    45. --
    46.  
    47. COPY schema1.t1 (id, name) FROM stdin;
    48. 1       a
    49. 2       b
    50. \.
    51.  
    52.  
    53. --
    54. -- TOC entry 3580 (class 2606 OID 16449)
    55. -- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: schema1; Owner: lijiaman
    56. --
    57.  
    58. ALTER TABLE ONLY schema1.t1
    59.     ADD CONSTRAINT t1_pkey PRIMARY KEY (id);
    60.  
    61.  
    62. -- Completed on 2022-08-05 05:12:35 CST
    63.  
    64. --
    65. -- PostgreSQL database dump complete
    66. --

    (四)使用pg_restore恢复的例子

    特别注意：pg_restore [选项]... [文件名]，文件名直接跟在选项后面，不是--file来指定

    (4.1)恢复数据库db1

    pg_restore --username=lijiaman --password --host=192.168.10.11 --port=5432 --dbname=db1 --format=custom /home/postgres/pg_backup/db1.dump  --verbose

    (4.2)使用--clean选项导入时覆盖之前的表

    pg_restore --username=lijiaman --password --host=192.168.10.11 --port=5432 --dbname=db1 --format=custom /home/postgres/pg_backup/db1.dump  --verbose --clean

    (4.3)将之前导出的db1数据库恢复到db2

    1. -- 1.创建db2数据库
    2. db1=# create database db2;
    3. CREATE DATABASE
    4.  
    5. -- 2.使用之前db1数据库的备份，执行恢复到db2
    6. pg_restore --username=lijiaman --password --host=192.168.10.11 --port=5432 --dbname=db2 --format=custom /home/postgres/pg_backup/db1.dump  --verbose
    7.  
    8. -- 3.查看恢复情况
    9. db1=# \c db2
    10. 您现在已经连接到数据库 "db2",用户 "postgres".
    11. db2=# \dn
    12.     架构模式列表
    13.   名称   |  拥有者
    14. ---------+----------
    15.  public  | postgres
    16.  schema1 | postgres
    17.  schema2 | postgres
    18. (3 行记录)
    19.  
    20. db2=# \dt
    21.               关联列表
    22.  架构模式 | 名称 |  类型  |  拥有者
    23. ----------+------+--------+----------
    24.  public   | t1   | 数据表 | postgres
    25.  public   | t2   | 数据表 | postgres
    26. (2 行记录)
    27.  
    28. db2=# set search_path to schema1
    29. db2-# ;
    30. SET
    31. db2=# \dt
    32.               关联列表
    33.  架构模式 | 名称 |  类型  |  拥有者
    34. ----------+------+--------+----------
    35.  schema1  | t1   | 数据表 | lijiaman
    36.  schema1  | t2   | 数据表 | postgres
    37. (2 行记录)
    38.  
    39. db2=# \ds
    40.               关联列表
    41.  架构模式 | 名称 |  类型  |  拥有者
    42. ----------+------+--------+----------
    43.  schema1  | seq1 | 序列数 | postgres
    44. (1 行记录)

    (4.4)使用--schema-only选项，只恢复schema

    1. -- 1.创建数据库db4
    2. db1=# create database db4;
    3.  
    4. -- 2.只恢复表结构，视图等，不恢复里面的数据
    5. [postgres@pg01 pg_backup]$ pg_restore --username=lijiaman --password --host=192.168.10.11 --port=5432 --dbname=db4 --schema-only --format=custom /home/postgres/pg_backup/db1.dump  --verbose
    6.  
    7. -- 3.确认有表结构，无数据
    8. db1=# \c db4
    9. 您现在已经连接到数据库 "db4",用户 "lijiaman".
    10. db4=# \dt
    11.               关联列表
    12.  架构模式 | 名称 |  类型  |  拥有者
    13. ----------+------+--------+----------
    14.  public   | t1   | 数据表 | postgres
    15.  public   | t2   | 数据表 | postgres
    16. (2 行记录)
    17.  
    18. db4=# set search_path to schema1
    19. db4-# ;
    20. SET
    21. db4=# select * from t1;
    22.  id | name
    23. ----+------
    24. (0 行记录)

    (4.5)使用--data-only 选项，只恢复数据

    1. -- 1.在上一步基础上进行数据恢复
    2. [postgres@pg01 pg_backup]$ pg_restore --username=lijiaman --password --host=192.168.10.11 --port=5432 --dbname=db4 --data-only --format=custom /home/postgres/pg_backup/db1.dump  --verbose
    3. pg_restore: 为恢复数据库与数据库联接
    4. 口令:
    5. pg_restore: 为表"public.t1"处理数据
    6. pg_restore: 为表"public.t2"处理数据
    7. pg_restore: 为表"schema1.t1"处理数据
    8. pg_restore: 为表"schema1.t2"处理数据
    9. pg_restore: 执行 SEQUENCE SET seq1
    10. pg_restore: 执行 SEQUENCE SET t2_id_seq
    11. pg_restore: 执行 SEQUENCE SET seq1
    12. [postgres@pg01 pg_backup]$
    13.  
    14. -- 查看数据
    15. db4=# select * from t1;
    16.  id | name
    17. ----+------
    18.   1 | a
    19.   2 | b

    (4.6)只恢复schema1.t1和schema1.t2表到db5的schema1下面

    1. -- 1.创建数据库db1和schema1
    2. postgres=# create database db5;
    3. CREATE DATABASE
    4. postgres=# \c db5
    5. 您现在已经连接到数据库 "db5",用户 "lijiaman".
    6. db5=# create schema schema1;
    7. CREATE SCHEMA
    8.  
    9. -- 2.导入2个表
    10. [postgres@pg01 pg_backup]$ pg_restore --username=lijiaman --password --host=192.168.10.11 --port=5432 --dbname=db5 --schema=schema1 --table=t1 --table=t2 --format=custom /home/postgres/pg_backup/db1.dump  --verbose
    11. pg_restore: 为恢复数据库与数据库联接
    12. 口令:
    13. pg_restore: 创建TABLE "schema1.t1"
    14. pg_restore: 创建TABLE "schema1.t2"
    15. pg_restore: 为表"schema1.t1"处理数据
    16. pg_restore: 为表"schema1.t2"处理数据
    17. [postgres@pg01 pg_backup]$

    (五)psql恢复sql文件

    对于pg_dump备份出来的sql文件，直接执行sql文件即可恢复

    psql --username=lijiaman --password --host=192.168.10.11 --port=5432 --dbname=db1 --file=db1.sql

    附录

    测试数据

    1. \c db1    -- 切换到db1数据库
    2. set search_path to schema1;    -- 切换到schema1
    3.  
    4. -- 创建表
    5. create table t1(id int,name varchar(50));
    6. insert into t1 values(1,'a');
    7. insert into t1 values(2,'b');
    8. -- 创建主键约束
    9. alter table t1 add primary key(id);
    10.  
    11.  
    12. create table t2(id int,age int,address varchar(100));
    13. insert into t2 values(1,11,'beijing');
    14. insert into t2 values(2,12,'shenzheng');
    15. -- 创建索引
    16. create index idx_age on t2(age);
    17. -- 创建视图
    18. create view v_t2 as select * from t2 where id > 1;
    19. -- 创建序列
    20. create sequence seq1;
    21.  
    22. -- 创建函数
    23. CREATE FUNCTION public.variadic_example(VARIADIC numeric[]) RETURNS int
    24.   LANGUAGE sql AS 'SELECT 1';
  • 相关阅读:
    第一百四十二回 如何使用intl插件实现国际化
    计算机网络复习笔记——运输层
    高性能日志脱敏组件：已支持 log4j2 和 logback 插件
    离线数仓 (四) --------- 用户行为数据采集模块
    初识HashMap
    可视化设计：一文读懂桑基图，从来处来，到去出去。
    AI：37-基于深度学习的安全帽检测方法研究
    MySQL-Explain详解
    【AI】行业消息精选和分析（11月21日 星期二）
    EN 12259-3喷水灭火系统干式报警阀组件—CE认证
  • 原文地址：https://blog.csdn.net/weixin_30895723/article/details/126210418