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?

Improve this question
5
  • 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
    Commented Apr 6, 2017 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
    Commented Apr 6, 2017 at 2:48
  • The default would be backtics; did you change to use quotes instead?
    – Rick James
    Commented Apr 6, 2017 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
    Commented Apr 6, 2017 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
    Commented Apr 6, 2017 at 3:04

2 Answers 2

Reset to default
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

Improve this answer
2
  • sorry for silly questions, if i use COPY, do i still need BEGIN and COMMIT?
    – spartucus
    Commented Apr 7, 2017 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
    Commented Apr 7, 2017 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.

Improve this answer
1
  • I used COPY, it's really fast, thanks a lot.
    – spartucus
    Commented Apr 7, 2017 at 6:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.