can't restore database created with pg_dump

[Eric Smith]

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. I just created the default text format output with pg_dump, and am trying to restore using psql < dumpfile.

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 to 9.3.5. Any ideas?

Thanks,
Eric

[Adrian Klaver]

Post by Eric Smith
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. I just 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.

Post by Eric Smith
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 to 9.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 parameter to determine how string literals
will be processed. The presence of this parameter can also be taken as
an indication that the escape string syntax (E'...') is supported.
Escape string syntax (Section 4.1.2.2) should be used if an application
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 this is to use the 9.3
version of pg_dump.

Post by Eric Smith
Thanks,
Eric

[Adrian Klaver]

Thank you for the help!

For the archives, did you use the 9.3 pg_dump or change the string escaping?

Regards,
Eric

[Eric Smith]

Thank you for the help!

Regards,
Eric

Post by Eric Smith
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. I just 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.

Post by Eric Smith
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 to 9.3.5. Any ideas?

http://www.postgresql.org/docs/9.3/interactive/datatype-binary.html
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 parameter to determine how string literals will be processed. The presence of this parameter can also be taken as an indication that the escape string syntax (E'...') is supported. Escape string syntax (Section 4.1.2.2) should be used if an application 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 this is to use the 9.3 version of pg_dump.

Post by Eric Smith
Thanks,
Eric

--
Adrian Klaver
--
http://www.postgresql.org/mailpref/pgsql-general