Re: Restoring pg_dump Backup with psql Fails

"Phillip Smith" <phillip.smith@weatherbeeta.com.au> writes:
> I've attached the first 512 lines of the output from psql. The whole output
> is 3mb.

Hmm, these are a bit troubling:

> ERROR:  relation "grps" already exists
> ...
> ERROR:  relation "stock" already exists

because you claimed you were restoring into an empty database ---
either you're mistaken or there is an interesting bug in pg_dump.
Please look into that more closely.

It's hard to be entirely certain, but the subsequent errors seem to
mostly flow from having pre-existing tables "grps" and "stock" that
don't match what the dump script is expecting.

There seems to be a separate encoding problem too:

> ERROR:  invalid byte sequence for encoding "UTF8": 0x96
> HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is
controlledby "client_encoding". 
> CONTEXT:  COPY dwh_cust, line 7413
> ERROR:  invalid byte sequence for encoding "UTF8": 0xe86d65
> HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is
controlledby "client_encoding". 
> CONTEXT:  COPY dwh_stk, line 323461

You didn't mention what PG version you are dumping from, but I'm
thinking it was one that wasn't very strict about encoding correctness.
You may need to clean up the dump file with iconv or some similar tool
--- look through the PG archives for lots of previous discussions about
that.

            regards, tom lane