How To Load Data from MariaDB to PostgreSQL in 3 Methods: 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 are the Methods for Migrating MariaDB to PostgreSQL?

We’ve outlined a detailed, step-by-step guide for migrating data from MariaDB to PostgreSQL using three different methods:

Method 1: Automate the Data Integration Using Hevo Data

Step 1: Configure MariaDB as Your Source

• In your Hevo dashboard, navigate to Integrations. Select “+ Create Pipeline” and choose MariaDB as your Source.
• Enter the required details:

• Database Host: Specify the server address hosting MariaDB.
• Database Port: Enter the connection port for MariaDB access.
• Database User: Provide the username with database read privileges.
• Password: Enter the corresponding user password for authentication.

• Click “TEST CONNECTION” to verify access.
• Once connected, Hevo automatically detects tables and schemas, allowing you to select which ones to replicate.

Pro tip: Enable binlog-based incremental loading to capture only new or changed records and avoid full reloads.

Step 2: Configure PostgreSQL as Your Destination

• Choose PostgreSQL as your Destination.
• Enter the following details:

• Destination Name: Unique label for identifying this PostgreSQL connection.
• Database Host: PostgreSQL server address where data will be loaded.
• Database Port: Network port PostgreSQL uses for incoming connections.
• Username: Authentication ID to access the PostgreSQL database securely.
• Password: Secret key corresponding to the PostgreSQL username.
• Database Name: Target database where MariaDB data will migrate.
• Schema Name: Specific schema within the database for migration.

• Click “TEST CONNECTION” to confirm the configuration.
• Finally, select “SAVE & CONTINUE.”

Choose load type:

• Full Load for initial migration.
• Incremental Load / CDC for ongoing syncs.

Enable the following options in these specific scenarios:

• Connect through SSH: When direct network access to PostgreSQL is restricted.
• Use SSL: When handling sensitive or production-level data securely.
• Sanitize Table/Column Names: When names contain spaces or special characters.

Step 3: Map and transform data

Review Hevo’s automatic schema and data type mappings from MariaDB to PostgreSQL.

• Make adjustments wherever required (e.g., tweak column types or rename fields) and apply transformations.
• Use Hevo’s visual transformations for simple cleaning and standardization.
• Use Python transformations for complex logic (e.g., derived columns, conditional mapping, or data masking).
• Validate transformed data with a sample load to ensure compatibility and correctness before full migration.

Pro Tip: Document all schema and type changes to maintain downstream compatibility.

Step 4: Start the pipeline and monitor migration

• Troubleshoot any mapping or connectivity issues via the logs and re-run failed batches if necessary.
• Click Activate Pipeline to begin extraction, transform (if configured), and load.
• Monitor progress using Hevo’s dashboard. View logs, latency, and errors in real time.

Method 2: Manually Migrate Data Using CSV Files

You can manually migrate data from MariaDB to PostgreSQL by exporting tables as CSV files and then importing them into PostgreSQL.

Step 1: Prepare your MariaDB tables

Before exporting, review your table structures to ensure data types and columns are consistent with what you’ll create in PostgreSQL.

<em>DESCRIBE employee;</em>

Workflow:

• You review table structures and column types in MariaDB.
• Helps you recreate matching tables in PostgreSQL.
• Ensures consistency in column order and data types during import.

Step 2: Export tables from MariaDB as CSV

Use the SELECT … INTO OUTFILE command to export data from MariaDB.

<em>SELECT * </em>
<em>INTO OUTFILE '/tmp/employees.csv'</em>
<em>FIELDS ENCLOSED BY '"' </em>
<em>TERMINATED BY ',' </em>
<em>ESCAPED BY '"' </em>
<em>LINES TERMINATED BY '\n'</em>
<em>FROM employee;</em>

Workflow:

• The INTO OUTFILE command saves your table data into a CSV file.
• Each record is written as a comma-separated row.
• You repeat this command for all tables you want to migrate.

Step 3: Create matching tables in PostgreSQL

Before importing, create identical table structures in PostgreSQL.

<em>CREATE TABLE employees (</em>

<em>    emp_id INT,</em>
<em>    first_name VARCHAR(50),</em>
<em>    last_name VARCHAR(50),</em>
<em>    dob DATE,</em>
<em>    city VARCHAR(50)</em>
<em>);</em>

Workflow:

• You manually recreate the same schema in PostgreSQL.
• Data types and column names must match the CSV file.
• Adjust any incompatible types (e.g., DATETIME → TIMESTAMP).

Step 4: Import CSV files into PostgreSQL

Use the COPY command to load data directly into the PostgreSQL table:

<em>COPY employees(emp_id, first_name, last_name, dob, city)</em>
<em>FROM '/tmp/employees.csv'</em>
<em>DELIMITER ',' </em>
<em>CSV HEADER;</em>

Workflow:

• PostgreSQL reads the CSV and inserts data into the target table.
• COPY runs on the server; use \copy in psql for local files.
• Make sure the CSV path is accessible to PostgreSQL.

Step 5: Validate and finalize migration

After importing:

<em>SELECT COUNT(*) FROM employees;</em>
<em>SELECT * FROM employees LIMIT 5;</em>

Workflow:

• Check for data mismatches, missing rows, or format issues.
• Recreate indexes, constraints, and foreign keys manually.

You can repeat these steps for each table until your entire database is replicated in PostgreSQL.

Method 3: Manually migrate data using pgloader

pgloader is an open-source migration tool designed to load data from other databases into PostgreSQL. It supports MySQL, SQLite, MS SQL Server, and because MariaDB is fully compatible with MySQL, pgloader can easily migrate MariaDB data as well.

Step 1: Update and upgrade the apt Package

<em>sudo apt update && sudo apt upgrade -y</em>

Workflow:

• Updates the package lists and upgrades all installed packages.
• Ensures your system has the latest dependencies required for pgloader installation.

Step 2: Install pgloader

<em>sudo apt install pgloader -y</em>

Workflow:

• Install the pgloader utility on your system.
• pgloader automates schema creation and data migration between databases.

Step 3: Verify installation

<em>pgloader --version</em>

Workflow:

• Confirms that pgloader was successfully installed.
• Displays the current version, verifying your system is ready for migration.

Step 4: Run the migration command

Run the following command to start the MariaDB to PostgreSQL migration:

<em>pgloader mysql://<mysql_user>:<password>@<mysql_host>:<mysql_port>/<source_database> \</em>
<em>         postgresql://<pg_user>:<password>@<pg_host>:<pg_port>/<target_database></em>

Workflow:

• pgloader connects to your MariaDB (source) and PostgreSQL (target) databases.
• It automatically creates tables, copies schema definitions, and transfers all data.

Step 5: Handle type conversion issues (if any)

If the pgloader encounters incompatible data types between MariaDB and PostgreSQL, resolve it by specifying cast modifiers during execution:

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

Workflow:

• These flags instruct pgloader how to convert specific data types.
• Prevents migration failures due to schema mismatches.

Note: pgloader automatically reformats certain data values during migration. For example, invalid MySQL timestamps such as 0000-00-00 or 0000-00-00 00:00:00 are converted into NULL values in PostgreSQL.

Limitations of the pgloader method:

• Error handling: Requires manual fixes for casting or schema mismatches.
• No data transformation: pgloader focuses on replication, not transformation.
• Limited automation: Scheduling syncs must be done manually.

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.

You can explore the differences between MariaDB vs PostgreSQL to get a better understanding of the two databases.

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.

Learn More:

• MySQL to PostgreSQL
• MariaDB to SQL Server
• MariaDB to MySQL
• MariaDB to Redshift
• MariaDB to BigQuery

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