backslash encoded data changed during upgrade?

From: Doug Hunley <doug(dot)hunley(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: backslash encoded data changed during upgrade?
Date: 2012-11-26 17:12:40
Message-ID: CALxYTP4N8ztjo=H2hOKO05ZgaFpH24hmhPKsN8Yj3nP8PCGzyw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have an older J2EE-based application that we're trying to limp along
until everyone can get fully migrated off it, and one of the steps in
keeping it running is to move it to a new host. Said new host runs
PostgreSQL 9.2.1 where the existing host runs 8.4.6. On the old
system, the data looks like:
COPY report_parameter (id, report_id, parameter_name, parameter_value)
FROM stdin;
rptp1001 report1001 displayColumn
\\254\\355\\000\\005ur\\000\\023[Ljava.lang.S
tring;\\255\\322V\\347\\351\\035{G\\002\\000\\000xp\\000\\000\\000\\001t\\000\\001*

which as you can see is a bunch of backslash encoded crap. I
understand that the handling of this data changed in newer releases,
and when I check on the new system, said data looks like:
rptp1001 | report1001 | displayColumn |
\xaced0005757200135b4c6a6176612e6c616e672e53747269
6e673badd256e7e91d7b470200007870000000017400012a

which as you can see, is not the same data. I ran a plain text pg_dump
and verified that the data makes it out in the correct form, it's only
when loaded into the new system that it is incorrect.

I've checked the settings on the old:
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
escape_string_warning = off
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = on
#standard_conforming_strings = off

and the new:
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
escape_string_warning = off
#lo_compat_privileges = off
#quote_all_identifiers = off
#sql_inheritance = on
standard_conforming_strings = off

and cannot come up w/ a proper combination of on/off to preserve this
data. Can someone enlighten me to the magical incantation needed to
preserve the strings as \\254\\355\\000<foo> ?

thanks!
--
Douglas J Hunley (doug(dot)hunley(at)gmail(dot)com)
Twitter: @hunleyd Web:
douglasjhunley.com
G+: http://goo.gl/sajR3

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Janes 2012-11-26 17:22:52 Re: Restore postgres to specific time
Previous Message Albe Laurenz 2012-11-26 15:32:47 Re: Restore postgres to specific time