Обсуждение: backslash encoded data changed during upgrade?

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.hunley@gmail.com)
Twitter: @hunleyd                                               Web:
douglasjhunley.com
G+: http://goo.gl/sajR3

On Mon, Nov 26, 2012 at 6:12 PM, Doug Hunley <doug.hunley@gmail.com> wrote:
> 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> ?

They look like bytea fields, in which case the parameter you're
looking for is bytea_output (which is 'hex' by default now, used to be
'escape').

If you make the dump with the *new* version of pg_dump (meaning from
the system you're making the load on), it should set all parameters
required automatically. It's always recommended to use pg_dump from
the new version when moving between two versions.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Mon, Nov 26, 2012 at 12:22 PM, Magnus Hagander <magnus@hagander.net> wrote:
>
> They look like bytea fields, in which case the parameter you're
> looking for is bytea_output (which is 'hex' by default now, used to be
> 'escape').

YES! Thank you Magnus! This was exactly it.

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