How to Query Open-Source MariaDB from PostgreSQL

Galaxy Glossary

How can PostgreSQL query MariaDB open-source tables in real time?

Use mysql_fdw to access MariaDB tables inside PostgreSQL as if they were local.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Table of Contents

Why link PostgreSQL to open-source MariaDB?

Bringing MariaDB data into PostgreSQL lets teams join, filter, and report across both engines without ETL. Foreign Data Wrappers (FDW) make this seamless and real-time.

What extension enables the connection?

Install mysql_fdw (works for MySQL & MariaDB). It exposes remote tables as local foreign tables.

How do I install `mysql_fdw`?

On most Linux distributions: sudo apt install postgresql-15-mysql-fdw. Then inside psql: CREATE EXTENSION mysql_fdw;

Which roles and permissions are required?

Create a PostgreSQL role that owns the FDW objects.Ensure the MariaDB user has SELECT (and optional INSERT/UPDATE) on target tables.

How do I define the foreign server?

Use CREATE SERVER with host, port, and character set. Provide options like ssl_mode for encrypted traffic.

How is user mapping configured?

CREATE USER MAPPING links your PostgreSQL role to MariaDB credentials. Store passwords securely via create or replace user mapping.

Can I import existing schemas automatically?

Yes.IMPORT FOREIGN SCHEMA pulls table definitions, saving manual typing and matching data types closely.

How do I query the foreign tables?

Run normal SELECT, INSERT, UPDATE, or DELETE. PostgreSQL planner pushes down predicates where possible for performance.

How do I join local and remote data?

Foreign tables behave like regular ones, so joins, window functions, and CTEs work. Optimize with indexes on the MariaDB side.

Best practices for production?

Use connection pooling via foreign_server pool_size, enable SSL, and monitor query plans with EXPLAIN VERBOSE.Cache immutable reference tables in PostgreSQL if latency matters.

What are security considerations?

Avoid superuser mappings, prefer SSL, and rotate MariaDB passwords. Log FDW traffic to audit access.

How do I clean up?

Use DROP FOREIGN TABLE, DROP USER MAPPING, and DROP SERVER to remove connections safely without affecting MariaDB.

.

Why How to Query Open-Source MariaDB from PostgreSQL is important

How to Query Open-Source MariaDB from PostgreSQL Example Usage


-- Find customers who spent more than $500 using joined local + remote data
SELECT c.name, SUM(o.total_amount) AS lifetime_value
FROM Customers c            -- foreign table in MariaDB
JOIN Orders o ON o.customer_id = c.id   -- foreign table
WHERE o.order_date >= DATE '2023-01-01'
GROUP BY c.name
HAVING SUM(o.total_amount) > 500
ORDER BY lifetime_value DESC;

How to Query Open-Source MariaDB from PostgreSQL Syntax


-- 1. Install the extension
CREATE EXTENSION IF NOT EXISTS mysql_fdw;

-- 2. Create a foreign server pointing at MariaDB
CREATE SERVER mariadb_srv
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (
        host 'mariadb.example.com',
        port '3306',
        dbname 'ecommerce',
        ssl_mode 'REQUIRED',   -- OPTIONAL
        charset 'utf8mb4'
    );

-- 3. Map PostgreSQL role to MariaDB credentials
CREATE USER MAPPING FOR app_user
    SERVER mariadb_srv
    OPTIONS (
        username 'maria_app',
        password 'S3cureP@ss!'
    );

-- 4. Import selected tables from MariaDB
IMPORT FOREIGN SCHEMA ecommerce
    LIMIT TO (Customers, Orders, Products, OrderItems)
    FROM SERVER mariadb_srv INTO public;

-- 5. Query or modify data as needed
SELECT c.name, SUM(oi.quantity) AS items_bought
FROM Customers c
JOIN Orders o   ON o.customer_id = c.id
JOIN OrderItems oi ON oi.order_id = o.id
GROUP BY c.name;

Common Mistakes

Skipping <code>CREATE EXTENSION</code>. Without the extension, <code>CREATE SERVER</code> fails. Fix by installing the OS package and running <code>CREATE EXTENSION mysql_fdw;</code> first.
Importing every table with <code>IMPORT FOREIGN SCHEMA</code>. This slows planning. Fix by using <code>LIMIT TO (table1, table2)</code> or creating only the tables you need.

Frequently Asked Questions (FAQs)

Is mysql_fdw compatible with all MariaDB versions?

Yes, any MariaDB version exposing the MySQL protocol (10.x+) works, provided character sets align.

Can I write data back to MariaDB?

Yes. Grant INSERT/UPDATE/DELETE on MariaDB tables and set use_remote_estimate 'true' if write performance is critical.

Does FDW support transactions?

PostgreSQL treats each statement as its own transaction on the remote side. Two-phase commit isn’t supported, so use caution for cross-engine writes.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.