pg_dump子项section的三种开关

原创多米爸比 2022-01-20

2447

PostgreSQL数据库使用pg_dump做导出时，我们比较常见下面的两种使用方式：

仅导出结构

-s, --schema-only            dump only the schema, no data

仅导出数据

-a, --data-only              dump only the data, not the schema

这两种方式的使用比较熟知，那其实section子项也能实现只导出数据或只导结构，并且导出结构分的更细。

本文将详细测试section子项的三种开关(pre-data, data, post-data)，对比它与前两种方式的差异。

1.section子项解释

我们可以从官方文档查看pg_dump命令section子项的详细解释：

–section=sectionname
Only dump the named section. The section name can be pre-data, data, or post-data. This option can be specified more than once to select multiple sections. The default is to dump all sections.

The data section contains actual table data, large-object contents, and sequence values. Post-data items include definitions of indexes, triggers, rules, and constraints other than validated check constraints. Pre-data items include all other data definition items.

上面第一段的描述是比较容易理解的，我们可以按section子项，按部分导出，也可以多次指定section子项，默认是导出所有的三个部分。

第二段描述section子项三种开关的具体功能：

pre-data section

这部分描述比较间接，包含post-data部分描述之外的其他数据定义项。

data section

这部分描述也比较清晰：包含表数据、大对象、序列值。

post-data section

这部分描述比较关键：包含索引、触发器、规则和约束(constraints other than validated check constraints)。

通过文档的字面理解，除了post-data section包含的constraints other than validated check constraints之外，其它的解释还是非常清晰的。

下面通过实验来进一步验证post-data section包含哪些约束：

2.结构准备

下面我们准备两个表，首先是t1表

create table t1(id bigserial primary key);

create index idx_t1_id on t1(id);

create rule rule_t1 as on delete 
    to t1 do nothing;

CREATE FUNCTION fun_tg() RETURNS trigger AS $$ 
declare
begin 
  -- do something
end; 
$$ language plpgsql;

create trigger tg_t1
    AFTER UPDATE OR INSERT OR DELETE on t1
	for each row execute procedure fun_tg();

然后是persons表

create table persons(
id int  primary key,
name varchar unique,
age int constraint check_age check(age>=0 and age>150),
sex boolean not null,
pid int references t1(id)
);

create index idx_persons_age on persons(age);

create unique index idx_persons_pid on persons(pid);

可以看到我们构造了索引、触发器、规则和约束，其中约束包括非空约束、检查约束、唯一约束、主键和外键约束。

3.导出语句

schema-only

pg_dump --port=1208 \
--dbname=postgres \
--username=test \
--schema=test \
--schema-only \
--format=p \
--file=file1.sql

pre-data

pg_dump --port=1208 \
--dbname=postgres \
--username=test \
--schema=test \
--section=pre-data \
--format=p \
--file=file2.sql

post-data

pg_dump --port=1208 \
--dbname=postgres \
--username=test \
--schema=test \
--section=post-data \
--format=p \
--file=file3.sql

pre-data and post-data

pg_dump --port=1208 \
--dbname=postgres \
--username=test \
--schema=test \
--section=pre-data \
--section=post-data \
--format=p \
--file=file4.sql

4.结果分析

对比file1.sql和file4.sql

diff file1.sql file4.sql

结果是一致的，schema-only与pre-data and post-data效果几乎是一样。

查看file3.sql

使用下面的命令过滤头部信息及空行以及注释

cat file3.sql |grep -v "^SET\|set_config\|^--\|^$"

命令执行结果如下

ALTER TABLE ONLY test.persons
    ADD CONSTRAINT persons_name_key UNIQUE (name);
ALTER TABLE ONLY test.persons
    ADD CONSTRAINT persons_pkey PRIMARY KEY (id);
ALTER TABLE ONLY test.t1
    ADD CONSTRAINT t1_pkey PRIMARY KEY (id);
CREATE INDEX idx_persons_age ON test.persons USING btree (age);
CREATE UNIQUE INDEX idx_persons_pid ON test.persons USING btree (pid);
CREATE INDEX idx_t1_id ON test.t1 USING btree (id);
CREATE RULE rule_t1 AS
    ON DELETE TO test.t1 DO NOTHING;
CREATE TRIGGER tg_t1 AFTER INSERT OR DELETE OR UPDATE ON test.t1 FOR EACH ROW EXECUTE FUNCTION test.fun_tg();
ALTER TABLE ONLY test.persons
    ADD CONSTRAINT persons_pid_fkey FOREIGN KEY (pid) REFERENCES test.t1(id);

可以看到post-data导出包含索引、规则、触发器、唯一约束、主键、外键约束，不包含检查约束和非空约束。