Thread: 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. +