How to Migrate MariaDB to Postgres: A Step-By-Step Guide

Migrating from MariaDB to PostgreSQL involves more than simply copying tables and rows. The two databases differ in data types, auto-increment behavior, SQL syntax, collations, stored procedures, triggers, and other database features, so a successful migration requires both data movement and compatibility planning.

There are three practical ways to migrate MariaDB to PostgreSQL:

• CDC (change data capture): Best for production migrations where you want to keep PostgreSQL synchronized with ongoing MariaDB changes and minimize downtime during cutover.
• pgloader: Best for one-time database migrations where automatic schema discovery and data type conversion can simplify the move.
• CSV and PostgreSQL COPY: Best for smaller or simpler migrations where you only need to transfer selected tables or datasets.

For production systems that continue receiving inserts, updates, and deletes during the migration, an initial backfill followed by CDC is typically the safest approach. It allows you to load existing data into PostgreSQL, continuously replicate new changes, validate the destination, and cut over once PostgreSQL has caught up.

In this guide, we'll walk through all three approaches and explain the compatibility checks, validation steps, and migration considerations you should address before switching your application from MariaDB to PostgreSQL.

MariaDB vs PostgreSQL: What Changes During Migration?

MariaDB and PostgreSQL are both relational databases, but they differ in several areas that can affect a migration. Before moving production data, review your schema, data types, database objects, and application queries for compatibility.

For a small migration, some of these differences may be easy to handle manually. For larger production databases, run a compatibility assessment before moving data so that schema and application issues are discovered before the final cutover.

The migration method you choose also matters. Tools such as pgloader can automate many common schema and type conversions, while CDC-based approaches are better suited to keeping MariaDB and PostgreSQL synchronized during a low-downtime migration.

MariaDB to PostgreSQL Migration Methods Compared

The best migration method depends on the size of your database, how much downtime you can tolerate, and whether you need to keep MariaDB and PostgreSQL synchronized during the move.

If your application can tolerate downtime and you mainly need a one-time migration, pgloader is often the simplest option.

For smaller datasets or a few selected tables, exporting to CSV and loading the files with PostgreSQL COPY can be sufficient.

For production databases that continue receiving writes during the migration, a CDC-based approach is usually better because it can combine an initial backfill with continuous replication of new inserts, updates, and deletes until you are ready to cut over.

Method 1: Use CDC for a Low-Downtime MariaDB to PostgreSQL Migration

For production systems that need to remain online during migration, change data capture (CDC) is usually a better fit than relying only on a one-time bulk load.

A CDC-based migration typically works in two stages:

1. Backfill the existing MariaDB data into PostgreSQL.
2. Continuously replicate new inserts, updates, and deletes from MariaDB while the application remains active.

Estuary can capture changes from MariaDB's binary log and materialize them into PostgreSQL, allowing the destination to stay synchronized while you validate the migrated data and prepare for cutover.

This approach is useful when you want to reduce the period in which application writes must be paused. Instead of taking the source database offline for the entire migration, you can move the historical data first, keep ongoing changes flowing, and switch the application to PostgreSQL after the destination has caught up.

How the CDC Migration Works

The migration flow looks like this:

MariaDB → Binary Log → Estuary CDC → PostgreSQL

Estuary first captures the existing source data and then continues reading changes from MariaDB's binlog. Those changes are materialized into PostgreSQL so the destination remains current as the source continues to receive writes.

Step 1: Setting Up MariaDB as the Data Source

On the Estuary dashboard, click on Sources on the left-side pane. Then, click on + NEW CAPTURE and search for MariaDB in the Search connectors box. You will see the MariaDB connector appearing in the search results. Click on the connector’s Capture button.

Image Source

Before you configure Estuary’s MariaDB CDC connector, make sure your MariaDB instance is ready for binary log based capture:

• Binary logging must be row-based: set binlog_format to ROW so changes are emitted as row events (not statement text).
• Keep binlogs long enough for recovery: configure binlog retention to at least 7 days so the connector can recover cleanly from temporary downtime without losing change history.
• Create a replication-capable user: the connector user needs:
  • REPLICATION CLIENT and REPLICATION SLAVE
  • SELECT access on the tables you want to capture
  • (If you use auto-discovery) read access to information_schema
• Handle DATETIME correctly: if you capture tables that contain DATETIME columns, MariaDB must expose a usable server time zone (IANA name like America/New_York or a UTC offset), or you must set a default timezone in the connector so values can be converted consistently.

