Greetings
In advance I'd like to apoligize to start a new thread,
but I couldn't figure out how to reply to Mr. Lane's
thread-sensitive.
Mr. Lane kindly replied (in a highly appreciated patronizing way :)
I will point out, however, that the given script does not in fact
fail to load in any Postgres version known to me. Maybe you should
post your real problem instead of an oversimplified example.
Right, the error occurs in exactly such a simple
example, but I'm not above to provide details galore ...
####################
1: INITIAL SITUATION
####################
A database is to be transferred from its old host to a new DB-server.
Old host: UBUNTU 5.10 _Breezy Badger_ - Release i386 (20051012)
PgSQL v : PostgreSQL 8.0.6-1~bre
New host: Debian GNU/Linux 4.0 r0 _Etch_ - Official i386
PgSQL v : postgresql-d 8.1.9-0etch
####################
2: DUMPING THE DATA
####################
On the new machine the following command was used to dump
the databases (in a script that loops through all avail.
DBs). That means: the "new" pg_dump" was used to connect
to the "old" DB to dump the "old" DB-data directly onto
the "new" machine (maybe that causes havoc???):
# pg_dump -h old.database-host.tld -U dbusername -d -E LATIN1 -O -f testdb_dump.sql testdb
This yields the following SQL-file ...
SQL OUTPUT OF pg_dump (file: testdb_dump.sql) >>>>>
--
-- PostgreSQL database dump
--
SET client_encoding = 'LATIN1';
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--
COMMENT ON SCHEMA public IS 'Standard public schema';
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = true;
--
-- Name: event; Type: TABLE; Schema: public; Owner: testuser; Tablespace:
--
CREATE TABLE event (
event_id integer DEFAULT nextval('event_id_seq') NOT NULL,
haupt character varying(11),
vorschau character varying(100),
bild_text character varying(46),
liste_titel character varying(37),
liste_text character varying(45),
link_0_id text,
link_1_id character varying(100),
link_1_text character varying(16),
link_2_id character varying(100),
link_2_text character varying(16),
bild_0 character varying(100),
bild_1 character varying(100),
bild_2 character varying(100),
bild_3 character varying(100),
bild_4 character varying(100),
bild_5 character varying(100)
);
--
-- Name: event_id_seq; Type: SEQUENCE; Schema: public; Owner: testuser
--
CREATE SEQUENCE event_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
--
-- Name: event_id_seq; Type: SEQUENCE SET; Schema: public; Owner: testuser
--
SELECT pg_catalog.setval('event_id_seq', 74, true);
--
-- Data for Name: event; Type: TABLE DATA; Schema: public; Owner: testuser
--
INSERT INTO event VALUES (29, 'sect', '../img/somejpg.jpg', '', 'Some text', '09/2005', '', 'gallery_name', 'Title', 'some-more text', 'images', '', '', '', '', '', '');
--
-- Name: event_pkey; Type: CONSTRAINT; Schema: public; Owner: testuser; Tablespace:
--
ALTER TABLE ONLY event
ADD CONSTRAINT event_pkey PRIMARY KEY (event_id);
--
-- Name: event; Type: ACL; Schema: public; Owner: testuser
--
REVOKE ALL ON TABLE event FROM PUBLIC;
REVOKE ALL ON TABLE event FROM testuser;
GRANT ALL ON TABLE event TO testuser;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE event TO www;
--
-- Name: event_id_seq; Type: ACL; Schema: public; Owner: testuser
--
REVOKE ALL ON TABLE event_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE event_id_seq FROM testuser;
GRANT ALL ON TABLE event_id_seq TO testuser;
GRANT INSERT,SELECT,UPDATE ON TABLE event_id_seq TO www;
--
-- PostgreSQL database dump complete
--
<<<<< EOF testdb_dump.sql <<<<<
NOTE: The SQL-file has been edited as the inserts for table
event have been removed just as the GRANTs/REVOKEs on
scheme PUBLIC.
NOTE 2: Also the MySQL-tool "replace" has been run on
the file to remove the "::text"-typecasts on the
sequence's nextval-statement (to prevent double-casting
in PgSQL 8.1.series the like of "::text)::regclass")
##############
3: DATA IMPORT
##############
On the shell, the import was manually prepared
by issuing the following commands ...
3.1 Deleting old DB (should it exists)
# dropdb -h localhost -U dbusername testdb
DROP DATABASE
3.2 Creating a new DB
# createdb -h localhost -U dbusername -E LATIN1 testdb
CREATE DATABASE
3.3 Importing the data
# psql -h localhost -U dbusername -f testdb_dump.sql -d testdb
OUTPUT OF psql (Beware! German version :) >>>>>
SET
SET
SET
COMMENT
SET
SET
SET
psql:testdb_dump.sql:44: FEHLER: Relation »event_id_seq« existiert nicht
CREATE SEQUENCE
setval
--------
74
(1 Zeile)
psql:testdb_dump.sql:68: FEHLER: Relation »event« existiert nicht
psql:testdb_dump.sql:75: FEHLER: Relation »event« existiert nicht
REVOKE
REVOKE
GRANT
GRANT
psql:testdb_dump.sql:92: FEHLER: Relation »event« existiert nicht
psql:testdb_dump.sql:93: FEHLER: Relation »event« existiert nicht
psql:testdb_dump.sql:94: FEHLER: Relation »event« existiert nicht
psql:testdb_dump.sql:95: FEHLER: Relation »event« existiert nicht
REVOKE
REVOKE
GRANT
GRANT
<<<<< END OF OUTPUT OF psql <<<<<
As can be seen clearly, psql quite firmly thinks the dump
is not OK and refers to line 44 as the erroneous line
(which is ");", the closing bracket of the "CREATE TABLE"-statement")
######################
4: CHECKING THE IMPORT
######################
A psql to the new db and a check upon the contents
of the newly created DB reveals ...
# psql testdb
testdb=# \d
Liste der Relationen
Schema | Name | Typ | Eigentümer
--------+--------------+---------+-------------
public | event_id_seq | Sequenz | klep
(1 Zeile)
Which clearly states:
The table "events" HAS NOT been created.
--------------------
5: VERIFYING RESULTS
--------------------
To verify our findings so far, the above SQL-file
was edited thusly, so that the creation of the
sequence will be triggered before the table-creation ...
EDITED SQL-FILE "testdb_dump.sql" >>>>>
[...]
--
-- Name: event_id_seq; Type: SEQUENCE; Schema: public; Owner: testuser
--
CREATE SEQUENCE event_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
--
-- Name: event; Type: TABLE; Schema: public; Owner: testuser; Tablespace:
--
CREATE TABLE event (
event_id integer DEFAULT nextval('event_id_seq') NOT NULL,
.
[...]
<<<<< EOF "testdb_dump.sql"/edited version >>>>>
The following "psql"-command yields ...
# psql -h localhost -U dbusername -f testdb_dump.sql -d testdb
SET
SET
SET
COMMENT
SET
SET
SET
CREATE SEQUENCE
CREATE TABLE
setval
--------
74
(1 Zeile)
INSERT 625784 1
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
###############
VOILA! NO ERROR
###############
I hope that is enough "real problem"
regards, Klaus L.
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
