invalid input syntax for type bytea

Janning Vygen <vygen@gmx.de> writes:

i have a databse in postgresql 7.4 with some pdf files in a bytea column.

everything works fine but sometimes when i dump and restore i get

psql:../tmp/dump.sql:704022: ERROR: invalid input syntax for type bytea
CONTEXT: COPY dk_dokument, line 127, column dk_content:
"%PDF-1.4\015%\342\343\317\323\015\01220 0 obj\015<< \015/Linearized 1
\015/O 22 \015/H [ 660 209 ] \..."

You might look into whether your handling of the dump file is perfectly
clean --- is there any possibility of newline transformations getting
applied to it, for example? AFAICS the above cannot happen if the
identical data dumped out by pg_dump is reloaded.

regards, tom lane

Am Montag, 4. April 2005 17:36 schrieb Tom Lane:

Janning Vygen <vygen@gmx.de> writes:

i have a databse in postgresql 7.4 with some pdf files in a bytea column.

everything works fine but sometimes when i dump and restore i get

psql:../tmp/dump.sql:704022: ERROR: invalid input syntax for type bytea
CONTEXT: COPY dk_dokument, line 127, column dk_content:
"%PDF-1.4\015%\342\343\317\323\015\01220 0 obj\015<< \015/Linearized 1
\015/O 22 \015/H [ 660 209 ] \..."

You might look into whether your handling of the dump file is perfectly
clean --- is there any possibility of newline transformations getting
applied to it, for example? AFAICS the above cannot happen if the
identical data dumped out by pg_dump is reloaded.

what i do is part of my nightly cronjob.

On my DB Server i do:
$ /usr/local/pgsql/bin/pg_dump -O myuser > dump.sql

On my Testserver i do:
$ scp -q -C myuser@dbserver:dump.sql ./dump.sql
$ cp ./dump.sql ~/dump.sql
$ psql -q -f ~/dump.sql dbname >/dev/null

I can't see any command which does newline translations or something else, but
maybe my '-C' in scp makes the difference. I had problems with pg_dump when i
used the compressed format with -Fc

But both servers should be identical. But i compared gzip, ssh, and zlib and
they are not:

dbserver$ openssh-3.5p1-42
testserver$ openssh-3.5p1-107

The rest is identical. i am wondering about this minor package number making
any difference.

Ok. It seems to be my problem and not postgresql specific. Many thanks for
setting me on the right track.

kind regards,
Janning Vygen