Thread: Backup/Restore bytea data

Hi,
I am having an issue upgrading a really old 8.2 db up to 9.2.2. One of the
tables contains a bytea field.  When I backup & restore using pgadmin from
my 9.2.2 install, it doesn't convert this field correctly.

The original 8.2 database was created like:
  CREATE DATABASE test... ENCODING = 'SQL_ASCII' ...;

I tried creating the new database as:
  CREATE DATABASE test WITH OWNER = steve
  ENCODING = 'UTF8' TABLESPACE = pg_default
  LC_COLLATE = 'English_United States.1252'
  LC_CTYPE = 'English_United States.1252'
  CONNECTION LIMIT = -1;

And I also tried creating it w/ENCODED back to 'SQL_ASCII', but it still
give me bad data in the bytea field.

I can confirm it is not the same data by executing:
  select encode(data, 'escape') from pic_data where key = 36
I see it starts w/special character when selecting it from the old database;
in the new db, I see a string starting w/"\211PNG".

I've googled around and found someone else converting the full back using
iconv, so they can import into a UTF8 db, but that didn't work for me. Plus,
I didn't think I would need to do anything for an SQL_ASCII->SQL_ASCII
backup/restore.

What am I missing here?

Thanks.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Backup-Restore-bytea-data-tp5740005.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 14/01/13 01:13, sub3 wrote:
> Hi,
> I am having an issue upgrading a really old 8.2 db up to 9.2.2. One of the
> tables contains a bytea field.  When I backup&  restore using pgadmin from
> my 9.2.2 install, it doesn't convert this field correctly.

Could this be due to your bytea_output setting?
   http://www.postgresql.org/docs/9.2/static/datatype-binary.html

Not sure how this could snag you if you are dumping using 9.2, but this:

> I see it starts w/special character when selecting it from the old database;
> in the new db, I see a string starting w/"\211PNG".

is clearly in "escape" rather than "hex" format.

--
   Richard Huxton
   Archonet Ltd