Migrate the MySQL database to PostgreSQL
- 2020-05-07 20:36:09
- OfStack
In North America, there is growing enthusiasm for PostgreSQL. With the development of PostgreSQL, PostgreSQL 8.x has surpassed MySQL 5.x technically, and it is only a matter of time before the market surpasses MySQL. In the end, users may have access to an open source database comparable to Oracle.
The Internet company I work for, which serves about 500,000 business users, has been upgraded and transplanted for many times. At present, the company has transplanted all background databases from MySQL to PostgreSQL, and I have completed half of the database transplantation, so I have accumulated some experience in the database transplantation from MySQL to PostgreSQL. I hope I can give you some inspiration to use PostgreSQL.
1) preparation: full backup of database using MySQL data backup tool:
mysqldump -h [hostname] -u [username] -p [password] --extended-insert=false [dbname] > mysql-db.sql
Note that disable extended-insert, PostgreSQL does not support MySQL extended-insert
2) convert : convert mysql-db.sql into SQL Script that PostgreSQL can import.
MySQL and PostgreSQL have a definite difference in the semantics of SQL. For example, MySQL does not support sequence, trigger and other functions, but it provides some own grammar rules for this purpose. Compared with some system functions, MySQL and PostgreSQL have a big difference. To this end, I wrote a paragraph of semantic analysis and transformation program mysql2psql
> mysql2psql mysql-db.sql postgres-db.sql
3) import : import the data file into the database using pgAdmin provided by PostgreSQL.
4) SQL statement modification : in the actual application, the front-end system will often embed some SQL statements with database characteristics, and with the change of the background database, the front-end system program also needs to make corresponding modifications.
The most common differences between MySQL and PostgreSQL include differences in the use of Group by and Join, differences in the naming and calling of system functions, and so on.
The Internet company I work for, which serves about 500,000 business users, has been upgraded and transplanted for many times. At present, the company has transplanted all background databases from MySQL to PostgreSQL, and I have completed half of the database transplantation, so I have accumulated some experience in the database transplantation from MySQL to PostgreSQL. I hope I can give you some inspiration to use PostgreSQL.
1) preparation: full backup of database using MySQL data backup tool:
mysqldump -h [hostname] -u [username] -p [password] --extended-insert=false [dbname] > mysql-db.sql
Note that disable extended-insert, PostgreSQL does not support MySQL extended-insert
2) convert : convert mysql-db.sql into SQL Script that PostgreSQL can import.
MySQL and PostgreSQL have a definite difference in the semantics of SQL. For example, MySQL does not support sequence, trigger and other functions, but it provides some own grammar rules for this purpose. Compared with some system functions, MySQL and PostgreSQL have a big difference. To this end, I wrote a paragraph of semantic analysis and transformation program mysql2psql
> mysql2psql mysql-db.sql postgres-db.sql
3) import : import the data file into the database using pgAdmin provided by PostgreSQL.
4) SQL statement modification : in the actual application, the front-end system will often embed some SQL statements with database characteristics, and with the change of the background database, the front-end system program also needs to make corresponding modifications.
The most common differences between MySQL and PostgreSQL include differences in the use of Group by and Join, differences in the naming and calling of system functions, and so on.