Re: dump/restore to 7.4devel giving "[archiver (db)] error returned by PQputline"

Tom wrote:

> This is odd and disturbing.  7.2 and later servers should never generate
> an unescaped \r in COPY output, so the first error shouldn't appear;
> and even if it did, the new FE/BE protocol is supposed to prevent loss
> of message-boundary sync, which the second error suggests is happening
> anyway.  It's really not clear what's being sent or who's at fault.
>
> Unfortunately, I can't think of any painless method of tracing down an
> error that is happening 146178 lines into a bulk COPY :-(.  If you are
> running this across a TCP socket, maybe you could capture the traffic
> with tcpdump --- if so, looking at the last few dozen packets in each
> direction would be mighty useful ...

Gladly, but that's new to me so it might take a bit.


Is it useful to see what pg_restore does without the "-d" flag?

Switching back to tools I know, I see that trying
   pg_restore --no-acl -O -v -U logs < /mnt/data1/tmp/logs2.dmp > bad.sql
with no "-d" flag, followed by
   cat -n bad.sql | LANG=C grep d_ref
tells me that that copy command started 26,869,936 lines into the dump...
followed by
   tail +26869938 bad.sql | head 146188 | tail -20
and do a hex dump of the result, I do indeed see a "0x0d" character
at the end of one of the fields there.


I can create a one-row-table
   create table tmp_bad as select * from d_ref where ref_id=145278;
and dump it
   copy tmp_bad to '/tmp/zzz';
and I see the "0x0d" in a hex dump of that file as well.


Given your first statement, I think this means my old 7.2 database is
somehow broken.


On this database
   select version();
gives me
   Postgresql 7.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)


Later, (though probably not til the weekend) I'd be happy to try
the tcpdump if that's a better tool, though; since it sounds like
the new protocol should have recovered more gracefully than it did.

   Thanks for your help!
   Ron