postgresql - PostgreSQL 将大型 csv 文件加载到表中时出现问题

Question

在我的设置中，PostgreSQL 9.2.2 似乎在尝试将大型 csv 文件加载到表中时出错。

csv 文件的大小约为 9GB

这是我用来执行批量加载的 SQL 语句:

copy chunksBase (chunkId, Id, chunk, chunkType) from path-to-csv.csv' delimiters ',' csv

这是几分钟后出现的错误:

pg.ProgrammingError: ERROR:  out of memory
DETAIL:  Cannot enlarge string buffer containing 1073723635 bytes by 65536 more bytes.
CONTEXT:  COPY chunksbase, line 47680536

我认为缓冲区不能恰好分配超过 1GB，这让我认为这可能是 postgresql.conf 问题。

这是 postgresql.conf 中未注释的行:

bash-3.2# cat postgresql.conf | perl -pe 's/^[ \t]*//' | grep -v '^#' | sed '/^$/d'
log_timezone = 'US/Central'
datestyle = 'iso, mdy'
timezone = 'US/Central'
lc_messages = 'en_US.UTF-8'         # locale for system error message
lc_monetary = 'en_US.UTF-8'         # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'          # locale for number formatting
lc_time = 'en_US.UTF-8'             # locale for time formatting
default_text_search_config = 'pg_catalog.english'
default_statistics_target = 50 # pgtune wizard 2012-12-02
maintenance_work_mem = 768MB # pgtune wizard 2012-12-02
constraint_exclusion = on # pgtune wizard 2012-12-02
checkpoint_completion_target = 0.9 # pgtune wizard 2012-12-02
effective_cache_size = 9GB # pgtune wizard 2012-12-02
work_mem = 72MB # pgtune wizard 2012-12-02
wal_buffers = 8MB # pgtune wizard 2012-12-02
checkpoint_segments = 16 # pgtune wizard 2012-12-02
shared_buffers = 3GB # pgtune wizard 2012-12-02
max_connections = 80 # pgtune wizard 2012-12-02
bash-3.2# 

没有明确将缓冲区设置为 1GB。

这是怎么回事？即使解决方案是增加 postgresql.conf 中的缓冲区，为什么 postgres 似乎尝试在单个副本调用中将整个 csv 文件批量加载到 ram 中？人们会认为加载大型 csv 文件是一项常见任务；我不是第一个遇到这个问题的人；所以我认为 postgres 会处理大容量负载的分块，以便从一开始就不会达到缓冲区限制。

作为解决方法，我将 csv 拆分为较小的文件，然后为每个文件调用复制。这似乎工作正常。但这不是一个特别令人满意的解决方案，因为现在我必须维护要加载到 postgres 中的每个大型 csv 的拆分版本。必须有一种更合适的方法来将大型 csv 文件批量加载到 postgres 中。

EDIT1:我正在确保 csv 文件没有任何格式错误。我通过尝试将所有拆分的 csv 文件加载到 postgres 中来做到这一点。如果所有都可以加载，那么这表明这里的问题不太可能是由于 csv 文件格式错误造成的。我已经发现了一些问题。尚不确定这些问题是否会在尝试加载大型 csv 时导致字符串缓冲区错误。

Answer 1

原来是一个格式错误的csv文件。

我将大型 csv 分成较小的 block (每个 block 有 100 万行)并开始将每个 block 加载到 postgres 中。

我开始收到更多信息错误:

pg.ProgrammingError: ERROR:  invalid byte sequence for encoding "UTF8": 0x00
CONTEXT:  COPY chunksbase, line 15320779

pg.ProgrammingError: ERROR:  invalid byte sequence for encoding "UTF8": 0xe9 0xae 0x22
CONTEXT:  COPY chunksbase, line 369513

pg.ProgrammingError: ERROR:  invalid byte sequence for encoding "UTF8": 0xed 0xaf 0x80
CONTEXT:  COPY chunksbase, line 16602

总共有5行是无效的utf8字节序列，在几亿中。删除这些行后，9GB 的大 csv 加载得很好。

如果最初加载大文件时出现无效字节序列错误就好了。但至少在我开始隔离问题后它们出现了。

请注意，最初加载大文件时错误中提到的行号与加载较小的 csv 子集文件时发现的编码错误无关。初始行号是文件中刚好发生 1GB 数据的位置，因此它与 1GB 缓冲区分配错误有关。但是，那个错误与真正的问题无关......