pg_dump子項section的三種開關

原創多米爸比 2022-01-20

5029

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
  return new;
end; 
$$ language plpgsql;

create trigger tg_t1
    AFTER UPDATE 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導出包含索引、規則、觸發器、唯一約束、主鍵、外鍵約束，不包含檢查約束和非空約束。