MySQL to PostgreSQL: How to Replicate Your Data
Replicate MySQL into PostgreSQL with Airbyte. Binlog setup, the managed provider quirks that break CDC, and what a migration does not carry across.
Replicating MySQL to PostgreSQL is usually a migration in progress. The application is moving, or the analytics layer standardised on Postgres, or an acquisition left you with two database engines and one reporting requirement. Whatever the reason, the pipeline runs alongside the old system rather than replacing it in one step.
The connectors on both ends are certified and the setup is well trodden. The thing that actually derails these projects is where MySQL is hosted, because managed providers each handle binary logs differently and several will break CDC without telling you.
How should you replicate from MySQL?
The MySQL source offers CDC through the binary log and a standard cursor-based method. Airbyte recommends CDC wherever possible, because it records deletions, scales to tables of a terabyte and beyond, and gives you a reliable cursor that does not depend on your schema having a sensible updated_at column.
Start by creating a dedicated read-only user and granting the replication permissions: GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO <user_name>; If you use the standard method instead, only SELECT is needed.
Then enable binary logging. Self-managed servers need these settings in the configuration file: log_bin = mysql-bin, binlog_format = ROW, binlog_row_image = FULL, and a unique non-zero server-id. Set binlog retention to around seven days, which on a standard install means binlog_expire_logs_seconds = 864000.
One structural requirement is easy to miss: tables need a primary key for CDC incremental syncs. Tables without one can still be replicated by CDC, but only in full refresh mode. Audit your schema for primary-key-less tables before you plan the cutover.
Where is your MySQL hosted?
This is the question that decides how much work you are in for. Managed MySQL providers each treat binary logs differently, and the defaults are frequently incompatible with CDC.
That last row is worth acting on even when nothing forces you to. Pointing Airbyte at a replica rather than the primary reduces load on the server your application depends on and gives you room to do maintenance without pausing replication.
What breaks after it is running?
One failure accounts for most support conversations. If syncs unexpectedly revert to full refreshes, check the logs for a message saying the saved offset is no longer present on the server. That means MySQL removed binlog files Airbyte still needed, usually because update volume outpaced your retention window.
The fixes are to sync more frequently, raise binlog retention, or both. The MySQL troubleshooting guide covers the scenarios in detail. There is also an initial wait time defaulting to 300 seconds, adjustable between 300 and 1200, which governs how long the connector waits before processing begins. Raise it if large snapshots are timing out.
During the initial snapshot Airbyte does not take table locks, except on MyISAM tables, so writes continue normally. It does assume no schema changes happen mid-snapshot, so avoid running migrations during that first sync.
What lands in PostgreSQL?
Add a Postgres destination with host, port, database, default schema, and a user that can write. From version 3.0.0 it uses Direct Load, writing straight into final tables with no raw tables, and each table carries your columns plus _airbyte_raw_id, _airbyte_extracted_at, and an _airbyte_meta JSONB column.
Set expectations on what a migration this way does and does not give you. Airbyte replicates table contents, not schema objects, so indexes, foreign keys, sequences, triggers, and views do not come across. For an application cutover you will recreate those separately.
Two Postgres-side limits also apply. Airbyte's documentation recommends Postgres destinations for roughly 10GB or less, so a large MySQL database is better pointed at a warehouse. And Postgres truncates identifiers at 63 bytes, which matters more here than usual because MySQL permits longer names than Postgres will keep.
Frequently asked questions
Why does my RDS MySQL keep triggering full refreshes?
RDS ignores binlog_expire_logs_seconds and uses a binlog retention hours parameter that defaults to 0, deleting logs immediately. Set it to at least 24 hours with mysql.rds_set_configuration, otherwise the binlogs Airbyte needs are gone before the next sync.
Do all my tables need primary keys?
For CDC incremental syncs, yes. Tables without a primary key can still be replicated by CDC but only in full refresh mode, so audit for them before planning a cutover.
Will my indexes and constraints be recreated in Postgres?
No. Airbyte replicates table data. Indexes, foreign keys, sequences, triggers, and views must be created on the destination separately, which is a distinct workstream in any migration plan.
Can I replicate from a MySQL replica instead of the primary?
Yes, and it is usually the better choice. Enable log_replica_updates so the replica writes transactions to its own binlog, then point the connector at it. This reduces load on the primary and gives you more maintenance flexibility.
Does the connector work with MariaDB?
It is compatible, with limitations noted specifically around CDC replication. Test CDC against your MariaDB version early rather than assuming it behaves identically to MySQL.
Get your MySQL data into PostgreSQL
Check your host's binlog behaviour first, audit for tables without primary keys, and plan the schema objects as separate work. Outgrowing Postgres as a destination? See our guides to MySQL to ClickHouse and PostgreSQL to PostgreSQL.
Integrate with 600+ apps using Airbyte
Move data from 600+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.