Migrating from MySQL to PostgreSQL using 4 Easy Methods

Easily move your data from MySQL to PostgreSQL to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

Migrating from MySQL to PostgreSQL can seem daunting, but it doesn’t have to be. PostgreSQL offers significant advantages over MySQL regarding features, performance, and stability. Transferring your database to PostgreSQL can position you for better scalability down the road, such as:

• Support for advanced data types like JSON and arrays
• A rich library of ready-to-use extensions
• Sophisticated concurrency and locking
• Excellent geospatial support

In this blog, we will learn 4 different methods of data migration from MySQL to PostgreSQL. By the end of this blog, you will learn the step-by-step process of connecting MySQL to PostgreSQL and also understand which method is the best for your case.

Need for migrating data from MySQL to PostgreSQL

• Advanced Features and Capabilities: PostgreSQL offers advanced features such as support for complex queries, full-text search, JSONB data type, and better handling of concurrent transactions through Multi-Version Concurrency Control (MVCC).
• Enhanced Performance and Scalability: PostgreSQL often performs better for complex queries and large datasets due to its sophisticated query planner and optimizer. It also scales well for read-heavy and write-heavy workloads.
• Compliance and Extensibility: PostgreSQL is highly standards-compliant (e.g., ANSI SQL) and offers extensive support for custom data types, operators, and functions. This flexibility can be crucial for businesses needing custom solutions.

Pre-Migration Considerations

A successful migration requires careful planning. Key considerations include:

1. Data Type Compatibility:

• Analyze: Assess and address differences in data types between MySQL and PostgreSQL.
• Transform: Define and implement data type transformations where necessary.

2. Migration Configuration:

• Create a configuration file: This allows for granular control over the migration process, including data mapping, transformations, and schema modifications.

3. Application Compatibility:

• Analyze: Evaluate the impact of the migration on existing applications.
• Test: Thoroughly test applications after migration to ensure compatibility.

4. Thorough Testing: Conduct unit, integration, and performance tests.

5. Backup and Recovery:

• Develop a disaster recovery plan.
• Create full backups.
• Test backup and restore procedures.

Methods To Migrate or Connect MySQL to PostgreSQL

Let us look at the four simple methods you can use for MySQL to PostgreSQL migration step by step.

1. Method 1: Connect MySQL to PostgreSQL with Pgloader
2. Method 2: Connect MySQL to PostgreSQL with Foreign Data Wrapper
3. Method 3: Connect MySQL to PostgreSQL with Py-mysql2pgsql
4. Method 4: Connect MySQL to PostgreSQL using Hevo Data

Method 1: Connect MySQL to PostgreSQL with Pgloader

What is Pgloader? An Overview

Pgloader is an open-source database migration tool that enables you to seamlessly move your data from different RDBMSs, like MySQL and SQLite to PostgreSQL. It has the capability to perform real-time transformations on the data it retrieves and execute raw SQL commands both before and after the loading process. It employs the COPY PostgreSQL protocol to stream the data into the server efficiently and handles errors by generating a pair of reject.dat and reject.log files.

You can connect MySQL to PostgreSQL using Pgloader in 6 simple steps:

Before you move forward with connecting Pgloader to PostgreSQL, a few prerequisites that must be taken into consideration:

• You must have access to servers that have a firewall.
• Both servers must also have a non-root user with sudo privileges.
• Both servers must also have a non-root user with sudo privileges.
• Use your PostgreSQL server as the client machine of MySQL to be able to migrate data using Pgloader.

Step 1: Create a Sample Database in MySQL

Begin by opening the MySQL prompt on MySQL server:

mysql -u root -p

Enter your MySQL user and password to open the prompt.

Now, let’s create a new database by giving the following command:

CREATE DATABASE sample_db;

With the USE command, switch to this database.

USE sample_db;

Use the following command to create a table in the database.

CREATE TABLE source_table (
	employee_id INT PRIMARY KEY,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	start_date DATE,
	salary VARCHAR(50)
);

Then, insert the following entries into the table.

