Migrate MySQL to PostgreSQL: 4 Easy Methods

Deciding to move data from MySQL to PostgreSQL is a great decision. Because PostgreSQL has a more advanced algorithm that can manage large data sets better. PostgreSQL also supports more data types, functions, and operators. This, in turn, leads to easier modeling of complex data.

Also, PostgreSQL has a larger community, which will help you with any doubts when you migrate mysql to Postgresql. So, without a doubt, you should proceed with the data integration. We are here to help you with every tiny bit of information on how to migrate MySQL to PostgreSQL data.

By the end of reading this blog, you will have a clear understanding of the following,

• The significance of performing this replication and all the existing methods for this.
• The steps involved in each of these methods with diagrams and code scripts required for the same. 
• How to choose which method is best for your use case? 

Let’s get started?

Steps To Migrate or Connect MySQL to PostgreSQL

There are several ways to go about MySQL to PostgreSQL migration, such as using Foreign Data Wrapper, Federated Tables, exporting CSV files, Pgloader, and Py-mysql2pgsql. This article will talk about four of these simple methods you can use for MySQL to PostgreSQL migration step by step.

Method 1: Using Hevo Data to Connect MySQL to PostgreSQL

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. 

You can also read this article to learn about the best data migration tools for 2023.

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.

• 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.

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

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: Using Pgloader to Connect MySQL to PostgreSQL

Pgloader is an open-source database migration tool that enables you to seamlessly move your data from different RDMSs, like MySQL and SQLite to PostgreSQL. 

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. Your MySQL server must be configured to be able to accept encrypted connections. Finally, use your PostgreSQL server as the client machine of MySQL to be able to migrate data using Pgloader.

Step 1: 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 2: 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 3: 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 4: Execute this Command to Perform the Migration

$ pgloader mysql://mysql_username:password@mysql_server_ip_/source_database_name? useSSL=true postgresql:// postgresql://postgresql_role_name:password@postgresql_server_ip/target_database_name?option_1=value&option_n=value

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

• Open up the PostgreSQL prompt.

postgres-server$ sudo -i -u postgres psql

• Using the following command, connect to the target database into which the data was loaded.

postgres-server$ \c xxx

• Run this command to check if the target database contains the moved data.

xxx=# SELECT * FROM source_db.sample_table;

Step 6: On the Same System, Migrate MySQL to PostgreSQL. 

Also, use a Linux user who has access to root MySQL user to execute the following command.

$ pgloader mysql://root@localhost/source_dbpgsql://sammy:postgresql_password@localhost/target_db

Step 7: Finally, Migrate from the CSV File Using this Command

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

Method 4: Using Py-mysql2pgsql to Connect MySQL to PostgreSQL

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

Conclusion

This article has taken you through the process of connecting MySQL to PostgreSQL using various methods, including 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.

The article also explored tools like Pgloader and Py-msql2pgsql to migrate data from MySQL to PostgreSQL. You can also check out this article for information on replicating MySQL database in easy methods.

As you can see, 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 firsthand.

Check out the Hevo Pricing here.

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