Port a MySQL database to PostgreSQL

Source: Internet
Author: User

In North America, people are enthusiastic about PostgreSQL. With the development of PostgreSQL, PostgreSQL 8.x has already surpassed MySQL 5.x technically, and market transcendence is only a matter of time. In the end, users may have the opportunity to enjoy open-source databases comparable to Oracle.
The Internet company I work for serves about 0.5 million business users. After many upgrades and transplantation, the company has all migrated backend databases from MySQL to PostgreSQL, I have completed half of the database migration, so I have accumulated some experience in migrating databases from MySQL to PostgreSQL. I am going to write it here, hoping to inspire everyone to use PostgreSQL.
1) preparation: Use the MySQL Data Backup Tool for full backup of the database:
Mysqldump-h [hostname]-u [username]-p [password] -- extended-insert = false [dbname]> mysql-db. SQL
Note: disable extended-insert. PostgreSQL does not support extended-insert of MySQL.
2) Conversion: Convert mysql-db. SQL to SQL Script that PostgreSQL can import.
MySQL and PostgreSQL have some differences in SQL semantics. For example, MySQL does not support sequence, trigger, and other functions. However, it provides some proprietary syntax rules and compares some system functions, there is a big difference between MySQL and PostgreSQL. For this reason, I have compiled a program for semantic analysis and conversion, mysql2psql.
> Mysql2psql mysql-db. SQL postgres-db. SQL
3) Import: Use pgAdmin provided by PostgreSQL to import data files to the database.
4) SQL statement Modification: In practical applications, front-end systems often embed some SQL statements with database features. As the background database changes, front-end system programs also need to be modified accordingly.
The most common differences between MySQL and PostgreSQL include Group by, Join usage, and system function naming and calling.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.