Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

I've got a table with around 2.7 million records, 63 fields, and several indexes. I need to be able to get this table from mysql to postgresql quickly (say within an hour). I know mysql can export the data quickly, but postgresql seems to be very slow when importing the data. Any ideas? I've been importing a CSV produced from mysqldump for about 3 hours now.

share|improve this question

3 Answers

up vote 3 down vote accepted

The fastest way I can think of would be to drop all the indexes and triggers (or at least ALTER TABLE foo DISABLE TRIGGER ALL;) on the table in postgresql, add the \. end-of-input line to the end of your CSV file, then put the file on the server (preferably on a drive other than the one being used for the DB, or maybe somewhere on the network the server can access via NFS) and COPY foo FROM '/some/file.csv' WITH CSV; (8.x syntax, which is supported in 9.0. Depending on the CSV file format you may need to make modifications.) Afterwards, recreate the indexes and re-enable the triggers.

Some of the configuration options for postgres can affect data load times. Disabling autovacuum and increasing the time between checkpoints can help with huge loads, but you'll need to restart postgresql in order to change the checkpoint_segments or autovacuum settings, and you'll want to set autovacuum back on when you're done.

share|improve this answer
+1 for disabling triggers/indexing and using COPY (it's much faster to create an index once the table is populated than to update it as you insert). Also definitely crank up checkpoint_segments if your data set is large (many bytes, not just many rows) to avoid rolling WAL segments constantly (the extra disk I/O rolling WAL segments will really slow your load down) – voretaq7♦ Mar 31 '11 at 5:55
up vote 1 down vote

mysqldump --compatible=postgresql -h... -u... -p... dbname tablename > PostgresqlData.sql

This option exists

--compatible=name   Change the dump to be compatible with a given mode. By                      default tables are dumped in a format optimized for
                  MySQL. Legal modes are: ansi, mysql323, mysql40,
                  postgresql, oracle, mssql, db2, maxdb, no_key_options,
                  no_table_options, no_field_options. One can use several
                  modes separated by commas. Note: Requires MySQL server
                  version 4.1.0 or higher. This option is ignored with
                  earlier server versions.

Give it a Try !!!

share|improve this answer
I'm not sure if the --compatible=postgresql feature generates COPY or INSERT statements (the former being substantially faster than the latter for huge data sets) -- do you happen to know offhand? – voretaq7♦ Mar 31 '11 at 5:53
@Rolando, how this command can be used to convert a whole database instead of a single table? – alfish Jan 1 '12 at 18:01
up vote 1 down vote

There are any number of professional ETL tools that can migrate between databases at fairly high speeds - for example, I have used Pentaho's open source PDI (nee Kettle) hit in the range of 100K rows/second. These also bring several other advantages, such as allowing you to massage data to few new structures, a graphical interface for the mapping process, and so forth.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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