How to Migrate MariaDB to PostgreSQL : Migration in 3 Easy Methods

MariaDB is a popular Relational Database Management System used for eCommerce websites, Content Management Systems, logging applications, etc. However, other Databases such as PostgreSQL excels MariaDB in terms of performance. 

You can simply migrate from MariaDB to PostgreSQL manually using CSV files or open-source data migration tools like pgloader. You can also use third-party tools to eliminate manual intervention & completely automate the process.

What is MariaDB? An Overview

• MariaDB is an open-source relational database management system (RDBMS) developed in 2009 by the original creators of MySQL.
• It is primarily designed as a drop-in replacement for MySQL, meaning tools and applications compatible with MySQL can work with MariaDB without significant modifications.
• Like MySQL, MariaDB supports external plugins to enhance database functionalities, making it suitable for various use cases such as data warehousing and logging applications.
• MariaDB offers better performance than MySQL when querying views. While MySQL queries all tables connected to the desired view, MariaDB only queries the tables specified in the query.
• Furthermore, MariaDB supports more storage engines than MySQL, including TokuDB, Spider, and Connect.

What is PostgreSQL? An Overview

• PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.
• It is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.
• PostgreSQL is used in various applications, including web applications, data warehousing, and analytics, due to its versatility and powerful features.
• It is suitable for both small-scale applications and large enterprises requiring a robust, scalable database solution.

How to Connect MariaDB to PostgreSQL?

To set up the MariaDB PostgreSQL connector, you can follow any of the 3 methods given below: 

• Method 1: Manually Replicate Tables from MariaDB to PostgreSQL using CSV files
• Method 2: Manually Migrate from MariaDB to PostgreSQL using pgloader
• Method 3: Automate MariaDB to PostgreSQL Connection using Hevo

Method 1: Manually Replicate Tables from MariaDB to PostgreSQL using CSV files

Using a brute force approach to replicate tables one at a time, you can export your MariaDB tables as CSV files and then upload them to PostgreSQL. To get started with this manual MariaDB to PostgreSQL method, follow these steps;

• Step 1: MariaDB accepts similar SQL commands to MySQL. Using the Into Outfile command, you can export your MariaDB table as a CSV file. For instance, consider exporting the “employee” table as an employees.csv file by running the following commands: 

select * from employee
INTO OUTFILE 'employees.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\n';

• Step 2: To import the CSV file into your PostgreSQL database, you can use the COPY command. Check out the article on easy ways to import your CSV files to PostgreSQL to know about the other 2 methods.

COPY employees(emp_id,first_name,last_name,dob,city)
FROM ‘C:employees.csv’
DELIMITER ‘,’
CSV HEADER;

By following the same procedure for other tables, you can easily replicate data from MariaDB to PostgreSQL. However, this can be a time-intensive task if the number of tables is huge.

Method 2: Manually Migrate from MariaDB to PostgreSQL using pgloader

Pgloader is an open-source tool that allows you to load data from databases like MySQL, SQLite & MS SQL Server to PostgreSQL. Though MariaDB offers additional features, it offers the same enterprise requirements as MySQL i.e. software that supports MySQL is compatible with MariaDB. To get started with MariaDB to PostgreSQL Migration, follow these simple steps:

• Step 1: You need to first update & upgrade the apt package in your instance.

apt update && apt upgrade -y

• Step 2: Install pgloader for your MariaDB PostgreSQL Integration process.

apt install pgloader

• Step 3: You can verify that installation is done successfully by checking the version of pgloader installed.

pgloader --version

• Step 4: Assuming that you have created the desired PostgreSQL database in your instance, run the following command to start the MariaDB to PostgreSQL Integration process. 

pgloader mysql://mysqluser:password@<mysql-server>:<mysql-port>/<source-database> postgresql://<pgsql-role>:password@<pgsql_server>:<postgresql-port>/<target-database>

Here, you may face an error if pgloader is unable to effectively perform the data type conversion as you move from MariaDB to PostgreSQL. You can easily fix this by executing the cast modifier commands first and then Step 4. For instance,

pgloader --cast "type float to real drop typemod" \
--cast "type int to integer drop typemod" \
--cast "type bigint when (= 20 precision) to bigint drop typemod" \

