PostgreSQL to MySQL: How to Move Your Data

Moving PostgreSQL into MySQL is a relational to relational pipeline, which sounds like it should be the easiest job in this series and is not quite. The two databases agree about tables, rows and SQL, and disagree about several types that Postgres has and MySQL does not.

This guide covers the managed path with Airbyte, using logical replication so deletes and updates both arrive. The two things worth planning are how those Postgres-specific types should land, and how you monitor a replication slot, because the worst failure here happens on your source database rather than your destination.

PostgreSQL to MySQL at a glance:

Why move data from PostgreSQL to MySQL?

Two situations account for most of these pipelines.

The first is an application or tool that speaks MySQL and nothing else. Plenty of software, particularly older self-hosted products, supports one database engine, and rewriting it is not on anyone's roadmap. Replicating the tables it needs is cheaper than the alternative.

The second is a migration between engines, where the pipeline runs alongside the old system during cutover and keeps MySQL current until traffic switches. In that case the type decisions below stop being a convenience and become the design of your new schema, so they deserve proportionate care.

What do you need before you start?

The PostgreSQL side carries the prerequisites, and one of them affects the health of your production database:

Logical replication enabled. Change data capture reads the write-ahead log through a logical replication slot, so the server has to be configured for it. On managed services this is usually a parameter change and a restart rather than something you can toggle live.

A replication slot and a publication. The slot holds Airbyte's position in the log, the publication defines which tables are included, and both are created on the source and remain yours to manage.

Monitoring on that slot. PostgreSQL retains write-ahead log segments a slot has not yet confirmed, so a connection that is paused, broken or forgotten causes WAL to accumulate. Left long enough it fills the disk of the database serving your application, not anything belonging to Airbyte.

A list of your Postgres-specific columns. Arrays, ranges, enums, custom composite types, JSONB and anything from an extension. Each of these needs a decision about how it should land in MySQL, and making those decisions before the first sync is far easier than after downstream code depends on the result.

On the destination side you need a MySQL database and a user able to create and write tables in the target schema. If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on both databases.

How do you build a PostgreSQL to MySQL pipeline in Airbyte?

Step 1: Decide what happens to the awkward types

Go through your schema and find the columns MySQL has no direct equivalent for. Where a Postgres array holds a small fixed set, a MySQL JSON column or a separate child table are both reasonable answers and they lead to different downstream code. Where an enum is really a lookup, consider making it one. Doing this deliberately in a view is cleaner than discovering how the pipeline resolved it.

Step 2: Configure the PostgreSQL source

Click Sources in the left navigation, then New Source, and select Postgres, following adding a source. Supply the host, port, database, schema and credentials, and choose logical replication as the replication method, supplying your slot and publication names. Note that change data capture applies to tables rather than views, so a view-based type strategy uses cursor-based incremental instead and needs a maintained timestamp column.

Step 3: Configure the MySQL destination

Click Destinations, then New Destination, and select MySQL, following adding a destination. Supply the host, port, database and credentials. Airbyte adds its own metadata columns to each table, so if this is a migration, decide whether the application tolerates them or reads views that exclude them.

Step 4: Create the connection and verify the types

Click Connections, then New connection, select your tables and a sync mode per stream. Run against a subset first and compare the resulting MySQL column types and a sample of values against Postgres before extending. Finding a type problem on three tables is a morning. Finding it on ninety, after an application has been built on them, is not.

Which Postgres types cause trouble in MySQL?

The ones Postgres has and MySQL does not. Arrays are the most common: a text array column is idiomatic Postgres and has no MySQL counterpart, so it becomes JSON or gets normalised into a child table. Range types, composite types and extension-provided types have the same problem.

Enums exist in both but behave differently enough to be worth checking, particularly around how each handles a value that is no longer in the definition. And JSONB maps to MySQL's JSON type reasonably, though the operators your queries use will not carry across, so any downstream SQL touching those columns needs rewriting rather than repointing.

One thing that is not a problem, unusually. Identifier length matches up: PostgreSQL allows 63 characters and MySQL 64, so column and table names travel in this direction without collisions. Going the other way is where names can be too long, which is a small mercy specific to this pairing.

What happens if the pipeline stops?

This deserves its own section because the consequence lands on your production database rather than on the pipeline. A logical replication slot marks the oldest position a consumer still needs, and PostgreSQL will not discard write-ahead log segments beyond it.

So if the connection is paused for a sprint, fails repeatedly over a holiday, or somebody deletes the Airbyte connection without dropping the slot, WAL accumulates. On a busy database that consumes disk quickly, and the failure mode is an outage on the system serving your users rather than a stale table.

Two habits prevent it. Monitor replication slot lag alongside your other database metrics and alert on it, treating it as a database health signal rather than a pipeline one. And make dropping the slot part of your decommissioning checklist, because an abandoned slot is the version of this problem nobody is watching for.

Frequently asked questions

Are deletions in PostgreSQL reflected in MySQL?

Yes, when the stream uses logical replication. Cursor-based incremental cannot observe a deletion, so a view-based approach needs a soft-delete column or a periodic full refresh instead.

Why is my PostgreSQL disk filling up?

An unconsumed logical replication slot. PostgreSQL retains write-ahead log beyond the slot's position, so a paused or abandoned connection causes WAL to accumulate. Monitor slot lag and drop slots you no longer use.

What happens to my array columns?

MySQL has no array type, so they land as JSON or need normalising into a child table. Decide which before the first sync, since the two lead to different downstream code.

Does this migrate my indexes and functions?

No. The pipeline moves rows. Constraints, indexes, triggers, sequences and procedural logic have to be recreated separately as part of the wider migration.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. You may want PostgreSQL views to shape awkward types deliberately, and any downstream SQL using Postgres-specific JSON operators will need rewriting for MySQL.

Get your PostgreSQL data into MySQL

List your Postgres-specific columns and decide how each should land before you configure anything, verify types on a subset before extending, and monitor the replication slot as a database health metric. The worst failure on this pipeline is not a stale table, it is a full disk on the database serving your application.

Airbyte's connector catalog includes 700+ pre-built connectors, so the same PostgreSQL source can feed a warehouse alongside this one. For the equivalent pipeline in the other direction, see MySQL to PostgreSQL, and for PostgreSQL into a document store, PostgreSQL to MongoDB.

Start syncing now →