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

目标数据库已经有我要还原的表格，因此我使用了截断，然后删除了所有索引。目标系统具有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多个小时（！）。之后，我必须创建我丢弃的索引和外国限制因素，这可能还需要几个小时。我有一种感觉可以做一些不同的事情，以使整个过程更快。请给我任何可以帮助我提高此过程的指针。我还想提到我已经看过副本，但是因为它不能保持主要键的顺序，因此该选项对我不利。如果我不知道保留数据顺序的任何特殊设置的副本设置，那么很高兴知道！

整个过程的目的是更改列的某些数据类型，这些数据类型在使用Alter Table table column contry进行完成后也需要类似的时间。

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的生成以创建表。

Answer 2

如上所述，使用生成复制语句的pg_dump。

另外，在沉重的写入流量下，将写很多数据，因此您希望WAL在单独的快速磁盘或SSD上。

另一个选择是将其设置为，因此没有写给WAL。此导入是全或什么都没有操作。它可以起作用，要么您将截断并再次运行。因此，将资源专用于WAL以确保桌子防崩溃和酸是没有意义的（除非服务器是复制主人）。

有两种方法：

• 放下表格，然后确保整个还原发生在一次交易中。"开始；创建表...复制...提交。"在这种情况下，不会将数据写给WAL。这是因为如果交易失败，则根本没有创建表。因此，不需要它是酸的。
• 如果这是一台测试服务器，并且您只想使用数据，则可以将所有内容设置为Unloggoged，但是当然，所有数据都会在崩溃的情况下删除。

现在，该副本不会并行化，但是一旦加载数据，索引和约束创建就可以并行化。因此，如果您使用pg_restore恢复索引，请确保使用-J选项使用内核。如果不这样做，只需打开几个psql窗口。

VAINTANDAND_WORK_MEM的调整也有助于创建索引。如果服务器什么都不做，只需还原，使用RAM进行分类和索引创建确实会加快速度。