How to install MariaDB in PostgreSQL

Galaxy Glossary

How do I install mysql_fdw so PostgreSQL can query MariaDB?

Install mysql_fdw so PostgreSQL can query MariaDB tables through foreign data wrappers.

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

Example H2

Example H3

Why integrate MariaDB with PostgreSQL?

Running analytics in PostgreSQL while transactional data lives in MariaDB is common. The mysql_fdw extension lets you query MariaDB tables from Postgres without ETL, keeping data fresh and reducing duplication.

What prerequisites are required?

You need PostgreSQL 13+, superuser rights, network access to the MariaDB server, and the system packages postgresql-server-dev and libmysqlclient-dev so that mysql_fdw can compile.

How do I install mysql_fdw?

On Debian-based Linux run: sudo apt install postgresql-13-mysql-fdw. For source install, clone the GitHub repo, run make && sudo make install, then restart PostgreSQL so the shared library loads.

How do I enable the extension in a database?

Connect to the target database and run CREATE EXTENSION IF NOT EXISTS mysql_fdw;. The command registers the foreign-data wrapper and makes the server and user-mapping commands available.

How do I create a foreign server and user mapping?

Define the remote host: CREATE SERVER mariadb_srv FOREIGN DATA WRAPPER mysql_fdw OPTIONS(host 'maria.prod', port '3306'); Then map credentials: CREATE USER MAPPING FOR CURRENT_USER SERVER mariadb_srv OPTIONS(username 'report', password '********');

How can I import existing MariaDB tables?

Run IMPORT FOREIGN SCHEMA ecommerce FROM SERVER mariadb_srv INTO public;. Postgres creates foreign tables Customers, Orders, Products, and OrderItems that point to the original MariaDB objects.

How do I query MariaDB data from Postgres?

Use normal SQL: SELECT c.name, o.total_amount FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.order_date > CURRENT_DATE - INTERVAL '30 days';. Postgres plans the join; mysql_fdw pushes filters to MariaDB when possible.

What are best practices?

Index join columns in MariaDB, set OPTIONS(use_remote_estimate 'true') on the server for better planner stats, and restrict imported columns to those you need to cut network overhead.

What common mistakes should I avoid?

Do not store sensitive passwords in plain text; use CREATE USER MAPPING with a dedicated least-privilege MariaDB account. Avoid wide SELECT * queries; fetch only required columns to keep latency low.

Why How to install MariaDB in PostgreSQL is important

How to install MariaDB in PostgreSQL Example Usage


SELECT p.name, p.price, oi.quantity, (p.price*oi.quantity) AS line_total
FROM orderitems oi
JOIN products   p ON p.id = oi.product_id
JOIN orders     o ON o.id = oi.order_id
WHERE o.order_date >= DATE '2024-01-01'
ORDER BY line_total DESC;

How to install MariaDB in PostgreSQL Syntax


-- Install extension (superuser)
CREATE EXTENSION IF NOT EXISTS mysql_fdw;

-- Register remote MariaDB host
CREATE SERVER mariadb_srv
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host 'maria.prod', port '3306');

-- Map Postgres role to MariaDB credentials
CREATE USER MAPPING FOR CURRENT_USER
  SERVER mariadb_srv
  OPTIONS (username 'report', password 'secret');

-- Import ecommerce tables
IMPORT FOREIGN SCHEMA ecommerce
  FROM SERVER mariadb_srv INTO public;

-- Example query using imported tables
SELECT c.id, c.name, o.total_amount
FROM customers   c
JOIN orders      o ON o.customer_id = c.id
WHERE o.order_date > CURRENT_DATE - INTERVAL '7 days';

Common Mistakes

Forgetting to restart PostgreSQL after installing the shared library. Fix by running <code>sudo systemctl restart postgresql</code> or <code>pg_ctl restart</code> so the server can load mysql_fdw.
Granting application roles direct superuser access to create the extension. Instead, have a DBA install mysql_fdw once, then grant USAGE on the foreign server and SELECT on the foreign tables.