Replicating PostgreSQL to MySQL : 3 Easy Methods

PostgreSQL is a free database management system that can handle both relational (single-valued) and non-relational (multi-valued, hierarchical) data types.

MySQL is an Oracle-developed open-source Relational Database Management System. It’s one of the first database solutions, with a slew of useful features. MariaDB is a modern solution that functions as a lightweight version of MySQL with more features and a greater capacity to handle complicated data.

In some circumstances, organizations prefer PostgreSQL to MySQL, and transferring data helps them to examine and deal with data more quickly.

This article talks about the different methods you can use to connect PostgreSQL to MySQL seamlessly. It also gives a brief introduction to PostgreSQL and MySQL Server before diving into the PostgreSQL to MySQL data transfer methods.

What is PostgreSQL?

PostgreSQL is also known as Postgres. It’s a free-to-use RDBMS platform that supports SQL and has a lot of extensibility. On July 8, 1996, it was released after being developed in a lab at the University of California, Berkeley. It was made to take the place of the previous Ignes database. Structured objects are used to store data in PostgreSQL.

It is written in C and has a monolithic architecture, which means that all of the components are connected and work in a symmetrical and systematic manner. It has a vast community and provides a lot of community assistance as well as extra help to its paying members. Healthcare, banking, and manufacturing are just a few of the businesses that employ it. It also makes it possible to install new backup solutions.

Monolithic architecture implies that all of the components are linked and work in a logical order. It offers both community support and additional assistance to some of its paid customers. It is frequently used in the healthcare, banking, and industrial areas due to its innovative backup methods.

Key Features of PostgreSQL

PostgreSQL offers a few unique features that make it a viable alternative to other mainstream RDBMSs. Some of these qualities are as follows:

• PostgreSQL is capable of handling a wide variety of data and document types.
• Since the architecture is monolithic, the components operate together in an automated manner.
• It can be used in bank systems for risk management, business intelligence, and other transactional workflows.
• It provides multiple fail-safes and redundancies to assure Storage Reliability.
• It’s open-source, free, and has a lot of functionality.
• It has limited scalability because its processing capacity is reliant on the machine it operates on.
• It complies with the ACID (Atomicity, Consistency, Isolation, and Durability) standards and runs on a variety of operating systems.

When Should you use PostgreSQL: Key Use Cases

• Finance Industry: PostgreSQL is well-suited to the finance industry. Because PostgreSQL is entirely ACID compliant, it’s ideal for OLTP workloads (Online Transaction Processing). PostgreSQL, on the other hand, isn’t only a great OLTP database; it’s also a capable analytical database that works well with Matlab and R.
• GIS Information: PostgreSQL isn’t just a financial database; it also contains a GIS extension called “PostGIS” that has hundreds of functions for processing geometric data in various formats. Because it is extremely standard compatible, PostGIS has become one of the de-facto standards in the Open Source GIS sector.
• Scientific Data: Terabytes of data can be generated by research and scientific projects, all of which must be managed in the most cost-effective and efficient manner possible. PostgreSQL features strong analytical capabilities and a powerful SQL engine that makes working with large amounts of data simple.

What is MySQL?

MySQL is a Relational Database Management System (RDBMS) that is widely used to manage Relational Databases. The Structured Query Language (SQL) is used by MySQL to create, update, and query the database. For accessing and managing records in any database, SQL is the most extensively used language. MySQL is an open-source and free database software that is supported by Oracle and is released under the GNU license.

When compared to Microsoft SQL Server and Oracle Database, MySQL is a faster, more scalable, and easier-to-use Database Management System. It is based on the Client-Server concept, which implies that the database is normally hosted on a server and data is accessed via network clients and workstations. The server responds to the clients’ Graphical User Interface (GUI) queries with the desired result. MySQL works with a variety of operating systems and languages, including PHP, PERL, JAVA, C++, and C.

Key Features of MySQL 

The following are some of the reasons for MySQL’s enormous popularity.

• MySQL is an Open-source Relational Database, which means you can use it for free.
• It is based on the SQL language, which is well-known and widely used. You may run queries on Tables, Rows, Columns, and Indexes with it.
• It keeps data in Tables, which are also known as Relations, which are collections of Rows and Columns.
• It works well with enormous data sets and can accommodate tables with up to 50 million rows or more.
• MySQL is extremely adaptable, and the open-source GPL license allows developers and programmers to quickly modify the SQL software to meet their specific needs.

When Should you use MySQL: Key Use Cases

MySQL is the most widely used open-source database for web development by businesses. It’s an important aspect of the Software Stack Model, which makes it easier to create Web applications and websites. The proper uses for MySQL are listed below.

