With Postgres 10.12 on Windows 10, I am importing at 67 GB CSV file into a database using a simple COPY query:
COPY table_name FROM 'c:/temp/data.csv' WITH (FORMAT csv, HEADER);
The import fails silently at random intervals, with the intervals ranging from 1.x minutes to over 10 minutes. This is the only process on the device with major resource use, so the time-difference cannot be explained by other processes competing for resources.
(Why 10.12? Postgres 11+ has a bug that blocks straightforward imports of large files. Yes, I know I could use the cat command with Postgres 12 (see third answer), and I actually did, and ran into the same problem.)
While the query runs, the Postgres process has sustained, high disk activity, generally 16 - 46 MB/s (running this on a Surface Pro 4 i7).
The response from the query always indicates success. The latest time I ran it, the response was Query returned successfully in 13 min 28 secs.
The resulting table is always empty but behaves strangely. If I count rows (
SELECT COUNT(*) FROM table_name), I have to wait through about 45 seconds of very high disk activity before it returns 0. Viewing the table through Pgadmin 4 has a similar outcome: many seconds of high disk activity, then 0 rows returned.
There is data in there somewhere, however. If I go to C:\Program Files\PostgreSQL\10\data\base and check the size, there's over 11 GB of data in there, so some data is coming in. I inspected further and found several 1 GB files in C:\Program Files\PostgreSQL\10\data\base\16393.
I have segmented the first 45 lines of the CSV into a separate file. If I import that file instead, it runs fine, and I get 44 rows in the table (row 1 is a header and is not imported).