Also, note that pgloader also performs data reformatting during the MariaDB to PostgreSQL transfer. For instance, the transformation of MySQL datestamps 0000-00-00 and 0000-00-00 00:00:00 to PostgreSQL NULL value. You need to keep this modification in mind while designing an application that uses this PostgreSQL database.   

This method allows you to only replicate data from MariaDB to PostgreSQL without any data transformation. For cases, where you need to update data every day or hour, this approach becomes exhausting. To remedy this, you can use cloud-based No-Code ETL tools like Hevo Data that completely automate the process in a matter of minutes.

If you are looking to streamline your PostgreSQL workflow, do read our blog on PostgreSQL import CSV, saving you time and effort. And if you’re interested in optimizing performance and achieving high availability, don’t miss our guide on setting up PostgreSQL clusters [+clustering options].

Method 3: Automate MariaDB to PostgreSQL Connection using Hevo

Hevo is a No-code Data Pipeline solution that can help you seamlessly replicate data in real-time from 150+ data sources(Including 40+ free sources) like MariaDB to Databases such as PostgreSQL, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. 

Without the need for manually extracting CSV files & uploading them to PostgreSQL, you can effortlessly replicate data from MariaDB to PostgreSQL using Hevo by following the simple steps given below:

• Step 1: To replicate data from MariaDB to PostgreSQL, you can first configure MariaDB as a source by providing your MariaDB credentials such as your Database User and Password. You will also need to give the Database host, Port, and a unique name for this Pipeline. 

• Step 2: For completing the process to replicate data from MariaDB to PostgreSQL, you can start by providing your PostgreSQL credentials such as your Database User and Password. You will also need to give Database Host, Port, schema & a unique name for this destination. 

This completes the No-Code & Automated method of connecting MariaDB to PostgreSQL using Hevo.

More reasons to Love Hevo:

• Reliability at Scale – With Hevo, you get a world-class fault-tolerant architecture that scales with zero data loss and low latency. 
• Monitoring and Observability – Monitor pipeline health with intuitive dashboards that reveal every stat of the pipeline and data flow. Bring real-time visibility into your ELT with Alerts and Activity Logs 
• Stay in Total Control – When automation isn’t enough, Hevo offers flexibility – data ingestion modes, ingestion, and load frequency, JSON parsing, destination workbench, custom schema management, and much more – for you to have total control.    
• Auto-Schema Management – Correcting improper schema after the data is loaded into your warehouse is challenging. Hevo automatically maps source schema with the destination warehouse so that you don’t face the pain of schema errors.
• 24×7 Customer Support – With Hevo you get more than just a platform, you get a partner for your pipelines. Discover peace with round-the-clock “Live Chat” within the platform. What’s more, you get 24×7 support even during the 14-day full-feature free trial.
• Transparent Pricing – Say goodbye to complex and hidden pricing models. Hevo Pricing brings complete visibility to your ELT spend. Choose a plan based on your business needs. Stay in control with spend alerts and configurable credit limits for unforeseen spikes in the data flow. 

Learn more about Hevo

For more information on the data migration process, read the Hevo documentation:

• MariaDB source connector
• PostgreSQL destination

Why Connect MariaDB to PostgreSQL?

MariaDB is a popular RDBMS, though it has its drawbacks. Migrating from MariaDB to PostgreSQL allows you to leverage the following benefits:

• PostgreSQL outperforms MariaDB in terms of reads and writes and is, therefore, more efficient.
• You can use PostgreSQL Materialized views for expensive joins and aggregation that are frequently performed.
• Using Partial Indexes, PostgreSQL allows an index for a subset of the data. This is especially useful for recently inserted or frequently queried data.
• Offering better performance than MariDB’s query cache, PostgreSQL uses an internal cache and the server’s page cache for retrieving frequently accessed data.

Additional Resources on Mariadb to Postgresql

• Difference between Mariable vs PostgreSQL
• Migrating data from MySQL to PostgreSQL

Conclusion

• In this article, you have learned about 3 different methods to effectively connect MariaDB to PostgreSQL.
• If you know SQL and only need a few tables to replicate from MariaDB to PostgreSQL without any data cleaning, you can use the CSV files method.
• If you need to perform a one-time database replication with no transformation, you can try the open-source tool pgloader.

However, if you require to frequently replicate data with complex data transformations & standardization, then Hevo is the right choice for you! 

FAQ on MariaDB to PostgreSQL