Re: backslash encoded data changed during upgrade?

On Mon, Nov 26, 2012 at 6:12 PM, Doug Hunley <doug(dot)hunley(at)gmail(dot)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/