MySQL to PostgreSQL Tutorial

[MySQL to PostgreSQL Converter] [Introduction to Migration] [FAQ]

The process of database migration from MySQL, MariaDB or Percona to PostgreSQL consists of the following steps:

Step 1 of 6. Select Conversion Mode

On this screen you need to select the conversion scenario. MySQL to PostgreSQL converter provides two options to migrate MySQL:

Move to PostgreSQL server directly - use this approach to migrate the source database to the target PostgreSQL server directly. PostgreSQL server must be launched and the user must have necessary privileges to write into database on the target server. See Connect to PostgreSQL Server section for related information.

Export into script file - use this approach to export MySQL database into a local script file instead of moving it to PostgreSQL server. The resulting script file contains SQL statements to create all tables, indexes, constraints and to fill tables with the data. See Import PostgreSQL script files article for related information.

MySQL to PostgreSQL: Select Migration Mode

Step 2 of 6. Connect to MySQL

MySQL to PostgreSQL converter can connect MySQL server using either local or remote mode. Local mode is being used to connect MySQL server launched on the same machine where you run MySQL to PostgreSQL tool from. Remote mode allows you to connect to MySQL server launched on another computer.

By default the program uses local connection mode. It is indicated by radio button "Local" selected. To establish remote connection you should select "Remote" radio button. For remote MySQL servers you should also enter host and TCP/IP port to connect through. Host could be either network name or IP address. TCP/IP port value is necessary only if it differs from the default MySQL port number 3306. Otherwise you may leave this field as is.

Finally, you are entering user name and password. You can leave these fields empty for anonymous connection.

Step 3 of 6. Connect to PostgreSQL

This wizard screen collects information to connect to PostgreSQL server. The converter is able to connect to PostgreSQL server using either local or remote mode. Local mode is being used to connect PostgreSQL server launched on the same machine where you run the program from. Remote mode allows you to connect to PostgreSQL server launched on another computer.

By default the program uses local connection mode. It is indicated by radio button "Local" selected. To establish remote connection you should select "Remote" radio button. For remote PostgreSQL servers you should also specify the host and the port to connect through. Host could be either network name or IP address. TCP/IP port value is necessary only if it differs from the default PostgreSQL port number 5432. Otherwise you may leave this field as is.

Enter user name and password or leave these fields blank for anonymous connection.

By default PostgreSQL server connects to database with name equal to the username specified. If there is no such database or PostgreSQL server does not allows access to it, custom database may be specifies on this wizard page.

Step 4 of 6. Select Databases

On this wizard page the source and destination databases or PostgreSQL script file must be specified. Existing PostgreSQL database can be processed according to one of the following options:

Overwrite the entire database
Overwrite existing tables only
Skip existing tables
Merge
Synchronize

Existing PostgreSQL script file will be overwritten.

Also, there are some settings to customize the database migration process:

Convert table definitions only - select this checkbox to convert only table definitions of MySQL database. All the data will not be migrated.
Skip converting indexes - select this checkbox if you don't want to convert indexes
Make names case sensitive - select this checkbox to make table and column names case sensitive. Such names must be enclosed in quotes when composing query.

Step 5 of 6. Select Tables

Select tables and compose filtering queries to migrate to PostgreSQL server.

To add a table for migration, select it in "Available tables" list and click "Add" button (or double-click the selected item). Use "Add all" button to add all available tables. To remove a table from "Selected tables" list highlight it and click "Remove" button. Use "Remove all" button to remove all items from "Selected tables" list.

Use "Add Query" button for composing SELECT-query to filter data that will be migrated to PostgreSQL. See Using Queries article for the further information.

To edit table or query double click the corresponding item in "Selected tables" list box. If it is a table, dialog box appears to edit table attributes and to define custom type mapping. See Edit Table article for the further information. If selected item is a query, you will see dialog box to edit SQL-statement of the query.

Step 6 of 6. Select Views

This wizard page is designed to manage MySQL views for migrating to PostgreSQL.

To add MySQL view for converting it into PostgreSQL format, highlight it in "Available queries" list and click "Add" button (or double-click the selected item). Use "Add all" button to add all MySQL views. To remove an item from "Selected queries" list, highlight it and click "Remove" button (or double-click the selected item). Use "Remove all" button to remove all MySQL views from "Selected queries" list.

Sometimes it is important to convert views into PostgreSQL format according to the particular order (for example, to preserve dependencies). Use "up" and "down" buttons in right part of the wizard page to organize all queries in the appropriate order.

Have questions? Contact us