up vote 6 down vote favorite
2
share [fb]

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...

link|improve this question
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 '11 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 '11 at 17:32
@regilero: thanks for the advice, I'll try! – Sergey Kudriavtsev Jul 16 '11 at 19:05
What data type on pgsql's end are you trying to insert it into? I'd do bytea myself. – Scott Marlowe Jul 16 '11 at 20:05
1  
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 '11 at 8:28
show 3 more comments
feedback

migrated from stackoverflow.com Aug 1 '11 at 4:38

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 4 down vote accepted

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'
link|improve this answer
Thanks for a suggestion, Grzegorz, I'll give this a try and report the results. – Sergey Kudriavtsev Jul 17 '11 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 '11 at 6:25
feedback

Your Answer

 
or
required, but never shown