Introduction

MySQL-to-PostgreSQL is a program to migrate MySQL databases to PostgreSQL server. Depending on your privileges on the target server you can export MySQL data into new database or overwrite the contents of an existing PostgreSQL database. The program has high performance due to direct connection to data source and destination databases (it does not use ODBC or any other middleware software). Command line support allows to script, automate and schedule the conversion process.

Features

  • All versions of PostgreSQL are supported
  • All versions of Unix and Windows MySQL are supported
  • Converts indexes with all necessary attributes
  • Option to convert individual tables (see Select Tables article for related information)
  • Merges MySQL data into an existing PostgreSQL database
  • Synchronizes PostgreSQL database with MySQL data
  • Command line support (see Command Line article for related information)
  • Stores configuration into profile
  • Quick Launch
  • Easy-to-use wizard-style interface
  • Full install/uninstall support

Requirements

  • PostgreSQL environment (server or client components)
  • Necessary privileges to write data on target PostgreSQL server


Add Query

This dialog allows you to compose a SELECT-query and result of this query will be migrated to PostgreSQL server as if it would be a regular table. Select name of table where you wish to store query results in the destination MySQL database. Enter query in the corresponding field. Also, you can test query on source database by pushing "Test Query" button. If query is not correct please check that it is a correct SQL SELECT-statement and all table and field names are spelled properly. You can always check syntax of the query by running it from any MySQL client application.

See Using Queries article for the further information.


Command Line

To perform batch conversion or call conversion procedure from an automation script you can use console version of MySQL-to-PostgreSQL converter called S2PAGENT.EXE. Find this file in the installation folder. You can either run this tool directly from command line or call it from any script as well. The program supports the following command-line options:

--dest=... | PostgreSQL database name or script file
--dump | convert MySQL database into PostgreSQL file
--help | display help message and exit
--logfile=... | path to the logfile where execution traces will be written
--mode=... | how to process existing PostgreSQL database (0 - overwrite the entire database, 1 - overwrite existing tables only, 2 - skip existing tables, 3 - merge)
--myport=... | MySQL port
--mysqlh=... | MySQL server IP address or network name
--mysqlu=... | MySQL user name
--mysqlp=... | MySQL user password
--no_schema | do not include schema name in table name
--pgport=... | PostgreSQL port
--posgsh=... | PostgreSQL server IP address or network name
--posgsu=... | PostgreSQL user name
--posgsp=... | PostgreSQL user password
--profile=... | path to the file to load conversion settings from
--silent | use this option to disable program output
--skip_idx | skip converting indexes
--src=... | MySQL database name
--tab_def | convert table definitions only
--tab_file=... | file containing table names to convert (one table name per line)

In the following example the program converts all tables from MySQL database "mysql_db" into PostgreSQL database "pgs_db":

S2PAGENT.EXE --src="mysql_db" --posgsu=postgres --posgsu=qwerty --dest="pgs_db"
--mysqlh=mysqlhost --mysqlu=administrator --mysqlp=the_passsword

Notes:

  1. If you specified a profile, there is no need to specify any other parameters
  2. The default mode is 'overwrite'. You can change it using '--mode' parameter
  3. You can omit 'mysqlh' parameter to connect local MySQL server
  4. It is necessary to specify 'port' parameter only if it differs from the default MySQL port 3306
  5. If you omit 'tab_file' parameter, all tables from the specified schema will be converted
  6. Command line parameters that contain spaces should be enclosed in quotes (for example --dest="my database")


Connect to PostgreSQL Server

On this wizard screen you should enter the necessary settings to establish connection with PostgreSQL server.

MySQL-to-PostgreSQL can connect to both local PostgreSQL server (launched on the same machine where you run the program) and remote one (PostgreSQL server launched on another computer). To connect to local server, leave "Host" field empty or specify "127.0.0.1" or "localhost". For remote server specify its IP address or network name in the "Host" field.

It is necessary to specfy PostgreSQL port only if it differs from the default value 5432. Otherwise, you may leave this field empty.

By default PostgreSQL server tries to connect to database with name equal to the username specified. If there is no such database or PostgreSQL server does not allows access to it, you can specify custom database on this wizard page.