Specify the required details on the MariaDB connector page. You’ll need to provide a connector name, the MariaDB host (or host and port), a database user with replication permissions, and the corresponding password. If your database time zone cannot be detected automatically, you may also configure a default timezone in the connector. After filling in the details, click on NEXT, then click on Save and Publish.

The MariaDB connector is a Change Data Capture (CDC) connector that captures change events from a MariaDB database via the Binary Log.

Step 2: Setting Up Postgres as the Destination

Now, you must set up the destination end of the pipeline. To do this, click the Materialize Connections option that appears in the pop-up window after a successful capture. Alternatively, go to the Estuary dashboard and click Destinations in the left-side pane. Next, click on the + NEW MATERIALIZATION button.

Search for Postgres in the Search connectors box. The search results will show the PostgreSQL connector, among other connector options. Click on the Materialization button of the connector.

Before you use this connector, ensure you complete the following prerequisites:

• A PostgreSQL database and credentials for a user that can create tables. Estuary automatically creates and manages the destination tables based on your collections, so you don't need to create tables beforehand.
  
• At least one collection.

This redirects you to the PostgreSQL connector page, where you must enter some required details.

Specify a connector name, the PostgreSQL address (host or host and port), database user, and password. If no port is provided, the default PostgreSQL port is used. While the connector will automatically select collections for you, consider using the Source Collections feature to manually select the collections you want to materialize into your Postgres database.

Click on NEXT, followed by Save and Publish. The connector will materialize collections into tables in a PostgreSQL database.

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

• MariaDB source connector
• PostgreSQL materialization connector

Method 2: Migrate MariaDB to PostgreSQL with pgloader

pgloader is a good option for one-time MariaDB-to-PostgreSQL migrations because it can automate much of the schema discovery, data loading, and common data type conversion work.

It can migrate tables and data directly from MariaDB into PostgreSQL, making it more practical than manually exporting and importing many tables.

A typical migration command looks like this:

plaintext
pgloader mysql://user:password@mariadb-host/database \
         postgresql://user:password@postgres-host/database

Before running pgloader against a production database, test the migration on a staging environment and review how MariaDB-specific types and database objects are converted.

When pgloader is a good choice

Use pgloader when:

• You need a one-time or bulk database migration.
• You want automated schema discovery and common type conversions.
• You can plan a maintenance window or separate cutover process.
• You do not need continuous synchronization between MariaDB and PostgreSQL after the initial load.

What pgloader does not solve automatically

Even when the tables and rows migrate successfully, you may still need to review or recreate:

• Stored procedures and functions
• Triggers
• Complex views
• MariaDB-specific SQL
• Application queries
• Custom type mappings
• Indexes or constraints that require PostgreSQL-specific changes

pgloader is primarily a migration tool, not an ongoing CDC replication system. If the MariaDB source continues receiving writes after the initial migration begins, you need a strategy to capture those changes before switching the application to PostgreSQL.

To import multiple files into a table at once, you can follow the steps mentioned here.

Method 3: Migrate MariaDB to PostgreSQL with CSV and COPY

For small databases, selected tables, or simple one-time migrations, you can export data from MariaDB to CSV and load it into PostgreSQL with the COPY command.

This approach is easy to understand, but it is mostly a data transfer method. You are responsible for creating the PostgreSQL schema and handling differences in data types, constraints, indexes, sequences, triggers, and other database objects.

Step 1: Export Data from MariaDB

You can export a table from MariaDB using SELECT ... INTO OUTFILE:

plaintext
SELECT
    first_name,
    last_name,
    contact_number,
    city
FROM customers
INTO OUTFILE '/path/customers.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Keep in mind that SELECT ... INTO OUTFILE may require the MariaDB FILE privilege, and the server's secure_file_priv setting may restrict where files can be written.

The command above does not create a CSV header row.

Step 2: Create the PostgreSQL Table

Create the destination table in PostgreSQL with compatible column types before importing the data:

plaintext
CREATE TABLE customers (
    first_name TEXT,
    last_name TEXT,
    contact_number TEXT,
    city TEXT
);

Adjust the column types, constraints, and keys based on your actual MariaDB schema.

Step 3: Import the CSV into PostgreSQL

Load the exported file with PostgreSQL COPY:

plaintext
COPY customers (
    first_name,
    last_name,
    contact_number,
    city
)
FROM '/path/customers.csv'
WITH (
    FORMAT CSV
);

Do not use the HEADER option unless your exported CSV file actually contains a header row.

