Thread

1. Backup/Restore bytea data

   sub3 <steve@subwest.com> — 2013-01-14T01:13:00Z

   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.
   
   
   

2. Re: Backup/Restore bytea data

   Richard Huxton <dev@archonet.com> — 2013-01-14T08:53:22Z

   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