INSERT INTO source_table (employee_id, first_name, last_name, start_date, salary) 
VALUES (1, 'Elizabeth', 'Cotten', '2007-11-11', '$105433.18'),
(2, 'Yanka', 'Dyagileva', '2017-10-30', '$107540.67'),
(3, 'Lee', 'Dorsey', '2013-06-04', '$118024.04'),
(4, 'Kasey', 'Chambers', '2010-08-18', '$116456.98'),
(5, 'Bram', 'Tchaikovsky', '2018-09-16', '$61989.50');

Now, we are ready to migrate our data from MySQL to PostgreSQL.

Step 2: Install Pgloader On Your System

Head to the Ubuntu APT repositories to install Pgloader using the apt command. However, if you want to use the SSL option in version v.3.5.1 and later, you’ll need to install Pgloader from the GitHub repository with the source code. 

Once the tool is installed, follow these steps to proceed with data migration.

• Use the following command to update the package index of the PostgreSQL server:

postgres-server$ 
sudo apt update

•  To install the dependencies of Pgloader, use the following commands and press ENTER.

postgres-server$ sudo apt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev

• Next, head to GitHUb project’s release page to find the most recent version of the tool. Then select Assets and copy the source code (tar.gz) link. Paste it into the curl command and then replace the https URL.

postgres-server$ curl -fsSLO https://github.com/dimitri/pgloader/releases/download/v3.6.9/pgloader-bundle-3.6.9.tgz

• Input the following command to extract the tarball.

postgres-server$ tar xvf v3.6.9.tar.gz

• Use the following command to head over to the parent directory of the new Pgloader.

postgres-server$ cd pgloader-3.6.9/

• Once done, compile the Pgloader binary using the following command.

postgres-server$ make pgloader

• Using the following command, move the Pgloader binary file to the /usr/local/bin directory.

postgres-server$ sudo mv ./build/bin/pgloader /usr/local/bin/

• Finally, input the following command to confirm the installation of the Pgloader version.

postgres-server$ pgloader –version

Step 3: Build a PostgreSQL Database and Role

The pgloader command copies source data from a file or a database and inserts it into a Postgres database. That is why you either need to run Pgloader as a Linux user with access to Postgres database or designate a Postgres role with the required rights in your load command.

• Use the following command to create a new role and request a password for the role.

postgres-server$ sudo -u postgres createuser --interactive -P

• You’ll then be prompted to add a name for the new role.

Enter name of role to add:

• In the next step, you’ll be required to create and  confirm the password.

Enter password for new role:
Enter it again:

• The script will then ask you if you would like to classify the new role as a superuser. 

Type ‘y’ and press ENTER.

Output

. . .

Shall the new role be a superuser? (y/n) y

• Finally, you’ll need to create a target database to load your data.

postgres-server$ sudo -u postgres createdb xxx

Step 4: Create a Dedicated User in MySQL and Manage Certificates

It is imperative to encrypt your network connection before you can even think of migrating data from one system to another. Encryption prevents your data from falling into the wrong hands and provides a safe environment for the data to be migrated. With a dedicated MySQL user, Pgloader will migrate your data over an SSL connection.

• Type in the following command into a MySQL prompt.

mysql -u root -p

• Next, create a MySQL user and remember to replace your_postgres_server_id with the public IP address of Postgres. You’ll also need to replace your password with a safe password or passphrase.

mysql> CREATE USER 'new_mysql_name'@'your_postgres_server_ip' IDENTIFIED BY 'password' REQUIRE SSL;

• Grant access to the new MySQL user to the target database.

GRANT ALL ON source_db.* TO ' new_mysql_name'@'your_postgresql_server_ip';

• Run the following command to reload the grant tables and enable privilege changes.

mysql> FLUSH PRIVILEGES;

• Exit the MySQL prompt using the following command.

mysql> exit

• The next step would be to enable the SSL for Pgloader. Use the following command to add the ca.pem client-cert.pem files to Ubuntu’s trusted certificate store. Also, change the file names and add the .crt file extension.

postgres-server$ sudo cp ~/client-ssl/ca.pem /usr/local/share/ca-certificates/ca.pem.crt

postgres-server$ sudo cp ~/client-ssl/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crt

• Finally, look for the certificates using the following command.

