1
  • OS: Windows10 x64
  • HDD: 1TB
  • RAM: 8GB
  • CPU: Intel Core i5-4460
  • PostgreSQL: 9.6

I want to import almost 37GB data from MySQL into PostgreSQL, and the data is sql file, which full of INSERT INTO tbl_1 VALUES(x, x, x); statements.

I'm using \i data.sql to import it, but after 15 hours work, the PostgreSQL's data size is only 1GB. While during this 15 hours, there are no other tasks running, only the importing.

So, is there anyway i can do to speed up the procedure?

  • mysqldump is willing to emit batch inserts; do they work in PostgreSQL? For MySQL it is a 10x speedup. Also, what happens with the indexes? – Rick James Apr 6 '17 at 2:40
  • @RickJames yeah, the batch inserts have " around table name, I use sed tool to remove them. For indexes, i remove all indexes creation in schemas file, only left table creation. – spartucus Apr 6 '17 at 2:48
  • The default would be backtics; did you change to use quotes instead? – Rick James Apr 6 '17 at 2:55
  • think the fasted way will be always copy from csv - postgresql.org/docs/9.4/static/sql-copy.html, so if prepare csv file from SQL than copy into table, it must be faster – a_vlad Apr 6 '17 at 3:02
  • @RickJames My colleague give me the exported MySQL file(contains one schemas file, and one batch inserts file which use quotes). These two make a difference while importing into PG? – spartucus Apr 6 '17 at 3:04
0

If you are not running the \i data.sql command within a transaction, then the insertion of every row is getting committed individually. This is very slow.

If you can't switch over to a copy based method, then at least run your load script in a transaction. For example, issue BEGIN; before the \i. Don't forget to commit afterwards. Or run the whole script with psql -1 -f data.sql

  • sorry for silly questions, if i use COPY, do i still need BEGIN and COMMIT? – spartucus Apr 7 '17 at 1:57
  • No, all the tuples inserted by the same COPY will automatically be part of a single transaction. But it also won't hurt, and may be a good idea for other reasons. – jjanes Apr 7 '17 at 2:04
0

You should try pgloader : http://pgloader.io/howto/mysql.html It can load data from MySQL to PostgreSQL using COPY mode, which is faster than INSERT.

  • I used COPY, it's really fast, thanks a lot. – spartucus Apr 7 '17 at 6:21

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.