How To Load Data from MariaDB to PostgreSQL: Step-by-Step Explanation

MariaDB is a popular relational database management system used for e-commerce websites, content management systems, logging applications, etc. However, other databases, such as PostgreSQL, are better than MariaDB in terms of performance.

MariaDB to PostgreSQL migration can be performed using CSV files or open-source data migration tools like pgloader. Alternatively, you can use automated tools like Hevo to eliminate manual intervention and fully automate the process.

What is MariaDB?

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?

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.

What are the Methods to Migrate MariaDB Table to PostgreSQL?

In this section, I have given step-by-step demonstrations of three methods by which you can successfully perform MariaDB to Postgres migration.

Method 1: Automate the Data Integration using Hevo Data

Step 1.1: Configure MariaDB as Your Source

Step 1.2: Configure PostgreSQL as Your Destination

This completes the no-code & automated method of connecting MariaDB to Postgres using Hevo.

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

• MariaDB source connector
• PostgreSQL destination

Method 2: Manually Migrate Data using CSV files

To replicate tables using a brute force approach, export your MariaDB tables as CSV files and upload them to PostgreSQL. You can begin this manual process by following these steps:

• Step 2.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.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 learn about the other two methods.

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

You can easily replicate your data by following the same procedure for other tables. However, this can be a time-intensive task if the number of tables is huge.

Method 3: Manually Migrate Data 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.

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

apt update && apt upgrade -y

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

apt install pgloader

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

pgloader --version

• Step 3.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 cannot perform the data type conversion effectively. You can easily fix this by executing the cast modifier commands first and then move on to Step 3.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 data 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 only to replicate data 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.

Why Connect MariaDB Database 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.
• PostgreSQL outperforms MariaDB’s query cache by leveraging both an internal cache and the server’s page cache to efficiently retrieve frequently accessed data.

Additional Resources on MariaDB PostgreSQL Integration

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

Conclusion

In this article, you have learned about three different methods to effectively connect your MariaDB database to PostgreSQL. If you know SQL and only need to replicate a few tables 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 need to frequently replicate data with complex data transformations and standardization, then Hevo is the right choice for you!

Try Hevo and see the magic for yourself. Sign up for a free 14-day trial to streamline your data integration process.

FAQs