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';
