How Mysql migrates to postgrepsql

It is believed that many inexperienced people have no idea about how to migrate Mysql to postgrepsql. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1mysql migrates data to postgrepsqlmysql2pgsql

The tool mysql2pgsql supports the migration of tables in MYSQL to HybridDB/Greenplum Database/PostgreSQL/PPAS without landing. The principle of this tool is to connect the source mysql database and the destination database at the same time, query the data to be exported from the mysql library, and then import it to the destination through the COPY command. This tool supports multithreaded import (each worker thread is responsible for importing part of the database tables).

Parameter configuration

Modify the configuration file my.cfg, configuration source and destination library connection information.

The connection information of the source database mysql is as follows:

Note: in the connection information of the source database mysql, the user needs to have read access to all user tables.

Mysql2pgsql usage

The use of mysql2pgsql is as follows:

. / mysql2pgsql-l-d-n-j-s

Parameter description:

-l: optional parameter that specifies a text file that contains tables that need to be synchronized; if this parameter is not specified, all tables under the specified database in the configuration file are synchronized. Is a file name that contains the collection of tables to be synchronized and the conditions for querying on the table. An example of the content format is as follows:

Typical usage

Full library migration

The steps for full library migration are as follows:

Partial table migration

Get the DDL of the corresponding table on the destination side with the following command.

. / mysql2pgsql-d

Based on these DDL, add distribution key and other information to create a table on the destination side.

Synchronize all tables by executing the following command:

. / mysql2pgsql

This command migrates all mysql table data from the database specified in the configuration file to the destination. The process uses five threads (that is, the default number of threads is 5) to read and import all the table data involved.

Edit a new file, tab_list.txt, and put in the following:

Execute the following command to synchronize the specified T1 and T2 tables (note that the T2 table migrates only data that meets the C1 > 138888 criteria):

T1

T2: select * from T2 where C1 > 138888

. / mysql2pgsql-l tab_list.txt

After reading the above, have you mastered how to migrate Mysql to postgrepsql? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!