Migrate from MySQL to PostgreSQL

Estimated reading time: 3 minutes

The most popular open-source DBMS are MySQL and PostgreSQL, both have wide community of users, a wealth of administration tools and programming APIs. Despite the fact that both are highly competitive, many businesses long to migrate from MySQL to PostgreSQL for a variety of reasons. These include compatibility with a large range of indexing models, asynchronous commit, synchronous and asynchronous replication, and complete adherence to ANSI SQL standards.

Generally, there are steps involved in database migration:

1. The definitions of tables are extracted along with the relevant constraints and indexes. The definitions are exported in form of SQL CREATE statements and then converted to PostgreSQL syntax and loaded to the target database.
2. Export data from a MySQL database using an intermediary storage format, like CSV files. Then it must be converted according to PostgreSQL syntax requirements and imported into previously created tables.
3. All entries of the database logic such as views, triggers, and stored procedures must be extracted in form of SQL statements and source code and then converted from MySQL to PostgreSQL syntax.
4. Finally, make all necessary validations to confirm that all the database objects have been converted correctly.

Approaches to migrate from MySQL to PostgreSQL that do not imply automation require lot of efforts. Also, there is high risk of data corruption or loss due to human factor having tendency of errors increased over prolongation of the procedure. This is the reason why the database migration muts be automated using the dedicated trustful software.

For instance, one of the software companies specializing in database migration and synchronization called Intelligent Converters has developed automation tool to migrate from MySQL to PostgreSQL. This tool reaches high performance of about 20,000 rows per second due to efficient techniques of direct reading and writing, without using middleware libraries or components.

Key features of the program:

• Support all modern version of MySQL and PostgreSQL including forks and DBaaS solutions
• All database types including spatial, binary and large objects are supported with necessary properties and attributes
• Merges and synchronizes MySQL data into an existing PostgreSQL database
• Customize migration by editing the target tables and filtering data via SELECT-queries
• Automate and schedule migration using command line version

Migration of row sets that are result of SELECT queries is a powerful and flexible feature that allows not only filtering particular record and columns, but also transforming data before loading into PostgreSQL table.

Another feature that facilitates changing the type of columns in the generated database is a dialogue window known as “custom column mapping.” A table’s column properties, including its type, name, NULL attribute, and default value, can also be altered. It is also possible to exclude some columns from conversion through this tool.

When the target database server does not allow a remote connection, the data can be exported into a SQL script to migrate from MySQL to PostgreSQL. In this method, the database administrator takes the following fundamental actions:

• Export of database into ‘SQL-statement’ files.
• Creation of tables containing indexes and constraints.
• Filling the tables with data.

The output script file can be imported to PostgreSQL server via standard tools such as phpPgAdmin or command line client psql that is a part of PostgreSQL installation.