Обсуждение: backup + restore fails
I have installed the new postgresql-8.1.msi on windows XP.=20
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" ("=E4")
-- Started on 2006-09-15 14:56:51 Westeurop=E4ische 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=E4ische Normalzeit
SET client_encoding =3D '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=20
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=E4ische Normalzeit
SET client_encoding =3D '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=20
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=E4ische Normalzeit
SET client_encoding =3D '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=20
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=E4ische Normalzeit
SET client_encoding =3D 'UTF8';
SET check_function_bodies =3D false;
SET client_min_messages =3D 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:=20
--
CREATE PROCEDURAL LANGUAGE plpgsql;
SET search_path =3D public, pg_catalog;
SET default_tablespace =3D '';
SET default_with_oids =3D false;
--
-- TOC entry 1183 (class 1259 OID 16418)
-- Dependencies: 5
-- Name: test; Type: TABLE; Schema: public; Owner: postgres; Tablespace:=20
--
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:=20
--
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=E4ische 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.Schoenen@sds-bs.de
Holger Schoenen writes:
> ERROR: invalid byte sequence for encoding "UTF8": 0xe46973
> Command was: --
[...]
> -- Started on 2006-09-15 14:56:51 Westeuropäische Normalzeit
The same problem was recently reported on the pgsql-de-allgemein list.
Would just avoiding %Z in Win32's strftime be an acceptable solution?
elog.c is already doing this, however because of the length of the
zone names, not the localization problem. The attached patch is
completely untested because I don't have access to a win32 box.
regards,
andreas
Index: src/bin/pg_dump/pg_backup_archiver.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.137
diff -c -r1.137 pg_backup_archiver.c
*** src/bin/pg_dump/pg_backup_archiver.c 14 Oct 2006 23:07:22 -0000 1.137
--- src/bin/pg_dump/pg_backup_archiver.c 20 Oct 2006 18:59:11 -0000
***************
*** 2780,2785 **** { char buf[256];
! if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&tim)) != 0) ahprintf(AH, "-- %s %s\n\n", msg,
buf);}
--- 2780,2793 ---- { char buf[256];
! if (strftime(buf, 256,
! /* Win32 timezone names are long and localized and
! * can interfere with utf-8 dumps */
! #ifndef WIN32
! "%Y-%m-%d %H:%M:%S %Z",
! #else
! "%Y-%m-%d %H:%M:%S",
! #endif
! localtime(&tim)) != 0) ahprintf(AH, "-- %s %s\n\n", msg, buf); }
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.84
diff -c -r1.84 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 7 Oct 2006 20:59:05 -0000 1.84
--- src/bin/pg_dump/pg_dumpall.c 20 Oct 2006 18:59:12 -0000
***************
*** 1320,1325 **** char buf[256]; time_t now = time(NULL);
! if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&now)) != 0) printf("-- %s %s\n\n", msg, buf);
}
--- 1320,1333 ---- char buf[256]; time_t now = time(NULL);
! if (strftime(buf, 256,
! /* Win32 timezone names are long and localized and
! * can interfere with utf-8 dumps */
! #ifndef WIN32
! "%Y-%m-%d %H:%M:%S %Z",
! #else
! "%Y-%m-%d %H:%M:%S",
! #endif
! localtime(&now)) != 0) printf("-- %s %s\n\n", msg, buf); }
Andreas Seltenreich <andreas+pg@gate450.dyndns.org> writes:
> Would just avoiding %Z in Win32's strftime be an acceptable solution?
> elog.c is already doing this, however because of the length of the
> zone names, not the localization problem. The attached patch is
> completely untested because I don't have access to a win32 box.
Done, thanks.
regards, tom lane
This will be fixed in 8.2 by suppressing %Z on Windows.
---------------------------------------------------------------------------
Andreas Seltenreich wrote:
> Holger Schoenen writes:
>
> > ERROR: invalid byte sequence for encoding "UTF8": 0xe46973
> > Command was: --
> [...]
> > -- Started on 2006-09-15 14:56:51 Westeurop?ische Normalzeit
>
> The same problem was recently reported on the pgsql-de-allgemein list.
>
> Would just avoiding %Z in Win32's strftime be an acceptable solution?
> elog.c is already doing this, however because of the length of the
> zone names, not the localization problem. The attached patch is
> completely untested because I don't have access to a win32 box.
>
> regards,
> andreas
>
> Index: src/bin/pg_dump/pg_backup_archiver.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v
> retrieving revision 1.137
> diff -c -r1.137 pg_backup_archiver.c
> *** src/bin/pg_dump/pg_backup_archiver.c 14 Oct 2006 23:07:22 -0000 1.137
> --- src/bin/pg_dump/pg_backup_archiver.c 20 Oct 2006 18:59:11 -0000
> ***************
> *** 2780,2785 ****
> {
> char buf[256];
>
> ! if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&tim)) != 0)
> ahprintf(AH, "-- %s %s\n\n", msg, buf);
> }
> --- 2780,2793 ----
> {
> char buf[256];
>
> ! if (strftime(buf, 256,
> ! /* Win32 timezone names are long and localized and
> ! * can interfere with utf-8 dumps */
> ! #ifndef WIN32
> ! "%Y-%m-%d %H:%M:%S %Z",
> ! #else
> ! "%Y-%m-%d %H:%M:%S",
> ! #endif
> ! localtime(&tim)) != 0)
> ahprintf(AH, "-- %s %s\n\n", msg, buf);
> }
> Index: src/bin/pg_dump/pg_dumpall.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
> retrieving revision 1.84
> diff -c -r1.84 pg_dumpall.c
> *** src/bin/pg_dump/pg_dumpall.c 7 Oct 2006 20:59:05 -0000 1.84
> --- src/bin/pg_dump/pg_dumpall.c 20 Oct 2006 18:59:12 -0000
> ***************
> *** 1320,1325 ****
> char buf[256];
> time_t now = time(NULL);
>
> ! if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&now)) != 0)
> printf("-- %s %s\n\n", msg, buf);
> }
> --- 1320,1333 ----
> char buf[256];
> time_t now = time(NULL);
>
> ! if (strftime(buf, 256,
> ! /* Win32 timezone names are long and localized and
> ! * can interfere with utf-8 dumps */
> ! #ifndef WIN32
> ! "%Y-%m-%d %H:%M:%S %Z",
> ! #else
> ! "%Y-%m-%d %H:%M:%S",
> ! #endif
> ! localtime(&now)) != 0)
> printf("-- %s %s\n\n", msg, buf);
> }
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
-- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +