Thank you for the help!
Regards,
Eric
On Sep 20, 2014, at 6:55 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 09/19/2014 07:51 PM, Eric Smith wrote:
>> All,
>>
>> I created a backup using pg_dump, postgres 8.3. I'm trying to restore that into a new postgres 9.3.5 installation.
Ijust created the default text format output with pg_dump, and am trying to restore using psql < dumpfile.
>
> First, you should use the pg_dump command from 9.3.5 to dump the Postgres 8.3 database, it will 'know' about new
features.This leads to the comments below.
>
>>
>> I have images saved as bytea, and I get the following error when trying to restore:
>>
>> ERROR: invalid input syntax for type bytea.
>> CONTEXT: COPY images, line 8, column imageData:
"MM\000*\003':\242\200?\300\0208$\026\015\007\204BaP\270d6\035\017\210Da\317\307\350\030\000\001\177\..."
>>
>>
>> On some of my installations the restore process works, and on some it fails. All installations are moving from 8.3
to9.3.5. Any ideas?
>
> The default output format for bytea changed in 9.0:
>
> http://www.postgresql.org/docs/9.3/interactive/datatype-binary.html
>
> So did the handling of escape strings:
>
> http://www.postgresql.org/docs/9.3/interactive/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION
>
> standard_conforming_strings (boolean)
>
> This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL
standard.Beginning in PostgreSQL 9.1, the default is on (prior releases defaulted to off). Applications can check this
parameterto determine how string literals will be processed. The presence of this parameter can also be taken as an
indicationthat the escape string syntax (E'...') is supported. Escape string syntax (Section 4.1.2.2) should be used if
anapplication desires backslashes to be treated as escape characters.
>
>
> My guess is that the installations differ on the escape string handling. Again, I would think the best way to handle
thisis to use the 9.3 version of pg_dump.
>
>>
>> Thanks,
>> Eric
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general