Re: Need help importing dump with bytea into db

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Philippe Dirkse <philippe(dot)dirkse(at)alten(dot)nl>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Need help importing dump with bytea into db
Date: 2012-08-13 14:23:29
Message-ID: 14626.1344867809@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Philippe Dirkse <philippe(dot)dirkse(at)alten(dot)nl> writes:
> I have the strangest problem. Scenario: Server running postgresql 8.4.10, 32-bit Ubuntu, local machine running postgresql 9.1.4, 64-bit OSX Snow Leopard. I want to create a local copy of the database running on the server for development purposes, as the server database is 'live'.

> So, on my Mac I use pg_dump to create a dump from the server database. This database contains one table that has a bytea column which stores pictures (png,jpg). Examining the dump, I see this data is 'escaped' as octects for all 'non-readable' characters. Now, when I use psql (or pg_restore for that matter) to import the dump into my local database, the bytea data is not 'unescaped' properly. It appears as if the characters for the string representation of the actual bytes are returned.

My first guess is that there's nothing actually wrong with the data, but
you're getting confused by the fact that 9.1 by default outputs bytea
data in hex rather than the traditional "escaped" format. See the
bytea_output configuration parameter.

regards, tom lane

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Michael Wood 2012-08-14 06:25:05 Re: how to get desired html format output?
Previous Message Philippe Dirkse 2012-08-13 07:56:37 Need help importing dump with bytea into db