0

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).

Improve this question
  • What does the log files show, if the log file show nothing need to crank the logging up to see why its failing, – zsheep Feb 21 '20 at 14:55
  • Copy uses a tranaction so it if fails it will roll back all the records written to disk, vacuum will get around to shrinking the file back to 0 bytes. the reason it took 45 seconds for count to run is postgresql has to scan through the table to see if any of the tuples are committed which none were so it returned 0 – zsheep Feb 21 '20 at 15:01
  • 1
    Thank you for suggesting I look at the log. I am facepalming at myself for not thinking of that. The underlying error appears easy to solve. I've addressed it, and I'm trying to import now. Looks like there is some bug with either Postgres or pgamin where, for long queries, a success report is given when in fact there was a fatal error. – Aren Cambre Feb 21 '20 at 20:12
0

This ended up being a bug with either Postgres or pgAdmin. The problem is I was getting a success message when the query ended instead of a failure message. That threw me off. After checking the logs, I found the error, and it was easy to solve.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.