Migrate MySQL to PostgreSQL: 2 Easy Methods

on Data Integration • October 29th, 2021 • Write for Hevo

MySQL to PostgreSQL

The prospect of migrating data from one database to another can be very tricky and challenging, but the benefits of having a seamless transfer of data across different platforms is an enormous way of increasing the efficiency of any enterprise as well as increasing the productivity level of the outfit as downtime is significantly reduced. 

Users migrate data from one database to another, such as migrating data from MySQL to PostgreSQL is widely done by companies.

Organizations today produce lots of data using various tools, and merging this diverse information dramatically saves time.

If done correctly, the reduced cost of replicating these data on a different platform can help create an efficient work environment. 

The need to integrate various database tools gave birth to this article to show you how to migrate data from MySQL to PostgreSQL using the two most easy-to-use methods.

Let us understand both methods in detail.

Table of Contents

Save 20 Hours of Frustration Every Week

Did you know that 75-90% of data sources you will ever need to build pipelines for are already available off-the-shelf with No-Code Data Pipeline Platforms like Hevo? 

Ambitious data engineers who want to stay relevant for the future automate repetitive ELT work and save more than 50% of their time that would otherwise be spent on maintaining pipelines. Instead, they use that time to focus on non-mediocre work like optimizing core data infrastructure, scripting non-SQL transformations for training algorithms, and more. 

Step off the hamster wheel and opt for an automated data pipeline like Hevo. With a no-code intuitive UI, Hevo lets you set up pipelines in minutes. Its fault-tolerant architecture ensures zero maintenance. Moreover, data replication happens in near real-time from 150+ sources to the destination of your choice including Snowflake, BigQuery, Redshift, Databricks, and Firebolt. 

Start saving those 20 hours with Hevo today.

Get started for Free with Hevo!

Steps To Migrate or Connect MySQL to PostgreSQL

There are several ways to connect MySQL to PostgreSQL, such as using Foreign Data Wrapper, Federated Tables, exporting CSV files, and pgLoader. This article talks about the two simple methods you can use to connect MySQL to PostgreSQL as follows:

Method 1: Using Hevo Data to Connect MySQL to PostgreSQL

Hevo logo
Image Source

Hevo Data, a No-code Data Pipeline, helps you transfer data from MySQL and another 150+ data sources to PostgreSQL & lets you visualize it in a BI tool.

Hevo is fully managed and completely automates the process of loading data from your desired source, enriching it, and transforming it into an analysis-ready form without having to write a single line of code.

Its fault-tolerant architecture ensures that the data is handled securely and consistently with zero data loss.

It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on critical business needs and perform insightful analysis using various BI tools such as Power BI, Tableau, etc. 

Steps to use Hevo Data for MySQL PostgreSQL Connection:

Hevo Data focuses on two simple steps to get you started:

  • Configure 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.
MySQL Source Set up in Hevo.
Image Source
  • 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.
PostgreSQL Destination Set up in Hevo.
Image Source

Method 2: Using Foreign Data Wrapper to Connect MySQL to PostgreSQL

Connect MySQL to PostgreSQL using Foreign Data Wrapper
Image Source

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 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 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 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)

Conclusion

This article has taken you through the process of creating a Foreign Data Wrapper that can be used to create and import foreign tables on PostgreSQL and MySQL. 

It explained this by showing you how to use the CREATE EXTENSION command, CREATE SERVER command, CREATE USER MAPPING command, CREATE FOREIGN TABLE, and IMPORT FOREIGN SCHEMA to enable you to connect MySQL to PostgreSQL, therefore, sharing tables, columns, and other relevant information across both platforms.

Extracting complex data from a diverse set of data sources can be challenging; this is where Hevo saves the day!

Get started for Free with Hevo!

Want to take Hevo for a spin?

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Check out the pricing here.

Do let us know if the methods were helpful and if you would recommend any other methods in the comments below.

No-code Data Pipeline for PostgreSQL & MySQL