Connect to MySQL Server

On this wizard screen you should enter the necessary settings to establish connection with MySQL server. MySQL-to-PostgreSQL is able to connect to 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 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 you're willing to connecting to MySQL over SSH, see Connecting to MySQL over SSH article for related information.

NOTE: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by MySQL-to-PostgreSQL. See Troubleshooting article to learn how to resolve this problem.


Connecting to MySQL over SSH

Database administrators and security people know that it is good practice to open only local access to MySQL server for security reasons. However, if you want to connect any of Intelligent Converters applications to MySQL server hosted on a Linux/Unix system, it is necessary to open remote access from your local IP address on the destination server. Obviously it makes MySQL server less secure. To improve the situation you can use SSH protocol for secure data transfer between local machine running Intelligent Converters application and MySQL server.

The most easy way of connecting MySQL through SSH is to use port-forwarding through an SSH tunnel. This technique is based on creating an alias that makes any client application think it's connecting to "localhost" machine, while it's really connecting to the remote server through the tunnel.

If you are using the command line ssh, the command would look like this:

ssh -L <localport>localhost<remoteport> <username>@<servername>

If you use the standard MySQL port, both "localport" and "remoteport" must be set to 3306. You can do the same via tools having graphical user interface like Putty or SecureCRT.

Once you've got the ssh tunnel going, you can open any of Intelligent Converters application and enter in the details for remote MySQL server, using 'localhost' as the server host. See below example of MySQL connection page for MySQL-to-PostgreSQL converter.


Customize Conversion

MySQL-to-PostgreSQL provides two options to convert MySQL database into PostgreSQL data source:

  • Migrate to PostgreSQL server directly - use this approach to migrate data from the source database to PostgreSQL server directly. To use this option you need direct access to PostgreSQL server and necessary privileges to create a database on the target server. See Connect to PostgreSQL Server article for related information.
  • Export into script file - use this approach to get more control over the process. Following this way, MySQL-to-PostgreSQL exports the contents of the source database into a local script file (.sql) instead of moving it to PostgreSQL server directly. The resulting file contains SQL statements to create all tables and to fill them with the data. See Importing script file article for related information.

You can choose one of these approaches on the first page of MySQL-to-PostgreSQL wizard using the corresponding radio buttons.


Edit Table

On this wizard screen users can edit table name and customize types mapping.

MySQL-to-PostgreSQL converts MySQL types into the most appropriate PostgreSQL types. However, sometime it is necessary to change type for one or multiple columns. To customize type mapping select the appropriate item in the listview on the left and specify new name, type and default value for the field using "Field info" group of controls. Please note that listview on the left contains column attributes for the original MySQL database, new custom types and attributes will be displayed in "Field info" controls when the corresponding item is highlighted in the listview.


Importing script file

This article explains how to export script file to PostgreSQL server using the most popular tools.

1. PostgreSQL console client (psql)

Syntax of the command line depends on the structure of the dump file. If the dump file contains 'CREATE DATABASE' statement you should call the tool as follows:

psql -h hostname -U username -f {SQL script file name}

Otherwise:

psql -h hostname -d databasename -U username -f {SQL script file name}

Of course you need to replace parameters in figure brackets {...} by actual values.

2. phpPgAdmin

To import SQL script file into PostgreSQL database using this tool, take the followin gsteps:

  1. Select the database
  2. Navigate to the "SQL" button
  3. Click the "Choose File" button and select the SQL file from your computer
  4. Click "Execute"


Merging and Overwriting

If you try to migrate MySQL data to an existing PostgreSQL database, MySQL-to-PostgreSQL allows you to choose one of the following options: overwrite the database, merge into the database or specify another destination name.

  • Select 'Overwrite the entire database' option to replace the entire contents of the destination PostgreSQL database with MySQL data. Note that all data contained in PostgreSQL database will be lost.
  • Select 'Overwrite existing tables only' option to replace the contents of PostgreSQL tables having the same names as tables in source MySQL database.
  • Select 'Skip existing tables only' option to not convert existing tables.
  • Select 'Merge' option to merge MySQL data into the destination PostgreSQL database. MySQL-to-PostgreSQL implements quite simple merging algorithm. If a table has primary key or an unique index, the program inserts into PostgreSQL database only those records whose key/index value does not exist in the destination table. Otherwise, MySQL-to-PostgreSQL appends the whole table contents to the corresponding PostgreSQL table. Note: the structures of source and destination tables must be identical.
  • Select 'Synchronize' option to update existing PostgreSQL tables with MySQL data. See Synchronization article for the further information.