postgres-server$ sudo update-ca-certificates

Step 5: Migration of Data

pgLoader enables users to migrate an entire database with just a single command. In the case of migrating from a MySQL to PostgreSQL database on a separate server, the command would take the following syntax:

pgloader mysql://mysql_username:password@mysql_server_ip_/source_database_name?option_1=value&option_n=value 

postgresql://postgresql_role_name:password@postgresql_server_ip/target_database_name?option_1=value&option_n=value

This encompasses the pgloader command along with two connection strings—one for the source database and the other for the target database. In both connection strings, the declaration begins by specifying the type of DBMS to which the connection string refers, followed by the username and password with database access (separated by a colon), the server’s host address where the database is located, the name of the database that pgLoader should target, and various options influencing pgLoader’s behavior.

You can use the following command to migrate data from MySQL to PostgreSQL. Make sure to enter your database details in the command.

pgloader mysql://pgloader_my:mysql_password@mysql_server_ip/sample_db?useSSL=true postgresql://pgloader_pg:postgresql_password@localhost/new_db

You will see the following output if the command is successful.

Output
. . .
             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
        fetch meta data          0          2                     0.111s
         Create Schemas          0          0                     0.001s
       Create SQL Types          0          0                     0.005s
          Create tables          0          2                     0.017s
         Set Table OIDs          0          1                     0.010s
-----------------------  ---------  ---------  ---------  --------------
 source_db.sample_table          0          5     0.2 kB          0.048s
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          4                     0.052s
 Index Build Completion          0          1                     0.011s
         Create Indexes          0          1                     0.006s
        Reset Sequences          0          0                     0.014s
           Primary Keys          0          1                     0.001s
    Create Foreign Keys          0          0                     0.000s
        Create Triggers          0          0                     0.000s
       Install Comments          0          0                     0.000s
-----------------------  ---------  ---------  ---------  --------------
      Total import time          ✓          5     0.2 kB          0.084s

Step 6: Execute the Following Commands to Check if the Migration Was Successful

• To verify the accuracy of the data migration, access the PostgreSQL prompt

postgres-server$ sudo -i -u postgres psql

• Next, establish a connection to the database where you loaded the data:

postgres-server$ \c new_db

• Then run the following query to test whether the migrated data is stored in your PostgreSQL database:

SELECT * FROM sample_db.source_table;

If the data was loaded correctly, you will see an output table of the data you migrated.

Output
 employee_id | first_name |  last_name  | start_date |   salary   
-------------+------------+-------------+------------+------------
           1 | Elizabeth  | Cotten      | 2007-11-11 | $105433.18
           2 | Yanka      | Dyagileva   | 2017-10-30 | $107540.67
           3 | Lee        | Dorsey      | 2013-06-04 | $118024.04
           4 | Kasey      | Chambers    | 2010-08-18 | $116456.98
           5 | Bram       | Tchaikovsky | 2018-09-16 | $61989.50
(5 rows)

Close the Postgres prompt with the following command:

\q

Other Options for Data Migration using pgloader

pgloader is a flexible tool that supports multiple ways of connecting MySQL to PostgreSQL. Let us look at some of them:

MySQL to PostgreSQL with pgloader Load File

A load file or command file in pgloader is a file that instructs pgloader on how to carry out the migration. It can include various commands and options that gives you a larger control over data loading to PostgreSQL, allowing you to perform more complex migrations.

Let us take an example and perform the same migration as we did in Step 5.

Begin by creating a new load file on the Postgres server.

nano pgload_test.load

Next, add details of your MySQL and PostgreSQL servers.

LOAD DATABASE
     FROM      mysql://pgloader_my:mysql_password@mysql_server_ip/sample_db?useSSL=true
     INTO     pgsql://pgloader_pg:postgresql_password@localhost/new_db

 WITH include drop, create tables

ALTER SCHEMA 'sample_db' RENAME TO 'public'
;

Breakdown of the code:

• LOAD DATABASE instructs pgloader to retrieve data from a separate database rather than an archive or a file.
• FROM specifies the database source, in this case MySQL.
• INTO specifies the database destination, in this case PostgreSQL.
• The WITH clause defines specific behavior for pgloader. In this example we have used the following two:
  • include drop: When utilizing this option, pgLoader will eliminate any tables in the destination PostgreSQL database that are also present in the source MySQL database. If you opt for this approach during data migration to an existing PostgreSQL database, it is advisable to create a comprehensive backup of the entire database to prevent any data loss.
  • create tables: This option instructs pgLoader to generate new tables in the destination PostgreSQL database using the metadata stored in the MySQL database. Conversely, if the alternative option, create no tables is chosen, the target tables must preexist in the PostgreSQL database before initiating the migration.
• ALTER SCHEMA: After the WITH clause, you can append specific SQL commands like this to guide pgLoader in executing additional actions. In this example, we direct pgLoader to modify the schema of the new PostgreSQL database from source_db to public, but only after creating the schema.

After completing the addition of this content, save and close the load file. To apply it, include the filename as an argument when invoking the pgloader command:

pgloader pgload_test.load

To test if the migration was successful, repeat Step 5.

Migration of MySQL database to PostgreSQL Locally

You have the option to utilize pgLoader for migrating a MySQL database to a PostgreSQL database located on the same machine. Simply execute the migration command from a Linux user profile with access for the root MySQL user:

pgloader mysql://root@localhost/sample_db pgsql://sammy:postgresql_password@localhost/target_db

Conducting a local migration in this manner eliminates the need to modify MySQL’s default networking configuration or your system’s firewall rules.

MySQL to PostgreSQL Migration from CSV Files

It is possible to migrate data from MySQL to PostgreSQL from a CSV file.

Suppose you have a CSV file load.csv, the command for migration will look like this:

pgloader load.csv pgsql://sammy:password@localhost/target_db

Also Read: How to Export a PostgreSQL Table to a CSV File

Skip these long steps for your data migration and sign up for Hevo’s 14-day free trial to Get Started with Hevo for Free

Method 2: Connect MySQL to PostgreSQL with Foreign Data Wrapper

To connect MySQL to PostgreSQL through Foreign Data Wrapper, you must take note of the following as this is what will be required to create the connection:

• CREATE EXTENSION command to create a MySQL Foreign Data Wrapper extension to the PostgreSQL host.
• CREATE SERVER command to define a connection to the MySQL Server.
• CREATE USER MAPPING command to define a mapping that associates a Postgres role with the server.
• CREATE FOREIGN TABLE command to define a single table in the Postgres database that corresponds to a table that resides on the MySQL Server or use the IMPORT FOREIGN SCHEMA command to import multiple remote tables in the local schema.

Step 1: Create an Extension

In the first step of using Foreign Data Wrapper to connect MySQL to PostgreSQL, the CREATE EXTENSION command is used to load a new extension to a current database, and there must not be an extension of the same name already loaded.

It creates new SQL objects such as functions, data types, operators, and index support methods. The user who runs CREATE EXTENSION becomes the owner of the extension.

To create an extension to connect to MySQL, create the mysql_fdw extension to connect to the Postgres database from which you will query the MySQL server by inputting mysql_fdw as the extension_name. 

CREATE EXTENSION [IF NOT EXISTS] extension_name [WITH] [SCHEMA schema_name];

Here is the list of parameters used in this step to connect MySQL to PostgreSQL:

• IF NOT EXISTS: This is used to instruct the server to issue a notification to you instead of returning an error if an extension with the same name already exists.
• extension_name: This is the name of the extension to be installed. As can be seen from the illustration above, mysql_fdw is used as the extension name.
• schema_name: Use this to specify the schema’s name in which to install the extension’s object, and the named schema must already exist. An example of CREATE EXTENSION showing how to connect to MySQL using mysql_fdw is as follows:

CREATE EXTENSION mysql_fdw;

Step 2: Create a Server

The command CREATE SERVER defines a new foreign server and its connections. It gives the connection information that a Foreign Data Wrapper uses to access an external data resource.

The server is owned by the user who defines it, and its name must be unique within the database. An example of this is given below:

CREATE SERVER [ IF NOT EXISTS ] server_name[TYPE ‘server_type’]
FOREIGN DATA WRAPPER fdw_name
[OPTIONS (option ‘value’ [, …] ) ]

