Shulou(Shulou.com)05/31 Report--

What this article shares to you is about the experience of MySQL transfer to PostgreSQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article. Let's take a look at it.

The experience of MySQL transferring to PostgreSQL

Labour Day holiday, overtime, idle, intend to transfer their own data on MySQL (5.0.17) to PostgreSQL (8.0.0), refer to online articles to import, as a result, got into a trouble. Finally succeeded, now tell the steps, lest other friends go astray.

Data: two tables used for telecom service bill analysis, each with about 2 million records

Steps:

1. Use mysqldump to export the file, generated two files bill01.sql,bill02.sql (400m or so a file), if your amount of data is not large, it is best not to export the entire library, otherwise edit the export file back to let your machine refreshing to death, my machine P42.8 memory 1G can not stand.

two。 Because the database server is remote, it is very inconvenient to edit, so download it back to this machine. Open it with UltraEdit32 and delete the previous part of the exported file that created the table, the last LOCK of the file, and so on, leaving only the data. Delete the symbols used for MySQL such as the `symbol in the file before the Insert xxxx values (delete) in the file. In short, it forms a file with only data, one record per line, and each field is separated by a sign.

3. Establish the corresponding table structure on PostgreSQL and upload the data file to the server.

4. Log in to the database with psql, COPY bill01 FROM'/ data/bill02.txt' WITH DELIMITER AS','

It's basically OK.

Using this method is much faster than the exported SQL command. It took my machine 10 hours to import more than 2 million records, and the exported SQL command needs to be modified to be used on PostgreSQL. With this method, it takes 10 minutes.

Problems to pay attention to:

If the field is Integer and NULL is allowed, replace NULL with a number before import, otherwise an error will be reported.

Be sure to remove the `symbol, which is supported by MySQL and cannot be recognized when PostgreSQL is imported

The above is the experience of MySQL's transfer to PostgreSQL, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.