Introduction

PostgreSQL-to-MySQL is a program to migrate PostgreSQL databases to MySQL server. Depending on your privileges on the target MySQL server you can export PostgreSQL data into new database or overwrite the contents of an existing MySQL 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)
  • Option to export PostgreSQL data into MySQL dump file
  • Filter data to convert via SELECT-queries (see Using Queries article for related information)
  • Merges PostgreSQL data into an existing MySQL database
  • Command line support (see Command Line article for related information)
  • Easy-to-use wizard-style interface
  • Full install/uninstall support

Requirements

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


Add Query

This dialog allows you to compose a SELECT-query and result of this query will be migrated to MySQL server as if it would be a regular table. Select name of table where you wish to store query results in the desintation 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 query by running it from pgAdmin or any other PostgreSQL client tool.

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 PostgreSQL-to-MySQL converter called P2SAGENT.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=... | MySQL database or dump file name
--help | display help message and exit
--logfile=... | path to the logfile where execution traces will be written
--mode=... | how to process an existing MySQL database (0 - overwrite the entire database, 1 - overwrite existing tables only, 2 - skip existing tables, 3 - merge, 4 - synchronize)
--mysqlh=... | MySQL server IP address or network name
--mysqlu=... | MySQL user name
--mysqlp=... | MySQL user password
--myport=... | MySQL port
--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=... | PostgreSQL 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 PostgreSQL database "db 1" into MySQL database "db1_sql":

P2SAGENT.EXE --src="db 1" --posgsu=postgres --posgsp=qwerty --dest="db1_sql"
--mysqlh=orahost --mysqlu=SYSTEM --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 (3306 - for MySQL, 5432 - for PostgreSQL)
  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.

PostgreSQL-to-MySQL 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. PostgreSQL-to-MySQL 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 PostgreSQL-to-MySQL 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.

You should have the sufficient privileges to create new database on MySQL server. Otherwise you will be able only to import PostgreSQL data into an existing MySQL database.

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


Conversion Settings

On this wizard screen you can specify PostgreSQL schema (drop down the combo box to select from list or enter it manually) and customize the conversion process using the following settings:

  • MySQL character set - tune this parameter to set up correct encoding of text values in the database. Please refer to MySQL documentation to better understand the concept of character sets and collations.
  • MySQL engine type - storage engine determines basic characteristics of handling tables such as storage limits, transaction-safe, locking granularity, etc. Each of them has its own cons and pros. Please refer to MySQL documentation for the further information.
  • Convert table definitions only - select this checkbox to convert only table definitions of the original PostgreSQL database
  • Skip converting indexes - select this checkbox if you don't want to convert indexes

Note: leave schema name blank to convert tables from all schemas.


Customize Conversion

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

  • Move to MySQL server directly - use this approach to export the contents of the source database into a database on the target MySQL server. You need MySQL server launched and also necessary privileges to write into a database on the target server. See Connect to MySQL Server article for related information.
  • Store into dump file - use this approach to perform deferred conversion and get more control over the process. Following this way, the program stores the contents of the source database into a local dump file instead of moving it to MySQL server directly. The resulting file contains MySQL statements to create all tables and to fill them with the data. See Importing dump files article for related information.

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


Importing dump files

This article explains how to move dump file contents to MySQL server using the most popular tools.

1. MySQL console client (mysql)
Syntax of the command line depends on the structure of the dump file. If the dump file contains 'CREATE DATABASE' statement you should call MySQL console client as follows:

mysql --host=... --user=... --password=... -vvf < dump_file_name

Otherwise:

mysql --host=... --user=... --password=... -vvf MySQL_database_name < dump_file_name

where 'MySQL_database_name' is a name of an existing database.

2. phpMyAdmin
Note, that phpMyAdmin allows to import dump file into an existing MySQL database only, so the dump file should not contain 'CREATE DATABASE' statement.

Step 1. Click on the appropriate database name in the left frame.

Step 2. Click 'SQL' tab on the top of the screen.

Step 3. Enter full path to the dump file in 'Location of the textfile' field or click 'Browse' button to locate it through the directory tree.

Step 4. Click 'Go' button.


Merging and Overwriting