If you decide to enter another destination name just click 'Cancel' button.


Profile

Profile is a set of parameters that entirely defines the program session. It includes MySQL and PostgreSQL connection information (host, user name, etc) and all necessary settings. If you have created a profile, you can load it by pushing "Load Settings" button on the first wizard screen.

Once you enter all the information for converting databases, you can store it into a profile and reuse in next sessions. If you don't use any profile initially or change some parameters while working with the Database Conversion Wizard, you can store the updated information on exit by pushing "Save Settings" button on the last wizard screen.

NOTE: do not modify a profile manually since it may cause incorrect interpretation of conversion settings.


Quick Launch

Once you stored conversion settings into profile you can run the program in Quick Launch mode. Just launch Windows Explorer and double-click on the profile description (.s2p) file with left mouse button. This will cause MySQL-to-PostgreSQL wizard to launch conversion loading the necessary settings from the specified profile. Then you will be taken right to the screen with progress of conversion without entering all conversion settings.

See Profile article for more information about profiles.


Select Databases

On this wizard screen you should enter source MySQL and destination PostgreSQL database names. Also, you can customize the conversion process using the following settings:

  • Convert table definitions only - select this checkbox to convert only table definitions of the original PostgreSQL database
  • Skip converting indexes - select this checkbox to if you don't want to convert indexes select
  • 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.


Select Tables

On this wizard page you can manage the list of MySQL tables to convert.

In the left part of the wizard page you can see "Available tables" list box. It contains all available tables of the source database except those that you have selected already. On the right you can see "Selected tables" list box. To add new MySQL table for migrating to PostgreSQL server you should select it in "Available tables" list box and click "Add" button (or double-click the selected item). Also you can use "Add all" button to insert all available MySQL tables. To remove an item from "Selected tables" list you should select it and click "Remove" button. You can use "Remove all" button to remove all items from "Selected tables" list box.

Push "Add Query" button to add SELECT-query filtering data that will be migrated to PostgreSQL server. See Using Queries article for the further information.

To edit selected 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.


Select Views

On this wizard screen you can manage the list of MySQL views to convert into PostgreSQL. In the left part of the screen you can see "Available views" list box. It contains all available views of the source database except those that you have selected already. On the right you can see "Selected queries" list box. To add new MySQL view for converting into PostgreSQL you should select it in "Available queries" list box and click "Add" button (or double-click the selected item). Also you can use "Add all" button to add all available PostgreSQL views. To remove an item from "Selected views" list you should select it and click "Remove" button. You can use "Remove all" button to remove all items from "Selected views" list box.

Some views may depend on another views that must be created first. Use the up and down buttons to control the order in which views are converted. Highlight an item in "Selected views" list and then you will be able to move it up and down using those buttons.


Synchronization

MySQL-to-PostgreSQL implements synchronization as combination of insert MySQL records that are missing in PostgreSQL table and update existing PostgreSQL records with MySQL data. For synchronization purpose source and destination tables must have identical structures and have primary key or unique index defined.

Assume both MySQL and PostgreSQL databases have table "People" defined as below:

    Table1(
        ID Integer NOT NULL,
        FName Varchar(50),
        LName Varchar(50),
        EMail Varchar(50),
        Primary Key (ID)
    );

This table has the following contents in MySQL database:

2 | Andrew | White | a.white@corporation.com
3 | Phil | Johnson | p.johnson@gmail.com

In PostgreSQL database:

