2 added 216 characters in body
source | link

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


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

1
source | link

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)

     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?