If the CSV file is stored on the machine running your PostgreSQL client rather than on the PostgreSQL server, \copy in psql may be more convenient:

plaintext
\copy customers(first_name, last_name, contact_number, city)
FROM '/local/path/customers.csv'
WITH (FORMAT CSV);

When CSV is a good choice

CSV works well when:

• You only need to migrate a few tables.
• The dataset is relatively small.
• The source and destination schemas are simple.
• Some migration downtime is acceptable.
• You are comfortable creating and validating the PostgreSQL schema manually.

Limitations of CSV-based migration

CSV does not automatically migrate:

• Primary and foreign keys
• Indexes
• Default values
• Auto-increment or sequence behavior
• Views
• Triggers
• Stored procedures and functions
• Ongoing inserts, updates, and deletes

For a production database that continues receiving writes during migration, use a migration method that can capture ongoing changes rather than relying only on CSV exports.

How to Validate a MariaDB to PostgreSQL Migration

A migration isn't complete just because the data copied successfully. Before switching your application to PostgreSQL, validate both the data and the database behavior.

At minimum, check the following:

• Row counts: Compare the number of rows in important MariaDB and PostgreSQL tables.
• Primary and foreign keys: Confirm that key relationships were recreated correctly.
• Indexes: Verify that indexes needed for application performance exist in PostgreSQL.
• NULL values: Check for unexpected NULLs introduced during conversion.
• Numeric precision: Validate decimal, financial, and other precision-sensitive fields.
• Dates and timestamps: Confirm date values, time zones, and timestamp behavior.
• Character encoding: Test names, symbols, accented characters, and other non-ASCII text.
• Generated IDs: Verify identity columns or sequences are correctly positioned so new inserts do not create duplicate keys.
• Critical aggregates: Compare totals, counts, balances, or other business-level calculations between the two databases.
• Application queries: Test important reads and writes against PostgreSQL, especially queries that previously relied on MariaDB-specific SQL.

For CDC-based migrations, also verify that new inserts, updates, and deletes made in MariaDB appear correctly in PostgreSQL before cutover.

A good validation process combines database-level checks with application-level testing. Matching row counts alone does not guarantee that the migrated system will behave correctly in production.

How to Cut Over from MariaDB to PostgreSQL with Minimal Downtime

Once PostgreSQL is fully loaded and validated, the final step is switching your application from MariaDB to PostgreSQL.

A typical low-downtime cutover looks like this:

1. Complete the initial backfill - Make sure the existing MariaDB data has been loaded into PostgreSQL.
2. Keep ongoing changes synchronized - Continue replicating inserts, updates, and deletes from MariaDB while validation and application testing are in progress.
3. Test the application against PostgreSQL - Verify important reads, writes, transactions, ORM behavior, and database-specific queries before changing production traffic.
4. Choose a cutover window - Schedule the final switch during a lower-traffic period if possible.
5. Pause or redirect writes to MariaDB - This prevents new changes from being created while the final synchronization completes.
6. Wait for PostgreSQL to catch up - Confirm that all remaining source changes have been applied to the destination.
7. Run final validation checks - Recheck critical tables, row counts, recent transactions, generated IDs, and application workflows.
8. Switch the application connection to PostgreSQL - Update the database connection and closely monitor application errors, query performance, and data consistency.
9. Keep MariaDB available during a rollback window - Avoid immediately deleting or permanently shutting down the source database. Keep it available until the PostgreSQL deployment has been stable for an appropriate period.

For production migrations, the goal is to make the final cutover window as short as possible. Performing the bulk backfill and most validation before the switch reduces the amount of work that must happen while application writes are paused.

🔄 Want real-time replication instead of one-time migration?
Tools like pgloader are great for initial setup — but not for ongoing data syncs.
Estuary keeps MariaDB and PostgreSQL in sync, with built-in CDC and no code required.
👉 Get Started with Estuary — Free Tier

Conclusion

The best way to migrate from MariaDB to PostgreSQL depends on your database size, complexity, and downtime requirements.

For small or selective migrations, CSV and PostgreSQL COPY can be enough. For a one-time database migration with automated schema and type conversion, pgloader is usually the better fit.

For production systems that must remain online during the move, use an initial backfill plus CDC so PostgreSQL can stay synchronized with new MariaDB changes until cutover. Before switching applications, validate the migrated data, database objects, generated IDs, and application queries.

If minimizing downtime is important, Estuary can continuously capture changes from MariaDB and materialize them into PostgreSQL while you prepare and validate the destination.