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

2022/8/8 2:22:55

编程Tag： postgresql T2 dump public -- PG schema1

本文主要是介绍postgresql逻辑备份工具pg_dump和pg_resotre学习，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

（一）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 可以组合使用和指定
多次用于选择多个对象.

如果没有提供输入文件名, 则使用标准输入.

（三）使用pg_dump备份的例子

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

[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
口令:
pg_dump: 最后的内置 OID 是 16383
pg_dump: 读扩展
pg_dump: 识别扩展成员
pg_dump: 读取模式
pg_dump: 读取用户定义表
pg_dump: 读取用户定义函数
pg_dump: 读取用户定义类型
pg_dump: 读取过程语言
pg_dump: 读取用户定义聚集函数
pg_dump: 读取用户定义操作符
pg_dump: 读取用户定义的访问方法
pg_dump: 读取用户定义操作符集
pg_dump: 读取用户定义操作符
pg_dump: 读取用户定义的文本搜索解析器
pg_dump: 读取用户定义的文本搜索模板
pg_dump: 读取用户定义的文本搜索字典
pg_dump: 读取用户定义的文本搜索配置
pg_dump: 读取用户定义外部数据封装器
pg_dump: 读取用户定义的外部服务器
pg_dump: 正在读取缺省权限
pg_dump: 读取用户定义的校对函数
pg_dump: 读取用户定义的字符集转换
pg_dump: 读取类型转换
pg_dump: 读取转换
pg_dump: 读取表继承信息
pg_dump: 读取事件触发器
pg_dump: 查找扩展表
pg_dump: 正在查找关系继承
pg_dump: 正在读取感兴趣表的列信息
pg_dump: 正在查找表"public.t1"的列和类型
pg_dump: 正在查找表"public.v1"的列和类型
pg_dump: 正在查找表"public.t2"的列和类型
pg_dump: 正在查找表"public.t2"的默认表达式
pg_dump: 正在查找表"schema1.t1"的列和类型
pg_dump: 正在查找表"schema1.t2"的列和类型
pg_dump: 正在查找表"schema1.v_t2"的列和类型
pg_dump: 在子表里标记继承字段
pg_dump: 读取索引
pg_dump: 为表"public.t1"读取索引
pg_dump: 为表"public.t2"读取索引
pg_dump: 为表"schema1.t1"读取索引
pg_dump: 为表"schema1.t2"读取索引
pg_dump: 在分区表中标记索引
pg_dump: 读取扩展统计信息
pg_dump: 读取约束
pg_dump: 读取触发器
pg_dump: 读取重写规则
pg_dump: 读取策略
pg_dump: 为表"public.t1"读取行安全性启用状态
pg_dump: 为表"public.t1"读取策略
pg_dump: 为表"public.v1"读取行安全性启用状态
pg_dump: 为表"public.v1"读取策略
pg_dump: 为表"public.seq1"读取行安全性启用状态
pg_dump: 为表"public.seq1"读取策略
pg_dump: 为表"public.t2_id_seq"读取行安全性启用状态
pg_dump: 为表"public.t2_id_seq"读取策略
pg_dump: 为表"public.t2"读取行安全性启用状态
pg_dump: 为表"public.t2"读取策略
pg_dump: 为表"schema1.t1"读取行安全性启用状态
pg_dump: 为表"schema1.t1"读取策略
pg_dump: 为表"schema1.t2"读取行安全性启用状态
pg_dump: 为表"schema1.t2"读取策略
pg_dump: 为表"schema1.v_t2"读取行安全性启用状态
pg_dump: 为表"schema1.v_t2"读取策略
pg_dump: 为表"schema1.seq1"读取行安全性启用状态
pg_dump: 为表"schema1.seq1"读取策略
pg_dump: 读取发布
pg_dump: 读取发布成员资格
pg_dump: 为表"public.t1"读取发行会员资格
pg_dump: 为表"public.t2"读取发行会员资格
pg_dump: 为表"schema1.t1"读取发行会员资格
pg_dump: 为表"schema1.t2"读取发行会员资格
pg_dump: 读取订阅
pg_dump: 正在读取大对象
pg_dump: 读取从属数据
pg_dump: 正在保存encoding = UTF8
pg_dump: 正在保存standard_conforming_strings = on
pg_dump: 正在保存search_path =
pg_dump: 保存数据库定义
pg_dump: 正在转储表"public.t1"的内容
pg_dump: 正在转储表"public.t2"的内容
pg_dump: 正在转储表"schema1.t1"的内容
pg_dump: 正在转储表"schema1.t2"的内容

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

[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
口令:
pg_dump: 最后的内置 OID 是 16383
pg_dump: 读扩展
pg_dump: 识别扩展成员
pg_dump: 读取模式
pg_dump: 读取用户定义表
pg_dump: 读取用户定义函数
pg_dump: 读取用户定义类型
pg_dump: 读取过程语言
pg_dump: 读取用户定义聚集函数
pg_dump: 读取用户定义操作符
pg_dump: 读取用户定义的访问方法
pg_dump: 读取用户定义操作符集
pg_dump: 读取用户定义操作符
pg_dump: 读取用户定义的文本搜索解析器
pg_dump: 读取用户定义的文本搜索模板
pg_dump: 读取用户定义的文本搜索字典
pg_dump: 读取用户定义的文本搜索配置
pg_dump: 读取用户定义外部数据封装器
pg_dump: 读取用户定义的外部服务器
pg_dump: 正在读取缺省权限
pg_dump: 读取用户定义的校对函数
pg_dump: 读取用户定义的字符集转换
pg_dump: 读取类型转换
pg_dump: 读取转换
pg_dump: 读取表继承信息
pg_dump: 读取事件触发器
pg_dump: 查找扩展表
pg_dump: 正在查找关系继承
pg_dump: 正在读取感兴趣表的列信息
pg_dump: 正在查找表"public.t1"的列和类型
pg_dump: 正在查找表"public.v1"的列和类型
pg_dump: 正在查找表"public.t2"的列和类型
pg_dump: 正在查找表"public.t2"的默认表达式
pg_dump: 正在查找表"schema1.t1"的列和类型
pg_dump: 正在查找表"schema1.t2"的列和类型
pg_dump: 正在查找表"schema1.v_t2"的列和类型
pg_dump: 在子表里标记继承字段
pg_dump: 读取索引
pg_dump: 为表"public.t1"读取索引
pg_dump: 为表"public.t2"读取索引
pg_dump: 为表"schema1.t1"读取索引
pg_dump: 为表"schema1.t2"读取索引
pg_dump: 在分区表中标记索引
pg_dump: 读取扩展统计信息
pg_dump: 读取约束
pg_dump: 读取触发器
pg_dump: 读取重写规则
pg_dump: 读取策略
pg_dump: 为表"public.t1"读取行安全性启用状态
pg_dump: 为表"public.t1"读取策略
pg_dump: 为表"public.v1"读取行安全性启用状态
pg_dump: 为表"public.v1"读取策略
pg_dump: 为表"public.seq1"读取行安全性启用状态
pg_dump: 为表"public.seq1"读取策略
pg_dump: 为表"public.t2_id_seq"读取行安全性启用状态
pg_dump: 为表"public.t2_id_seq"读取策略
pg_dump: 为表"public.t2"读取行安全性启用状态
pg_dump: 为表"public.t2"读取策略
pg_dump: 为表"schema1.t1"读取行安全性启用状态
pg_dump: 为表"schema1.t1"读取策略
pg_dump: 为表"schema1.t2"读取行安全性启用状态
pg_dump: 为表"schema1.t2"读取策略
pg_dump: 为表"schema1.v_t2"读取行安全性启用状态
pg_dump: 为表"schema1.v_t2"读取策略
pg_dump: 为表"schema1.seq1"读取行安全性启用状态
pg_dump: 为表"schema1.seq1"读取策略
pg_dump: 读取发布
pg_dump: 读取发布成员资格
pg_dump: 为表"public.t1"读取发行会员资格
pg_dump: 为表"public.t2"读取发行会员资格
pg_dump: 为表"schema1.t1"读取发行会员资格
pg_dump: 为表"schema1.t2"读取发行会员资格
pg_dump: 读取订阅
pg_dump: 正在读取大对象
pg_dump: 读取从属数据
pg_dump: 正在保存encoding = UTF8
pg_dump: 正在保存standard_conforming_strings = on
pg_dump: 正在保存search_path =
pg_dump: 创建SCHEMA "schema1"
pg_dump: 创建SCHEMA "schema2"
pg_dump: 创建TYPE "public.mood"
pg_dump: 创建FUNCTION "public.variadic_example(numeric[])"
pg_dump: 创建SEQUENCE "public.seq1"
pg_dump: 创建TABLE "public.t1"
pg_dump: 创建TABLE "public.t2"
pg_dump: 创建SEQUENCE "public.t2_id_seq"
pg_dump: 创建SEQUENCE OWNED BY "public.t2_id_seq"
pg_dump: 创建VIEW "public.v1"
pg_dump: 创建SEQUENCE "schema1.seq1"
pg_dump: 创建TABLE "schema1.t1"
pg_dump: 创建TABLE "schema1.t2"
pg_dump: 创建VIEW "schema1.v_t2"
pg_dump: 创建DEFAULT "public.t2 id"
pg_dump: 为表"public.t1"处理数据
pg_dump: 正在转储表"public.t1"的内容
pg_dump: 为表"public.t2"处理数据
pg_dump: 正在转储表"public.t2"的内容
pg_dump: 为表"schema1.t1"处理数据
pg_dump: 正在转储表"schema1.t1"的内容
pg_dump: 为表"schema1.t2"处理数据
pg_dump: 正在转储表"schema1.t2"的内容
pg_dump: 执行 SEQUENCE SET seq1
pg_dump: 执行 SEQUENCE SET t2_id_seq
pg_dump: 执行 SEQUENCE SET seq1
pg_dump: 创建CONSTRAINT "public.t2 t2_pkey"
pg_dump: 创建CONSTRAINT "schema1.t1 t1_pkey"
pg_dump: 创建INDEX "public.idx_id"
pg_dump: 创建INDEX "schema1.idx_age"
pg_dump: 创建ACL "public.TABLE t1"

查看其备份结果：

[postgres@pg01 pg_backup]$ cat db1.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.5
-- Dumped by pg_dump version 12.5

-- Started on 2022-08-05 04:42:07 CST

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- TOC entry 9 (class 2615 OID 16405)
-- Name: schema1; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA schema1;


ALTER SCHEMA schema1 OWNER TO postgres;

--
-- TOC entry 6 (class 2615 OID 16406)
-- Name: schema2; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA schema2;


ALTER SCHEMA schema2 OWNER TO postgres;

--
-- TOC entry 639 (class 1247 OID 16397)
-- Name: mood; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.mood AS ENUM (
    'sad',
    'ok',
    'happy'
);


ALTER TYPE public.mood OWNER TO postgres;

--
-- TOC entry 213 (class 1255 OID 16451)
-- Name: variadic_example(numeric[]); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.variadic_example(VARIADIC numeric[]) RETURNS integer
    LANGUAGE sql
    AS $$SELECT 1$$;


ALTER FUNCTION public.variadic_example(VARIADIC numeric[]) OWNER TO postgres;

--
-- TOC entry 206 (class 1259 OID 16392)
-- Name: seq1; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.seq1
    AS integer
    START WITH 5
    INCREMENT BY 1
    NO MINVALUE
    MAXVALUE 10
    CACHE 2;


ALTER TABLE public.seq1 OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 204 (class 1259 OID 16385)
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t1 (
    id integer,
    name character varying(50)
);


ALTER TABLE public.t1 OWNER TO postgres;

--
-- TOC entry 208 (class 1259 OID 16409)
-- Name: t2; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t2 (
    id integer NOT NULL,
    name character varying(20)
);


ALTER TABLE public.t2 OWNER TO postgres;

--
-- TOC entry 207 (class 1259 OID 16407)
-- Name: t2_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.t2_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.t2_id_seq OWNER TO postgres;

--
-- TOC entry 3734 (class 0 OID 0)
-- Dependencies: 207
-- Name: t2_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.t2_id_seq OWNED BY public.t2.id;


--
-- TOC entry 205 (class 1259 OID 16388)
-- Name: v1; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW public.v1 AS
 SELECT t1.id,
    t1.name
   FROM public.t1;


ALTER TABLE public.v1 OWNER TO postgres;

--
-- TOC entry 212 (class 1259 OID 16452)
-- Name: seq1; Type: SEQUENCE; Schema: schema1; Owner: postgres
--

CREATE SEQUENCE schema1.seq1
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE schema1.seq1 OWNER TO postgres;

--
-- TOC entry 209 (class 1259 OID 16438)
-- Name: t1; Type: TABLE; Schema: schema1; Owner: lijiaman
--

CREATE TABLE schema1.t1 (
    id integer NOT NULL,
    name character varying(50)
);


ALTER TABLE schema1.t1 OWNER TO lijiaman;

--
-- TOC entry 210 (class 1259 OID 16441)
-- Name: t2; Type: TABLE; Schema: schema1; Owner: postgres
--

CREATE TABLE schema1.t2 (
    id integer,
    age integer,
    address character varying(100)
);


ALTER TABLE schema1.t2 OWNER TO postgres;

--
-- TOC entry 211 (class 1259 OID 16444)
-- Name: v_t2; Type: VIEW; Schema: schema1; Owner: postgres
--

CREATE VIEW schema1.v_t2 AS
 SELECT t2.id,
    t2.age,
    t2.address
   FROM schema1.t2
  WHERE (t2.id > 1);


ALTER TABLE schema1.v_t2 OWNER TO postgres;

--
-- TOC entry 3586 (class 2604 OID 16412)
-- Name: t2 id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.t2 ALTER COLUMN id SET DEFAULT nextval('public.t2_id_seq'::regclass);


--
-- TOC entry 3721 (class 0 OID 16385)
-- Dependencies: 204
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.t1 (id, name) FROM stdin;
1       a
\.


--
-- TOC entry 3724 (class 0 OID 16409)
-- Dependencies: 208
-- Data for Name: t2; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.t2 (id, name) FROM stdin;
1       a
2       b
\.


--
-- TOC entry 3725 (class 0 OID 16438)
-- Dependencies: 209
-- Data for Name: t1; Type: TABLE DATA; Schema: schema1; Owner: lijiaman
--

COPY schema1.t1 (id, name) FROM stdin;
1       a
2       b
\.


--
-- TOC entry 3726 (class 0 OID 16441)
-- Dependencies: 210
-- Data for Name: t2; Type: TABLE DATA; Schema: schema1; Owner: postgres
--

COPY schema1.t2 (id, age, address) FROM stdin;
1       11      beijing
2       12      shenzheng
\.


--
-- TOC entry 3735 (class 0 OID 0)
-- Dependencies: 206
-- Name: seq1; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.seq1', 5, false);


--
-- TOC entry 3736 (class 0 OID 0)
-- Dependencies: 207
-- Name: t2_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.t2_id_seq', 2, true);


--
-- TOC entry 3737 (class 0 OID 0)
-- Dependencies: 212
-- Name: seq1; Type: SEQUENCE SET; Schema: schema1; Owner: postgres
--

SELECT pg_catalog.setval('schema1.seq1', 1, false);


--
-- TOC entry 3589 (class 2606 OID 16414)
-- Name: t2 t2_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.t2
    ADD CONSTRAINT t2_pkey PRIMARY KEY (id);


--
-- TOC entry 3591 (class 2606 OID 16449)
-- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: schema1; Owner: lijiaman
--

ALTER TABLE ONLY schema1.t1
    ADD CONSTRAINT t1_pkey PRIMARY KEY (id);


--
-- TOC entry 3587 (class 1259 OID 16424)
-- Name: idx_id; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_id ON public.t1 USING btree (id);


--
-- TOC entry 3592 (class 1259 OID 16450)
-- Name: idx_age; Type: INDEX; Schema: schema1; Owner: postgres
--

CREATE INDEX idx_age ON schema1.t2 USING btree (age);


--
-- TOC entry 3733 (class 0 OID 0)
-- Dependencies: 204
-- Name: TABLE t1; Type: ACL; Schema: public; Owner: postgres
--

GRANT ALL ON TABLE public.t1 TO lijiaman;


-- Completed on 2022-08-05 04:42:10 CST

--
-- PostgreSQL database dump complete
--

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

[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
# 可以在导出的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;

（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

结果如下：

[postgres@pg01 pg_backup]$ cat  db1_schema1_t1_t2.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.5
-- Dumped by pg_dump version 12.5

-- Started on 2022-08-05 05:03:36 CST

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 209 (class 1259 OID 16438)
-- Name: t1; Type: TABLE; Schema: schema1; Owner: lijiaman
--

CREATE TABLE schema1.t1 (
    id integer NOT NULL,
    name character varying(50)
);


ALTER TABLE schema1.t1 OWNER TO lijiaman;

--
-- TOC entry 210 (class 1259 OID 16441)
-- Name: t2; Type: TABLE; Schema: schema1; Owner: postgres
--

CREATE TABLE schema1.t2 (
    id integer,
    age integer,
    address character varying(100)
);


ALTER TABLE schema1.t2 OWNER TO postgres;

--
-- TOC entry 3710 (class 0 OID 16438)
-- Dependencies: 209
-- Data for Name: t1; Type: TABLE DATA; Schema: schema1; Owner: lijiaman
--

COPY schema1.t1 (id, name) FROM stdin;
1       a
2       b
\.


--
-- TOC entry 3711 (class 0 OID 16441)
-- Dependencies: 210
-- Data for Name: t2; Type: TABLE DATA; Schema: schema1; Owner: postgres
--

COPY schema1.t2 (id, age, address) FROM stdin;
1       11      beijing
2       12      shenzheng
\.


--
-- TOC entry 3580 (class 2606 OID 16449)
-- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: schema1; Owner: lijiaman
--

ALTER TABLE ONLY schema1.t1
    ADD CONSTRAINT t1_pkey PRIMARY KEY (id);


--
-- TOC entry 3581 (class 1259 OID 16450)
-- Name: idx_age; Type: INDEX; Schema: schema1; Owner: postgres
--

CREATE INDEX idx_age ON schema1.t2 USING btree (age);


-- Completed on 2022-08-05 05:03:38 CST

--
-- PostgreSQL database dump complete
--

（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

结果如下:

[postgres@pg01 pg_backup]$ cat db1_schema1_t1.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.5
-- Dumped by pg_dump version 12.5

-- Started on 2022-08-05 05:12:33 CST

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

ALTER TABLE ONLY schema1.t1 DROP CONSTRAINT t1_pkey;
DROP TABLE schema1.t1;
SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 209 (class 1259 OID 16438)
-- Name: t1; Type: TABLE; Schema: schema1; Owner: lijiaman
--

CREATE TABLE schema1.t1 (
    id integer NOT NULL,
    name character varying(50)
);


ALTER TABLE schema1.t1 OWNER TO lijiaman;

--
-- TOC entry 3709 (class 0 OID 16438)
-- Dependencies: 209
-- Data for Name: t1; Type: TABLE DATA; Schema: schema1; Owner: lijiaman
--

COPY schema1.t1 (id, name) FROM stdin;
1       a
2       b
\.


--
-- TOC entry 3580 (class 2606 OID 16449)
-- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: schema1; Owner: lijiaman
--

ALTER TABLE ONLY schema1.t1
    ADD CONSTRAINT t1_pkey PRIMARY KEY (id);


-- Completed on 2022-08-05 05:12:35 CST

--
-- PostgreSQL database dump complete
--

（四）使用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.创建db2数据库
db1=# create database db2;
CREATE DATABASE

-- 2.使用之前db1数据库的备份，执行恢复到db2
pg_restore --username=lijiaman --password --host=192.168.10.11 --port=5432 --dbname=db2 --format=custom /home/postgres/pg_backup/db1.dump  --verbose

-- 3.查看恢复情况
db1=# \c db2
您现在已经连接到数据库 "db2",用户 "postgres".
db2=# \dn
    架构模式列表
  名称   |  拥有者
---------+----------
 public  | postgres
 schema1 | postgres
 schema2 | postgres
(3 行记录)

db2=# \dt
              关联列表
 架构模式 | 名称 |  类型  |  拥有者
----------+------+--------+----------
 public   | t1   | 数据表 | postgres
 public   | t2   | 数据表 | postgres
(2 行记录)

db2=# set search_path to schema1
db2-# ;
SET
db2=# \dt
              关联列表
 架构模式 | 名称 |  类型  |  拥有者
----------+------+--------+----------
 schema1  | t1   | 数据表 | lijiaman
 schema1  | t2   | 数据表 | postgres
(2 行记录)

db2=# \ds
              关联列表
 架构模式 | 名称 |  类型  |  拥有者
----------+------+--------+----------
 schema1  | seq1 | 序列数 | postgres
(1 行记录)

（4.4）使用`--schema-only`选项，只恢复schema

-- 1.创建数据库db4
db1=# create database db4;

-- 2.只恢复表结构，视图等，不恢复里面的数据
[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

-- 3.确认有表结构，无数据
db1=# \c db4
您现在已经连接到数据库 "db4",用户 "lijiaman".
db4=# \dt
              关联列表
 架构模式 | 名称 |  类型  |  拥有者
----------+------+--------+----------
 public   | t1   | 数据表 | postgres
 public   | t2   | 数据表 | postgres
(2 行记录)

db4=# set search_path to schema1
db4-# ;
SET
db4=# select * from t1;
 id | name
----+------
(0 行记录)

（4.5）使用`--data-only` 选项，只恢复数据

-- 1.在上一步基础上进行数据恢复
[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
pg_restore: 为恢复数据库与数据库联接
口令:
pg_restore: 为表"public.t1"处理数据
pg_restore: 为表"public.t2"处理数据
pg_restore: 为表"schema1.t1"处理数据
pg_restore: 为表"schema1.t2"处理数据
pg_restore: 执行 SEQUENCE SET seq1
pg_restore: 执行 SEQUENCE SET t2_id_seq
pg_restore: 执行 SEQUENCE SET seq1
[postgres@pg01 pg_backup]$

-- 查看数据
db4=# select * from t1;
 id | name
----+------
  1 | a
  2 | b

（4.6）只恢复schema1.t1和schema1.t2表到db5的schema1下面

-- 1.创建数据库db1和schema1
postgres=# create database db5;
CREATE DATABASE
postgres=# \c db5
您现在已经连接到数据库 "db5",用户 "lijiaman".
db5=# create schema schema1;
CREATE SCHEMA

-- 2.导入2个表
[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
pg_restore: 为恢复数据库与数据库联接
口令:
pg_restore: 创建TABLE "schema1.t1"
pg_restore: 创建TABLE "schema1.t2"
pg_restore: 为表"schema1.t1"处理数据
pg_restore: 为表"schema1.t2"处理数据
[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

附录

测试数据

\c db1    -- 切换到db1数据库
set search_path to schema1;    -- 切换到schema1

-- 创建表
create table t1(id int,name varchar(50));
insert into t1 values(1,'a');
insert into t1 values(2,'b');
-- 创建主键约束
alter table t1 add primary key(id);


create table t2(id int,age int,address varchar(100));
insert into t2 values(1,11,'beijing');
insert into t2 values(2,12,'shenzheng');
-- 创建索引
create index idx_age on t2(age);
-- 创建视图
create view v_t2 as select * from t2 where id > 1;
-- 创建序列
create sequence seq1;

-- 创建函数
CREATE FUNCTION public.variadic_example(VARIADIC numeric[]) RETURNS int
  LANGUAGE sql AS 'SELECT 1';

这篇关于postgresql逻辑备份工具pg_dump和pg_resotre学习的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！