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  postgres@measures ERROR: canceling statement due to user request 2019-10-16 10:28:05.657 -03  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  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  LOG: using stale statistics instead of current ones because stats collector is not responding . . . 2019-10-17 06:10:31.423 -03  WARNING: worker took too long to start; canceled 2019-10-17 06:57:19.150 -03  WARNING: autovacuum worker started without a worker entry 2019-10-17 08:04:47.445 -03  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  LOG: listening on IPv4 address "127.0.0.1", port 5412 2019-10-17 08:04:48.048 -03  LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5412" 2019-10-17 08:04:53.879 -03  LOG: database system was interrupted; last known up at 2019-10-16 15:47:25 -03 2019-10-17 08:05:10.887 -03  LOG: database system was not properly shut down; automatic recovery in progress 2019-10-17 08:05:11.534 -03  LOG: redo starts at 14/B8254908 2019-10-17 08:05:11.847 -03  LOG: invalid record length at 14/B8260848: wanted 24, got 0 2019-10-17 08:05:11.847 -03  LOG: redo done at 14/B82607D0 2019-10-17 08:05:16.417 -03  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?