If you try to migrate PostgreSQL data to an existing MySQL database, PostgreSQL-to-MySQL 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 MySQL database with PostgreSQL data. Note that all data contained in MySQL database will be lost.
  • Select 'Overwrite existing tables only' option to replace the contents of MySQL tables having the same names as tables in source PostgreSQL database.
  • Select 'Skip existing tables only' option to not convert existing tables.
  • Select 'Merge' option to merge PostgreSQL data into the destination MySQL database. PostgreSQL-to-MySQL implements quite simple merging algorithm. If a table has primary key or an unique index, the program inserts into MySQL database only those records whose key/index value does not exist in the destination MySQL table. Otherwise, PostgreSQL-to-MySQL appends the whole table contents to the corresponding MySQL table. Note: the structures of source and destination tables must be identical.

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


MySQL dump file settings

On this wizard screen you can specify how the resulting dump file should be constructed. There are following settings available:

  • MySQL version - this parameter determines the syntax of MySQL statements being used in output dump file. 'Old' versions of MySQL (before 3.23.6) put more restrictions on database objects names. Note that 'new' syntax is not accepted by 'old' versions of MySQL. Use 'old' syntax whenever you are not sure about the version of your MySQL server.
  • Line breaks - this parameter specifies how to separate text lines in the dump file. If the destination MySQL server is running on MS Windows platform, you should select 'MS Windows style' option. Otherwise select 'Unix style'.
  • Add lock for write around 'INSERT' statements - it makes importing the contents of the dump file into a database much faster. To use this feature in MySQL 4.0 and higher you need global LOCK TABLES privilege and SELECT privilege on the involved tables. In MySQL 3.23 and earlier you need to have SELECT, INSERT, DELETE and UPDATE privileges for the tables.
  • Insert 'CREATE DATABASE' statement - select this option to put 'CREATE DATABASE' statement in the resulting dump file. This setting doesn't take effect without the option below activated.
  • Insert 'CREATE TABLE' statements - select this option to put 'CREATE TABLE' statements in the resulting dump file. You should always select it in order to obtain a valid dump file. The only exception is appending the data to existing tables.
  • Insert comments - select this option to add brief comments. It makes the resulting dump file more readable.

See Importing dump files article to learn how to move dump file contents to MySQL server.


Profile

Profile is a set of parameters that entirely defines the program session. It includes MySQL and PostgreSQL connection characteristics (host, user name, etc) and all of the necessary information for the database being converted. 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 (.p2s) file with left mouse button. This will cause PostgreSQL-to-MySQL 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 PostgreSQL schema name and the destination MySQL database name. If you have necessary permissions to list databases on PostgreSQL server, drop down the combo box to select source PostgreSQL database from the list.

Also, you can specify log file to write execution traces of the conversion process. Leave this field blank to disable logging.


Select Tables

On this wizard page you can manage the list of PostgreSQL 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 PostgreSQL table for migrating to MySQL 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 PostgreSQL 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 MySQL server. See Using Queries article for the further information.


Troubleshooting

While working with PostgreSQL-to-MySQL 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: The program can't start because LIBPQ.dll is missing from your computer. Try reinstalling the program to fix this problem.
R: LIBPQ.DLL is a part of PostgreSQL installation that is required for using PostgreSQL-to-MySQL. Check that you have PostgreSQL installed on the same machine where you run the application from. Also please make sure that the path to DB2 components folder is in $PATH system variable. If the problem persists, copy pgs2sql.exe into PostgreSQL BIN subfolder and try to run it from there.

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 pgs2sql.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 PostgreSQL-to-MySQL. You can use OLD_PASSWORD function to make the certain account compatible with PostgreSQL-to-MySQL. 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 PostgreSQL-to-MySQL.

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 MySQL server.

P: I cannot uninstall PostgreSQL-to-MySQL.
R: If you need to remove PostgreSQL-to-MySQL 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 pgs2sql.log

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


Using Queries

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

   Create Table1(
    ID SERIAL,
    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

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

Conversion Settings

Customize Conversion

Importing dump files

Merging and Overwriting

MySQL dump file settings

Profile

Quick Launch

Select Databases

Introduction

Select Tables

Troubleshooting

Using Queries