15

I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the most important table in my app.

Table structure is very simple:

mysql> show create table samples;
.. skipped ...
CREATE TABLE `samples` (
      `File_ID` int(11) NOT NULL,
      `File` longblob,
      PRIMARY KEY (`File_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

but it is very large (> 20 Gb).

I've tried to use --hex-blob parameter of mysqldump - but data in this format is not accepted by PostgreSQL when I tried to use resulting dumpfile as a command file. Another option I've tried is using --tab option to just get a dump and then insert it to PostgreSQL with COPY command - but --hex-blob is not working with --tab and PostgreSQL still does not accept the dumpfile saying there are invalid characters in it.

I'd be very glad to get any advice on this matter - although I'm starting to think that writing a custom migration tool is not a bad idea after all...

6
  • Last I checked, PostgreSQL's dblink could only connect to other PostgreSQL instances. I don't think MySQL Proxy is any better. You might need to use your favorite language (IE: Java, etc) to read from MySQL in order to insert into PostgreSQL...
    – OMG Ponies
    Jul 16, 2011 at 17:27
  • @OMG: As I've noticed in question, I'm almost ready to go this way - just have a slight hope that someone knows something on this matter.
    – Sergey Kudriavtsev
    Jul 16, 2011 at 17:32
  • What data type on pgsql's end are you trying to insert it into? I'd do bytea myself.
    – Scott Marlowe
    Jul 16, 2011 at 20:05
  • @Scott: Yes, of course it's bytea.
    – Sergey Kudriavtsev
    Jul 16, 2011 at 21:08
  • 2
    posgresql 9 supports hex syntax for bytea directly, see here : postgresql.org/docs/9.0/interactive/… (you'll need a bit of sed though)
    – peufeu
    Jul 17, 2011 at 8:28

1 Answer 1

14

I think that simplest way is to use that --hex-blob switch on mysqldump and restore by psql, with decode(string text, type text). However it's not that simple, because you need to change a little produced dump (sed, awk), adding that decode function. For example:

mysqldump -u root -p --skip-quote-names --hex-blob --skip-triggers \
    --compact --no-create-info mysql samples > prepg.dump

sed "s/0x\([0-9,A-F]*\))/decode('\1','hex'))/g" prepg.dump > pg.dump

psql session:

CREATE TABLE samples
(
    file_id integer PRIMARY KEY,
    file bytea
);

\i 'path/to/pg.dump'
4
  • Thanks for a suggestion, Grzegorz, I'll give this a try and report the results.
    – Sergey Kudriavtsev
    Jul 17, 2011 at 6:13
  • Worked like a charm :) Test 10 files imported correctly, all the checksums are matching originals. Thank you very much!
    – Sergey Kudriavtsev
    Jul 17, 2011 at 6:25
  • 1
    If you have other fields you won't want it to match commas or the trailing parenthesis. This should work: sed "s/0x\([0-9A-F]*\)/decode('\1','hex')/g" prepg.dump > pg.dump
    – tjmcewan
    Dec 6, 2013 at 11:05
  • I think it's helpful to understand that the sed expression is stripping off the 0x and then wrapping the rest in the decode function. So, if your input is 0xDD76A2A5E2, your output will be decode('DD76A2A5E2','hex') Jun 25, 2021 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.