Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

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

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

    
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

1 Answer 1

up vote 10 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'
share|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
    
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 '13 at 11:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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