Here is the list of parameters used in this step to connect MySQL to PostgreSQL:

• IF NOT EXISTS: This is used to instruct the server to issue a notification to you instead of returning an error if an extension with the same name already exists.
• server_name: This is used to identify the name of the foreign server to be created e.g., mysql_server.
• server_type: This is an optional server type and may be helpful in Foreign Data Wrappers.
• FOREIGN DATA WRAPPER fdw_name: This identifies the name of the Foreign Data Wrapper that manages the server. To connect MySQL to Postgres, use mysql_fdw when creating the server connection.
• OPTIONS (option ‘value’ [, …] ): This is used to specify the options for the server as they clearly define the connection details of the server. They include the following:
  • host: Use this to input the address or hostname of the MySQL server; the default value is 127.0.0.1.
  • port: This is where you type the port number of the MySQL server. The default value is 3306.
  • secure_auth: This is to enable or disable secure authentication; the default value is true.
  • init_command: This is the SQL statement that executes when connecting to the MySQL Server.
  • ssl_key: This is the pathname of the client’s private key file.
  • ssl_cert: This is the name of the client public key certificate file.
  • ssl_ca: This is the name of the Certificate Authority (CA) certificate file. If this option is used, it must specify the same certificate used by the server.
  • ssl_capath: This is the directory’s name that contains the trusted SSL CA certificate files.
  • ssl_cipher: This is the list of permissible ciphers for SSL encryption.
  • use_remote_estimate: This is included to instruct the server to use EXPLAIN commands on the remote server when estimating processing cost; its default value is false.

