Re: problem with backup and restore (probaly stupit newb thing)

From: Richard Huxton <dev(at)archonet(dot)com>
To: Joel Fradkin <jfradkin(at)wazagua(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: problem with backup and restore (probaly stupit newb thing)
Date: 2005-02-03 17:38:20
Message-ID: 4202618C.6010408@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message PFC 2005-02-03 18:15:04 Re: PL/PgSQL - returning multiple columns ...
Previous Message Din Adrian 2005-02-03 16:59:40 Re: [PERFORM] Tunning postgresql on linux (fedora core 3)