Get mysqldump to dump data suitable for psql input (escaped single quotes)

Question

I'm trying to port a database from MySQL to PostgreSQL. I've rebuilt the schema in Postgres, so all I need to do is get the data across, without recreating the tables.

I could do this with code that iterates all the records and inserts them one at a time, but I tried that and it's waaayyyy to slow for our database size, so I'm trying to use mysqldump and a pipe into psql instead (once per table, which I may parallelize once I get it working).

I've had to jump through various hoops to get this far, turning on and off various flags to get a dump that is vaguely sane. Again, this only dumps the INSERT INTO, since I've already prepared the empty schema to get the data into:

      /usr/bin/env \
      PGPASSWORD=mypassword \
      mysqldump \
      -h mysql-server \
      -u mysql-username \
      --password=mysql-password \
      mysql-database-name \
      table-name \
      --compatible=postgresql \
      --compact \
      -e -c -t \
      --default-character-set=utf8 \
      | sed "s/\\\\\\'/\\'\\'/g" \
      | psql \
      -h postgresql-server \
      --username=postgresql-username \
      postgresql-database-name

Everything except that ugly sed command is manageable. I'm doing that sed to try and convert MySQL's approach to quoting single-quotes inside of strings ('O\'Connor') o PostgreSQL's quoting requirements ('O''Connor'). It works, until there are strings like this in the dump: 'String ending with a backslash \\'... and yes, it seems there is some user input in our database that has this format, which is perfectly valid, but doesn't pass my sed command. I could add a lookbehind to the sed command, but I feel like I'm crawling into a rabbit hole. Is there a way to either:

a) Tell mysqldump to quote single quotes by doubling them up b) Tell psql to expect backslashes to be interpreted as quoting escapes?

I have another issue with BINARY and bytea differences, but I've worked around that with a base64 encoding/decoding phase.

EDIT | Looks like I can do (b) with set backslash_quote = on; set standard_conforming_strings = off;, though I'm not sure how to inject that into the start of the piped output.

score 3 · Accepted Answer · answered Sep 02 '12 at 14:04

3

Dump the tables to TSV using mysqldump's --tab option and then import using psql's COPY method.

answered Sep 02 '12 at 14:04

eggyal

122,705
18
212
237

Thanks, I'll take a look at this. I had started out trying CSV, but that falls apart with binary data and with large text fields. Presumably TSV has ways of dealing with this? – d11wtq Sep 02 '12 at 14:07
@d11wtq: Use [`--hex-blob`](http://dev.mysql.com/doc/en/mysqldump.html#option_mysqldump_hex-blob). Large text fields shouldn't be a problem. – eggyal Sep 02 '12 at 14:09
this looks very promising! Thanks a bunch :) – d11wtq Sep 02 '12 at 14:10
I didn't end up using hex-blob, since it meant I'd need to use sed again, which is fraught with edge-cases. Instead, I performed the conversion to hex in MySQL, then exported the field as text in the format that Postgres expects: `CREATE FUNCTION PG_BYTEA(s BLOB) RETURNS TEXT DETERMINISTIC RETURN CONCAT('\\x', HEX(s));`. This works a treat, provided `backslash_quote` is on and `standard_conforming_strings` are off in Postgres. – d11wtq Sep 03 '12 at 13:21

lee · Answer 2 · 2015-10-21T15:27:54.563

The file psqlrc and ~/.psqlrc may contain SQL commands to be executed when the client starts. You can put these three lines, or any other settings you would like in that file.

SET standard_conforming_strings = 'off';
SET backslash_quote = 'on';
SET escape_string_warning = 'off';

These settings for psql combined with the following mysqldump command will successfully migrate data only from mysql 5.1 to postgresql 9.1 with UTF-8 text (Chinese in my case). This method may be the only reasonable way to migrate a large database if creating an intermediate file would be too large or too time consuming. This requires you manually migrate the schema, since the two database's data types are vastly different. Plan on typing out some DDL to get it right.

mysqldump \
--host=<hostname> \
--user=<username> \
--password=<password> \
--default-character-set=utf8 \
--compatible=postgresql \
--complete-insert \
--extended-insert \
--no-create-info \
--skip-quote-names \
--skip-comments \
--skip-lock-tables \
--skip-add-locks \
--verbose \
<database> <table> | psql -n -d <database>

It should be mentioned that you *have* to migrate the schema separately, as it's impossible to dump it in Postgres format. It requires manual editing. Why did you need `complete-insert` and `extended-insert`? You can combine all the skips with `--compact`. The rest was necessary, though I didn't want `verbose`. I had trouble with MySQL defaults for timestamps, so I had to use `sed`. Here was my final command: `mysqldump --compress --compatible postgresql --no-create-info --compact --default-character-set=utf8 dbname | sed $'s/\'0000-00-00 00:00:00\'/NULL/g' | psql dbname` — Chloe, Sep 22 '15 at 23:58
Ok I found out. `--complete-insert` is good for when your production schema doesn't match the column order of your development schema, for whatever reason. You were right. `--extended-insert` is on by default. — Chloe, Sep 23 '15 at 23:43

score 1 · Answer 3 · answered Sep 02 '12 at 14:02

1

Try this:

sed -e "s/\\\\'/\\\\\\'/g" -e "s/\([^\\]\)\\\\'/\1\\'\\'/g"

Yeah, "Leaning Toothpick Syndrome", I know.

answered Sep 02 '12 at 14:02

Ansgar Wiechers

193,178
25
254
328

TBH, I think I want to avoid the sed. It feels like the wrong solution. I have written a little script that I pipe the data through to add some headers to put postgres in a less-ansi-strict mode and that is working for now, but @eggyal's solution is hopefully cleaner. – d11wtq Sep 02 '12 at 14:08

Get mysqldump to dump data suitable for psql input (escaped single quotes)

3 Answers3

Linked