Thread: DB with bytea types reload problem

I am using 7.3.2

I've made a mistake and saved a database like this

    pg_dump -a -d <db> > out.sql

...instead of using a binary output format, which I guess I need. I
can't reload the db due to some thumbnail images which are stored as
bytea.

    psql: \i out.sql

Causes multiple errors, such as:

    psql:out.sql:505: invalid command \N
    psql:out.sql:505: invalid command \

The sql dump file has lines starting something like this for the photo
table:
    1   500 375 h
\\377\\330\\377\\340\\000\\020JFIF\\000\\001\\001\\001\\000H\\000H\\000\\000\\377\\341\\023>Exif\\000\\000MM\\000*\\000\\000\\000\\010\\000\\012\\00....

Is there any way I can retrieve the image data?

Thanks,
Rory
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

Rory Campbell-Lange wrote:
>     pg_dump -a -d <db> > out.sql

 From this:

 > The sql dump file has lines starting something like this for the photo
 > table:
 >     1   500 375 h \\377\\330\\377\\340...

it doesn't look to me like you actually ran that command. The -d says
you should have INSERT statements, but you seem to have copy out data.
Try doing:

COPY tablename FROM 'out.sql';

or some variation thereof.

Joe