How to Connect to MySQL on AWS in PostgreSQL

Galaxy Glossary

How do I connect PostgreSQL to an AWS MySQL database?

Use the mysql_fdw extension to query an AWS-hosted MySQL database directly from PostgreSQL.

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 connect PostgreSQL to MySQL on AWS?

Cross-database querying avoids ETL overhead, keeps reports current, and simplifies migrations. Using mysql_fdw, PostgreSQL treats AWS RDS or Aurora MySQL tables as native foreign tables you can read and write.

Which AWS endpoints and credentials are required?

You need the RDS/Aurora endpoint, port (default 3306), a MySQL user with SELECT/INSERT/UPDATE privileges, and the database name that holds your ecommerce schema.

How do I install `mysql_fdw`?

On Amazon Linux: sudo yum install postgresql15-contrib mysql_fdw. On Debian/Ubuntu: sudo apt-get install postgresql-15-mysql-fdw. Restart PostgreSQL after installation.

What is the exact syntax for wiring up the FDW?

Run the commands in the Syntax section. They create the extension, point a foreign server at your AWS endpoint, map credentials, then import tables.

How do I query AWS MySQL tables from PostgreSQL?

After IMPORT FOREIGN SCHEMA, run normal SELECT, INSERT, or UPDATE on the created foreign tables, as shown in the Example Query.

Can I write back to MySQL?

Yes—mysql_fdw supports DML. Ensure the mapped MySQL user has write privileges and include OPTIONS ("use_remote_estimate" 'true') for better planner stats.

How to handle data-type mismatches?

Use explicit casts or create PostgreSQL views that cast MySQL columns (e.g., DECIMAL→numeric, TINYINT(1)→boolean). Test inserts to confirm.

What about performance and cost?

Keep the FDW in the same AWS region as MySQL to minimize latency. Push down filters in your queries (WHERE, LIMIT) so MySQL does the heavy lifting and reduces data transfer.

Best practices for production?

Encrypt traffic with SSL, rotate MySQL credentials, and restrict the FDW role to least privilege. Monitor pg_stat_foreign_tables for slow scans.

Why How to Connect to MySQL on AWS in PostgreSQL is important

How to Connect to MySQL on AWS in PostgreSQL Example Usage


-- Compare customer spend across engines
SELECT c.id,
       c.name,
       SUM(oi.quantity * p.price) AS lifetime_value
FROM   Customers c
JOIN   Orders o       ON o.customer_id = c.id
JOIN   OrderItems oi  ON oi.order_id   = o.id
JOIN   Products p     ON p.id          = oi.product_id
WHERE  o.order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP  BY c.id, c.name
ORDER  BY lifetime_value DESC
LIMIT  10;

How to Connect to MySQL on AWS in PostgreSQL Syntax


-- 1. Enable extension once per database
CREATE EXTENSION IF NOT EXISTS mysql_fdw;

-- 2. Register the AWS MySQL server
CREATE SERVER aws_mysql
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host 'mydb-mysql.c8hdf2.us-east-1.rds.amazonaws.com',
           port '3306');

-- 3. Map PostgreSQL role to MySQL credentials
CREATE USER MAPPING FOR report_user
  SERVER aws_mysql
  OPTIONS (username 'analytics', password 'S3cr3tPwd!');

-- 4. Import only needed ecommerce tables
IMPORT FOREIGN SCHEMA ecommerce
  LIMIT TO (Customers, Orders, Products, OrderItems)
  FROM SERVER aws_mysql INTO public;

-- 5. Optional: improve planner estimates
ALTER SERVER aws_mysql OPTIONS (add "use_remote_estimate" 'true');

Common Mistakes

Omitting <code>IMPORT FOREIGN SCHEMA</code> filters pulls the entire MySQL catalog, cluttering PostgreSQL. Always use <code>LIMIT TO (table1, table2 ...)</code> to import only what you need.
Using a wide-open MySQL user. Grant only required privileges (e.g., SELECT, INSERT) and avoid SUPER or ALL to reduce security risk.

Frequently Asked Questions (FAQs)

Is mysql_fdw built into AWS RDS PostgreSQL?

No. RDS PostgreSQL supports mysql_fdw in custom DB parameter groups. For self-managed PostgreSQL, compile or install the package manually.

Does mysql_fdw support SSL to MySQL?

Yes. Add OPTIONS (sslmode 'require') in CREATE SERVER to enforce encryption.

Can I migrate data bi-directionally?

Use INSERT INTO local_table SELECT * FROM foreign_table to pull data or the reverse to push. For large volumes, rely on pg_dump / mysqldump plus aws dms.

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.