postgresql - pg_restoring 非常大的单表使用 -j 选项需要几个小时

Question

我正在处理一个有 700 多万行的表(没有分区)。我想将此数据加载到另一个数据库，所以我使用了以下 pg_dump 命令，

pg_dump -Fc --column-inserts --data-only --table='tname' -U 
postgres -d dbname > /root/tname_experiment_inserts_custom_format.dump

在目标系统上，我使用了以下命令，

pg_restore -d dest_dbname -U postgres -j 7 /root/tname_experiment_inserts_custom_format.dump

目标数据库已经有我要恢复的表，所以我使用了 TRUNCATE，然后删除了所有索引。目标系统有 32GB 物理内存，我在 postgres 配置文件中做了以下设置，

log_min_duration_statement = -1
autovacuum = off
maintenance_work_memory = 7gb 
wal_level = minimal
fsync = off
full_page_writes= off
synchronous_commit= off
max_wal_size= 20GB
wal_buffers= 16MB

当我对 pg_restore 计时时，一个小时内只有大约 1600 万行被插入。这意味着恢复数据需要 40 多个小时 (!)。之后，我必须创建我删除的索引和外部约束，这可能还需要几个小时。我有一种感觉，我可以做一些不同的事情来使整个过程更快。请给我任何可以帮助我提高这个过程效率的指示。我还想提一下，我已经看过 COPY，但是因为它不维护主键的顺序，所以这个选项对我来说并不好。如果我不知道保留数据顺序的 COPY 的任何特殊设置，那么知道它会很棒!

整个事情的目的是改变列的一些数据类型，当使用 alter table alter column query 完成时也花费了类似的时间。

Answer 1

给定这张表:

---

CREATE TABLE abc(a serial NOT NULL
        ,b text
        ,c DATE NOT NULL
        );
INSERT INTO abc(b,c) VALUES
        ('cow' , '2017-01-01')
        , ('pig' , '2017-01-02')
        , ('dog' , '2017-01-03')
        , ('cat' , '2017-01-04')
        ;

---

pg_dump -U postgres mydb --column-inserts --data-only

会产生这样的输出:

---

--
-- Data for Name: abc; Type: TABLE DATA; Schema: tmp; Owner: postgres
--

INSERT INTO abc (a, b, c) VALUES (1, 'cow', '2017-01-01');
INSERT INTO abc (a, b, c) VALUES (2, 'pig', '2017-01-02');
INSERT INTO abc (a, b, c) VALUES (3, 'dog', '2017-01-03');
INSERT INTO abc (a, b, c) VALUES (4, 'cat', '2017-01-04');

--
-- Name: abc_a_seq; Type: SEQUENCE SET; Schema: tmp; Owner: postgres
--

SELECT pg_catalog.setval('abc_a_seq', 4, true);

---

省略 --colum-inserts 将产生:

---

--
-- Data for Name: abc; Type: TABLE DATA; Schema: tmp; Owner: postgres
--

COPY abc (a, b, c) FROM stdin;
1   cow 2017-01-01
2   pig 2017-01-02
3   dog 2017-01-03
4   cat 2017-01-04
\.

--
-- Name: abc_a_seq; Type: SEQUENCE SET; Schema: tmp; Owner: postgres
--

SELECT pg_catalog.setval('abc_a_seq', 4, true);

---

因此 --column-inserts 将生成每行一个插入语句，这非常慢。--data-only 标志仅禁止生成用于创建表的 DDL。