MySQL to PostgreSQL

On this screen of MySQL to Postgres migration wizard you need to select the conversion scenario. The converter provides two options to migrate MySQL database:

• 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-Postgres 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 the converter 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 (if MySQL server allows it).

To establish connection with PostgreSQL server you should specify the host and port to connect through. Host could be either network name or IP address. For local connections, you may leave this field empty or enter 127.0.0.1. The 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 must be specified on this page.

Click the "Advance..." button to enter SSL settings if it is necessary:

• CA Certificate - the file containing SSL certificate authority (CA) certificate(s). For example, when connecting to Azure Database for PostgreSQL with "Enforce SSL connection" enabled, you have to specify certificate BaltimoreCyberTrustRoot.crt from the installation folder of the product.
• Revocation list - the file name of the SSL certificate revocation list (CRL). Certificates listed in this file, if it exists, will be rejected while attempting to authenticate the server's certificate.
• Client Certificate - the file name of the client SSL certificate, replacing the default ~/.postgresql/postgresql.crt
• Client Key - the location for the secret key used for the client certificate. It can either specify a file name that will be used instead of the default ~/.postgresql/postgresql.key, or it can specify a key obtained from an external engine (engines are OpenSSL loadable modules). An external engine specification should consist of a colon-separated engine name and an engine-specific key identifier.

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, users should select code page for the target PostgreSQL server.

Finally, the path to log file where execution traces will be written can be specified on this wizard page.

MySQL-to-PostgreSQL allows to customize migration via the following settings:

• PostgreSQL schema - you can use existing schema (when merging data into existing database) or create new one.
• Create tables LOGGED/UNLOGGED. Unlogged tables do not require writing data into write-ahead log that makes them considerably faster but not crash-safe.
• Migrate data via INSERT/COPY statements. INSERT approach loads data row by row while COPY loads all the rows in one command. COPY is faster, but INSERT may give more diagnostic information in case of an issue.
• "Do not migrate the data" - use this option if you need to convert only table definitions from MySQL to PostgreSQL.
• "Make names case sensitive" - use this option to make table and column names case sensitive. In this case, names must be enclosed in quotes when composing a query.

Select tables and compose filtering queries to migrate from MySQL to Postgres.

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.

This wizard page is designed to select views for MySQL to Postgres migration.

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.