• MySQL is a database management system that can handle huge databases and has a well-designed user management system. As a result, it’s excellent for dealing with large amounts of data and situations requiring multiple user access.
• The most prevalent application of MySQL is for web development. MySQL is used in conjunction with Scripting Languages like PHP or Perl to construct Websites that interact with a MySQL Database in real-time.
• MySQL is a database management system that is used for a variety of reasons, including E-Commerce. It may be used to store everything from client information to a large product inventory for an online business.
• MySQL enables Database Professionals to customize the Database Server to meet their individual needs. MySQL can meet the performance needs of any system thanks to its high-speed load capabilities, unique memory caches, and other performance-enhancing features.
• MySQL is well-known for its scalability, and it can manage both highly embedded applications and large data warehouses.

Methods to Connect PostgreSQL to MySQL

Method 1: Connect PostgreSQL to MySQL Server using Hevo

Hevo helps you directly transfer data from various sources such as PostgreSQL Database, Business Intelligence tools, Data Warehouses, or a destination of your choice such as MySQL in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data 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 in a secure, consistent manner with zero data loss.

Sign up here for a 14-Day Free Trial!

The following steps can be implemented to connect PostgreSQL to MySQL using Hevo:

• Configure Source: Connect Hevo Data with PostgreSQL providing a unique name for your Pipeline, along with details such as  Database Host, Database Port, Database User, Database Password, Database Name and about your Data Source. You also can choose the Data Ingestion method while configuring PostgreSQL as a source

• Configure Destination: Establish a connection to MySQL by providing information about its credentials such as Destination Name, Database Host, Database Port, Database User, Database Password, Database Name and Schema Name. 

Here are more reasons to try Hevo:

• Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
• Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
• Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
• Scalable Infrastructure: Hevo has in-built integrations for 100+ sources that can help you scale your data infrastructure as required.
• Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within Data Pipelines.
• Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Method 2: Manually Connect PostgreSQL to MySQL using CSV Files

Let’s understand this manual method of connecting PostgreSQL to MySQL with help of an example: 

Step 1: Extracting Table

The first step in migrating data from your PostgreSQL to MySQL is to extract the table definition from PostgreSQL. The most common option is to use pg_dump, but let’s look at how it works with the first table (actor):

-bash-4.2$ <strong>pg_dump -st actor dvdrental</strong>
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.8
-- Dumped by pg_dump version 12.8

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: actor; Type: TABLE; Schema: public; Owner: postgres
--

<span class="tadv-color" style="color:var(--paletteColor1, #dc9d4a)">CREATE TABLE public.actor (
    actor_id integer DEFAULT nextval('public.actor_actor_id_seq'::regclass) NOT NULL,
    first_name character varying(45) NOT NULL,
    last_name character varying(45) NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);</span>


ALTER TABLE public.actor OWNER TO postgres;

--
-- Name: actor actor_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

<span class="tadv-color" style="color:var(--paletteColor1, #dc9d4a)">ALTER TABLE ONLY public.actor
    ADD CONSTRAINT actor_pkey PRIMARY KEY (actor_id);</span>


--
-- Name: idx_actor_last_name; Type: INDEX; Schema: public; Owner: postgres
--

<span class="tadv-color" style="color:var(--paletteColor1, #dc9d4a)">CREATE INDEX idx_actor_last_name ON public.actor USING btree (last_name);</span>


--
-- Name: actor last_updated; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER last_updated BEFORE UPDATE ON public.actor FOR EACH ROW EXECUTE FUNCTION public.last_updated();


--
-- PostgreSQL database dump complete
--

Step 2: Table Definition

Only the text in orange is significant to us when importing the data from that output (eventually the indexes can also be created later).

If you’re familiar with MySQL, you’ll see that the SQL syntax returned will not work there.

Let’s take a look at the definition of the table:

<span class="tadv-color" style="color:var(--paletteColor1, #dc9d4a)">CREATE TABLE public.actor (
    actor_id integer DEFAULT nextval('public.actor_actor_id_seq'::regclass) NOT NULL,
    first_name character varying(45) NOT NULL,
    last_name character varying(45) NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);</span>

As you can see:

• There is a prefix that is public.
• The main key isn’t defined right now (although it will be later as a constraint).
• The following is an example of a user-defined type, public.actor_id seq is the sequence. 
• Character changing is actually VARCHAR.
• The timestamp column makes use of various MySQL-specific information, without regard to time zone.

This is a manual step, the statement must be rewritten to work in MySQL:

CREATE TABLE actor (
    actor_id integer auto_increment NOT NULL primary key,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    last_update timestamp DEFAULT now() NOT NULL
);

Step 3: Adding Secondary Indexes

You can now add the secondary indexes to your MySQL Database Service instance using the newly rewritten statement:

You must carry out this operation for all tables in the dvdrental schema:

dvdrental-# dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)

When numerous USER-DEFINED data types are utilized, like in the table film, this is the most difficult part:

rating public.mpaa_rating DEFAULT 'G'::public.mpaa_rating,

