Am Montag, 4. April 2005 17:36 schrieb Tom Lane:
Janning Vygen <firstname.lastname@example.org> 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:
The rest is identical. i am wondering about this minor package number making
Ok. It seems to be my problem and not postgresql specific. Many thanks for
setting me on the right track.