1 | John | Smith | j.smith@msn.com
2 | Andew | White | a.white@yahoo.com
After migrating MySQL data to PostgreSQL in "Synchronize" mode MySQL table is kept untouched and PostgreSQL table is modified as (pay attention to the cell marked red):
1 | John | Smith | j.smith@msn.com
2 | Andew | White | a.white@corporation.com
3 | Phil | Johnson | p.johnson@gmail.com


Troubleshooting

While working with MySQL-to-PostgreSQL converter you may encounter unexpected behavior or result. Use the following information to solve some common problems and get the correct conversion results. This summary is organized as list of problem-resolution pairs. Each problem description starts with "P:" and each resolution - with "R:".

P: Error message "The ordinal XXXX could not be located in the dynamic link library LIBEAY32.dll."
R: Check that you have PostgreSQL installed on the same machine where you run the application from. Also please make sure that the path to PostgreSQL installation folder is in $PATH system variable. If the problem persists, copy sql2pgs.exe into PostgreSQL BIN subfolder and try to run it from there.

P: Error message "The program does not support authentication protocol requested by server..."
R: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by MySQL-to-PostgreSQL. You can use OLD_PASSWORD function to make the certain account compatible with MySQL-to-PostgreSQL. Login to MySQL server with administrator privileges and run the following queries:

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('some_pass')
        -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;

Of course, you should place actual values instead of 'some_pass', 'some_host' and 'some_user'. Now you can use 'some_user' account with MySQL-to-PostgreSQL.

P: Error message "Can't connect to MySQL server on <host name or IP>".
R: Make sure MySQL server is launched on the target machine. Also please check the port number field to verify that you have specified the correct number.

P: Error message "Access denied for user: <user-name>"
R: You will not be able to connect to the target MySQL server until you have the appropriate permissions. Ask your MySQL server administrator to give you sufficient permissions to create new databases on the target MySQL server.

P: Error message "Access denied for user: <user name> to database <database>"
R: If the destination database already exists, you should have necessary privileges to add/remove tables. If the destination database doesn't exist, you should have sufficient privileges to create new databases on the target PostgreSQL server.

P: I cannot uninstall MySQL-to-PostgreSQL.
R: If you need to remove MySQL-to-PostgreSQL product from your computer, you can use "Add or Remove Programs" option in the Windows Control Panel. If this option is not available for some reason, you can call uninstall procedure running the following command line from the installation folder:

UNWISE.EXE sql2pgs.log

If you came across some other kind of problem while using MySQL-to-PostgreSQL converter, please contact our support team at service@convert-in.com


Using Queries

Queries give you the ability to filter MySQL data for migrating to PostgreSQL server. The following examples are provided to illustrate how to use queries for particular purposes. Assume that we have table "Table1" as below:

   Table1(
    ID INT,
    FName VARCHAR(50),
    LName VARCHAR(50),
    Birthday DATE,
    Notes TEXT
   );

Example 1. Convert certain records.

    SELECT * FROM Table1 WHERE ID > 1000
Example 2. Choose and rename columns.

    SELECT FName as FirstName, LName as LastName FROM Table1

Example 3. Skip records containing NULL values.

    SELECT * FROM Table1 WHERE Notes IS NOT NULL

Example 4. Merge two tables.

Assume, you have two tables defined as follows:

   ProductLines(
    productline VARCHAR(50),
        decsription VARCHAR(1000),
    photo       IMAGE
   );
   Products(
    code        VARCHAR(15),
    name        VARCHAR(70),
    productline VARCHAR(50),
    vendor      VARCHAR(50),
    description TEXT,
    quantity    SMALLINT,
    price       FLOAT
   );

If you want to get product code and product name from the "Products" table and description of product lines from the "ProductLines" table, it is possible to be done using the following query:

SELECT code,
       name,
       description
FROM Products T1
INNER JOIN ProductLines T2 ON T1.productline = T2.productline;

You can add queries on "Select Tables" wizard screen. See Select Tables article for related information.


Contents

Add Query

Command Line

Connect to PostgreSQL Server

Connect to MySQL Server

Connecting to MySQL over SSH

Customize Conversion

Edit Table

Importing script file

Merging and Overwriting

Profile

Quick Launch

Select Databases

Introduction

Select Tables

Select Views

Troubleshooting

Synchronization

Using Queries