{"id":45802,"date":"2026-11-09T16:24:04","date_gmt":"2026-11-09T16:24:04","guid":{"rendered":"https:\/\/www.withdata.com\/blog\/?p=45802"},"modified":"2026-11-10T05:29:00","modified_gmt":"2026-11-10T05:29:00","slug":"mysql-to-postgresql-four-routes","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/dbcopier\/mysql-to-postgresql-four-routes.html","title":{"rendered":"MySQL to PostgreSQL: Four Routes, and What Breaks"},"content":{"rendered":"<p>Moving a MySQL database to PostgreSQL is not one task. It is three, and choosing the wrong route is why the migration &#8220;works&#8221; for two hours and then fails on the 600,000th row.<\/p>\n<p>These four routes are what people actually use. I tested them on a MySQL 8.0.35 \u2192 PostgreSQL 15.1 pair with a 100,000-row table (8 columns: <code>int auto_increment<\/code> primary key, varchar, decimal, int, <code>tinyint(1)<\/code>, <code>enum<\/code>, <code>datetime<\/code>, <code>text<\/code>), then checked what survived.<\/p>\n<h2>1. First, decide which of the three problems you have<\/h2>\n<table>\n<thead>\n<tr>\n<th>What you have<\/th>\n<th>What usually hurts<\/th>\n<th>Route<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>A small database (< 100 MB), clean schema<\/td>\n<td>Type mapping only<\/td>\n<td>pgloader or a dump rewrite<\/td>\n<\/tr>\n<tr>\n<td>A large database, or a migration with a downtime window<\/td>\n<td><code>mysqldump<\/code> size, restore time, sequences<\/td>\n<td>dump + rewrite, or a direct copy<\/td>\n<\/tr>\n<tr>\n<td>A schema that does not line up (different column names, only part of the tables, a filtered subset)<\/td>\n<td>Rewriting INSERTs by hand<\/td>\n<td>direct copy with a field map<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The third case is the one that eats weeks, and it is the one the command line tools handle worst.<\/p>\n<h2>2. Route one: pgloader<\/h2>\n<p>pgloader is the default answer in every forum thread, and it is a good tool.<\/p>\n<p>&#8220;<code> pgloader mysql:\/\/user:pass@127.0.0.1:3306\/shop postgresql:\/\/user:pass@127.0.0.1:5432\/shop <\/code>&#8220;<\/p>\n<p>It reads the MySQL catalog, writes the DDL, and streams the rows. When the schema is clean and the types are ordinary, it just works.<\/p>\n<p>Where it breaks: the cast rules. <code>tinyint(1)<\/code>, <code>enum<\/code>, <code>set<\/code>, unsigned ints, zero dates, and <code>json<\/code> are all types MySQL allows and PostgreSQL does not have. pgloader ships with a default cast list that covers most of them, so a run can finish while a handful of columns land wrong, or it can stop on a type nobody wrote a rule for. A community answer describes the usual outcome exactly: &#8220;pretty good success with pgloader&#8221; \u2014 <em>after<\/em> fixing the parts it got wrong.<\/p>\n<h2>3. Route two: dump, then rewrite<\/h2>\n<p>&#8220;<code> mysqldump --compatible=postgresql --no-create-info --complete-insert shop > shop_data.sql mysqldump --no-data shop > shop_schema.sql     # the DDL still has to be edited by hand <\/code>&#8220;<\/p>\n<p><code>--compatible=postgresql<\/code> helps with the data, not the schema. Backticks, <code>ENGINE=InnoDB<\/code>, <code>AUTO_INCREMENT<\/code>, <code>UNSIGNED<\/code>, <code>ENUM(...)<\/code>, <code>ON UPDATE CURRENT_TIMESTAMP<\/code>, and the key definitions all need editing. It is fine for one table and painful for forty.<\/p>\n<p>The failure mode is not an error message, it is a 300 MB <code>.sql<\/code> file that half-loaded and a <code>COPY<\/code> that stopped at line 2 million with a column count mismatch.<\/p>\n<h2>4. Route three: CSV in the middle<\/h2>\n<p>&#8220;<code> mysql -e \"SELECT * FROM orders\" shop > orders.tsv psql -c \"\\copy orders FROM 'orders.tsv'\" <\/code>&#8220;<\/p>\n<p>This is the route people fall back to, and it is honest work: it is verifiable, it needs no tool, and it works when both databases can be reached from the same machine. A PostgreSQL regular summed it up in a 2026 thread: export the data into CSV table by table, then import table by table <strong>&#8220;if it&#8217;s tiny&#8221;<\/strong>. Add millions of rows and you are back to writing an ETL. The real cost is not the copy, it is the bookkeeping: column order, quoting, <code>NULL<\/code> versus <code>\\N<\/code>, <code>timestamptz<\/code> versus <code>timestamp<\/code>, and foreign keys that have to load in the right order.<\/p>\n<h2>5. Route four: connect both databases and copy table by table<\/h2>\n<p>The fourth route removes the file in the middle. You connect the MySQL source and the PostgreSQL target, pick the tables, map the fields, and let the tool write the rows in batches. It is what DBeaver&#8217;s and dbForge&#8217;s migration wizards do, and the dbForge answer in that same thread is worth quoting because it names the real requirement: import &#8220;through ODBC&#8221;, with &#8220;control over mapping, which is useful when the schemas don&#8217;t line up perfectly.&#8221;<\/p>\n<p><a href=\"https:\/\/www.withdata.com\/db-copier\/\" target=\"_blank\" rel=\"noopener noreferrer\">DBCopier<\/a> is our tool of that shape, so this is the route I can show end to end.<\/p>\n<p><strong>The GUI path.<\/strong> Start it and the first window is <code>DBCopier Logon<\/code>, with two tabs, <strong>DB (Source)<\/strong> and <strong>DB (Target)<\/strong>.<\/p>\n<ol>\n<li>On <strong>DB (Source)<\/strong>: choose the source type (<code>MySQL \/ MariaDB<\/code>), fill host \/ port \/ database \/ user \/<\/li>\n<\/ol>\n<p>password, press <strong>Test<\/strong> (the status line under the buttons replaces <code>Not Connected.<\/code> with the server version), then <strong>Connect<\/strong>.<\/p>\n<ol>\n<li>Switch to <strong>DB (Target)<\/strong>: the same for PostgreSQL, <strong>Test<\/strong>, <strong>Connect<\/strong>, and close the dialog<\/li>\n<\/ol>\n<p>with <strong>Ok<\/strong>.<\/p>\n<ol>\n<li><code>Start a task - DBCopier<\/code> appears: press <strong>Convert From Table<\/strong> (the other three are query, all<\/li>\n<\/ol>\n<p>tables, and tables with a similar name).<\/p>\n<ol>\n<li><strong>Open Source<\/strong>: pick the schema and the table. The grid below previews the first 20 rows, so you<\/li>\n<\/ol>\n<p>can see what you are about to move before moving it.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.withdata.com\/wp\/wp-content\/uploads\/2026\/11\/dbcopier-open-source.png\" alt=\"Open Source: Schema bdv_e2e, Table orders, first 20 rows preview\" width=\"776\"><\/p>\n<ol>\n<li><strong>Set Target<\/strong>: the schema and the table name on the PostgreSQL side, the <strong>Load Type<\/strong><\/li>\n<\/ol>\n<p>(<code>Append<\/code> here), and the <strong>Columns Map<\/strong> &#8211; one row per field, with <code>Include<\/code>, the source column, the target field, an optional <code>Date Format<\/code> (<code>YYYY-MM-DD HH24:MI:SS<\/code> for <code>created<\/code>) and <code>NULLIF<\/code>. Fields with the same name are matched for you.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.withdata.com\/wp\/wp-content\/uploads\/2026\/11\/dbcopier-set-target.png\" alt=\"Set Target: Append, Columns Map with 8 fields\" width=\"776\"><\/p>\n<ol>\n<li><strong>Summary<\/strong>: the two connection strings, the first 10 rows as they will arrive, and the knobs that<\/li>\n<\/ol>\n<p>matter when something goes wrong &#8211; <code>Convert Rows<\/code>, <code>Skip Rows<\/code>, <code>Errors to allow<\/code>, and the log file path.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.withdata.com\/wp\/wp-content\/uploads\/2026\/11\/dbcopier-summary.png\" alt=\"Summary: from\/to, first 10 records, errors to allow, log file\" width=\"776\"><\/p>\n<ol>\n<li><strong>Convert Data<\/strong> asks <code>Start Converting?<\/code>, then shows progress and ends with the three numbers<\/li>\n<\/ol>\n<p>worth screenshotting:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.withdata.com\/wp\/wp-content\/uploads\/2026\/11\/dbcopier-convert-data.png\" alt=\"Convert Data: 100%, 100000 records in 6.4 seconds\" width=\"776\"><\/p>\n<p>&#8220;<code> 100000 records,  Elapsed: 00:00:06.437.  From Table: bdv_e2e.orders,  To Table: \"public\".\"orders\". Total Convert Records: 100000 End Convert <\/code>&#8220;<\/p>\n<p><strong>The same copy from the command line<\/strong> &#8212; for a nightly run instead of a hand click:<\/p>\n<p>&#8220;<code>bat DBCopier.exe copy --from=\"MySQL:user:pass@127.0.0.1:3306\/shop\" --from-table=orders ^ --to=\"PostgreSQL:user:pass@127.0.0.1:5432\/shop\"   --to-table=orders ^ --loadtype=append --yes <\/code>&#8220;<\/p>\n<p>No <code>--from-schema<\/code>, no pre-created table: the target table is created from the source (columns, types, <code>not null<\/code>, defaults, the primary key), the rows are written in batches, and the source&#8217;s secondary indexes are built <strong>after<\/strong> the rows are in.<\/p>\n<p>On the command line the same copy reported a <strong>peak working set of 27 MB<\/strong> for those 100,000 rows: the rows are committed in batches of a few hundred to a thousand instead of being materialised, which is why the number stays flat as the table grows. (Elapsed time varies with the machine and the disks; the GUI run above is the one quoted.)<\/p>\n<p>The same route works when the source is not MySQL at all \u2014 the DBF to PostgreSQL walkthrough (<a href=\"\/blog\/dbftopostgres\/copy-dbf-table-to-postgresql.html\">copy a DBF table to PostgreSQL<\/a>) is the same three steps with a different connection string.<\/p>\n<h2>6. The three things that actually break<\/h2>\n<p>These are the failures that survive a &#8220;successful&#8221; migration. All three were reproduced here.<\/p>\n<p><strong>Sequences do not follow the data.<\/strong> <code>AUTO_INCREMENT<\/code> becomes <code>serial<\/code>\/<code>GENERATED<\/code>, the rows come in with explicit ids, and the sequence stays at 1. The first application insert then fails:<\/p>\n<p>&#8220;<code> ERROR: duplicate key value violates unique constraint \"orders_pkey\" DETAIL: Key (id)=(1) already exists. <\/code>&#8220;<\/p>\n<p>The fix is one statement, after the copy:<\/p>\n<p>&#8220;<code>sql select setval(pg_get_serial_sequence('orders','id'), (select max(id) from orders)); <\/code>&#8220;<\/p>\n<p><strong><code>tinyint(1)<\/code> is a boolean, not a number.<\/strong> MySQL has no boolean type; <code>tinyint(1)<\/code> is the convention. Copy it into a <code>bigint<\/code> column and every query that expects <code>true<\/code>\/<code>false<\/code> breaks; copy it into a type that keeps MySQL&#8217;s display width (<code>bigint(1)<\/code>) and PostgreSQL refuses the whole <code>create table<\/code> with a syntax error at <code>(<\/code>.<\/p>\n<p><strong>Trailing spaces that were never there.<\/strong> MySQL&#8217;s <code>enum<\/code> has no PostgreSQL equivalent, so the driver hands it over as a fixed-width <code>CHAR<\/code> and the value arrives padded: <code>'paid            '<\/code> instead of <code>'paid'<\/code>. The column looks right and the data compares wrong. Either trim on export, or use a tool that trims it (<a href=\"https:\/\/www.withdata.com\/db-copier\/\" target=\"_blank\" rel=\"noopener noreferrer\">DBCopier<\/a> trims <code>enum<\/code> values when it reads MySQL).<\/p>\n<p>Two more worth knowing: MySQL identifiers are case-insensitive and quoted with backticks, so <code>\"OrderItems\"<\/code> copied verbatim becomes a different table name; and <code>0000-00-00<\/code> dates are rejected by PostgreSQL outright.<\/p>\n<h2>7. How to check the copy, not just the row count<\/h2>\n<p>Row counts lie when a table has been appended to twice. Compare something that aggregates:<\/p>\n<p>&#8220;<code>sql -- source and target must print the same numbers select count(*), sum(qty), round(sum(amount),2), max(length(payload)) from orders; <\/code>&#8220;<\/p>\n<p>Then check the shape, because a type that silently widened still passes the sum:<\/p>\n<p>&#8220;<code>sql \\d orders <\/code>&#8220;<\/p>\n<p>and check that the indexes came over:<\/p>\n<p>&#8220;<code>sql select indexname from pg_indexes where tablename = 'orders'; <\/code>&#8220;<\/p>\n<p>If the list holds only the primary key, the copy moved data and left the query plans behind: a <code>where customer = ?<\/code> on a 100,000-row table reads every row (measured: 99,999 rows removed by filter) instead of an index-only scan (0.2 ms). Build them after the load:<\/p>\n<p>&#8220;<code>sql create index idx_customer on orders(customer); <\/code>&#8220;<\/p>\n<h2>8. FAQ<\/h2>\n<h3>Can I migrate with a downtime window?<\/h3>\n<p>Yes, and it is the simple case: stop writes, copy, verify, switch. The four routes above all work.<\/p>\n<h3>Can I migrate without stopping writes?<\/h3>\n<p>Not by copying tables. PostgreSQL can read MySQL through <code>mysql_fdw<\/code>, and logical replication tools move changes, but the initial load still happens while the target is being built.<\/p>\n<h3>Does the tool convert stored procedures and triggers?<\/h3>\n<p>No. They are MySQL&#8217;s dialect; they have to be written again for PostgreSQL.<\/p>\n<h3>What about MySQL <code>datetime<\/code> and time zones?<\/h3>\n<p><code>datetime<\/code> has no zone; PostgreSQL&#8217;s <code>timestamp<\/code> matches it, <code>timestamptz<\/code> does not. Choose the column type on purpose, not by default.<\/p>\n<h3>Is <code>pgloader<\/code> enough on its own?<\/h3>\n<p>For a clean schema, often yes. Check <code>tinyint(1)<\/code>, <code>enum<\/code>, unsigned integers, and the indexes afterwards; they are the parts that finish quietly and wrong.<\/p>\n<h3>Can I copy only some rows?<\/h3>\n<p>With a direct copy, yes \u2014 a query source or a row limit. A dump rewrite means editing the dump.<\/p>\n<h2>9. What this is not<\/h2>\n<p>DBCopier copies data and structure between databases; it is not an ORM migration and does not rewrite application code. If the two schemas disagree on purpose (different column names, merged tables), map the fields explicitly \u2014 that is the case it exists for.<\/p>\n<hr\/>\n<h3>Older walkthroughs<\/h3>\n<p>If you are still on the older MySQLToPostgres product, its step-by-step pages are here: <a href=\"\/blog\/mysqltopostgres\/mysql-to-postgresql.html\">MySQL to PostgreSQL<\/a> \u00b7 <a href=\"\/blog\/mysqltopostgres\/switch-from-mysql-to-postgresql.html\">switch an existing task<\/a> \u00b7 <a href=\"\/blog\/mysqltopostgres\/schedule-and-automate-mysql-to-postgresql-task.html\">schedule a task<\/a>. The four routes above are product independent; those pages are the per-product instructions.<\/p>\n<hr\/>\n<h2>10. Doing the same copy on your own database<\/h2>\n<blockquote>\n<p><strong>Copy a MySQL database into PostgreSQL without a dump file in the middle.<\/strong> <a href=\"https:\/\/www.withdata.com\/db-copier\/\" target=\"_blank\" rel=\"noopener noreferrer\">Try DBCopier<\/a> \u2014 Windows \/ macOS \/ Linux, 20+ databases, one-time price.<\/p>\n<\/blockquote>\n<hr\/>\n","protected":false},"excerpt":{"rendered":"<p>Moving a MySQL database to PostgreSQL is not one task. It is three, and choosing the wrong route is why the migration &#8220;works&#8221; for two hours and then fails on the 600,000th row. These four routes are what people actually use. I tested them on a MySQL 8.0.35 \u2192 PostgreSQL 15.1 pair with a 100,000-row [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2774],"tags":[13770,13854,13856,497,13855],"class_list":["post-45802","post","type-post","status-publish","format-standard","hentry","category-dbcopier","tag-dbcopier","tag-migrate-mysql-to-postgresql","tag-mysql-to-postgres","tag-mysql-to-postgresql","tag-pgloader"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45802","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=45802"}],"version-history":[{"count":10,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45802\/revisions"}],"predecessor-version":[{"id":45856,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45802\/revisions\/45856"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=45802"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=45802"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=45802"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}