Set up and configure PostgreSQL ODBC drivers for the MySQL Workbench Migration Wizard

If you plan to use the MySQL Workbench Migration Wizard to migrate databases from PostgreSQL to MySQL you first need to configure an ODBC driver to connect to your PostgreSQL server.

In this post I’ll cover how to set up and configure psqlODBC, the official ODBC driver for PostgreSQL.

Installing the psqlODBC Driver

The procedure is different for every platform so make sure to follow the instructions that correspond to the OS where you have MySQL Workbench installed. The driver needs to be installed in that machine, as explained in the MySQL Workbench documentation.

Important: Always install the 32 bit version of the driver, even on x64 systems, as MySQL Workbench is a 32 bits application.

Windows

If you are running MySQL Workbench on Windows download the MSI package for psqlODBC. Go to its download page (http://www.postgresql.org/ftp/odbc/versions/msi/) and pick the most recent file from the list. (The most recent one is at the bottom of the page.)

Unzip the downloaded file and install the provided MSI package. This is all you have to do. You can skip the rest of this post.

Linux

The Migration Wizard uses iODBC as a driver manager for all of its ODBC connections in Linux. This may give you some troubles because most Linux distributions provide ODBC drivers compiled against unixODBC. This is another driver manager not supported by MySQL Workbench so you won’t be able to use those drivers unless you compile them against iODBC. Here’s what you should do.

Make sure that you have iODBC installed. If you are using Debian, Ubuntu or another Debian based distro, type this command in your terminal:

$> sudo apt-get install iodbc libiodbc2-dev libpq-dev libssl-dev

For RPM based distros (RedHat, Fedora, etc.) type this command instead of the previous one:

$> sudo yum install iodbc iodbc-dev libpqxx-devel openssl-devel

Now we need to install the PostgreSQL ODBC drivers.

Download the psqlODBC source tarball from here. Use the latest version available for download. As of this writing the latest version corresponds to the file psqlodbc-09.01.0200.tar.gz. Extract this tarball to a directory in your hard drive and open a terminal and cd into that directory.

Type this in the terminal window:

$> ./configure --with-iodbc --enable-pthreads
$> make
$> sudo make install

Verify that you have the file psqlodbcw.so in the /usr/local/lib directory.

Mac OS X

To build psqlODBC in Mac OS X, you need to have Xcode and the “Command Line Tools” additional component installed, so that the gcc compiler is available. You can get Xcode for free in the AppStore and install the Command Line Tools from Preferences -> Downloads.

Download the psqlODBC source tarball from here. Use the latest version available for download. As of this writing the latest version corresponds to the file psqlodbc-09.01.0200.tar.gz. Extract this tarball to a directory in your hard drive and open a terminal and cd into that directory.

Type this in the terminal window:

$> ./configure --with-iodbc --enable-pthreads
$> CFLAGS="-arch i386 -arch x86_64" make
$> sudo make install

Registering your ODBC Driver (Linux and Mac OSX)

Open the iODBC Data Source Administrator application either by typing iodbcadm-gtk in the command line or by launching it from the Overview page of the MySQL Workbench Migration Wizard, using the Open ODBC Administrator button. From the main MySQL Workbench screen you can start the Migration Wizard by clicking on the Database Migration launcher in the Workbench Central panel or through Database > Migrate in the main menu.

Go to the ODBC Drivers tab. You should have something like this:

iodbcadm-gtkClick on the Add a driver button and fill the values as shown here:

psqlODBC_config

After you click OK you should have the psqlODBC driver registered.

Sergio de la Cruz

17 thoughts on “Set up and configure PostgreSQL ODBC drivers for the MySQL Workbench Migration Wizard”

  1. in Ubuntu 14.04 you have to install it this way:

    sudo apt-get install iodbc libiodbc2 libiodbc2-dev libpq-dev libssl-dev
    sudo apt-get install odbcinst1debian2 odbc-postgresql

  2. I am trying to install the odbc driver and when I run the command above on my Mac I get “configure: error: iodbc-config not found (required for iODBC build)”

    What can be the problem?

    Thanks!

  3. Problem: trying to install the odbc driver and when I run the command above and get “configure: error: iodbc-config not found (required for iODBC build)”

    Solution:
    ./configure –with-iodbc=/usr/local/iODBC/bin/iodbc-config –enable-pthreads

  4. ERROR:
    There is a problem while building.
    configure: error: iodbc-config not found (required for iODBC build)

    SOLUTION:
    Correct command for MacOS (macOS) is:
    ./configure –with-iodbc=/usr/local/iODBC/bin/iodbc-config -enable-pthreads

    /usr/local/iODBC/include/sql.h:89:10: fatal error: ‘iODBC/sqltypes.h’ file not found
    #include
    ^~~~~~~~~~~~~~~~~~

  5. So this can be done on macOS Sierra. Instead of using the iODBC install as recommended above, I used brew and installed the unixodbc (brew install unixodbc). Once that is done you can run the configure command with the unixodbc flag and the location of you odbc_config file. (./configure –with-unixodbc=/usr/local/Cellar/unixodbc/2.3.5_1/bin/odbc_config -enable-pthreads).
    Then –> CFLAGS=”-arch i386 -arch x86_64″ make
    Then –> sudo make install

    Hope this helps.

  6. When you get this error: “error: invalid variable name” than make sure you replace the dashes for original dashes, because you could accidentally copy/pasted the wrong unicode character for the dashes — like i did.

  7. Coming back to my previous comment…

    In the end I had a lot of problems with compiling psqlodbc.so myself, while using iODBC to “activate” the compiled driver. To summarize: I got error after error.

    I eventually gave up using the above described method, as it took me hours, and investigated other possibilities. Eventually i came up with this new method which only took me 15 minutes to get the PostgreSQL driver running.

    By the way, this only method only describes how to do this on a Mac and i only tested it on macOS High Sierra (10.13.3)

    Installation instructions:

    1. Compile psqlodbc.so using brew by using: brew install psqlodbc (see: http://brewformulas.org/Psqlodbc)
    2. Install ODBC Manager instead of iODBC Driver Manager at http://www.odbcmanager.net/
    3. Point to the psqlodbcw.so driver file using ODBC Manager (mine was located at /usr/local/Cellar/psqlodbc/10.01.0000/lib/psqlodbcw.so)

    Done!

    Hope this saves you some time.

  8. WOW! Just wow.

    Your comment stopped the insanity that I was going through. Literally: error after error. I was at a point where I was getting tired of googling for solutions to resolve this mess. Thank you Mike for literally saving me from this throbbing headache. Almost started to tear my hair out with this “compile your driver” mumbo-jumbo. They give 3 simple commands as if “it just works!”.
    Once again, thanks for sharing your solution. Worked great for me (macOS mojave).

    Made an account on the site just to say thanks.

Leave a Reply