1 of 2

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

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)

        CODIGO integer NOT NULL,
        DTAREG timestamp NOT NULL,
        PERIOD integer NOT NULL,
        FLDCODIGO integer,
        REGVALUE double precision,
        CLICODIGO integer,
        SITCODIGO integer,

.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


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 "", 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?