Can't see process progress, machine getting slow trying to import 500 million records

Question

Working environment: PostgreSQL 12.0, Ubuntu 18.10, 4GB memory, i5-3230M CPU @ 2.60GHz × 4, Os 64 bits

I'm trying to import a .csv file with more than 500 million records into a PostgreSQL db table, using my PC. (we're testing on my PC until it works, after that we will do it on a server)

My company is migrating from Firebird to Postgres, so this should be one time task. After that, we expect to insert ~200k records each day.

Create table with (SQL generated from original firebird database)

     CREATE TABLE REGDATA
     (
        CODIGO integer NOT NULL,
        DTAREG timestamp NOT NULL,
        PERIOD integer NOT NULL,
        FLDCODIGO integer,
        REGVALUE double precision,
        CLICODIGO integer,
        SITCODIGO integer,
        CONSTRAINT PK_REGDATA PRIMARY KEY (CODIGO)
     );

.csv is generated using FBExport 1.9

./fbexport -Sc -D /opt/firebird/bin/measures.fdb -H localhost -U user -P password -F /home/dani/Documents/raw_regdata.out -Q "SELECT * FROM REGDATA"

I convert raw_regdata.out to utf8 to work with postgres encoding. Getting rid of some characters (ã,º,etc)

iconv -c -t utf8 /home/dani/Documents/raw_regdata.out > /home/dani/Documents/utf8_regdata.out

I've followed Postgres guidelines on how to import large data, like:

• Dropping all Indexes;
• Increasing maintenance_work_mem (maintenance_work_mem = 512MB)
• Increasing max_wal_size (max_wal_size = 4GB)
• Setting wal_level = minimal
• Setting max_wal_senders = 0

After connecting to Postgres cluster, I use COPY to import the data

COPY REGDATA(CODIGO,DTAREG,PERIOD,FLDCODIGO,REGVALUE,CLICODIGO,SITCODIGO) from '/home/dani/Documents/utf8_regdata.out' DELIMITER ',' CSV HEADER;

And here comes the problem, I cant see if the command is progressing or not, my PC gets very slow after some time and it just freezes.

1st Attempt: let it run for 2-3 hours, then killed the process (Ctrl+c on terminal), from logs I see that it was progressing (line 131158327):

2019-10-16 10:28:05.657 -03 [9258] postgres@measures ERROR:  canceling statement due to user request
2019-10-16 10:28:05.657 -03 [9258] postgres@measures CONTEXT:  COPY regdata, line 131158327: ""178865944","13.03.2015 12:10:00","600","22439","358.60000000000002","9","37""
2019-10-16 10:28:05.657 -03 [9258] postgres@measures STATEMENT:  COPY REGDATA(CODIGO,DTAREG,PERIOD,FLDCODIGO,REGVALUE,CLICODIGO,SITCODIGO) from '/home/dani/Documents/utf8_regdata.out' DELIMITER ',' CSV HEADER;

But I try to select something and get nothing, I think since Copy only commits after the whole process finish, it just undo everything and I end up with a empty table

2nd Attempt: let it run 24 hours (even if my PC is totally locked), then kill the process again, hoping to see more progress on logs, but for my surprise I dont see any similar logs, only the same 3 warnings over and over again:

2019-10-16 17:42:31.061 -03 [5646] LOG:  using stale statistics instead of current ones because stats collector is not responding
.
.
.
2019-10-17 06:10:31.423 -03 [2734] WARNING:  worker took too long to start; canceled
2019-10-17 06:57:19.150 -03 [5964] WARNING:  autovacuum worker started without a worker entry
2019-10-17 08:04:47.445 -03 [2327] LOG:  starting PostgreSQL 12.0 (Ubuntu 12.0-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
2019-10-17 08:04:47.622 -03 [2327] LOG:  listening on IPv4 address "127.0.0.1", port 5412
2019-10-17 08:04:48.048 -03 [2327] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5412"
2019-10-17 08:04:53.879 -03 [2546] LOG:  database system was interrupted; last known up at 2019-10-16 15:47:25 -03
2019-10-17 08:05:10.887 -03 [2546] LOG:  database system was not properly shut down; automatic recovery in progress
2019-10-17 08:05:11.534 -03 [2546] LOG:  redo starts at 14/B8254908
2019-10-17 08:05:11.847 -03 [2546] LOG:  invalid record length at 14/B8260848: wanted 24, got 0
2019-10-17 08:05:11.847 -03 [2546] LOG:  redo done at 14/B82607D0
2019-10-17 08:05:16.417 -03 [2327] LOG:  database system is ready to accept connections

There's a way of monitoring the progress (which line it is currently working on), and it is normal to get a dead machine while the process is active?

---

edit: Looking on original firebird database, I can see primary key is not sorted:

Answer 1

There is no built in way to monitor the progress. v12 has added progress monitoring for some actions, but not for COPY.

What I have done in this situation is use strace -s 1024 -y -p <backend pid> to get a trace of the loading process. This will show what data it is reading from the "/home/dani/Documents/utf8_regdata.out" file (along with a bunch of other stuff). Then you can just grab a recent primary key out of that stream, and do fgrep -n ,<primarykey> /home/dani/Documents/utf8_regdata.out to see what line number it is at. It is not the most graceful procedure, but is better than being completely in the dark.

If you did not drop the primary key and the data is not sorted in primary key order when you load it, then once the size of the primary key index exceeds some threshold you will get extreme IO congestion. That would explain the "stale statistics" and the "worker took too long to start", and the system can also become so slow that it appears to be dead (especially if you are using a GUI).

The threshold of performance collapse for the index size is generally going to be somewhere between shared_buffer and greatest(shared_buffers,RAM - shared_buffers), but exactly where will depend on kernel versions and settings.

Answer 2

Another way is to use count_estimate function to get an idea of the progress counting the records with a SELECT count_estimate('SELECT * FROM REGDATA')