Skip site navigation (1) Skip section navigation (2)

backup + restore fails

From: "Schoenen, Holger" <holger(dot)schoenen(at)sds-bs(dot)de>
To: "'pgsql-bugs(at)postgresql(dot)org'" <pgsql-bugs(at)postgresql(dot)org>
Subject: backup + restore fails
Date: 2006-09-15 13:07:00
Message-ID: 47CEFD79750B0A4FA01C60600C0CD763F8E1E0@sds-ex-mh04 (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
I have installed the new postgresql-8.1.msi on windows XP. 
All standard as proposed except: encoding UTF8 and locale Germany/german as
a service. (UTF8 seams to be important, the locale not?)

Using pgAdmin-III I have connected and created a new database. Optionally I
can create a table - same result. Analyse: no errors found.

Then: backup compressed and restore using pgAdminIII.
I will get the error: pg_restore: [archiver (db)] could not execute query:
ERROR:  invalid byte sequence for encoding "UTF8": 0xe46973
    Command was: --
Why that???

In the backup or in the SQL-dump (see below) the are special german
"Umlaute" ("ä")
-- Started on 2006-09-15 14:56:51 Westeuropäische Normalzeit
This is only comment and I have no influence on it, but possibly the source
of the problem?

If I hit "ok" expecting to close the window the same happens again. I have
clipped the result here:

--- clipp ---
C:\Programme\PostgreSQL\8.1\bin\pg_restore.exe -i -h localhost -p 5432 -U
postgres -d ibv -v "C:\eclispace\ibv\ibv-site\tableN1.backup"
pg_restore: connecting to database for restore
pg_restore: [archiver (db)] Error while INITIALIZING:
pg_restore: [archiver (db)] could not execute query: ERROR:  invalid byte
sequence for encoding "UTF8": 0xe46973
    Command was: --
-- PostgreSQL database dump
--

-- Started on 2006-09-15 13:16:16 Westeuropäische Normalzeit

SET client_encoding = 'UTF8';
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 249; 2612 16386 PROCEDURAL
LANGUAGE plpgsql 
pg_restore: [archiver (db)] could not execute query: ERROR:  language
"plpgsql" already exists
    Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
pg_restore: creating TABLE test
pg_restore: [archiver (db)] Error from TOC entry 1183; 1259 16404 TABLE test
postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "test"
already exists
    Command was: CREATE TABLE test (
    spalte character varying NOT NULL
);
pg_restore: restoring data for table "test"
pg_restore: creating CONSTRAINT test_pkey
pg_restore: [archiver (db)] Error from TOC entry 1506; 2606 16410 CONSTRAINT
test_pkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  multiple
primary keys for table "test" are not allowed
    Command was: ALTER TABLE ONLY test
    ADD CONSTRAINT test_pkey PRIMARY KEY (spalte);
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for PROCEDURAL LANGUAGE plpgsql
pg_restore: setting owner and privileges for TABLE test
pg_restore: setting owner and privileges for CONSTRAINT test_pkey
WARNING: errors ignored on restore: 4

Prozess beendet mit Exitcode 1.
C:\Programme\PostgreSQL\8.1\bin\pg_restore.exe -i -h localhost -p 5432 -U
postgres -d ibv -v "C:\eclispace\ibv\ibv-site\tableN1.backup"
pg_restore: connecting to database for restore
pg_restore: [archiver (db)] Error while INITIALIZING:
pg_restore: [archiver (db)] could not execute query: ERROR:  invalid byte
sequence for encoding "UTF8": 0xe46973
    Command was: --
-- PostgreSQL database dump
--

-- Started on 2006-09-15 13:16:16 Westeuropäische Normalzeit

SET client_encoding = 'UTF8';
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 249; 2612 16386 PROCEDURAL
LANGUAGE plpgsql 
pg_restore: [archiver (db)] could not execute query: ERROR:  language
"plpgsql" already exists
    Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
pg_restore: creating TABLE test
pg_restore: [archiver (db)] Error from TOC entry 1183; 1259 16404 TABLE test
postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "test"
already exists
    Command was: CREATE TABLE test (
    spalte character varying NOT NULL
);
pg_restore: restoring data for table "test"
pg_restore: creating CONSTRAINT test_pkey
pg_restore: [archiver (db)] Error from TOC entry 1506; 2606 16410 CONSTRAINT
test_pkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  multiple
primary keys for table "test" are not allowed
    Command was: ALTER TABLE ONLY test
    ADD CONSTRAINT test_pkey PRIMARY KEY (spalte);
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for PROCEDURAL LANGUAGE plpgsql
pg_restore: setting owner and privileges for TABLE test
pg_restore: setting owner and privileges for CONSTRAINT test_pkey
WARNING: errors ignored on restore: 4

Prozess beendet mit Exitcode 1.
--- clipp end ---

When I delete the table before restoring I get this:
--- clipp ---
C:\Programme\PostgreSQL\8.1\bin\pg_restore.exe -i -h localhost -p 5432 -U
postgres -d ibv -v "C:\eclispace\ibv\ibv-site\tableN1.backup"
pg_restore: connecting to database for restore
pg_restore: [archiver (db)] Error while INITIALIZING:
pg_restore: [archiver (db)] could not execute query: ERROR:  invalid byte
sequence for encoding "UTF8": 0xe46973
    Command was: --
-- PostgreSQL database dump
--

-- Started on 2006-09-15 13:16:16 Westeuropäische Normalzeit

SET client_encoding = 'UTF8';
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 249; 2612 16386 PROCEDURAL
LANGUAGE plpgsql 
pg_restore: [archiver (db)] could not execute query: ERROR:  language
"plpgsql" already exists
    Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
pg_restore: creating TABLE test
pg_restore: restoring data for table "test"
pg_restore: creating CONSTRAINT test_pkey
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for PROCEDURAL LANGUAGE plpgsql
pg_restore: setting owner and privileges for TABLE test
pg_restore: setting owner and privileges for CONSTRAINT test_pkey
WARNING: errors ignored on restore: 2

Prozess beendet mit Exitcode 1.
--- clipp end ---

The table "test" is very simple: Only 1 field as primary key!

My active command level codepage: 850, in windows 1252.

The SQL-dump is:
--- clipp ---
--
-- PostgreSQL database dump
--

-- Started on 2006-09-15 14:56:51 Westeuropäische Normalzeit

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- TOC entry 1510 (class 0 OID 0)
-- Dependencies: 5
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


--
-- TOC entry 249 (class 2612 OID 16386)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: 
--

CREATE PROCEDURAL LANGUAGE plpgsql;


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1183 (class 1259 OID 16418)
-- Dependencies: 5
-- Name: test; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE test (
    spalte character varying NOT NULL
);


ALTER TABLE public.test OWNER TO postgres;

--
-- TOC entry 1507 (class 0 OID 16418)
-- Dependencies: 1183
-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY test (spalte) FROM stdin;
\.


--
-- TOC entry 1506 (class 2606 OID 16424)
-- Dependencies: 1183 1183
-- Name: test_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres;
Tablespace: 
--

ALTER TABLE ONLY test
    ADD CONSTRAINT test_pkey PRIMARY KEY (spalte);


--
-- TOC entry 1511 (class 0 OID 0)
-- Dependencies: 5
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2006-09-15 14:56:51 Westeuropäische Normalzeit

--
-- PostgreSQL database dump complete
--

--- clipp end ---

And this is from server log:
--- clipp ---
2006-09-15 14:38:14 LOG:  autovacuum: processing database "template1"
2006-09-15 14:39:14 LOG:  autovacuum: processing database "postgres"
2006-09-15 14:40:14 LOG:  autovacuum: processing database "ibv"
2006-09-15 14:41:14 LOG:  autovacuum: processing database "template1"
2006-09-15 14:41:18 ERROR:  invalid byte sequence for encoding "UTF8":
0xe46973
2006-09-15 14:41:18 ERROR:  language "plpgsql" already exists
2006-09-15 14:41:18 ERROR:  relation "test" already exists
2006-09-15 14:41:18 ERROR:  multiple primary keys for table "test" are not
allowed
2006-09-15 14:42:18 LOG:  autovacuum: processing database "postgres"
2006-09-15 14:43:18 LOG:  autovacuum: processing database "ibv"
2006-09-15 14:43:37 ERROR:  invalid byte sequence for encoding "UTF8":
0xe46973
2006-09-15 14:43:37 ERROR:  language "plpgsql" already exists
2006-09-15 14:43:37 ERROR:  relation "test" already exists
2006-09-15 14:43:37 ERROR:  multiple primary keys for table "test" are not
allowed
2006-09-15 14:44:37 LOG:  autovacuum: processing database "template1"
2006-09-15 14:45:26 ERROR:  invalid byte sequence for encoding "UTF8":
0xe46973
2006-09-15 14:45:26 ERROR:  language "plpgsql" already exists
2006-09-15 14:45:27 NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create
implicit index "test_pkey" for table "test"
2006-09-15 14:45:56 ERROR:  invalid byte sequence for encoding "UTF8":
0xe46973
2006-09-15 14:45:56 LOG:  autovacuum: processing database "postgres"
2006-09-15 14:45:56 ERROR:  language "plpgsql" already exists
2006-09-15 14:45:56 ERROR:  relation "test" already exists
2006-09-15 14:45:56 ERROR:  multiple primary keys for table "test" are not
allowed
2006-09-15 14:46:56 LOG:  autovacuum: processing database "ibv"
2006-09-15 14:47:47 ERROR:  invalid byte sequence for encoding "UTF8":
0xe46973
2006-09-15 14:47:47 ERROR:  language "plpgsql" already exists
2006-09-15 14:47:47 NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create
implicit index "test_pkey" for table "test"
2006-09-15 14:48:47 LOG:  autovacuum: processing database "template1"
2006-09-15 14:49:47 LOG:  autovacuum: processing database "postgres"
2006-09-15 14:50:47 LOG:  autovacuum: processing database "ibv"
2006-09-15 14:51:47 LOG:  autovacuum: processing database "template1"
2006-09-15 14:52:47 LOG:  autovacuum: processing database "postgres"
--- clipp end ---

Perhaps it might help you or me or both.

Best regards

Holger Schoenen
E-Mail: Holger(dot)Schoenen(at)sds-bs(dot)de



Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-09-15 13:41:01
Subject: Re: [ADMIN] Vacuum error on database postgres
Previous:From: Gregory StarkDate: 2006-09-15 12:45:07
Subject: Re: [ADMIN] Vacuum error on database postgres

pgsql-bugs by date

Next:From: Alan EscreetDate: 2006-09-15 20:47:38
Subject: BUG #2630: Can't unpack
Previous:From: Andy McCurdyDate: 2006-09-15 07:39:32
Subject: BUG #2629: libpq - Cannot deallocate prepared statement created with PQprepare()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group