From: | Magnus Hagander <magnus(at)hagander(dot)net> |
---|---|
To: | Doug Hunley <doug(dot)hunley(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: backslash encoded data changed during upgrade? |
Date: | 2012-11-26 17:22:57 |
Message-ID: | CABUevEzEpQyU1QPh_B8MSE2H49GuQ8aGw2iNHJ56nJAE3Dsryw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Doug Hunley | 2012-11-26 18:07:03 | Re: backslash encoded data changed during upgrade? |
Previous Message | Jeff Janes | 2012-11-26 17:22:52 | Re: Restore postgres to specific time |