[译文] pg_dump和insert

pg_dump(1)是用于备份 PostgreSQL 数据库的默认工具。
我经常得到关于如何生成更可移植的数据库转储输出的答案，可移植的意思是真正“可加载到另一个 PostgreSQL 版本甚至不同的数据库中”。
事实上，pg_dump默认COPY用于批量加载数据：

% pg_dump -a  -t wa -U luca testdb 
...
COPY luca.wa (pk, t) FROM stdin;
9200673 Record #1
9200674 Record #2
9200675 Record #3
9200676 Record #4
9200677 Record #5
9200678 Record #6
9200679 Record #7
9200680 Record #8
9200681 Record #9
...

您可以猜到，COPY仅在 PostgreSQL 中可用，而不能在其他数据库中使用。那么，如何处理可用于其他数据库的文本转储？
不用担心：pg_dump有一些功能可以满足这种需求。
特别是，以下选项可能很有用：

• –inserts删除COPY并用INSERT语句替换它，每个元组一个；
• –column-inserts与前面类似，但每个INSERT都有命名列的列表；
• rows-per-inserts单个INSERT语句可以处理的多个元组，对于更好的批量加载很有用（但可能不太便携）。

还有一些其他有用的选项：

• –quote-all-identifiers 强制引用标识符，这在为不同的数据库准备数据时很有用；
• –use-set-session-authorization 处理对象的所有权时，使用 SQL 标准命令；`
• –no-comments，这不是一个非常“技术”的方面，但是当您要将转储加载到另一个数据库时，您可能不想导入注释，因为它们可以以不同的方式处理。同样，还有其他–no特定于 PostgreSQL 的选项，例如–no-publications避免复制发布等。

在下面，我将使用wa只有两列和一堆记录的相同示例表，以便您可以轻松比较输出差异。

默认为 INSERT

为了更好地理解每个选项之间的区别，让我们看几个例子：

% pg_dump -a  -t wa  --inserts -U luca testdb 
...
INSERT INTO luca.wa VALUES (9200673, 'Record #1');
INSERT INTO luca.wa VALUES (9200674, 'Record #2');
INSERT INTO luca.wa VALUES (9200675, 'Record #3');
INSERT INTO luca.wa VALUES (9200676, 'Record #4');
INSERT INTO luca.wa VALUES (9200677, 'Record #5');
INSERT INTO luca.wa VALUES (9200678, 'Record #6');
INSERT INTO luca.wa VALUES (9200679, 'Record #7');
...

从上面可以看出，theCOPY已经被翻译成了INSERTs的集合。这当然具有 buk 加载速度较慢的缺点。
再举一个例子，让我们看看它是如何使用标识符引用改变输出的：

% pg_dump -a  -t wa  --inserts --quote-all-identifiers -U luca testdb 
...
INSERT INTO "luca"."wa" VALUES (9200673, 'Record #1');
INSERT INTO "luca"."wa" VALUES (9200674, 'Record #2');
INSERT INTO "luca"."wa" VALUES (9200675, 'Record #3');
INSERT INTO "luca"."wa" VALUES (9200676, 'Record #4');
INSERT INTO "luca"."wa" VALUES (9200677, 'Record #5');
INSERT INTO "luca"."wa" VALUES (9200678, 'Record #6');
...

并且表和模式名称已被引用。
如果还想要每个上的列列表INSERT怎么办？optin ``–column-inserts is there to explode the list of columns:

% pg_dump -a  -t wa  --column-inserts --quote-all-identifiers -U luca testdb 
...
INSERT INTO "luca"."wa" ("pk", "t") VALUES (9200673, 'Record #1');
INSERT INTO "luca"."wa" ("pk", "t") VALUES (9200674, 'Record #2');
INSERT INTO "luca"."wa" ("pk", "t") VALUES (9200675, 'Record #3');
INSERT INTO "luca"."wa" ("pk", "t") VALUES (9200676, 'Record #4');
INSERT INTO "luca"."wa" ("pk", "t") VALUES (9200677, 'Record #5');
INSERT INTO "luca"."wa" ("pk", "t") VALUES (9200678, 'Record #6');
INSERT INTO "luca"."wa" ("pk", "t") VALUES (9200679, 'Record #7');
INSERT INTO "luca"."wa" ("pk", "t") VALUES (9200680, 'Record #8');
INSERT INTO "luca"."wa" ("pk", "t") VALUES (9200681, 'Record #9');
...

不管使用与否–quote-all-identifiers，每个INSERT都有值所指的列的列表。
最后一种情况，在每个元组COPY和单个INSERT元组之间的中间路径是–rows-per-insert允许您指定每个元组INSERT将处理的最大行数：

% pg_dump -a  -t wa  --rows-per-insert=3 --quote-all-identifiers -U luca testdb 
...
INSERT INTO "luca"."wa" VALUES
        (9200688, 'Record #16'),
        (9200689, 'Record #17'),
        (9200690, 'Record #18');
INSERT INTO "luca"."wa" VALUES
        (9200691, 'Record #19'),
        (9200692, 'Record #20');
...

请注意最后一个如何INSERT只有两个元组而不是指定的3：pg_dump它足够聪明，可以让您INSERT不会丢失一行，因此如果没有足够的数据，则INSERT涉及的行更少。

避免ALTER TBALE设置所有权

如果转储包含表数据结构，pg_dump则会发出适当的命令来更改所有权。例如：

% pg_dump -C  -t wa  -U luca testdb
...
CREATE TABLE luca.wa (
    pk integer NOT NULL,
    t text
);

ALTER TABLE "luca"."wraparaound_pk_seq" OWNER TO "luca";
...

该选项–use-set-session-authorization会生成更便携的 SQL 输出：

% pg_dump -C  -t wa --use-set-session-authorization -U luca testdb
...
SET SESSION AUTHORIZATION 'luca';


CREATE TABLE luca.wa (
    pk integer NOT NULL,
    t text
);

...

可以发现，用户是在开始时设置的，因此所有创建的对象都会自动属于该用户。