An example of CREATE SERVER command for the MySQL server is shown below:

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw [OPTIONS (host ‘127.0.0.1’, port ‘3306’ ) ;

Step 3: Create User Mapping

This defines a mapping that joins a Postgres role to a foreign server like a MySQL server. It covers the connection information that a Foreign Data Wrapper uses together with the information from the foreign server to access an external data source.

CREATE USER MAPPING [ IF NOT EXISTS ] FOR { user_name | USER | CURRENT_USER | PUBLIC }
SERVER server_name
[OPTIONS ( option ‘value’ [, …] ) ]

Here is the list of parameters used in this step to connect MySQL to PostgreSQL:

• IF NOT EXISTS: This is used to instruct the server to issue a notification to you instead of returning an error if a mapping of the given user to the foreign server already exists.
• user_name: This is the existing user’s name mapped to the foreign server. USER and CURRENT_USER must match the name of the current user, and PUBLIC is used when public mapping is created with no user-specific mapping.
• server_name: This indicates the server name for which the user mapping will be created.
• OPTIONS ( option ‘value’ [, …] ): This specifies the options of the user mappings; they include:
  • username: The name of the user on the MySQL server.
  • password: The password associated with the username.

An example of CREATE USER MAPPING command for MySQL server below shows a user mapping named public with server name mysql_server. When connected to the server, it authenticates with a username ‘test’ and has ‘secret’ as a password:

CREATE USER MAPPING FOR public SERVER mysql_server OPTIONS ( username ‘test’, password ‘secret’ ) ;

Step 4: Create a Foreign Table

A foreign table acts as a pointer to a table that resides on the MySQL host. This means that you will create a foreign table definition on the Postgres Server that would connect to a table created on MySQL Server, where columns in the table will map to columns in the Postgres Server.

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name( [ { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation] [ column_constraint[ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
  PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ] partition_bound_spec
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED }
and table_constraint is:
[ CONSTRAINT constraint_name ]
CHECK ( expression ) [ NO INHERIT ]

Here is the list of parameters used in this step to connect MySQL to PostgreSQL:

• IF NOT EXISTS: This is used to instruct the server to issue a notification to you instead of returning an error if a relation with the same name already exists.
• table_name: This specifies the name of the foreign table and includes a schema name to specify the schema in which the foreign table would reside.
• column_name: This is the column’s name to be created in the new table; each column should correspond to a column on the MySQL server.
• data_type: This identifies the data type of the column. To have a smooth connection, specify the same data type for each column on the Postgres Server and the MySQL Server.
• COLLATE collation: This is used to assign a collation to the column and must be of a collatable data type. If this is not specified, the column data type’s default collation is used.
• INHERITS ( parent_table[, … ] ): The INHERITS clause is used to specify a list of tables from which the new foreign table automatically inherits all columns. Parent tables can be plain tables or foreign tables.
• PARTITION OF parent_table FOR VALUES partition_bound_spec: This is a form used to create the foreign table as a partition of the parent table with allocated bound values.
• CONSTRAINT constraint_name: This is used to specify an optional name for a column or table constraint, and if not specified, the server generates a constraint name automatically.
• NOT NULL: This simply means that the column is not permitted to have null values.
• NULL: This simply means that the column is allowed to have null values, which is the default value.
• CHECK (expression) [NO INHERIT]: The CHECK clause is used to specify an expression that produces a Boolean result that each row in a table must obey. A CHECK constraint specified as a column constraint references the column values only, whereas an expression of a table constraint references multiple tables.
• DEFAULT default_expr: The DEFAULT clause is used to specify a default data value for the column whose column definition appears within. The data type of the default expression must match that of the data type in the column.
• GENERATED ALWAYS AS ( generated_expr )STORED: This clause is used to create the column as a generated column, it can not be written to, and when it is read, it returns the result of the specified expression.
• server_name: This is the name of an existing foreign server that will use the foreign table. If connecting to MySQL to query a table that resides in the MySQL file system, ensure to include the SERVER and the server_name of the foreign server that uses the MySQL Data Adapter.
• OPTIONS (option ‘value’ [, …] ): These options would be associated with the new foreign table or one of its columns and are specific to each Foreign Data Wrapper. For connection to MySQL, specify the following options:
  • dbname: The name of the database on the MySQL server.
  • table_name: This is the table’s name on the MySQL server; the default value would be the foreign table’s name.
  • max_blob_size: This is used to specify the maximum blob size to read without truncation.

Below is an example of a syntax that shows how a table is created on a Postgres host that maps the columns of a MySQL table to the columns of a Postgres table for data stored on a MySQL Server. The first is for a MySQL table and has the following definitions:

CREATE TABLE warehouse (
warehouse_id      INT PRIMARY KEY,
warehouse_name    TEXT,
  warehouse_created TIMESTAMP);

For the Postgres Server, execute the following command to create a comparable table on it:

CREATE FOREIGN TABLE warehouse
(
  warehouse_id      INT,
  warehouse_name    TEXT,
  warehouse_created TIMESTAMP
)
SERVER mysql_server
         OPTIONS (dbname 'db', table_name 'warehouse');

Step 5: Import Foreign Schema

The IMPORT FOREIGN SCHEMA command is used to import table definitions on the Postgres Server from a foreign server, MySQL Server. It creates foreign tables that represent already existing tables on a foreign server. 

It usually imports all tables and views existing in a particular schema on the foreign server. However, you can specify a list of tables limited to a subset or even exclude specific tables.

IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema
    [ OPTIONS ( option 'value' [, ... ] ) ]

Here is the list of parameters used in this step to connect MySQL to PostgreSQL: 

• remote_schema: This is used to specify the remote schema to import from, which will be the MySQL database in this article.
• LIMIT TO ( Table_name [, …] ): This option will allow you to limit the list of tables to a specified subset to be imported. All views and tables on the foreign server (MySQL host) are imported except specified otherwise.
• EXCEPT ( table_name [, …] ): This option excludes specified foreign tables from an import. All the existing tables will be imported except the ones listed on it, as they would not be imported from the MySQL host.
• SERVER server_name: This is used to indicate the server from which the foreign tables will be imported.
• local_schema: This is used to specify the name of the local schema where the imported foreign tables will be created.
• OPTIONS ( option ‘value’  [, …] ): This is used for specific options during import. You can select the following:
  • import_default: This controls whether column default expressions will be included in the definitions of the importation of the foreign tables from the foreign server. Its default value is false.
  • import_not_null: It controls if column NOT NULL constraints should be included in the definitions of the importation of the foreign tables from the foreign server. The default value is true.

An example of syntax showing an IMPORT FOREIGN SCHEMA is shown below. First, the syntax is for a MySQL table created with the following definition:

CREATE TABLE color(cid INT PRIMARY KEY, cname TEXT);
INSERT INTO color VALUES (1, 'Red');
INSERT INTO color VALUES (2, 'Green');
INSERT INTO color VALUES (3, 'Orange');
CREATE TABLE fruit(fid INT PRIMARY KEY, fname TEXT);
INSERT INTO fruit VALUES (1, 'Orange');
INSERT INTO fruit VALUES (2, 'Mango');

On the Postgres server, execute the following command:

IMPORT FOREIGN SCHEMA edb FROM SERVER mysql_server INTO public;
SELECT * FROM color;
 cid | cname
 -----+--------
   1 | Red
   2 | Green
   3 | Orange
 (3 rows)
SELECT * FROM fruit;
 fid | fname
 -----+--------
   1 | Orange
   2 | Mango
 (2 rows)

Method 3: Connect MySQL to PostgreSQL with Py-mysql2pgsql

Py-mysql2pgsql is yet another tool that migrates data from MySQL to PostgreSQL. It also allows you to write a dump file, which is Postgres compliant (8.2 or higher). However, what you need to note here is that it cannot import spatial data from MySQL at the moment. 

Before migrating your data from MySQL to PostgreSQL, you must meet the following prerequisites:

• Python 2.7
• MySQL-python
• Psycopg2
• PyYAML
• Termcolor (not required when installing on Windows)
• Pytz

Follow the following steps to migrate data from MySQL to PostgreSQL successfully.

Step 1: Install the Py-mysql2pgsql Tool On Your System

For Windows, you’ll first need to install the driver dependencies and then the tool. 

Run the following command to get psycopg2 for Windows.

pip install psycopg2

You’ll also need to get MySQL-python for Windows.

Run the following command to install all the dependencies automatically from PyPI.

> pip install py-mysql2pgsql

To install from source, execute the following commands.

> git clone git://github.com/philipsoutham/py-mysql2pgsql.git
> cd py-mysql2pgsql
> python setup.py install

Step 2: Once Done, Verify Your Installation

[root@xxx /data/aken/tools/py-mysql2pgsql-0.1.5]# py-mysql2pgsql -h
usage: py-mysql2pgsql [-h] [-v] [-f FILE] [-V]
Tool for migrating/converting data from mysql to postgresql.
optional arguments:
-h, --help show this help message and exit
-v, --verbose Show progress of data migration.
-f FILE, --file FILE Location of configuration file (default:mysql2pgsql.yml). If there isn't one there already, one will be made for you.
-V, --version Add version and exit.

Step 3: Edit the mysql2pgsql.yml Migration Configuration File

# specify socket to choose a socket connection

# localhost is a special MySQL hostname overriding the port option

# choose tcp to c

Mysql:
hostname:
port:
socket:
username:
password:
database:
compress: false
destination:

# output goes to file if file is provided, otherwise postgres

file:
postgres:
hostname:
port:
username:
password:
database:

# Only the tables listed will be transformed. To convert all tables, leave empty.

#only_tables:

#- tablex

#- tablex

# if exclude_tables is given, the tables listed below will not be converted.

#exclude_tables:

#- tablex

#- tablex

#Only the schema definition will be exported or migrated if supress_data is true.

supress_data: false

#Only the schema definition will be exported or migrated if supress_ddl is true.

supress_ddl: false

# if force_truncate is true, a table is forced to be truncated before table loading

force_truncate: false

# if timezone is true, makes the tzinfo mysql data appendable or convertible to UTC

timezone: false

# If index prefix_is specified, indexes with that name prefix will be generated.

index_prefix:

Step 4: Start Migrating Data from MySQL to PostgreSQL

[root@xxx]# py-mysql2pgsql -v -f mysql2pgsql.yml

>>>>>>>>>> STARTING <<<<<<<<<<

START CREATING TABLES
START - CREATING TABLE tab_xxx
FINISH - CREATING TABLE tab_xxx
DONE CREATING TABLES
START WRITING TABLE DATA
START - WRITING DATA TO tab_xxx
FINISH - WRITING DATA TO tab_xxx
DONE WRITING TABLE DATA
START CREATING INDEXES AND CONSTRAINTS
START - ADDING INDEXES TO tab_xxx
FINISH - ADDING INDEXES TO tab_xxx
START - ADDING CONSTRAINTS ON tab_xxx
FINISH - ADDING CONSTRAINTS ON tab_xxx
DONE CREATING INDEXES AND CONSTRAINTS

>>>>>>>>>> FINISHED <<<<<<<<<<

Method 4: Connect MySQL to PostgreSQL using Hevo Data

Step 1: Configure your MySQL Source

Connect Hevo Data with your chosen data source, MySQL. You need to specify a few settings to configure this: the pipeline name, database host, name, port, user, password, and name.

Step 2: Choose your Destination and Integrate Data

Load data from MySQL to PostgreSQL by providing your PostgreSQL database credentials, such as your authorized PostgreSQL account, along with a name for your database, destination, and project Id.

You have now successfully established a connection between MySQL and PostgreSQL.

Post-Migration Testing & Validation

• Data Integrity:
  • Compare source (MySQL) and target (PostgreSQL) data meticulously.
  • Verify constraints, data types, and business rules.
• Performance:
  • Benchmark key workloads.
  • Monitor resource usage.
  • Compare performance with MySQL.
• Application Testing:
  • Integrate and test applications thoroughly.
  • Conduct user acceptance testing (UAT).
• Disaster Recovery:
  • Test backup/restore procedures.
  • Validate disaster recovery plan.
• Continuous Monitoring:
  • Monitor performance, identify anomalies.
  • Conduct regular integrity checks.
• Error Handling:
  • Configure robust error logging.
  • Establish error handling procedures.
• Backup Before Migration:
  • Back up MySQL before migration to prevent data loss.

Additional Resources on MYSQL to PostgreSQL

• Load Data from Postgresql to MYSQL

Performance Optimization Techniques

• Indexing

In PostgreSQL, indexing enhances query performance by allowing rapid data retrieval. They work by creating a sorted data structure that allows the database engine to locate the rows that satisfy a WHERE clause quickly. Some common index types include B-tree for equality and range queries, GIN for full-text search, and GiST for complex data types. 

• Query Optimization

Query optimization in PostgreSQL involves simplifying queries, using appropriate joins, and ensuring efficient use of indexes. Tools like ‘EXPLAIN’ help understand query execution plans and identify areas for improvement.

For example, Using SELECT * FROM EMP WHERE ID=8; would retrieve the entire row where ID is found to be 8.

However, if we want to retrieve only certain information like name and address, then it is advisable to use 

SELECT NAME, ADDRESS FROM EMP WHERE ID=8;

• Normalization and Denormalization

Normalization in PostgreSQL involves organizing data into tables and reducing redundancy by splitting large tables into smaller ones to maintain data integrity and minimize storage space. It aims to eliminate data anomalies and ensure each table serves a single purpose. 

On the other hand, denormalization involves combining tables to optimize query performance by reducing joins and improving data retrieval speed. It trades off some redundancy for faster read operations and is often used in read-heavy applications or for analytical purposes.

• Partitioning

Partitioning in PostgreSQL involves dividing large tables into smaller, more manageable chunks called partitions based on specific criteria such as ranges or lists of values. It enables operations to target only relevant partitions, which enhances query performance and maintenance.

• Caching

Caching stores frequently accessed data to accelerate query execution and improve overall performance. PostgreSQL uses shared_buffers to cache data pages in memory, reducing disk I/O and speeding up data retrieval. Additionally, PostgreSQL supports query-level caching with techniques like prepared statements and function result caching to enhance performance further for repetitive queries. 

Conclusion

This article has taken you through the process of

• Establishing MySQL as a source with Hevo Data
• Migrating data from MySQL to PostgreSQL using no-code data pipeline
• Using Pgloader for migration of data
• The use of CREATE EXTENSION command in Foreign Data Wrapper

Database Replication: Exploring 3 Methods and Choosing the Best Fit

Download for Free

FAQ on MySQL to PostgreSQL