How to Migrate from MariaDB to ParadeDB in PostgreSQL

Why migrate from MariaDB to ParadeDB?

ParadeDB builds on PostgreSQL’s reliability and advanced indexing, giving teams richer JSON, full-text, and analytical features lacking in MariaDB. Migrating unlocks better performance and a single engine for transactional and analytical workloads.

What is the fastest way to migrate?

Use pgloader to automate schema translation, type mapping, and data copy in a single command. It streams directly from MariaDB to ParadeDB without intermediate files, cutting downtime.

How does pgloader work?

pgloader connects to MariaDB, reads table definitions, rewrites them for PostgreSQL, and bulk loads data into ParadeDB using the COPY protocol. It can run incremental syncs for a zero-downtime cut-over.

Which prerequisites must be met?

Install ParadeDB ≥ 15, pgloader ≥ 3.6, and ensure network connectivity between hosts. Create an empty ParadeDB database and matching users with sufficient privileges.

What is the exact syntax for pgloader?

Basic syntax

pgloader mysql://USER:PWD@HOST/DBNAME \
postgresql://USER:PWD@HOST/DBNAME \
[OPTIONS]

Common options include --with data only, --with schema only, --dry-run, and --cast for custom type mapping.

Example with ecommerce tables

pgloader \
mysql://maria_user:secret@10.0.0.5/ecommerce \
postgresql://pg_user:secret@10.0.0.6/ecommerce \
--cast 'type datetime to timestamptz' \
--with "create tables, create indexes, reset sequences"

This moves Customers, Orders, Products, and OrderItems, translating DATETIME to TIMESTAMPTZ.

How do I verify the migration?

Run row counts in both systems and compare hashes:

-- ParadeDB
SELECT 'Customers' AS tbl, COUNT(*) FROM Customers
UNION ALL
SELECT 'Orders', COUNT(*) FROM Orders;

Spot-check critical queries for parity and run application test suites against ParadeDB.

Can I migrate incrementally?

Yes. Enable binlog on MariaDB and use pgloader’s --follow option to stream change events until switchover. Cut application traffic once parity is confirmed.

What about stored procedures and triggers?

MariaDB procedures must be rewritten in PL/pgSQL. Export them with mysqldump --routines, then translate manually or with tools like Ora2Pg.

Best practices for production cut-over

• Freeze DDL on MariaDB before final sync.
• Run pgloader with --verbose and log output.
• Create read-only replica for rollback.
• Switch application connection strings using feature flags.

What maintenance is needed post-migration?

Run VACUUM ANALYZE and create ParadeDB-specific indexes such as gin on JSON columns. Adjust connection pooling parameters to fit PostgreSQL.