This is how you can find out what it means:

dvdrental=# select enum_range(null::mpaa_rating);
      enum_range      
----------------------
 {G,PG,PG-13,R,NC-17}
(1 row)

You can see that this is an ENUM, so we’ll rename the column as follows:

rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',

Step 4: Exporting Data to CSV

It’s now time to export the PostgreSQL data to CSV:

dvdrental=# copy actor to '/vagrant/output/actor.csv' delimiter ',' CSV HEADER;
COPY 200
dvdrental=# copy address to '/vagrant/output/address.csv' delimiter ',' CSV HEADER;
COPY 603
dvdrental=# copy category to '/vagrant/output/category.csv' delimiter ',' CSV HEADER;
COPY 16
dvdrental=# copy customer to '/vagrant/output/customer.csv' delimiter ',' CSV HEADER;
COPY 599
dvdrental=# copy film to '/vagrant/output/film.csv' delimiter ',' CSV HEADER;
COPY 1000

Step 5: Importing Data to MySQL

After you’ve exported all of the tables you want to import, you can utilize MySQL Shell’s import table utility:

importTable() can be used in many threads(Not in this case since the data is too tiny). You can repeat the process for each table you want to import. importTable() can also combine several files into a single table. You can confirm that the data has been imported by looking at the following:

You have successfully migrated data from PostgreSQL to MySQL using CSV files.

Method 3: Manually Connect PostgreSQL to MySQL using pg2mysql php Script

Step 1: Downloading the Script and PHP

Downloading the script and unpacking the archive is the first step to connect PostgreSQL to MySQL using pg2mysql php script.

wget http://www.lightbox.ca/pg2mysql/pg2mysql-1.9.tar.bz2  (contains original script from the author)

wget https://www.easyredmine.com/ER/media/knowledge-base/pg2mysql-1.9.zip (contains our modified script for php version 7.4)

tar -xvf pg2mysql-1.9.tar.bz2(in case of original script from the author)
unzip pg2mysql-1.9.zip (in case of our modified script for php version 7.4)

Installing PHP

sudo apt install php7.4-cli (in case of our modified script)

Step 2: Creating Dump Database for PostgreSQL and running it

Make a .sql dump of your PostgreSQL database, being sure to use the “—format p —inserts” option.

sudo -u postgres pg_dump --format p --inserts DBNAME_HERE > /path/to/file.sql

Navigate to the pg2mysql-1.9 folder.

cd pg2mysql-1.9/

Run this file which you have created.

php pg2mysql_cli.php /path/to/pd/dump/file.sql /path/where/to/save/mysql/file.sql

There will be some lines like this.

Completed!     30820 lines        5539 sql chunks
Notes:
 - No its not perfect
 - Yes it discards ALL stored procedures
 - Yes it discards ALL queries except for CREATE TABLE and INSERT INTO 
 - Yes you can email us suggestsions: info[AT]lightbox.org
    - In emails, please include the Postgres code, and the expected MySQL code
 - If you're having problems creating your postgres dump, make sure you use "--format p --inserts"
 - Default output engine if not specified is MyISAM"

• The corrected sql dump file will now be created. It’s recommended that you read it and replace MyISAM with InnoDB wherever possible.
• At this point, you can restore the dump to a clean MySQL database.
• Since the script does not save indexes (mentioned by the creator), you must manually add them to each table (it will work without indexes but it may cause serious performance issues). You must manually add them or use your own custom script to do so.

Limitations of Manually Connecting PostgreSQL to MySQL 

• The data transfer from PostgreSQL to MySQL is only one-way. Two-way sync is essential to keep both tools up to date.
• Since the records must be updated on a regular basis, the manual approach takes time. This wastes time and resources that could be better spent on more important business tasks.
• Updating current data and maintaining workflows on multiple platforms necessitates a significant amount of engineering bandwidth, which can be a source of frustration for many users.
• During data transfer, there is no room for transformation. For firms who wish to update their data before exporting it from PostgreSQL to MySQL, this might be a considerable drawback.

Conclusion

In this article, you got a glimpse of how to connect PostgreSQL to MySQL after a brief introduction to the salient features, and use cases. The methods talked about in this article are using CSV files and pg2mysql php script.. The process can be a bit difficult for beginners. Moreover, you will have to update the data each and every time it is updated and this is where Hevo saves the day!

Visit our Website to Explore Hevo

Hevo Data provides its users with a simpler platform for integrating data from 100+ sources for Analysis. It is a No-code Data Pipeline that can help you combine data from multiple sources like PostgreSQL. You can use it to transfer data from multiple data sources into your Data Warehouses, Database, or a destination of your choice such as MySQL . It provides you with a consistent and reliable solution to managing data in real-time, ensuring that you always have Analysis-ready data in your desired destination.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about PostgreSQL to MySQL! Let us know in the comments section below!