Re: problem with backup and restore (probaly stupit newb thing) - Mailing list pgsql-sql

Joel Fradkin wrote:
> Can anyone help me out on this (I will need to backup and restore the data
> base, but am not savy on the proper save and restore syntax). I used the
> default ones in PGadmin as detailed below and it blew up on 
> pg_restore: restoring data for table "tblaction"
> pg_restore: ERROR:  invalid byte sequence for encoding "UNICODE": 0xe9
> CONTEXT:  COPY tblaction, line 1799, column value: "Chargé"
> pg_restore: [archiver (db)] error returned by PQendcopy
> pg_restore: *** aborted because of error

Looking at your output, that's not the only error.

> Backup command=
> D:\Program Files\pgAdmin III\pg_dump.exe -i -h 192.168.123.112 -p 5432 -U
> postgres -F c -b -v -f "D:\backups\postgrescompressed.backup" wazagua
>  
> Restorecommand=
> D:\Program Files\pgAdmin III\pg_restore.exe -i -h 192.168.123.112 -p 5432 -U
> postgres -d waztest -v "D:\backups\postgrescompressed.backup"
>  
> Using 
> ServerVersion: 07.03.0200 PostgreSQL 7.4.6 on i386-redhat-linux-gnu,
> compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2

Good to see all the information needed - means we don't need to make any  guesses.

> Resulting error text =
> D:\Program Files\pgAdmin III\pg_restore.exe -i -h 192.168.123.112 -p 5432 -U
> postgres -d waztest -v "D:\backups\postgrescompressed.backup"
> pg_restore: connecting to database for restore
> pg_restore: creating DUMP TIMESTAMP DUMP TIMESTAMP
> pg_restore: creating SCHEMA public
> pg_restore: creating COMMENT SCHEMA public
> pg_restore: creating FUNCTION plpgsql_call_handler()
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 9; 1255 16207920 FUNCTION
> plpgsql_call_handler() postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at
> or near "OWNER" at character 46
>     Command was: ALTER FUNCTION public.plpgsql_call_handler() OWNER TO
> postgres;

OK - let's start here. The syntax here is fine, I just tested it on an 
8.0 database. Are you sure the DB you are restoring to is version 8?

> pg_restore: creating PROCEDURAL LANGUAGE plpgsql
> pg_restore: creating FUNCTION inserttabledata(character varying, character
> varying)
> pg_restore: [archiver (db)] Error from TOC entry 10; 1255 16227934 FUNCTION
> inserttabledata(character varying, character varying) postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at
> or near "$" at character 94
>     Command was: CREATE FUNCTION inserttabledata(character varying,
> character varying) RETURNS boolean
>     AS $_$

This is odd too - another version 8 feature.

I've skipped to the end here:

> pg_restore: restoring data for table "tblaction"
> pg_restore: ERROR:  invalid byte sequence for encoding "UNICODE": 0xe9
> CONTEXT:  COPY tblaction, line 1799, column value: "Chargé"

Well, it's complaining that the data you're restoring isn't unicode 
(UTF-8). Was the database you dumped from set up to store UTF-8 or was 
it SQL-ASCII or ISOxxxx? I suppose it might be something to do with a 
linux<=>windows transfer, but the place to start is running "psql -l" on 
the server.

--  Richard Huxton  Archonet Ltd